Syntax error!

  • Thread starter Thread starter EMILYTAN via AccessMonster.com
  • Start date Start date
E

EMILYTAN via AccessMonster.com

I can't find out where should I put the semi colon...
Thanks

mySQL2 = "INSERT INTO Kanban ( PartNumber, K_Qty, K_Location, To_From, K_Date
)" & _" VALUES ( '" & Me.txtPartNumber & "', " & strQuantity & " , '" &
strLocation & "','LoanID " & Me.txtLoanID & "', '" & strDate & "')" & _"
WHERE LoanID = '" & Me.txtLoanID & "'"
 
I can't find out where should I put the semi colon...
Thanks

mySQL2 = "INSERT INTO Kanban ( PartNumber, K_Qty, K_Location, To_From, K_Date
)" & _" VALUES ( '" & Me.txtPartNumber & "', " & strQuantity & " , '" &
strLocation & "','LoanID " & Me.txtLoanID & "', '" & strDate & "')" & _"
WHERE LoanID = '" & Me.txtLoanID & "'"
Click to expand...

Hi,
Try:
mySQL2 = "INSERT INTO Kanban ( PartNumber, K_Qty, K_Location, To_From,
K_Date )" & _" VALUES ( '" & Me.txtPartNumber & "', " & strQuantity &
" , '" &
strLocation & "','LoanID " & Me.txtLoanID & "', '" & strDate & "')" &
_"
WHERE (LoanID = '" & Me.txtLoanID & "');"
HTH.
Luan
 
Still can't work...
Hahah

mySQL2 = "INSERT INTO Kanban ( PartNumber, K_Qty, K_Location, To_From,K_Date )
" & _
"VALUES ( '" & Me.txtPartNumber & "', " & strQuantity & " , '" & strLocation
& "','LoanID " & Me.txtLoanID & "', '" & strDate & "')" & _
"WHERE (LoanID = '" & Me.txtLoanID & "');"


CurrentDb.Execute mySQL2, dbFailOnError

I can't find out where should I put the semi colon...
Thanks
Click to expand...
[quoted text clipped - 6 lines]
Hi,
Try:
mySQL2 = "INSERT INTO Kanban ( PartNumber, K_Qty, K_Location, To_From,
K_Date )" & _" VALUES ( '" & Me.txtPartNumber & "', " & strQuantity &
" , '" &
strLocation & "','LoanID " & Me.txtLoanID & "', '" & strDate & "')" &
_"
WHERE (LoanID = '" & Me.txtLoanID & "');"
HTH.
Luan
Click to expand...
 
The semi-colon is always optional in Access SQL statements. However, you
cannot use WHERE when you're using INSERT INTO ... VALUES. Also, you've got
line continuation characters in your SQL (& _), but it doesn't appear that
you're actually continuing onto another line.

What are the data types of the different fields? As it stands, your SQL
implies PartNumber, K_Locationn, To_From and K_Date are all text fields. If,
for example, PartNumber is actually numeric, get rid of the single quotes
around Me.txtPartNumber. If K_Date is a Date field, you need to delimit it
with # characters and the date must be in a format Access will recognize.
(Access ignores regional settings in SQL statements, so it will always
interpret 11/07/2007 as 07 Nov, 2007, even if your regional settings have
your Short Date format as dd/mm/yyyy). That would mean that rather than

, '" & strDate & "')"

you need

, " & Format(strDate, "\#yyyy\-mm\-dd\#") & ")"

Your SQL is also appending the literal text LoanID in front of the value in
Me.txtLoanID to store in the To_From field. Is that what you want to do?
 
Back
Top