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
_____________________________________________
| 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
| > _____________________________________________
| >
| >
| > | 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
| > | > _____________________________________________
| > | >
| > | >
| > | > | > | 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
| > | > | >
| > | > | >
| > | > | > | > | > >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.
| > | > | >
| > | > | >
| > | > | >
| > | >
| > | >
| > | >
| >
| >
| >
