Why wont count work??? (1 Viewer)

[k209310]

I have a table with dates in it. If i count the number of dates in the entire table then everything works fine.

If i put a date in the criteria such as #12/12/2003# (you do need the # to tell access to look for a date dont you?) then the query returns nothing. The data is in the table and the field is set to date/time and is in the format short date. am i doing everything correctly?

Please Help this is annoying me.

Chris

 

[Rich]

Have you tried #12/12/03#and are you entering this directly on the criteria line of the date field?

 

[k209310]

I was entering the date data as a test. I was collecting inforamtion abour orders taken one year ago from date() using Between DateAdd("yyyy",-1,Date()) And Date() This is what i need in the criteria.

If i just want to list the dates then it works fine when i add the count fucntion then i get no results. With out the criteria the count fuction works fine. But obviosly returns a result relating to all of the data in the table.

I was experimenting bu putting a data in the criteria Ie count dates that are 12/12/2000 and i still get no result even tho the dates are in the table.

Im havent go a clue why count isnt working. Any ideas?

 

[Rich]

Post the SQL of the query

 

[k209310]

I didnt write the sql i used the design sheet and entered the Between Date Add fucntion in ti the criteria field. Heres the Sql tho.

SELECT Count(tblEnquiryRegister.enqRecieved) AS CountOfenqRecieved
FROM tblEnquiryRegister
HAVING (((Count(tblEnquiryRegister.enqRecieved)) Between DateAdd("yyyy",-1,Date()) And Date()));

 

[Joyster]

Try DCOUNT().

Example:
=DCount(" [ORDNUM]","Customerdb"," [PROJECTNAME] = Forms![Project information query]![PROJECTNAME] And [Customerdb]![ORDERSTATUS] <> 'cancelled' ")

 

[k209310]

Ok i have tried this where enqRecieved is the field i wish to search and tblEnquiryRegister is the table the date is within ##

=DCount("[enqRecieved]","tblEnquiryRegister","[enqRecieved] = #12/12/02#")

the date is definatly in the table but the query doesnt return any results.

Someone must have tried to COUNT a specific date in a table before. Any advice on this would be gratefull recieved as this problem is very annoying.

 

[Rich]

SELECT Count(Jobs.JobID) AS CountOfJobID
FROM Jobs
WHERE (((Jobs.jbDte) Between #1/1/2001# And #1/1/2002#));

 

[k209310]

Rich thank you so much thats a problem that has been bugging me for days. I didnt think counting a certain date would be so complicated. I hadnt realised i needed the two collums with the CountOfJobID: Count(tblEnquiryRegister.enqRecieved). I have looked in books and in help and there is nothing that even point syou in this direction of that.

anyway thanks so much for all your help and i hope i can repay you sometime.

Chris