MartinPrunty
Fri Mar 03 13:07:30 CST 2006
Thanks for your response. I've tried the new query, but I am still getting
an error message, which is:
Syntax error, (missing operator) in query expression
'Results.Requester_Email WHERE (((Results.Insurance_Company)='" &
> Insurance_Company & "' AND ((Results.Timestamp) => #" & Start_Date & "# And <=#" & End_Date & "#)) ORDER BY Results.Timestamp DESC,
> Results.Branch_Dept DESC'
I entered the code you recommended directly into the SQL view of the query
exactly as you had suggested. Any thoughts?
Thanks for your assistance.
"Martin Prunty" wrote:
> Thanks for your response. I've tried the new query, but I am still getting
> an error message, which is:
>
> Syntax error, (missing operator) in query expression
> 'Results.Requester_Email WHERE (((Results.Insurance_Company)='" &
> > Insurance_Company & "' AND ((Results.Timestamp) => #" & Start_Date & "# And <=#" & End_Date & "#)) ORDER BY Results.Timestamp DESC,
> > Results.Branch_Dept DESC'
>
> I entered the code you recommended directly into the SQL view of the query
> exactly as you had suggested. Any thoughts?
>
> Thanks for your assistance.
>
> "Stefan B Rusynko" wrote:
>
> > My post was using a string variable () to built a select (to be used in hand coded ASP)
> >
> > If you are using wizards don't wrap the whole thing in the string variable
> > strSQL = " ..... "
> > It is just
> >
> > SELECT Results.Insurance_Company, Results.Timestamp, Results.Branch_Dept,
> > Results.Claim_Number, Results.Requester, Results.Requester_Telephone, Results.Requester_Email,
> > Count("Insurance_Company","Insurance_Company_By_Date") AS [Total_Records]
> > FROM Results GROUP BY
> > Results.Insurance_Company, Results.Timestamp, Results.Branch_Dept,
> > Results.Claim_Number, Results.Requester, Results.Requester_Telephone,
> > Results.Requester_Email WHERE (((Results.Insurance_Company)='" &
> > Insurance_Company & "' AND ((Results.Timestamp) => #" & Start_Date & "# And <=#" & End_Date & "#)) ORDER BY Results.Timestamp DESC,
> > Results.Branch_Dept DESC
> >
> > --
> >
> > _____________________________________________
> > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
> > "Warning - Using the F1 Key will not break anything!" (-;
> > To find the best Newsgroup for FrontPage support see:
> >
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
> > _____________________________________________
> >
> >
> > "Martin Prunty" <MartinPrunty@discussions.microsoft.com> wrote in message news:9D03381E-D920-4CC0-A76D-65684E7A7D0C@microsoft.com...
> > | First an apology. Somehow, I didn't understand your response below. I've
> > | attempted to make your recommended code work, but have run into a problem
> > | with the query.
> > |
> > | When I enter the query you've suggested, Access seems to have a problem that
> > | the SQL code doesn't start with "Select", "Update", "Delete", etc. Here's
> > | the code I used:
> > |
> > | <%
> > | strSQL= "SELECT Results.Insurance_Company, Results.Timestamp,
> > | Results.Branch_Dept,
> > | Results.Claim_Number, Results.Requester, Results.Requester_Telephone,
> > | Results.Requester_Email,
> > | Count("Insurance_Company","Insurance_Company_By_Date") AS [Total_Records]
> > | FROM Results GROUP BY
> > | Results.Insurance_Company, Results.Timestamp, Results.Branch_Dept,
> > | Results.Claim_Number, Results.Requester,
> > | Results.Requester_Telephone,
> > | Results.Requester_Email WHERE (((Results.Insurance_Company)='" &
> > | Insurance_Company & "' AND
> > | ((Results.Timestamp) => #" & Start_Date & "# And <=#" & End_Date & "#))
> > | ORDER BY Results.Timestamp DESC, Results.Branch_Dept DESC"
> > |
> > | %>
> > |
> > | I understand that I will also have to enter the other code you suggested
> > | into the Form.
> > |
> > | Your help will be appreciated.
> > |
> > | "Stefan B Rusynko" wrote:
> > |
> > | > 1) bad practice and will cause you problems to use ANY field names or variable names w/ spaces in them
> > | > Rename them in your DB or your scripts to:
> > | > Start_Date, End_Date, Insurance_Company
> > | >
> > | > - am presuming from your code snippet that Start_Date, End_Date and Insurance_Company are coming from form fields
> > | >
> > | > 2) Yes you can not use PARAMETER
> > | > - you need to get the form field values from the form using valid ASP code as say
> > | > <%
> > | > Start_Date = Cdate(Request.Form("Start_Date"))
> > | > End_Date = Cdate(Request.Form("End_Date"))
> > | > Insurance_Company = Request.Form("Insurance_Company")
> > | > %>
> > | > Note that the above code will fail if your form passed any values that are not valid dates
> > | >
> > | > 3 ) You need to correctly date delimit (#for dates) any date variables, or other parameters (' for text), and separate the
> > string
> > | > from the ASP variable (using &) in your ASP code which should look like:
> > | >
> > | > <%
> > | > strSQL= "SELECT Results.Insurance_Company, Results.Timestamp, Results.Branch_Dept,
> > | > Results.Claim_Number, Results.Requester, Results.Requester_Telephone,
> > | > Results.Requester_Email, Count("Insurance_Company","Insurance_Company_By_Date") AS [Total_Records] FROM Results GROUP BY
> > | > Results.Insurance_Company, Results.Timestamp, Results.Branch_Dept, Results.Claim_Number, Results.Requester,
> > | > Results.Requester_Telephone,
> > | > Results.Requester_Email WHERE (((Results.Insurance_Company)='" & Insurance_Company & "' AND
> > | > ((Results.Timestamp) => #" & Start_Date & "# And <=#" & End_Date & "#)) ORDER BY Results.Timestamp DESC, Results.Branch_Dept
> > DESC"
> > | >
> > | > %>
> > | >
> > | > PS
> > | > can't guarantee your query will work as you were using Access syntax unsupported by ASP (Dcount change to Count, HAVING chges to
> > | > WHERE)
> > | >
> > | > --
> > | >
> > | > _____________________________________________
> > | > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
> > | > "Warning - Using the F1 Key will not break anything!" (-;
> > | > To find the best Newsgroup for FrontPage support see:
> > | >
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
> > | > _____________________________________________
> > | >
> > | >
> > | > "Martin Prunty" <MartinPrunty@discussions.microsoft.com> wrote in message
> > news:6C9587EB-5A8F-4CF1-8E0B-221AE46C627C@microsoft.com...
> > | > | Here is the code associated with the query. Thanks for your help.
> > | > |
> > | > |
> > | > | PARAMETERS [Start Date] DateTime, [End Date] DateTime, [Insurance Company]
> > | > | Text ( 255 );
> > | > | SELECT Results.Insurance_Company, Results.Timestamp, Results.Branch_Dept,
> > | > | Results.Claim_Number, Results.Requester, Results.Requester_Telephone,
> > | > | Results.Requester_Email,
> > | > | DCount("Insurance_Company","Insurance_Company_By_Date") AS [Total Records]
> > | > | FROM Results
> > | > | GROUP BY Results.Insurance_Company, Results.Timestamp, Results.Branch_Dept,
> > | > | Results.Claim_Number, Results.Requester, Results.Requester_Telephone,
> > | > | Results.Requester_Email
> > | > | HAVING (((Results.Insurance_Company)=[Insurance Company]) AND
> > | > | ((Results.Timestamp) Between [Start Date] And [End Date]))
> > | > | ORDER BY Results.Timestamp DESC , Results.Branch_Dept DESC;
> > | > |
> > | > |
> > | > | "Stefan B Rusynko" wrote:
> > | > |
> > | > | > Post a snippet of your code (the sql string or your query) and the table field name you are comparing the 2 dates against
> > | > | >
> > | > | > --
> > | > | >
> > | > | > _____________________________________________
> > | > | > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
> > | > | > "Warning - Using the F1 Key will not break anything!" (-;
> > | > | > To find the best Newsgroup for FrontPage support see:
> > | > | >
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
> > | > | > _____________________________________________
> > | > | >
> > | > | >
> > | > | > "Martin Prunty" <MartinPrunty@discussions.microsoft.com> wrote in message
> > | > news:C3361DF9-5E9B-49E4-BB83-2E66ED2B8CD7@microsoft.com...
> > | > | > | Many thanks.
> > | > | > |
> > | > | > | Could this be a result of using parameters in the query statement? I've
> > | > | > | tried to set the query up so the user can enter a date range (Start Date) and
> > | > | > | (End Date), which are not actual fields in the table. If this is the cause,
> > | > | > | do you have any suggestions on how to set this up so I won't encounter the
> > | > | > | original problem?
> > | > | > |
> > | > | > | Thanks again.
> > | > | > |
> > | > | > | "MD Websunlimited" wrote:
> > | > | > |
> > | > | > | > The message indicates that the name used for one of the database table columns is not present in the table being
> > referenced.
> > | > | > Check
> > | > | > | > your SQL statement and if it is correct; check the table to make sure it has the columns you believe it should.
> > | > | > | >
> > | > | > | > --
> > | > | > | > Mike -- FrontPage MVP '97 - '02
> > | > | > | >
http://www.websunlimited.com
> > | > | > | > FrontPage Add-in
> > | > | > | >
> > | > | > | >
> > | > | > | > "Martin Prunty" <MartinPrunty@discussions.microsoft.com> wrote in message
> > | > | > news:32B18CE5-B0B5-4FE8-A77E-1EA9EF94FBF8@microsoft.com...
> > | > | > | > >I have recently added a database query to Frontpage using the Database
> > | > | > | > > Interface Wizard. After publishing and testing, the query returns the
> > | > | > | > > following error message:
> > | > | > | > >
> > | > | > | > > ADODB.Command error '800a0cc1'
> > | > | > | > > Item cannot be found in the collection corresponding to the requested name
> > | > | > | > > or ordinal.
> > | > | > | > >
> > | > | > | > > It then references the following: _fpclass/fpdbrgn1.inc, line 408
> > | > | > | > >
> > | > | > | > > Can anyone give me an idea how to fix this problem?
> > | > | > | > >
> > | > | > | > > Thank you.
> > | > | > | >
> > | > | > | >
> > | > | > | >
> > | > | >
> > | > | >
> > | > | >
> > | >
> > | >
> > | >
> >
> >
> >