#####Using Control as a Query parameter#####

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi folks,

I'm have a query set up which retrieves data from a batchArchive table
and restores it to the 'main' Batch table. The dates are selected using the
DateTime Picker control which works as intended. What I wanted to do was
count the number of records that are going to be restored to the Batch table,
informing the user before the transfer takes place but the query always
returns nothing :

SELECT Count(BatchArchive.[Date Input]) AS [CountOfDate Input]
FROM BatchArchive
HAVING (((Count(BatchArchive.[Date
Input]))>=CDate([Forms]![ArchiveParams]![ActiveXCtl5]) And
(Count(BatchArchive.[Date
Input]))<=CDate([Forms]![ArchiveParams]![ActiveXCtl7])));

It's the same query I use for the import, with only COUNT added, anyone help?

Thanks,
Jim.
 
Hi folks,

I'm have a query set up which retrieves data from a batchArchive table
and restores it to the 'main' Batch table. The dates are selected using the
DateTime Picker control which works as intended. What I wanted to do was
count the number of records that are going to be restored to the Batch table,
informing the user before the transfer takes place but the query always
returns nothing :

SELECT Count(BatchArchive.[Date Input]) AS [CountOfDate Input]
FROM BatchArchive
HAVING (((Count(BatchArchive.[Date
Input]))>=CDate([Forms]![ArchiveParams]![ActiveXCtl5]) And
(Count(BatchArchive.[Date
Input]))<=CDate([Forms]![ArchiveParams]![ActiveXCtl7])));

It's the same query I use for the import, with only COUNT added, anyone help?
Click to expand...

You're putting the date range criterion ON THE COUNT, not on the date
itself. Try changing this to

SELECT Count(BatchArchive.[Date Input]) AS [CountOfDate Input]
FROM BatchArchive
WHERE [Date Input])>=CDate([Forms]![ArchiveParams]![ActiveXCtl5]) And
[Date Input]<=CDate([Forms]![ArchiveParams]![ActiveXCtl7]);

Equivalently, add the date field you're searching to the query grid
twice; set the Total row to Count for one of them, and to Where for
the other. Put the criteria under the latter.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top