Irritating Dates!!!

[SmagO]

Hi all

In South africa we use the date format dd/mm/yy
My regional settings are set to this format
My problem is that in my VB app the user must be able to search for soemthing by its date.
I have five records with the date #12/09/00#
When I search 12/09/00 it finds no records
I use SQL to do the search. (MS ACCESS 97)
What access seems to do is swop the date in the actual SQL query to 09/12/00 -- ie. mm/dd/yy
This is despite the fact that the records get saved in the dd/mm/yy format
anyone know why access does this or how I can get it to not swop the format??

the query part is

"where ((prEmergency.date) =#" & txtDate.Text & "#)

Thanks in advance

[AKA]

The format() function will make it possible to format the date the way Access wants it to appear in the SQL statement.

I make apps for Europe and US and have to consider both date formats.

[mehlar]

I have had the same frustrating problem. This function sorted it out. I use the same dateformat as you: dd/mm/yy.


Public Function AccDate(Date As Date) As String 'converter
AccDate = "#" & Month(Date) & "/" & Day(Date) & "/" & Year(Date) & "#"
End Function


Public Function GetWhatever() As String
Dim strSQL As String

strSQL = "SELECT this FROM that " _
& "WHERE (this.Date = " & AccDate(objTicket.GetDate) & ")"

Set primaryRS = New ADODB.Recordset
primaryRS.LockType = adLockOptimistic
primaryRS.CursorType = adOpenStatic
primaryRS.Open strSQL, dbConn
GetKapasitet = primaryRS.Fields!øohfewoijh
End Function

Hope you can sort something out from this.
If not, just mail me.
Lars

[Edited by mehlar on 09-13-2000 at 07:53 AM]

[*Vince]

Hi,

I have a question for you, are you using an ODBC connection or do you use JET ?

[mehlar]

I`m using ODBC

lars

[*Vince]

Do a test :

do the same thing with a JET Connection. I've experienced some problems with ODBC connections relating to date formats. See if the date gets mixed in JET ( no ). ODBC is quite fussy on dates...

[SmagO]

I am using Jet and am having the problem. But also had the problem with ODBC.
Thanks for mehlar...I will try it out