Dcount on Query design

[thanosgr]

Hi Im trying to count records, in time shift for a specific field, i use this code into the query desing and give me error

QUERY DESIGN

Field



TESTCOUNT("*", "[Employers]", "[timeshift] BETWEEN #7:00# AND #15:00#")

What is the wrong with it?

Thanks

[pbaldy]

timeshift is a date/time field? Not sure, as this worked for me:

?dcount("*", "tblReservations", "TimeValue(ReqDateTime) Between #8:00# And #11:00#")
12

In my case the field contains a date and time value.

[thanosgr]

Hi Pbaldy,, yes it is.. actually i want to count time shift based on 24 7-15, 15-23, and 23.00-7.00am...

ill try it and let you know..

[thanosgr]

Can you sent me the accdb file?, seems, i cant make it work
Thanks

[pbaldy]

It would make more sense for you to post your db with the failing effort. I tested in a production db that I wouldn't be able to post anyway.

[thanosgr]

timeshift-count.accdb

Here is the file

[pbaldy]

Sorry, the question mark was because I was testing in the Immediate window. You also still had my field name. This works:

SELECT tblReservations.Surname, tblReservations.Timeshift, DCount("*","tblReservations","TimeValue(timeshift) Between #7:00# And #15:00#") AS Eκφρ1
FROM tblReservations;

[thanosgr]

thanks it works, but its shows all the record, how can it shows only the records that match the timeshift between 7 and 3

Thanks

[pbaldy]

You would need a WHERE clause in you query (criteria in design view). It would basically be the same as the criteria in the DCount.