Access VBA - date format conversion

[b_abdelhamid]

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

 

[Clifford Bass]

Hi Ben,

Actually, the dates are stored as real numbers, not in any particular
date format. So in your VBA SQL command you should be able to do something
like this:

strSQL = "select * from MyTable " & _
"where MyDateField = #" & Format$([txtDateInput], "mm/dd/yyyy") & "#"

Hope that helps,

Clifford Bass

 

[John W. Vinson]

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.

The dates stored in your table are actually stored as a Double Float number, a
count of days and fractions of a day (times) since midnight, December 30,
1899. Don't confuse data *storage* with data *presentation*!

The restriction to US format dates applies *only* in SQL queries involving
literal dates; and several formats can be used - an unambiguous yyyy-mm-dd
format is another good choice (I know of nobody who uses yyyy-dd-mm).

But it is NOT necessary to change the dates stored in your table, or the
format in which they are displayed. You can use a query criterion such as

=Format([Forms]![YourFormName]![txtDateField], "yyyy\-mm\-dd")

to take whatever date the user has entered on the form (in the date setting
specified by the user's Start... Control Panel... Regional menu) and convert
it to a date that Access can search.

 

[Tom Wickerath]

Hi Ben,

Access MVP Allen Browne has you covered:

International Dates in Access
http://allenbrowne.com/ser-36.html

You might want to take a look at all the good information that Allen has
compiled:
http://allenbrowne.com/tips.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[b_abdelhamid]

including the backslashes works great (at least for my first trials)!
thanks a lot for the tip!!