Results 1 to 3 of 3

Thread: RunSQL Command

  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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 ".

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you, Hans. Works great.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •