query between yesterday and today

[netuse@debouck.]

I have a report based on a query that needs to look for records between 2000hrs on one day and 0600hrs the next but i cant seem to figure it out any help would be appreciated.

 

[Brianwarnock]

Between (DateAdd("h",-4,Date())) And (DateAdd("h",6,Date()))

Brian

 

[SQL_Hell]

Brian,

Do you not need to use now() rather than date(), I thought date() only returned the date without the time.

 

[Brianwarnock]

No , you must use Date() not Now() as you don't want the current time returned but if say today you would want 17/08/2004 00:00:00 which effectively is what Date() returns

Brian

 

[Brianwarnock]

SQL_HELL 's post made me have a rethink, if netuse@debouck actually meant upto 5:59:59 then my original post is in error as the times are inclusive, the code below would be correct.

Between Date()-#04:00:00# And Date()+#05:59:59#

Brian

 

[SQL_Hell]

Ok thanks for setting me straight Brian.

 

[Brianwarnock]

No problem, hope Netetc is happy, Dates can be a minefield

Brian

 

[netuse@debouck.]

hey brian thanks for the help i tried it but its returning records for today only let me give you a better picture

I have a daily log sheet fields are
date
disp
arriv
clrd
typeofcall
location

my shift hours overlap so i need to query my log to show records from 2000hrs today til 0600hrs tomorrow i should have given more info from the start sorry.

Net.

 

[WayneRyan]

net,

Just use the DateAdd as above, but use between 20 hours and 30 hours.
But, Date is reserved for use by Access.

Wayne

 

[netuse@debouck.]

thanks for the reply wayne which example above and are you saying i shouldn't use date as a field in my table. here is my table and query

 

[Brianwarnock]

Hi Net
your original post said yesterday and today, now you are saying today and tomorrow , which is it?

Date is a reserved word in Access using it as a control /field/ own function name etc can only lead to problems.

Brian

 

[Brianwarnock]

Found time to look at your database, you didn't mention that your date and time fields were separate!

I have amended your query , but it works on the current data for test purposes, I'm sure you will understand the logic and be able to amend it to your own requirements.

Why do you duplicate incident number and call date in your table, albeit in different formats?

Brian

 

[netuse@debouck.]

hey brian as you see my original posts was after midnight so it was yesterday and today and my last was befor midnight so today and tomorrow anyway

its like this i start my shift at 2000hrs and end the next morning at 0600hrs so that is my query i need to query from 2000hrs on one day until 0600hrs the next

 

[netuse@debouck.]

the logic behind that was i needed to have an incident # with format ddmmyy+1 that auto incremented for that day and reset on a new day ie 1808041,1808042

input via form

i have that post on here as well

net

 

[Brianwarnock]

The use of the Date()+/- notation was to enable the query to be rerun each day without amendment, if however you are going to vary the date and times dependent upon when you run, not unusual, then perhaps you should run the query from a form inputing the required date and time parameters as actual dates and times.

Brian

 

[netuse@debouck.]

its going to be run each morning so it should be fine i changed to date()-1 and date() but its not returning records for today

 

[Brianwarnock]

Have you looked at the query I posted back?

 

[netuse@debouck.]

yes i did and i modified as follows date()-2 changed to -1 for yesterday and date()-1 to date() to reflect today is that correct

 

[Brianwarnock]

Yes, if you have added data to the table for the date and time period selected in the criteria it should be selecting it.

Brian

 

[netuse@debouck.]

hey brian thanks alot i cant believe it was so easy i played with the +- notation but never thought of using multiple criteria stupid me