Access Glitch???? (1 Viewer)

[crich21]

I have tblOrderDetails.SelectionDate field that has a default value of 'Now()'

So my fields include a time stamp along with the date

I have a query with the following:

((tblOrderDetails.SelectionDate) Between [Forms]![frmDateSelection]![Calendar0] And [Forms]![frmDateSelection]![Calendar1]))

The calendar1 value is never included in the query results???
Is this a glitch? If I select 11/30/10 as then calendar1 date
when the statement is evaluated it doesn't include anything that has a time stamp on that date. Does Access assume it has to be before 12:00 am on 11/30/10??

How can I get around this?

 

[boblarson]

When you store a date with a time then when you use a calendar control to select the date - the current time is kind of hidden in there too. So, what you want to do is to have a hidden text box for each calendar control and in the AFTER UPDATE event of the calendar (You will need to go to the VBA window first and then select the calendar control from the top left drop down and then you can select the event. The events in the properties dialog aren't all listed. See below for a screenshot) then you would set the text box equal to the date plus the midnight time. The ending one you would set to the date and 11:59:59 PM.

So, the code might look like this:

Private Sub Calendar0_AfterUpdate()
    Me.MyTextBoxControlNameHere = Calendar0.Value & " " & #12:00:00 AM#
End Sub

 

[boblarson]

Forgot to include - then you can refer to the TEXT BOXES in the query.

 

[Brianwarnock]

Isn't it easier to strip the time out of the field in the query with Format or add 11:59:59 to the calendar1

Brian

 

[crich21]

Both of your ideas seem like they'll work. I think it would be easier to strip the time from the field in my query. Do I just change the field to
NewSelectionDate: Format([SelectionDate], 'm/d/yyyy')

 

[boblarson]

To keep it from changing to text use:

NewSelectionDateateValue([SelectionDate])