How to change mm-dd-yyyy to dd-mm-yyyy

  • Thread starter Thread starter Alp Bekisoglu
  • Start date Start date
A

Alp Bekisoglu

Hi,

I'm having a problem with proper format data entry via and VBA run SQL
statement. To check what's happening I have included a MsgBox as well as
Debug.Print in the code. These two indicate that the data is gathered
correctly, as dd-mm-yyyy. But when the SQL INSERT's tha data to the table
the very same value turns into mm-dd-yyyy.
Can someone please guide me in the right direction to deal with this
problem?
The date field (odeme_tar) in the table is formatted as Short Date with an
input mask of 99-99-9999;0;

A few lines off of Debug.Print (line 1 is source for MsgBox, 2 is the
strSQL):
1 07-07-2004 1st installment 6772.19 13544.39
INSERT INTO tb_tmp_pl (sirala, odeme_tar, izahat, miktar, sonuc) VALUES ('
1', #07-07-2004#, ' 1st installment', 6772.19, 13544.39);
2 07-08-2004 2nd installment 6772.19 6772.201
INSERT INTO tb_tmp_pl (sirala, odeme_tar, izahat, miktar, sonuc) VALUES ('
2', #07-08-2004#, ' 2nd installment', 6772.19, 6772.201);
3 07-09-2004 3rd installment 6772.2 4.882813E-04
INSERT INTO tb_tmp_pl (sirala, odeme_tar, izahat, miktar, sonuc) VALUES ('
3', #07-09-2004#, ' 3rd installment', 6772.2, 4.882813E-04);

And a copy of the above from the table itself:
sirala odeme_tar izahat miktar sonuc
1 07-07-2004 1st installment 6772.19 13544.39
2 08-07-2004 2nd installment 6772.19 6772.201
3 09-07-2004 3rd installment 6772.2 4.882813E-04


Thanks in advance

Alp
 
Hi Alp

SQL ignores your computer's regional settings and *always* interprets dates
as mm/dd/yyyy.

In a way, this is good, because it removes any coding ambiguities. However,
it means that those of us who live in parts of the world with more logical
date formatting need to make allowances :-)

I use a function, SqlDate, as follows:

Public Function SqlDate(vDate as Variant) As String
If IsDate(vDate) Then
SqlDate = Format( vDate, "\#mm\/dd\/yyyy\#" )
End If
End Function

This will also include the enclosing hash (pound) signs.

Just include it as you construct the SQL string - for example:

sSQL = "Insert into MyTable (DateField) values (" _
& SqlDate(DateValue) & ");"
 
Hi Graham,

Thank you very much for your advice and the function and I just loved the
remark on the formatting!
In the mean time I kind of won my war with that SQL restriction by using a
query converting the values into their "correct" places! I had to make 2 of
those, one for the form that displays the data and the other which copies it
to a permanent table. An awkward way but it worked! Looks like:
SELECT tb_tmp_pl.sirala, DateValue(Month([tb_tmp_pl]![odeme_tar]) & "/" &
Day([tb_tmp_pl]![odeme_tar]) & "/" & Year([tb_tmp_pl]![odeme_tar])) AS Tar,
tb_tmp_pl.izahat, tb_tmp_pl.miktar, tb_tmp_pl.sonuc
FROM tb_tmp_pl;
But I will definitely revert to your function since I would like to keep the
DB clean of unnecessary things and it is very neat.
I'm also trying to win another war in getting my help facility to be
recognized! Guess I will overcome that somehow as well. If I really had the
time I would for sure re-write Access Help at least to include more sensible
examples! Maybe someone should make it into a project that all could
contribute. Kind of open-sourcing the help.
Anyway, thanks a lot once again and have a great day!

All the best from K.L.

Alp

Graham Mandeno said:
Hi Alp

SQL ignores your computer's regional settings and *always* interprets dates
as mm/dd/yyyy.

In a way, this is good, because it removes any coding ambiguities. However,
it means that those of us who live in parts of the world with more logical
date formatting need to make allowances :-)

I use a function, SqlDate, as follows:

Public Function SqlDate(vDate as Variant) As String
If IsDate(vDate) Then
SqlDate = Format( vDate, "\#mm\/dd\/yyyy\#" )
End If
End Function

This will also include the enclosing hash (pound) signs.

Just include it as you construct the SQL string - for example:

sSQL = "Insert into MyTable (DateField) values (" _
& SqlDate(DateValue) & ");"
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Alp Bekisoglu said:
Hi,

I'm having a problem with proper format data entry via and VBA run SQL
statement. To check what's happening I have included a MsgBox as well as
Debug.Print in the code. These two indicate that the data is gathered
correctly, as dd-mm-yyyy. But when the SQL INSERT's tha data to the table
the very same value turns into mm-dd-yyyy.
Can someone please guide me in the right direction to deal with this
problem?
The date field (odeme_tar) in the table is formatted as Short Date with an
input mask of 99-99-9999;0;

A few lines off of Debug.Print (line 1 is source for MsgBox, 2 is the
strSQL):
1 07-07-2004 1st installment 6772.19 13544.39
INSERT INTO tb_tmp_pl (sirala, odeme_tar, izahat, miktar, sonuc) VALUES ('
1', #07-07-2004#, ' 1st installment', 6772.19, 13544.39);
2 07-08-2004 2nd installment 6772.19 6772.201
INSERT INTO tb_tmp_pl (sirala, odeme_tar, izahat, miktar, sonuc) VALUES ('
2', #07-08-2004#, ' 2nd installment', 6772.19, 6772.201);
3 07-09-2004 3rd installment 6772.2 4.882813E-04
INSERT INTO tb_tmp_pl (sirala, odeme_tar, izahat, miktar, sonuc) VALUES ('
3', #07-09-2004#, ' 3rd installment', 6772.2, 4.882813E-04);

And a copy of the above from the table itself:
sirala odeme_tar izahat miktar sonuc
1 07-07-2004 1st installment 6772.19 13544.39
2 08-07-2004 2nd installment 6772.19 6772.201
3 09-07-2004 3rd installment 6772.2 4.882813E-04


Thanks in advance

Alp
Click to expand...
Click to expand...
 
Back
Top