Date Full Day Query

[hmongie]

Hope someone can help.

I have a table with a bunch of fields. There are two critical fields. The Date and Time. I have these set to Date() and Time().

Now for a report. I would like to have a report that takes the input of a user to display the entries from only one time frame to another time frame.

For example, if a user is in need of the report for 01/01/07, they can choose the query and input in the date and time, but a full day is considered 01/01/07 starting at 6:00 am to 01/02/07 at 5:59 am. Therefore, if a user inputs that they would like to see the report for 01/01/07, entries that were inputted in prior to 01/01/07 at 5:58 AM should not appear.

My Query, criteria ask for the date and time, but I cannot figure out to ask the user for a logic that would be able to pull the whole "full day" reports.

Hope I'm making sense here.

 

[pbaldy]

Your mistake was in storing them separately, but it's easy to get around. Add a field to your query:

FullDateTime: CDate(DateField & " " & TimeField)

which will give you a field you can apply a date and time criteria to, like:

Between #07/26/2007 1:00:00 PM# And #07/27/2007 12:59:00 PM#

 

[hmongie]

Thanks for helping.

I can pull the dates and time but do you know how to make this accessible through a query that asks for an input.

For example my query will ask: [Enter in the date to search for:]
When the user inputs 01/01/07, all records from 01/01/07 6:00am through 01/02/07 5:59am are displayed.

Now, I only have two criticals fields, "Date" and "Time". Should I have something such as "BeginDate", "EndDate", "BeginTime", "EndTime"?

If I do take the second scenerio, do you know how to adjust the "EndTime" based on the "BeginTime" automatically?
Such as if BeginTime is < 6AM then EndDate is BeginDate-1. Something like that.

 

[pbaldy]

I don't think you need to add fields. To have them just input a date and get the described outcome, a criteria like this would work (I use form fields for user input, but it should work with the [Enter...] method too):

Between CDate([Forms]![frmReportDates]![txtDate] & " 6:00:00 AM") And CDate(DateAdd("d",1,[Forms]![frmReportDates]![txtDate]) & " 5:59:59 AM")

 

[hmongie]

Paul do you know how to do this.

Two fields,

1. BeginTime (set as =time() )
2. EndDate

I want the end date to automatically change based on the date.

ex. If BeginTime >=6:00 PM then set EndDate(Now()), if BeginTime < 6:00 PM then set EndDate(Now()-1)

Can you help with the field syntax? I think this will solve my problem.

MY SAMPLE THAT DOES NOT WORK(I placed this into the afterupdate but it doesn't work. My syntax is incorrect): =iif( [CTime] >=6PM,[Date()],[Date()-1])

 

[Brianwarnock]

Date() is not a field and does not require[], also the time format is wrong, plus er try

Me.fieldname = IIf(Time() >= #6:00:00 PM#, Date, Date - 1)

I would put it in the got focus of the field so that the date is calculted when you tab or click to the field or in the before update of the form.


Brian

 

[hmongie]

Hi Brian,

I've tried your code but it doesn't work. The date does not appear. Here's what I did.

Private Sub EndDate_AfterUpdate()
Me.EndDate = IIf([MyTime] >= #6:00:00 PM#, Date, Date - 1)
End Sub

MyTime is the time field that captures the current time or a time can be manually inputted into it.

EndDate is the field that I want to change based on the MyTime field.

Can you help with this?

 

[Brianwarnock]

Hi did you not notice the me. and no [] ?

Try
Me.EndDate = IIf(me.MyTime >= #6:00:00 PM#, Date, Date - 1)

I also always use Date() but i don't think the() are needed in code

Brian

 

[hmongie]

Thanks Brian.

Still doesn't work. No date appeared. Thanks for the help. I give up. I'll just add two more fields (EndDate and EndTime) and then query for the user to specify each items for a search. Thanks for all your time.

 

[Brianwarnock]

It works for me when I use the On Got Focus event of the field.
As soon as I click in the field or tab into it the date appears, I fiddled with the time so that I could test both sides of the IIf.

Brian

 

[hmongie]

Brian!!!!!!!!

It worked. Thanks much.


=IIf([MyTime]>=#6:00:00 AM#,[BeginDate],[BeginDate]-1)

I place it into the default value.

It doesn't change when the time() change but it does it auto. Thanks much for all your help.

The On Focus doesn't work for some reason. Thanks though.