One time-based column value is not being inserted into the corresponding table column.
tMetroPicks.dtExpectIn
is formatted as "hh:nn". The unbound column Me.nMAS03EI
on form fBoard
is also formatted the same.
I use the following INSERT
statement (which works flawlessly for every other column).
CurrentDb.Execute "INSERT INTO tMetroPicks (nPicker, nType, txRunNo, nLines, nBulk, nPrimary, dtTimeOut, dtExpectIn, dtActualIn, nPickHrs, nPickRate, nWeight) VALUES ('" & Me.nPICK03 & "', '" & Me.nM03PT & "', '" & Me.MAS03 & "', '" & Me.nM03TL & "', '" & Me.nM03BL & "', '" & Me.nM03PL & "', '" & Me.nM03TO & "', '" & Me.nM03EI.Value & "', '" & Me.nM03AI & "', '" & Me.nM03PH & "', '" & Me.nM03PR & "', '" & Me.nM03KG & "')"
DoCmd.RunCommand acCmdSaveRecord
Granted, nMAS03EI
value is derived from a formula (which works as expected).
=IIF([nM03TO] = "", "", [nM03TO] + (([nM03TL] / 6.1) / 24))
dtTimeOut
& dtActualIn
are time-based columns, but their values are time-stamped, not calculated. nMAS03PL
, nMAS03PH
& nMAS03PR
are also calculated columns, but they are inserted without issue. These are numeric value columns, not Date/Time
(yes! I am aware Time/Date are also numeric and formatting provides the desired Time/Date visual).
Could the fact that the errant column in question is a Date/Time calculated column be the issue here? (I even added .Value
to nMAS03EI
to eliminate ambiguity)
I look forward to your thoughts guys.
Thanks in advance, Mark
Date/time is not text, so use a date/time expression wrapped in octothorpes:
.. , #" & Format(Me!nM03TO.Value, "yyyy\/mm\/dd hh\:nn\:ss") & "#, ...
or, if time only:
.. , #" & Format(Me!nM03TO.Value, "hh\:nn\:ss") & "#, ...