Access VBA - date format conversion

[benattal]

Hi everyone,
here is the situation: I'm using VBA in access, and as you know, when we use an SQL command within VBA for filtering dates in a given table, we can only introduce US format date ("mm/dd/yyyy", in fact only US date format can be input through an SQL command in VBA :-( ). Unfortunately, all dates in my table ([MyTable].[Dates]) are formated according to the local configuration ("dd/mm/yyyy"). Hence, I'm looking for a command that converts [MyTable].[Dates] into the US format. Any clew?
Note: some could suggest to write the SQL directly in a query which uses [MyTable] thus avoiding the the use of VBA... the fact is that [MyTable] is a temporary one and will be deleted later on in the procedure. Furthermore I'm trying to have a memory efficient procedure.

thanks for your help,
ben

 

[stopher]

Hi and welcome to the forum.

This usually does the trick:

Format([Dates],"mm/dd/yy")

Chris

 

[raskew]

Hi -

The problem with Format() is that it returns a string.

It would appear that the DateValue() function returns US Short-Date format (you'll have to test it out using your configuration).

Here's an example from the debug (immediate) window:

x = format(date(), "dd/mm/yyyy")
? x
21/12/2008
y = datevalue(x)
? y
12/21/2008 

to show that y is actually a date, not a string:
? cdbl(y)
 39803

HTH - Bob

 

[rapsr59]

Cdate() converts a string to a date

    Dim dte As Date
    Dim str As String
 
    str = "31-12-2008"
    MsgBox str 'Display 31-12-2008
 
    str = Format(str, "mm/dd/yyyy")
    MsgBox str 'Display 12/31/08 as string
 
    dte = CDate(str)
    dte = dte - 20
 
    MsgBox dte 'Displays 12/11/08 as date value

so...

x = format(date(), "dd/mm/yyyy")
? x
21/12/2008
y = cdate(datevalue(x))

Hope this will help you!


Richard

 

[ChrisO]

Format([Dates],"mm/dd/yy")

is really not such a good idea because it depends on too many factors.

http://allenbrowne.com/ser-36.html

Hope that helps.

Regards,
Chris.

 

[stopher]

Format([Dates],"mm/dd/yy")

is really not such a good idea because it depends on too many factors.

http://allenbrowne.com/ser-36.html

Hope that helps.

Regards,
Chris.

But Allen Browne is proposing the use of Format() not condoning it. Ok so he's used backslashes to deal with folks who don't use slash as the delimiter but he is a Format fan!

The problem with Format() is that it returns a string.

I guess it depends how you read the question. If you want to return strictly a date datatype then I'd go with Bob. But if you just want to use the return for SQL then Format() with the hash (#) delimiter will suffice.

Chris

 

[benattal]

I tried cDate() without any success just before the post. However, using format() with the backslashes seems to work!!
thanks a lot for the tip!

ben

 

[raul_bla_bla]

sentenciaSQL = "UPDATE Numeraciones " & _
"SET Valor = " & Valor & ", " & _
"Fecha = #" & Format(fecha,"mm/dd/yyyy HH:nn:ss") & "#, " & _
"Id_Usuario = " & Id_Usuario & _
" WHERE Nombre = '" & Nombre & "'"


no search more, format in StringSQL