RunSQL Command

Topic

New Reply

Hello all,

I have the SQL below that works fine except that I have to enter the BoxNo twice, once in the InputBox Function and once during the DoCmd.RunSQL statement. I prefer entering it just once, but can’t quite figure it out. As always, any and all suggestions are appreciated. TIA.

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click

Dim stDocName As String
Dim BoxNo As Integer

BoxNo = InputBox(“Enter Box Number to Export”, “Box Number Export”)

DoCmd.RunSQL “SELECT Deeds.FILEBOXNUM, Deeds.INSTRUMENTNUMBER, Deeds.INSTRUMENTTYPE, Deeds.GRANTOR, Deeds.FIRSTNAME1, Deeds.LASTNAME INTO tblBoxNumber FROM Deeds WHERE (((Deeds.FILEBOXNUM)= BoxNo));”

DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tablename:=”tblBoxNumber”, _
filename:=”Y:BoxNumber” & BoxNo

Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub

Reply | Quote

Replies

You can’t use a VBA variable in an SQL statement. You have to concatenate the SQL string with the value of the variable.

If FILEBOXNUM is a number field:

DoCmd.RunSQL “SELECT FILEBOXNUM, INSTRUMENTNUMBER, INSTRUMENTTYPE, GRANTOR, FIRSTNAME1, LASTNAME INTO tblBoxNumber FROM Deeds WHERE FILEBOXNUM=” & BoxNo

If it is a text field, you have to enclose the value in quotes:

DoCmd.RunSQL “SELECT FILEBOXNUM, INSTRUMENTNUMBER, INSTRUMENTTYPE, GRANTOR, FIRSTNAME1, LASTNAME INTO tblBoxNumber FROM Deeds WHERE FILEBOXNUM=” & Chr(34) & BoxNo & Chr(34)

Chr(34) is the double quote character “.

Reply | Quote

Thank you, Hans. Works great.

Reply | Quote