Access VBA - date format conversion


B

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
 
Ad

Advertisements

C

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
 
J

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.
 
Ad

Advertisements

B

b_abdelhamid

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top