Prob with SQL transfer of Data (2)

[FALSE_EMAIL]

Can anybody help
I'm trying to transfer certain fields to a second table off a form, but I'm
having some trouble,
I want it to work on a button press but ask you for a serial number, I've
tried declaring it as a string and using an input box
but can't get the SQL code right. Can anybody help

currently it transfers everything but enters ' & vin & ' as the Vin and
not the value you type in the input box

Dim vin As String
Dim Date_time As Date
Date_time = Now()
vin = InputBox("Enter VIN")
Dim strSQL As String

Set db = CurrentDb()


strSQL = "INSERT INTO Vin_detail_tbl" & _
" (Vin_Num,Comp_id, Fault_id,Insp_point_id,Date_time,Shift)" & _
" VALUES (""& vin & ""," & _
Me.Component & "," & _
Me.Fault & "," & _
Me.Insp_point & ",#" & _
Date_time & "#," & _
Me.Shift & ");"

db.Execute strSQL, dbFailOnError

Kev

 

[Dan Artuso]

Hi,
Try this:
strSQL = "INSERT INTO Vin_detail_tbl" & _
" (Vin_Num,Comp_id, Fault_id,Insp_point_id,Date_time,Shift)" & _
" VALUES (""" & vin & """," & _
Me.Component & "," & _
Me.Fault & "," & _
Me.Insp_point & ",#" & _
Date_time & "#," & _
Me.Shift & ");"

 

[FALSE_EMAIL]

Thanks, that worked, I take it that 2 double quotes inserts it as the text
in the code but the 3 quotes indicates it as the string


Many thanks for your quick reply

Kev

 

[Dan Artuso]

Hi,
Two double quotes will evaluate to one quote in the string, which is what you want.
Remember, you need to tell Access where your string starts and ends.
You use the double quote to do this. If you want a double quote within the string,
you use two quotes to represent that.
For example:
"this is a string with a "" in it"
will evaluate to:
this is a string with a " in it