Date Expression

[Loggical via AccessMonster.com]

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

 

[Marshall Barton]

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()

 

[Allen Browne]

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

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

 

[Loggical via AccessMonster.com]

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())

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

 

[Allen Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

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())

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

 

[Loggical via AccessMonster.com]

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.

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]

 

[Allen Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

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.

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

 

[Loggical via AccessMonster.com]

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.

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]

 

[Allen Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

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.

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

 

[Loggical via AccessMonster.com]

No there actually named OccDate and OccTime

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]