Date Comparison swapping month and day in VB

[Mary Fall]

I wonder if someone can help. I have two unbound date columns Date From and Date To. A macro on the form works perfectly!!!! the ‘where’ statement shows:
[PurchaseDate]>=[Forms]![GeneralDateEntry]![DateFrom] And [PurchaseDate]<=[Forms]![GeneralDateEntry]![DateTo]

However, due to the amount of where statements – i am trying to amalgamate them into a VB string. All strings work - but not the dates....

---
If Not IsNull(Me.DateFrom) And Me.PurchasedTick = -1 Then
strWhere = strWhere & " AND [PurchaseDate] >= " & [Forms]![GeneralDateEntry]![DateFrom]
End If
If Not IsNull(Me.DateTo) And Me.PurchasedTick = -1 Then
strWhere = strWhere & " AND [PurchaseDate] <= " & [Forms]![GeneralDateEntry]![DateTo]
End If

DoCmd.OpenReport stDocument, acPreview, , strWhere
---
I put in dates from 01/10/2010 to 05/10/2010



It outputs everything from 10th Jan – 10th May! Swapping the month and day around.

I output a contents of strWhere just before calling the report – and it shows PurchaseDate>=#01/10/2010# And PurchaseDate<=#10/01/2010#

The dates are text fields formatted showing yearmonthday. In query :

PurchaseDate: CDate(DateSerial(Left([vehicles]![PurchaseDate],4),Mid([vehicles]![PurchaseDate],5,2),Right([vehicles]![PurchaseDate],2)))

I’m Confused ...especially as it works on the macro....any help very welcome.

Thanks
Mary

[NTC]

not sure I follow as to the issue. Just the display of the date?

I pull out this statement: " The dates are text fields formatted showing yearmonthday." what is the syntax entered into the format field of the textbox property?

because really behind the scenes all dates are sequential integers handled by the OS - everything else is simply display. So display formats generally this are handled at the display point i.e. form's field property. (I myself generally avoid special date displays and just go with the formats embedded into Access....)

Maybe this helps.

[jzwp11]

If I remember correctly, SQL always interprets dates as mm/dd/yyyy. I have been looking at a couple of other forums and found that people use the format like this to force SQL to evaluate the dates differently:

Format(yourdate, "\#yyyy\-mm\-dd\#")

[Mary Fall]

thanks guys. The format in the unbound date fields is 'Short Date' showing 19/06/2011 when i select it. All normal. In the report I use based on the query - it doesn't have Short Date option in the pull-down...may have something to do with it.... Will play around with the Format option.

[Mary Fall]

Sorted it!

In VB rather than having :
strWhere = strWhere & " AND [RegDate] <= #" & Me.DateTo & "#"

put the whole lot in quotes...
strWhere = strWhere & " AND [RegDate] <= [Forms]![GeneralDateEntry]![DateTo]"

Worked a treat

[jzwp11]

I'm glad you got it worked out.