Results 1 to 6 of 6
  1. #1
    eww is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    125

    Long Variable syntax


    I'm trying to adjust the rowsource of a combo box based on a few variables that I've made.

    Code:
        Dim strSQL As String
        strSQL = _
        "SELECT dbo_tblJobInfo.JobNum, dbo_tblJobInfo.Cstmr1, dbo_tblJobInfo.Sales, dbo_tblJobInfo.FabLoc, dbo_tblJobInfo.JobName, dbo_tblJobInfo.JobAddr, dbo_tblJobInfo.JobCity, dbo_tblJobInfo.JobZip, dbo_tblJobInfo.PrimCont, dbo_tblJobInfo.PrimContPhone, dbo_tblJobInfo.PrimContEmail, dbo_tblJobInfo.BillType, dbo_tblJobInfo.Detlr1, dbo_tblJobInfo.DetDBID " & _
        "FROM dbo_tblJobInfo " & _
        "WHERE dbo_tblJobInfo.DetDBID = '" & strDetDBID & "' OR dbo_tblJobInfo.FabLoc = lngFabDBID1 OR dbo_tblJobInfo.FabLoc = lngFabDBID2 OR dbo_tblJobInfo.FabLoc = lngFabDBID3 OR dbo_tblJobInfo.FabLoc = lngFabDBID4 OR dbo_tblJobInfo.FabLoc = lngFabDBID5 ;"
        Me.cboJobNum.RowSource = strSQL
    the string variable is working fine (strDetDBID) but I can't get the long variables (lngFabDBIDx) to work and I'm pretty sure it's just due to a syntax error that I can't seem to figure out. I've tried a couple of different ways. Any suggestions?

  2. #2
    eww is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    125
    I should probably mention that these are public variables that have already been declared and have values.

  3. #3
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Code:
      Dim strSQL As String
        strSQL = _
        "SELECT dbo_tblJobInfo.JobNum, dbo_tblJobInfo.Cstmr1, dbo_tblJobInfo.Sales, dbo_tblJobInfo.FabLoc, dbo_tblJobInfo.JobName, dbo_tblJobInfo.JobAddr, dbo_tblJobInfo.JobCity, dbo_tblJobInfo.JobZip, dbo_tblJobInfo.PrimCont, dbo_tblJobInfo.PrimContPhone, dbo_tblJobInfo.PrimContEmail, dbo_tblJobInfo.BillType, dbo_tblJobInfo.Detlr1, dbo_tblJobInfo.DetDBID " & _
        "FROM dbo_tblJobInfo " & _
        "WHERE dbo_tblJobInfo.DetDBID = '" & strDetDBID & "' OR dbo_tblJobInfo.FabLoc = " & lngFabDBID1 & " OR dbo_tblJobInfo.FabLoc = " & lngFabDBID2 & " OR dbo_tblJobInfo.FabLoc = " & lngFabDBID3 & "  OR dbo_tblJobInfo.FabLoc = " & lngFabDBID4 & " OR dbo_tblJobInfo.FabLoc = " & lngFabDBID5
        Me.cboJobNum.RowSource = strSQL

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You need to get the variables *outside* of the string:
    Code:
    strSQL = _
    "SELECT dbo_tblJobInfo.JobNum, dbo_tblJobInfo.Cstmr1, dbo_tblJobInfo.Sales, dbo_tblJobInfo.FabLoc, dbo_tblJobInfo.JobName, dbo_tblJobInfo.JobAddr, dbo_tblJobInfo.JobCity, dbo_tblJobInfo.JobZip, dbo_tblJobInfo.PrimCont, dbo_tblJobInfo.PrimContPhone, dbo_tblJobInfo.PrimContEmail, dbo_tblJobInfo.BillType, dbo_tblJobInfo.Detlr1, dbo_tblJobInfo.DetDBID " & _
             "FROM dbo_tblJobInfo " & _
             "WHERE dbo_tblJobInfo.DetDBID = '" & strDetDBID & "' OR dbo_tblJobInfo.FabLoc = " & lngFabDBID1 & _
             " OR dbo_tblJobInfo.FabLoc = " & lngFabDBID2 & " OR dbo_tblJobInfo.FabLoc = " & lngFabDBID3 & _
             " OR dbo_tblJobInfo.FabLoc = " & lngFabDBID4 & " OR dbo_tblJobInfo.FabLoc = " & lngFabDBID5 ;"

  5. #5
    eww is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    125
    Thanks for the help guys, I thought I had tried that way but I guess I hadn't. Just one minor correction in case anyone stumbles across this in the future, there needs to be another & " at the end.

    Code:
    strSQL = _
    "SELECT dbo_tblJobInfo.JobNum, dbo_tblJobInfo.Cstmr1, dbo_tblJobInfo.Sales, dbo_tblJobInfo.FabLoc, dbo_tblJobInfo.JobName, dbo_tblJobInfo.JobAddr, dbo_tblJobInfo.JobCity, dbo_tblJobInfo.JobZip, dbo_tblJobInfo.PrimCont, dbo_tblJobInfo.PrimContPhone, dbo_tblJobInfo.PrimContEmail, dbo_tblJobInfo.BillType, dbo_tblJobInfo.Detlr1, dbo_tblJobInfo.DetDBID " & _
             "FROM dbo_tblJobInfo " & _
             "WHERE dbo_tblJobInfo.DetDBID = '" & strDetDBID & "' OR dbo_tblJobInfo.FabLoc = " & lngFabDBID1 & _
             " OR dbo_tblJobInfo.FabLoc = " & lngFabDBID2 & " OR dbo_tblJobInfo.FabLoc = " & lngFabDBID3 & _
             " OR dbo_tblJobInfo.FabLoc = " & lngFabDBID4 & " OR dbo_tblJobInfo.FabLoc = " & lngFabDBID5 & ";"

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Oops... Darn AIR CODE compiler anyway. Of course it does! Go ahead and use the Thread Tool and mark this thread as Solved.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 11-16-2010, 03:56 PM
  2. Replies: 3
    Last Post: 10-15-2010, 11:17 AM
  3. Incomplete Syntax Clause (syntax error)
    By ajetrumpet in forum Programming
    Replies: 4
    Last Post: 09-11-2010, 10:47 AM
  4. Replies: 4
    Last Post: 08-05-2010, 01:26 PM
  5. Refering to variable form names inside a variable
    By redpetfran in forum Programming
    Replies: 2
    Last Post: 05-21-2010, 01:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums