Jet SQL - Difference on Vista and XP? 1

[Crookshanks]

Hello there,

I am working on an application that uses some JetSQl queries in runtime. Now I am developing on my laptop (XP vista Home) when the live environment uses windows XP through a Citrix environment.

The following queries does work on my laptop but does not work on the network.

hSQL = "SELECT SecurityCode, Bloomberg, Type, TypeSubcode FROM tbl_MarketData_Hisinv WHERE " & _
       "[Fundcode] = " & fundcode & " AND [Date] = #" & dateHisinv & "# AND " & _
       "([Type] = 'VMOB' OR [Type] = 'OPTI' or [Type] = 'FUTU')"

The part the system cannot handle on XP is the part with the date condition. If I remove that part it works in both environment. I am not saying that it is a environment thing but it sure looks like. Both systems run access 11.0.

Q: Are there other syntaxis for a date condition?
Q: Does anybody now about difference between XP and Vista?
Q: Does anybody see something illogical in the SQL-statement above?

Any help appreciated. Kind regards,
Yuri

 

[PHV]

Replace this:
& " AND [Date] = #" & dateHisinv & "# AND " &
with this:
& " AND [Date] = #" & Format(dateHisinv, "yyyy-mm-dd") & "# AND " &

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Crookshanks]

Thanks PHV, it helped. But why? Does JetSQL only recognize a specific dateformat?