Date Expression

I use this expression in the criteria in a Query. How can I modify it to
obtain records for a 24 hour time period? At present it produces records for
the previous day. I need it to produce records on the previous 24 hour time
period.

(Example) Tuesday 06:00 to Wednesday 06:00
Wednesday 06:00 to Thursday 06:00

DateAdd("d",-1,Date()) And DateAdd("d",1,Date())

Thanks in advance

--
Message posted via http://www.accessmonster.com

Loggical via AccessMonster.com wrote:

I use this expression in the criteria in a Query. How can I modify it to
obtain records for a 24 hour time period? At present it produces records for
the previous day. I need it to produce records on the previous 24 hour time
period.

(Example) Tuesday 06:00 to Wednesday 06:00
Wednesday 06:00 to Thursday 06:00

DateAdd("d",-1,Date()) And DateAdd("d",1,Date())


If you want to take the time of day into account, use Now()
instead of Date()

field Between DateAdd("d",-1,Now()) And Now()
--
Marsh
MVP [MS Access]

Use DateAdd() with "h" instead of "d".

Calculate the number of hours you want to subtract (18 rather than 24.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Loggical via AccessMonster.com" u28343@uwe wrote in message
news:9f5f213986bf9@uwe...
I use this expression in the criteria in a Query. How can I modify it to
obtain records for a 24 hour time period? At present it produces records
for
the previous day. I need it to produce records on the previous 24 hour
time
period.

(Example) Tuesday 06:00 to Wednesday 06:00
Wednesday 06:00 to Thursday 06:00

DateAdd("d",-1,Date()) And DateAdd("d",1,Date())

Thanks in advance

Thank you Allen,

This what I have and it doesn't show any results.
I have tried it in the Date field and the Time field. My Time field is
formatted as the following 00:00
(24 hr)

DateAdd("h",-18,Date()) And DateAdd("h",18,Date())

Allen Browne wrote:
Use DateAdd() with "h" instead of "d".

Calculate the number of hours you want to subtract (18 rather than 24.)

I use this expression in the criteria in a Query. How can I modify it to
obtain records for a 24 hour time period? At present it produces records
[quoted text clipped - 9 lines]

Thanks in advance

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200911/1

Did you include the greater than sign, e.g.:
= DateAdd("h",-18,Date()) And DateAdd("h",18,Date())

18 hours before today's date would be 6am yesterday.
18 hours after today's date would be 6pm today.
Is that the right range?
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Loggical via AccessMonster.com" u28343@uwe wrote in message
news:9f61a70a11e42@uwe...
Thank you Allen,

This what I have and it doesn't show any results.
I have tried it in the Date field and the Time field. My Time field is
formatted as the following 00:00
(24 hr)

DateAdd("h",-18,Date()) And DateAdd("h",18,Date())

Allen Browne wrote:
Use DateAdd() with "h" instead of "d".

Calculate the number of hours you want to subtract (18 rather than 24.)

I use this expression in the criteria in a Query. How can I modify it to
obtain records for a 24 hour time period? At present it produces records
[quoted text clipped - 9 lines]

Thanks in advance

Thank you Allen for your help.

I don't think this will give me the results I require. Trying to do this on
the Date field it requires the entry to be made in order of occurrence. This
is not necessarily the way the entries are made into the DB. I could have an
entry that should go into the DB at 05:00 AM not entered until 14:00PM.
Please correct me if I am wrong.
I have a time field which is the exact time of the occurrence. This field has
the times I'm looking for. Is there an expression I can use in this field?

Thanks again for your help. Much appreciated.

Allen Browne wrote:
Did you include the greater than sign, e.g.:
= DateAdd("h",-18,Date()) And DateAdd("h",18,Date())

18 hours before today's date would be 6am yesterday.
18 hours after today's date would be 6pm today.
Is that the right range?
Thank you Allen,

[quoted text clipped - 14 lines]

Thanks in advance

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200911/1

What's in the time field? Is it time only (i.e. it lacks the date)?

The best solution would be to put the date and time into the field.

Otherwise, it would be possible to use DateValue() on the date field (to get
just the date), and then a logical expression on the time field to figure
out if it's before 6am (and so treated as the previous date.)

For example, this should return -1 for times before 6am, and 0 for times
after that:
DateDiff("h", #0:00:00#, [YourTimeField]) 6

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Loggical via AccessMonster.com" u28343@uwe wrote in message
news:9f8566c616c93@uwe...
Thank you Allen for your help.

I don't think this will give me the results I require. Trying to do this
on
the Date field it requires the entry to be made in order of occurrence.
This
is not necessarily the way the entries are made into the DB. I could have
an
entry that should go into the DB at 05:00 AM not entered until 14:00PM.
Please correct me if I am wrong.
I have a time field which is the exact time of the occurrence. This field
has
the times I'm looking for. Is there an expression I can use in this field?

Thanks again for your help. Much appreciated.

Allen Browne wrote:
Did you include the greater than sign, e.g.:
= DateAdd("h",-18,Date()) And DateAdd("h",18,Date())

18 hours before today's date would be 6am yesterday.
18 hours after today's date would be 6pm today.
Is that the right range?
Thank you Allen,

[quoted text clipped - 14 lines]

Thanks in advance

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200911/1

Allen this is what I have done and it looks like it has given me the results
I require. If you see any problems with what I have done could you please let
me know?

In the Query I made another field and combined both the Date and Time Fields.

DateTime: ([Date]+[Time])

Then used the previous expression you gave in the DateTime critiera.
=DateAdd("h",-18,Date()) And DateAdd("h",6,Date())


Much appreciated for your time and help.




Allen Browne wrote:
What's in the time field? Is it time only (i.e. it lacks the date)?

The best solution would be to put the date and time into the field.

Otherwise, it would be possible to use DateValue() on the date field (to get
just the date), and then a logical expression on the time field to figure
out if it's before 6am (and so treated as the previous date.)

For example, this should return -1 for times before 6am, and 0 for times
after that:
DateDiff("h", #0:00:00#, [YourTimeField]) 6

Thank you Allen for your help.

[quoted text clipped - 23 lines]

Thanks in advance

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200911/1

That should be fine.

(Hopefully your fields are not actually named Date, Time and DateTime, as
these are reserved words.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Loggical via AccessMonster.com" u28343@uwe wrote in message
news:9f92a5c306c08@uwe...
Allen this is what I have done and it looks like it has given me the
results
I require. If you see any problems with what I have done could you please
let
me know?

In the Query I made another field and combined both the Date and Time
Fields.

DateTime: ([Date]+[Time])

Then used the previous expression you gave in the DateTime critiera.
=DateAdd("h",-18,Date()) And DateAdd("h",6,Date())


Much appreciated for your time and help.




Allen Browne wrote:
What's in the time field? Is it time only (i.e. it lacks the date)?

The best solution would be to put the date and time into the field.

Otherwise, it would be possible to use DateValue() on the date field (to
get
just the date), and then a logical expression on the time field to figure
out if it's before 6am (and so treated as the previous date.)

For example, this should return -1 for times before 6am, and 0 for times
after that:
DateDiff("h", #0:00:00#, [YourTimeField]) 6

Thank you Allen for your help.

[quoted text clipped - 23 lines]

Thanks in advance

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200911/1

No there actually named OccDate and OccTime

Allen Browne wrote:
That should be fine.

(Hopefully your fields are not actually named Date, Time and DateTime, as
these are reserved words.)

Allen this is what I have done and it looks like it has given me the
results
[quoted text clipped - 30 lines]

Thanks in advance

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200911/1