Dates going into and coming out of MySQL

[galorin]

I'm sure this problem has come up before, but I have looked and can't seem to find the answer.

What I need is a way to take a date entered into Access and swap it around so that it goes into MySQL properly. I suspect it'd be a subprocedure or global function.

I am using a pop-up calendar for date input. It puts the date in as dd/mm/yy but mysql expects yyyy-mm-dd, so when I enter, say 15/04/08 it goes into mysql as 2015-04-08. It comes back out fine (Access sees the date as it is stored in MySQL, it's just going in wrong), so I need to sanitise my data somehow.

Any suggestions?

 

[gwinn7]

If you are using ODBC/linked tables and passing a date value, then the driver should translate it fine. If not, then you will have to format the date into a string, something like...

INSERT INTO mysqltable (id, mydate)
VALUES (1, "'" & format(now(),"yyyy-mm-dd") & "'")

If its a pass-through query, you will need to format the date first as you build the SQL.

However, your issue is still kinda confusing to me because the ODBC driver should be taking care of that conversion.

Hope that helps,

Gary
gwinn7

 

[galorin]

I'm a bit confused as well. It seems that if I use the ADODB connection method (Below as several globals) then it goes in wrong. If I use the docmd.runsql method with the same string, it gets formatted properly.

Public Const DB_CONNECT As String = "Driver={MySQL ODBC 3.51 Driver};$USERNAMEPASSWORDSTUFF;Option=3;"

Public Sub connectDB()
Set cn = New ADODB.Connection

With cn
  .ConnectionString = DB_CONNECT
  .Open
End With

Set cmd = New ADODB.command
With cmd
 .CommandType = adCmdUnknown
End With

End Sub
Public Sub getData()

With cmd
  .ActiveConnection = cn
  .CommandText = SQLout
  Set rst = .Execute
End With

End Sub
Public Sub putData()
With cmd
 .ActiveConnection = cn
 .CommandText = SQLin
 .Execute
End With

End Sub