Date or Time range

[LtFass]

I am building a program for a fire dept to keep trak of thier daily log. I
have a form where the daily data is entered. below the form is subform bound
to a query that shows all of the activity for the day.
Problem I want them only to see what pretains to the current shift. The
shift begins at 08:00 and ends 24 hrs later at 07:59 the Date() function
works fine as a criteria however it changes at midnite and then the query
only shows the activity after the new date is entered.

 

[Ken Snell [MVP]]

You need to tell us information about how you're storing date and time
values (assuming that you are) in the table. Are you storing the date/time
as a single field (good idea if you are)? If yes, try a criterion expression
similar to this:

Between Date() + 8/24 And Date() + 8/24 + 1

 

[LtFass]

Umm The Date is stored in a field called date and is formatted as 99/99/9999
the time is stored in two fields start time and end time and is formatted as
99:99 . I placed the string below in the criteria and MS saw fit to include
the # before the 8/24 and included the year as if it was a date instead of an
equation. Thank you for help as this was a problem with our old system

 

[Ken Snell [MVP]]

Why store the date and time separately? They can be in the same field.

However, to search for both values, I recommend that your query include a
calculated field that combines the date field and the start time (I assume
that that is the time field that you want?):

CombDateTime: [DateField] + [StartTimeField]

Then use this criterion expression for this calculated field:

Between Date() + #8:00 AM# And Date() + #7:59 AM# + 1

 

[LtFass]

Ken, Thank you so much I belive that it will work (at least in the small
amount of test I have done so far) I will probably have more questions as
time goes but thanks it was a great help!!!!!

Why store the date and time separately? They can be in the same field.

However, to search for both values, I recommend that your query include a
calculated field that combines the date field and the start time (I assume
that that is the time field that you want?):

CombDateTime: [DateField] + [StartTimeField]

Then use this criterion expression for this calculated field:

Between Date() + #8:00 AM# And Date() + #7:59 AM# + 1

--

Ken Snell
<MS ACCESS MVP>

Umm The Date is stored in a field called date and is formatted as 99/99/9999
the time is stored in two fields start time and end time and is formatted as
99:99 . I placed the string below in the criteria and MS saw fit to include
the # before the 8/24 and included the year as if it was a date instead of an
equation. Thank you for help as this was a problem with our old system

 

[LtFass]

I am use the dates and seperate times to track other activities for specfic
engines. Thanks again for your help

Ken, Thank you so much I belive that it will work (at least in the small
amount of test I have done so far) I will probably have more questions as
time goes but thanks it was a great help!!!!!

Why store the date and time separately? They can be in the same field.

However, to search for both values, I recommend that your query include a
calculated field that combines the date field and the start time (I assume
that that is the time field that you want?):

CombDateTime: [DateField] + [StartTimeField]

Then use this criterion expression for this calculated field:

Between Date() + #8:00 AM# And Date() + #7:59 AM# + 1

--

Ken Snell
<MS ACCESS MVP>

Umm The Date is stored in a field called date and is formatted as 99/99/9999
the time is stored in two fields start time and end time and is formatted as
99:99 . I placed the string below in the criteria and MS saw fit to include
the # before the 8/24 and included the year as if it was a date instead of an
equation. Thank you for help as this was a problem with our old system

:

You need to tell us information about how you're storing date and time
values (assuming that you are) in the table. Are you storing the date/time
as a single field (good idea if you are)? If yes, try a criterion expression
similar to this:

Between Date() + 8/24 And Date() + 8/24 + 1

--

Ken Snell
<MS ACCESS MVP>

I am building a program for a fire dept to keep trak of thier daily log. I
have a form where the daily data is entered. below the form is subform
bound
to a query that shows all of the activity for the day.
Problem I want them only to see what pretains to the current shift. The
shift begins at 08:00 and ends 24 hrs later at 07:59 the Date() function
works fine as a criteria however it changes at midnite and then the query
only shows the activity after the new date is entered.