Date or Time range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 
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, 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!!!!!

Ken Snell said:
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>

LtFass said:
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
Click to expand...
Click to expand...
 
I am use the dates and seperate times to track other activities for specfic
engines. Thanks again for your help

LtFass said:
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!!!!!

Ken Snell said:
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>

LtFass said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top