Hello,


I'm using Access as a front end with MySQL as the backend utilizing MySQL ODBC 5.2 Unicode and linked tables. Let me explain the process.
I have a Variant called dInpNextCalibration. The reason it is Variant is because if a date is not available, I want it to be Null.

I get the date from a linked table with datatype datetime.


dInpNextCalibration = rsImportTable.Fields("Calibration_Next_Calibration ")

MsgBox'ing this, I get a date like 2012/07/26. Now I want to place this in a field in linked another table I call Calibrations.
Here is my SQL statement.
sInpCalibrationSQL = "INSERT INTO Calibrations(Calibration_ID, Calibration_Asset_Link, Calibration_Type, Calibration_Supplier, Calibration_Interval, Last_Calibration, Next_Calibration) VALUES(" & iInpCalibrationID & "," & iInpCalibrationAssetLink & "," & iInpCalibrationType & "," & iInpCalibrationSupplier & "," & iInpCalibrationInterval & "," & dInpLastCalibration & "," & dInpNextCalibration & ")"

Unfortunately, on the back server, everything but the dates get passed in properly. The dates get passed in as NULL and the frontend says 12:00:00 1899 or something (default I guess?).
How can I fix this so the query so that the date gets passed in properly?

Thank you in advance.

Edit:

I figured it out!
I need to put pound signs after and before the date. So its like this:
...",#" & dInpLastCalibration & "#'...

However, Null becomes a problem.

Perhaps I should concatonate then.
So it'd be like
If IsNull(dInpLastCalibration) = False
dInpLastCalibration = "#" & dInpLastCalibration & "#"
Else
dInpLastCalibration = "Null"
End If

This works for me great actually. I either end up ultimately with ...& ",#8/22/2013#,"... or ... & ",""Null""," &...
Well at least I think. Regardless it appears correctly in my backend.