Syntax

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

EMILYTAN via AccessMonster.com

Can someone help me to figure what is wrong with my syntax?
Thanks...
How to open with this ( " ) and sometimes with this ( ' )?


SQLString = "insert into WIPRawDetails (JobNumber, PartNumber, W_KittedQty) "
& _
"select '" & Me.txtJobNumber & "', " & _
'"& Me.txtPartNumber & "', " &_
'" & Me.I_Qty & "' , " &_ from WIPRawDetails "
 
I am going to guess that qty is a number field.

--Either single or double quotes around text values
--No quotes or other delimiters around number values
--# marks surround dates.

SQLString = "INSERT into WIPRawDetails " & _
" (JobNumber, PartNumber, W_KittedQty) " & _
" SELECT '" & Me.txtJobNumber & _
"', '" & Me.txtPartNumber & _
"', " & Me.I_Qty & _
" FROM WIPRawDetails "

'====Add this to check the SQL STRING
Debug.Print SQLString
STOP
'====You can drop it after you see your string is valid

Your SQL statement is going to add one record to the table WIPRawDetails for
every record that already exists in the table. Is that what you really want
to do?

I would guess that you want to add ONE record. The way to do that would be
to use the alternative Insert syntax.

SQLString = "INSERT into WIPRawDetails " & _
" (JobNumber, PartNumber, W_KittedQty) " & _
" Values ('" & Me.txtJobNumber & _
"', '" & Me.txtPartNumber & _
"', " & Me.I_Qty & _
" )"



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
hi Emily,
Can someone help me to figure what is wrong with my syntax?
Thanks...
How to open with this ( " ) and sometimes with this ( ' )?
Click to expand...
You always use " or ' to mark the start and end of a string. In VB(A)
only " is an allowed string delimiter. In Jet SQL you may use " or '.
SQLString = "insert into WIPRawDetails (JobNumber, PartNumber, W_KittedQty) "
& _
"select '" & Me.txtJobNumber & "', " & _
'"& Me.txtPartNumber & "', " &_
'" & Me.I_Qty & "' , " &_ from WIPRawDetails "
Click to expand...
First of all write it down in plain SQL using real data:

INSERT INTO WIPRawDetails (JobNumber, PartNumber, W_KittedQty)
SELECT 'abc123', 'abc123', 1
FROM WIPRawDetails

or

INSERT INTO WIPRawDetails (JobNumber, PartNumber, W_KittedQty)
SELECT "abc123", "abc123", 1
FROM WIPRawDetails

Question: Of what kind of data type are these fields? Text or number?
In the above example i have made the assumption that JobNumber and
PartNumber is alphanumeric and W_KittedQty is numeric.

When it comes down to assemble this SQL in VB(A) you need to escape " if
they are constants in your SQL. Escaping means that you need to double
them. So the two examples will look like:

SQLString = "INSERT INTO WIPRawDetails " & _
"(JobNumber, PartNumber, W_KittedQty) " &_
"SELECT '" & txtJobNumber.Value & "', '" & _
txtPartNumber.Value & "', " & Me.I_Qty & " " & _
"FROM WIPRawDetails"

and

SQLString = "INSERT INTO WIPRawDetails " & _
"(JobNumber, PartNumber, W_KittedQty) " & _
"SELECT """ & txtJobNumber.Value & """, """ & _
txtPartNumber.Value & """, " & Me.I_Qty & " " & _
"FROM WIPRawDetails"



mfG
--> stefan <--
 
Back
Top