Query using date parameters

M

Martin Prunty

I am trying to create a query that counts records using a range of date
parameters. After uploading the query, I receive the following error message:

ADODB.Command error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name
or ordinal.

E:\KUNDEN\HOMEPAGES\20\D154470220\INFONET_DATABASE_INTERFACE\QUERY_BY_MONTH\../../_fpclass/fpdbrgn1.inc, line 136

Following is the query. Can you help me fix this? Thanks.

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT Results.Insurance_Company, Results.Timestamp, Results.Branch_Dept,
Results.Claim_Number, Results.Requester, Results.Requester_Telephone,
Results.Requester_Email, (Select Count(*) from Results AS Results_1 WHERE
Results_1.Insurance_Company=Results.Insurance_Company) AS [Count]
FROM Results
WHERE (((Results.Timestamp) Between [Enter Start Date:] And [Enter End
Date:]))
ORDER BY Results.Insurance_Company, Results.Timestamp;
 
S

Stefan B Rusynko

You have asked this before and been advised you can't use PARAMETERS or form fields w/ spaces in their names

--

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


|I am trying to create a query that counts records using a range of date
| parameters. After uploading the query, I receive the following error message:
|
| ADODB.Command error '800a0cc1'
| Item cannot be found in the collection corresponding to the requested name
| or ordinal.
|
| E:\KUNDEN\HOMEPAGES\20\D154470220\INFONET_DATABASE_INTERFACE\QUERY_BY_MONTH\../../_fpclass/fpdbrgn1.inc, line 136
|
| Following is the query. Can you help me fix this? Thanks.
|
| PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
| SELECT Results.Insurance_Company, Results.Timestamp, Results.Branch_Dept,
| Results.Claim_Number, Results.Requester, Results.Requester_Telephone,
| Results.Requester_Email, (Select Count(*) from Results AS Results_1 WHERE
| Results_1.Insurance_Company=Results.Insurance_Company) AS [Count]
| FROM Results
| WHERE (((Results.Timestamp) Between [Enter Start Date:] And [Enter End
| Date:]))
| ORDER BY Results.Insurance_Company, Results.Timestamp;
|
 
M

Martin Prunty

Thank you for your response. I have made the correction you described below,
but now receive this error message:

ADODB.Command error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name
or ordinal.

E:\KUNDEN\HOMEPAGES\20\D154470220\INFONET_DATABASE_INTERFACE\NEWER_QUERY3\../../_fpclass/fpdbrgn1.inc, line 136

Can you please assist?
 
M

Martin Prunty

I would really appreciate assistance on this problem. I'm not certain why my
query does not work once I publish it.

Here is the query I am using:
PARAMETERS Enter_Insurance_Co Text ( 255 );
SELECT R1.Insurance_Company, R1.Timestamp, R1.Branch_Dept, R1.Claim_Number,
R1.Report_Type, R1.Requester, R1.Requester_Telephone, (SELECT COUNT(*)
FROM Results AS R2
WHERE R1.Insurance_Company=(Enter_Insurance_Co)
AND R2.Insurance_Company = R1.Insurance_Company
AND YEAR(R2.TimeStamp) = YEAR(R1.TimeStamp)
AND MONTH(R2.TimeStamp) = MONTH(R1.TimeStamp)) AS MonthlyCount
FROM Results AS R1
ORDER BY R1.Insurance_Company;

Here is the error message:

ADODB.Command error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name
or ordinal.

E:\KUNDEN\HOMEPAGES\20\D154470220\INFONET_DATABASE_INTERFACE\QUERY_BY_MONTH3\../../_fpclass/fpdbrgn1.inc, line 136



Thanks

Martin Prunty said:
Thank you for your response. I have made the correction you described below,
but now receive this error message:

ADODB.Command error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name
or ordinal.

E:\KUNDEN\HOMEPAGES\20\D154470220\INFONET_DATABASE_INTERFACE\NEWER_QUERY3\../../_fpclass/fpdbrgn1.inc, line 136

Can you please assist?

Stefan B Rusynko said:
You have asked this before and been advised you can't use PARAMETERS or form fields w/ spaces in their names

--

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


|I am trying to create a query that counts records using a range of date
| parameters. After uploading the query, I receive the following error message:
|
| ADODB.Command error '800a0cc1'
| Item cannot be found in the collection corresponding to the requested name
| or ordinal.
|
| E:\KUNDEN\HOMEPAGES\20\D154470220\INFONET_DATABASE_INTERFACE\QUERY_BY_MONTH\../../_fpclass/fpdbrgn1.inc, line 136
|
| Following is the query. Can you help me fix this? Thanks.
|
| PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
| SELECT Results.Insurance_Company, Results.Timestamp, Results.Branch_Dept,
| Results.Claim_Number, Results.Requester, Results.Requester_Telephone,
| Results.Requester_Email, (Select Count(*) from Results AS Results_1 WHERE
| Results_1.Insurance_Company=Results.Insurance_Company) AS [Count]
| FROM Results
| WHERE (((Results.Timestamp) Between [Enter Start Date:] And [Enter End
| Date:]))
| ORDER BY Results.Insurance_Company, Results.Timestamp;
|
Click to expand...
Click to expand...
 
S

Stefan B Rusynko

Stick w/ 1 thread
- see response in your other thread

You can Not use Parameters declaration in any custom query
That can only be used in Access, not in the web
--

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


|I would really appreciate assistance on this problem. I'm not certain why my
| query does not work once I publish it.
|
| Here is the query I am using:
| PARAMETERS Enter_Insurance_Co Text ( 255 );
| SELECT R1.Insurance_Company, R1.Timestamp, R1.Branch_Dept, R1.Claim_Number,
| R1.Report_Type, R1.Requester, R1.Requester_Telephone, (SELECT COUNT(*)
| FROM Results AS R2
| WHERE R1.Insurance_Company=(Enter_Insurance_Co)
| AND R2.Insurance_Company = R1.Insurance_Company
| AND YEAR(R2.TimeStamp) = YEAR(R1.TimeStamp)
| AND MONTH(R2.TimeStamp) = MONTH(R1.TimeStamp)) AS MonthlyCount
| FROM Results AS R1
| ORDER BY R1.Insurance_Company;
|
| Here is the error message:
|
| ADODB.Command error '800a0cc1'
| Item cannot be found in the collection corresponding to the requested name
| or ordinal.
|
| E:\KUNDEN\HOMEPAGES\20\D154470220\INFONET_DATABASE_INTERFACE\QUERY_BY_MONTH3\../../_fpclass/fpdbrgn1.inc, line 136
|
|
|
| Thanks
|
| "Martin Prunty" wrote:
|
| > Thank you for your response. I have made the correction you described below,
| > but now receive this error message:
| >
| > ADODB.Command error '800a0cc1'
| > Item cannot be found in the collection corresponding to the requested name
| > or ordinal.
| >
| > E:\KUNDEN\HOMEPAGES\20\D154470220\INFONET_DATABASE_INTERFACE\NEWER_QUERY3\../../_fpclass/fpdbrgn1.inc, line 136
| >
| > Can you please assist?
| >
| > "Stefan B Rusynko" wrote:
| >
| > > You have asked this before and been advised you can't use PARAMETERS or form fields w/ spaces in their names
| > >
| > > --
| > >
| > > _____________________________________________
| > > 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
| > > _____________________________________________
| > >
| > >
| > > |I am trying to create a query that counts records using a range of date
| > > | parameters. After uploading the query, I receive the following error message:
| > > |
| > > | ADODB.Command error '800a0cc1'
| > > | Item cannot be found in the collection corresponding to the requested name
| > > | or ordinal.
| > > |
| > > | E:\KUNDEN\HOMEPAGES\20\D154470220\INFONET_DATABASE_INTERFACE\QUERY_BY_MONTH\../../_fpclass/fpdbrgn1.inc, line 136
| > > |
| > > | Following is the query. Can you help me fix this? Thanks.
| > > |
| > > | PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
| > > | SELECT Results.Insurance_Company, Results.Timestamp, Results.Branch_Dept,
| > > | Results.Claim_Number, Results.Requester, Results.Requester_Telephone,
| > > | Results.Requester_Email, (Select Count(*) from Results AS Results_1 WHERE
| > > | Results_1.Insurance_Company=Results.Insurance_Company) AS [Count]
| > > | FROM Results
| > > | WHERE (((Results.Timestamp) Between [Enter Start Date:] And [Enter End
| > > | Date:]))
| > > | ORDER BY Results.Insurance_Company, Results.Timestamp;
| > > |
| > >
| > >
| > >
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problems counting records in query 4
Date Parameters not working consistently 4
Getting dates of parameters to appear in the query/report 5

Top