Hard code TIME to selected Date on form (to make it date&time) for my query criteria

[MsLady]

Hard code TIME to selected Date on form (to make it date&time) for my query criteria

Hello buddies , do you have any idea how to make this work?

To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.

This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only.

([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null)

How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated

 

[Pat Hartman]

Do you really want to include time in the expression or are you just trying to get around the fact that the date field includes time?

(DateValue(tblJobDetails.timeIn) >= [Forms]![frmPendingJobs]![cboDate] OR IsNull([Forms]![frmPendingJobs]![cboDate])) AND (DateValue(tblJobDetails.timeIn) <= [Forms]![frmPendingJobs]![cboDate2] OR IsNull([Forms]![frmPendingJobs]![cboDate2]))

 

[MsLady]

Do you really want to include time in the expression or are you just trying to get around the fact that the date field includes time?

(DateValue(tblJobDetails.timeIn) >= [Forms]![frmPendingJobs]![cboDate] OR IsNull([Forms]![frmPendingJobs]![cboDate])) AND (DateValue(tblJobDetails.timeIn) <= [Forms]![frmPendingJobs]![cboDate2] OR IsNull([Forms]![frmPendingJobs]![cboDate2]))

Thanks Pat.
I really want to include time (8:00AM) in the cboDate/cboDate2. So that it compares the time and selects only the data that falls within 8:00am - 8:00am (the next day). As a single day! instead of the natural 12:00am - 12:00am. feel me?

How do i plug in 8:00AM to the dates i select from cboDate/cboDate2? since tblJobDetails.timeIn has a time value already.

Thanks

 

[MsLady]

Please help

 

[Banana]

I don't have Access handy with me, but if you're saying that a date by itself defaults to 12:00 AM, you could at least add 8 hours to that date, thus making it MM/DD/YYYY 8:00 AM.

Just an idea off the top of my head.

 

[MsLady]

I don't have Access handy with me, but if you're saying that a date by itself defaults to 12:00 AM, you could at least add 8 hours to that date, thus making it MM/DD/YYYY 8:00 AM.

Just an idea off the top of my head.

Thanks Banana (nice name )

How do i add 8 hours to with code, just do "MM/DD/YYYY 8:00 AM" in my code?

 

[Banana]

Beat me. I don't have Access with me right now; see if DateAdd function supports adding Time to the Date. If not, take a look in Expression Builder. That's how I usually find the needed function to do my tasks. HTH.

 

[raskew]

Hi -

From the debug (immediate) window:

x = date()
y = cdbl(x) + 0.333333
? y
 38947.333333 
? cdate(y)
8/18/06 8:00:00 AM

Added:
Take a look at these two articles to see how Access stores dates internally:

http://support.microsoft.com/support.../Q130/5/14.ASP
http://support.microsoft.com/support.../Q136/1/27.ASP
HTH - Bob

 

[Pat Hartman]

(tblJobDetails.timeIn >= CDate([Forms]![frmPendingJobs]![cboDate] & " 8:00 AM") OR IsNull([Forms]![frmPendingJobs]![cboDate])) AND (tblJobDetails.timeIn <= CDate([Forms]![frmPendingJobs]![cboDate2] & " 8:00 AM") OR IsNull([Forms]![frmPendingJobs]![cboDate2]))

 

[MsLady]

(tblJobDetails.timeIn >= CDate([Forms]![frmPendingJobs]![cboDate] & " 8:00 AM") OR IsNull([Forms]![frmPendingJobs]![cboDate])) AND (tblJobDetails.timeIn <= CDate([Forms]![frmPendingJobs]![cboDate2] & " 8:00 AM") OR IsNull([Forms]![frmPendingJobs]![cboDate2]))

hehe...just what the doctor ordered Thanks Pat!

Banana, Raskew: Thank you both. I really apprecite your time.
That's what i was looking for. love you guys!!!

Raskew: would still like to read ur links, can you fix it please? doesn't work

 

[raskew]

Hi -

Raskew: would still like to read ur links, can you fix it please? doesn't work

Try this: http://support.microsoft.com/kb/130514/en-us

HTH - Bob