European Date format causing problems with INSERT INTO

  • Thread starter Thread starter Jeff Wilkin
  • Start date Start date
J

Jeff Wilkin

Hi all,

I'm getting Run-time Error 3075:
Syntax error in number in query expression '15.11.2003'

My form name is frmTransfers bound to tblTransfers
Subform control name is sfrmTranDetails bound to tblTranDetails
Parent/ child link is TransferID

[tblTransfers].[TranDate] is a short date, no input mask
[tblProdLoc].[DateStamp] is a short date, no input mask

the text control on my form does employ an input mask. Access doesn't seem
to like the "." in my system's date format. How can I work around this
without changing my system settings?

TIA,
Jeff

******** code *********
' the Sql works perfectly if I omit the date portions of the statement.

Private Sub QtyOut_AfterUpdate()

Dim inSql As String
Dim TranDate as Date

inSql = "INSERT INTO tblProdLoc (DateStamp, LocationID, ProductID,
QtyCount ) " & _
"Values ( " & Me.Parent.TranDate & "," & Me.Parent.LocationID & ", "
& Me.ProductID & ", " & Me.QtyOut & ")"

If Len(Me.ActiveControl) > 0 Then
CurrentDb.Execute inSql, dbFailonError
End If
End Sub
 
A literal date in a SQL string needs to be formatted to match the American
date system, and with the # delimiter:

inSql = "INSERT INTO tblProdLoc (DateStamp, LocationID, ProductID,
QtyCount ) " & _
"Values ( " & Format(Me.Parent.TranDate, "\#mm\/dd\/yyyy\#" & "," &
Me.Parent.LocationID & ", " & Me.ProductID & ", " & Me.QtyOut & ")"

For more information, see:
International Dates in Access
at:
http://allenbrowne.com/ser-36.html
 
Back
Top