Date format problem

[kon]

I found the problem but how can I fix it.
In the headmaster the date entered as dd/mm/yyyy witch is ok for me. Greek
date format. But in the variable
when I tried to create the sql string is mm/dd/yyyy. How can I change it to
dd/mm/yyyy ; shall I use the format function? or do i have to check an
option in Access or regional settings in Windows?

 

[Tim Ferguson]

when I tried to create the sql string is mm/dd/yyyy. How can I change
it to dd/mm/yyyy ; shall I use the format function? or do i have to
check an option in Access or regional settings in Windows?

You need to understand that date values are handled differently by VBA
and by the database engine Jet.

VBA uses the computer settings, so that something like

MyDate = CDate("01/04/2005")

will produce a January date in North America, and an April date
practically everywhere else.

Jet is completely regionally-insensitive, so that something like

WHERE MyDate = #01/04/2005#

will locate January dates everywhere in the world. If you look carefully,
when you type a date into a criterion line in the Query Designer, Access
will reformat it into a m/d/y date in the underlying SQL without asking
you. There is, however, a bug in Access that allows a line like

WHERE MyDate = #20/04/2005#

to be parsed as a d/m/y date instead of raising an error.

The bottom line is that whenever you create SQL directly you _must_ use a
Jet-compatible format: this means the USAian #mm/dd/yyyy# or the
international ISO #yyyy-mm-dd#. Also note that neither of these is what
you get if you allow VBA to do its default, therefore

"... WHERE MyDate = #" & MyDate & "#"

will fail for about 90% of the world's surface. You really do need
something like this:

"... WHERE MyDate = " & Format(MyDate, "\#yyyy\-mm\-dd\#")

and then it will always work.

There is lots more information on the usual web sites:
http://www.mvps.org/access/datetime/date0005.htm
http://www.fontstuff.com/access/acctut15.htm

.... and, just for once, the help files are pretty helpful too. Look up
Date Literals.

Hope that helps


Tim F

 

[kon]

I did the folowing
vardate = Format(vardate, "dd/mm/yyyy")
varcustomerid = Forms!master!cmbfullname
sqlstring = "INSERT INTO headmaster ( datefld,customerid)SELECT
#" & Format(vardate, "dd/mm/yyyy") & "#," & varcustomerid
cncurrent.Execute (sqlstring)
but it stills put it as "mm/dd/yyyy" What am I doing wrong ?

 

[kon]

I did the folowing
vardate = Format(vardate, "dd/mm/yyyy")
varcustomerid = Forms!master!cmbfullname
sqlstring = "INSERT INTO headmaster ( datefld,customerid)SELECT
#" & Format(vardate, "dd/mm/yyyy") & "#," & varcustomerid
cncurrent.Execute (sqlstring)
but it stills put it as "mm/dd/yyyy" What am I doing wrong ?

 

[kon]

Besides why for some reason the date worked fine and when I made some
changes then the date in Sql string changed to mm/dd/yyyy

 

[peregenem]

VBA uses the computer settings, so that something like

MyDate = CDate("01/04/2005")

will produce a January date in North America, and an April date
practically everywhere else.

Jet is completely regionally-insensitive, so that something like

WHERE MyDate = #01/04/2005#

will locate January dates everywhere in the world.

What about

SELECT CDATE('01/04/2005')

I don't think your assertion, 'Jet is completely
regionally-insensitive' can be correct because for me CDATE in Jet SQL
indeed honours (honors) regional settings.

 

[peregenem]

Please take a look at this test code. Doesn't it show that VBA and Jet
handle dates in the same way? Or am I missing yout point?

Sub test()

Dim Con As Object
Set Con = CreateObject("ADODB.Connection")
Con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=nonexistent.xls;" & _
"Extended Properties='Excel 8.0'"

Dim rs As Object
Set rs = _
Con.Execute( _
"SELECT MONTH(#1/4/2005#)," & _
" MONTH(CDATE('01/04/2005'))")

Const TEST_DATE = #1/4/2005#
MsgBox _
Month(TEST_DATE) & vbCr & _
Month(CDate("01/04/2005")) & vbCr & _
rs(0) & vbCr & _
rs(1)
End Sub

 

[peregenem]

CDATE is a VBA function, not a Jet function.

Why do you say that? I can use CDATE in Jet SQL in a non-VBA project
e.g. in C#:

OleDbCommand command =
new OleDbCommand("SELECT CDATE('01/04/2005');",
connection, null );

In the above, I don't think VBA is being used at any stage because, I
assume, Jet implements CDATE at a much lower level than VBA.

 

[Tim Ferguson]

I did the folowing
vardate = Format(vardate, "dd/mm/yyyy")
varcustomerid = Forms!master!cmbfullname
sqlstring = "INSERT INTO headmaster (
datefld,customerid)SELECT
#" & Format(vardate, "dd/mm/yyyy") & "#," & varcustomerid
cncurrent.Execute (sqlstring)
but it stills put it as "mm/dd/yyyy" What am I doing wrong ?

You aren't doing what I suggested. Jet is not regionally-aware; you have
to send it dates in USA or ISO format. Period(*). Jet is the same in
america, in europe, and even in the lost city of Atlantis. Don't complain
about it, just do it. Go back to my post and use the code that is there.


Incidentally, what _is_ varDate meant to be? It starts off life as a
string

vardate = Format(vardate, "dd/mm/yyyy")

but then you treat it as a DateTime value

... & Format(vardate, "dd/mm/yyyy") & ...

so I am not surprised that you are getting unreliable results.


Best of luck


Tim F


(*)Okay, for the pedants, there are other formats but these two are the
only practical alternatives.

 

[Tim Ferguson]

What am I doing wrong ?

You are posting the same message twice...


Tim F

 

[Rick Brandt]

Why do you say that? I can use CDATE in Jet SQL in a non-VBA project
e.g. in C#:

OleDbCommand command =
new OleDbCommand("SELECT CDATE('01/04/2005');",
connection, null );

In the above, I don't think VBA is being used at any stage because, I
assume, Jet implements CDATE at a much lower level than VBA.

Perhaps I'm incorrect. I was going by the fact that the help file topic for
CDATE is in the Visual Basic Reference and not in the Jet SQL Reference area
of the help file.

 

[peregenem]

Perhaps I'm incorrect. I was going by the fact that the help file topic for
CDATE is in the Visual Basic Reference and not in the Jet SQL Reference area
of the help file.

Yes, it isn't very helpful that the full list of functions supported by
Jet isn't provided by MS at all