Syntax error I can see the forest but not the trees

[Gaetanm via AccessMonster.com]

I'm having a problem I can't see the trees from the forest.
In the following SQL I added on the Job Sheet [Customer Name] and Me.
[txtcompanyname]
The SQL was working fine before the modification. It's proberbly very obvious
to someone
out there. I have been looking but just can't see it. Help


SQLstrg = "INSERT INTO [Job Sheet] ([CustomerID],[Customer Name],[Start Date],
[Notes])" & "VALUES(" & Me.[cboCustomer] & ", #" Me.[txtcompanyname] & "# ,"
& Chr(34)& Me.[txtDate] & "# ," & Chr(34) & Me.txtJobDescription & Chr(34) &
")"


Gaetanm

 

[Ken Snell \(MVP\)]

Wrong delimiter for text -- use ' not # (# is to delimit date/time values).
Also, always good to format date/time into US format, as that is the one
that Jet expects if the date value is ambiguous:

SQLstrg = "INSERT INTO [Job Sheet] ([CustomerID],[Customer Name],[Start
Date],
[Notes])" & "VALUES(" & Me.[cboCustomer] & ", '" Me.[txtcompanyname] & "' ,
#"
& Format(Me.[txtDate], "mm\/dd\/yyyy") & "# , '" & Me.txtJobDescription &
"')"

 

[John Spencer]

You need to delimit text values with quote marks and dates with # marks.

==================== all on one line ===================
SQLstrg = "INSERT INTO [Job Sheet] ([CustomerID],[Customer Name],[Start
Date], [Notes])" & "VALUES(" & Me.[cboCustomer] & ", " & Chr(34) &
Me.[txtcompanyname] & Chr(34) & """, #" & Me.[txtDate] & "# , " & Chr(34) &
Me.txtJobDescription & Chr(34) & ")"
================== all above on one line =================
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Gaetanm via AccessMonster.com]

John & Ken

Thank you for your help. With just a few minor changes in the sytax you sent
me it worked like a charm

thanks again

Gaetanm

You need to delimit text values with quote marks and dates with # marks.

==================== all on one line ===================
SQLstrg = "INSERT INTO [Job Sheet] ([CustomerID],[Customer Name],[Start
Date], [Notes])" & "VALUES(" & Me.[cboCustomer] & ", " & Chr(34) &
Me.[txtcompanyname] & Chr(34) & """, #" & Me.[txtDate] & "# , " & Chr(34) &
Me.txtJobDescription & Chr(34) & ")"
================== all above on one line =================

I'm having a problem I can't see the trees from the forest.
In the following SQL I added on the Job Sheet [Customer Name] and Me.

[quoted text clipped - 13 lines]

Gaetanm