Date field Question

[ptrifile]

I have a basic form with two date fields that use the calendar to input the date. After the dates are put in another field shows the results of a query. The problem I am running into is that the dates being queried are MMDDYY HH:MM:SS PM/AM. So if I try and enter a start date of 3/1/2012 and an end date of 3/1/2012 i do not get any results.

I believe i understand why this is happening but my question is this...is there a way to automatically have 8:30 am added to the end of the start date and 7:20 pm to the end date that is chosen from the calendars?

I hope this makes sense...thanks for any help.

Paul

 

[lameid]

I would expect something like the following should work.

[SomeField] Between ([Parameter1] + #8:30 am#) and ([Parameter2] + #7:20 pm#)

 

[lameid]

OOPs.... I was thinking query... just add the time to the control on its After Update event...

Something like...

If Int(Me!txt1) = Me!txt1 Then 'make sure no time is included yet
     Me!txt1 = Me!txt1 + #8:30 am#
End if

Argueably this should use the month, day, and year functions with dateserial instead of just INT. But Access stores the date in the integer portion and time in the decimal, so it should work until MS decides to change the way dates are stored (it has been this way since I can remember in Access 2.0 and probably before then).

 

[SkipVought]

The problem I am running into is that the dates being queried are MMDDYY HH:MM:SS PM/AM.

Are you absolutely sure that your Field Data Type is DATE?

MMDDYY is not a standard FORMAT for a Date. Seems to me that you might have TEXT field.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

Skip,

There is no actual Date datatype in Access only 'Date/Time'.

 

[SkipVought]

Date, Date/Time -- SEMANTICS!

It is a numeric not text.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

Yes... the issue could exist if it is either text (or other character based datatype) OR Date/Time.


But according to the OP, if he does effectively

Between Date() and Date() he does not receive results...

I would expect him to get results with

Between Date() and Now()

 

[SkipVought]

If the OP's field is TEXT, structured as MMDDYY HH:MM:SS PM/AM, then ANY BETWEEN query like Between Date() and Now() would fail to return.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}