Working with Date & Time

[Always_Learning]

Hi There,

I have a datetime field called EntryDate
I need to write a query where EntryDate = today and the time part of EntryDate is > 09:00 and < 18:00

I have tried the criteria in the screenshot attached but the date part will not work unless I put in the exact date and time of the records value.
If I try to enter #19/10/2013# no records come back but if I enter the exact value of #19/10/2013 09:55:47# it returns the record.

Obviously this was tried on the 19/10/2013

Thanks for your help.

Best Regards,

 

[spikepl]

Surely you do not think you are the first one with such problems? Improve your googling skills. I'm not saying that to be bitchy, but to help you learn to use your time effectively. 90+ % of issues brought up on AWF have been dealt with before.

 

[Galaxiom]

In the criteria of the field for the query
Between DateAdd("h",9,Date()) And DateAdd("h",18,Date())

 

[Galaxiom]

90+ % of issues brought up on AWF have been dealt with before.

And unfortunately, too often a novice will come up with a less than ideal answer like:

Where DateValue([EntryDate] = Date() And TimeValue([EntryDate]) Between #9:00# And #18:00#

 

[Always_Learning]

Hi Galaxiom,

Thanks for the reply.
That works perfectly but I was wondering, how would I check if the datetime is between 09:15 and 17:45

I appreciate your time.

Best Regards,

 

[Always_Learning]

In the criteria of the field for the query
Between DateAdd("h",9,Date()) And DateAdd("h",18,Date())

Hi Galaxiom,

Thanks for the reply.
That works perfectly but I was wondering, how would I check if the datetime is between 09:15 and 17:45

I appreciate your time.

Best Regards,

 

[Brianwarnock]

Between Date() + #09:15# And Date()+ #17:15#

 

[Always_Learning]

Hi Brian,

That works perfect.

Can I ask, What if the date is in one field and the time in another.

Both the fields are DateTime but I need to enter in the criteria for EntryDate todays date and in the EntryTime "Between 09:15 & 17:15"

Thanks for the continued help.

Best Regards,

 

[Galaxiom]

Where [datefield] = Date() And [timefield] Between #9:00# And #18:00#

Have you actually tried experimenting with anything for yourself?

 

[Always_Learning]

Where [datefield] = Date() And [timefield] Between #9:00# And #18:00#

Have you actually tried experimenting with anything for yourself?

Yes I have spent quite a bit of time.
I develop with MS SQL Server and the cross over to Access is causing a few issues.
This Access Application is using a MS Sql database backend and the queries being created in Access and calling to MS Sql I find a little tricky.

Thanks for your help.

 

[namliam]

Use ODBC queries, so you can use MS SQL syntax? Should perform better too.

 

[Galaxiom]

Use ODBC queries, so you can use MS SQL syntax? Should perform better too.

Assuming you are referring to PassThrough queries.

They don't really affect performance because JET/ACE recognises when a query can be passed to the MS SQL engine for processing even when it is an Access query.

BTW There is an Access Option in the Object Designers section to make Access use MS SQL syntax instead.