Run-time error 3075 Syntax error (missing operator) in qery expression

[DEMCO]

Hello,



I have made a form which exports to a Excel file when I use a existing query. The form has two combo drop down list which are in the SQL as "FYCombBo" and "PenAppComBo".

What I would like to do is have a VBA made query using the SQL below, but when I try this, I get error 3075. I have been working on this for a few days and am looking for help.

The reason I want to use SQL in the VBA for this export process is that I need to have this work when one or both of the combo boxes are empty and right now I am getting an error when one or both of them are empty when I use the existing query

I would be very thankful for any help.

TSQL = " SELECT DPWProgramCostStatus.DatePrepared, DPWProgramCostStatus.SubmittedFHWA, DPWProgramCostStatus.FHWAEffective, DPWProgramCostStatus.FY, DPWProgramCostStatus.PendingApproval, DPWProgramCostStatus.ProjectNumber, DPWProgramCostStatus.ProjectTitle, DPWProgramCostStatus.[1240ModNo], DPWProgramCostStatus.FinalVoucher, DPWProgramCostStatus.TOTAL," & _
"DPWProgramCostStatus.NATLHWYSYSTERRITORIESSTEA3HT 10, DPWProgramCostStatus.NHSTERRITORIESSLUEXTLT1E, DPWProgramCostStatus.NHSTERRITORIESFY06LT10, DPWProgramCostStatus.PRTERRITORIALHWYSMAP21MT10, DPWProgramCostStatus.PRTERRITORIALHWYSMAP21EXTMT1E , DPWProgramCostStatus.NATLHWYSYSTERRITORIESTEA21QT1 0, DPWProgramCostStatus.PRTERRITORIALHWYSFMISZT10," & _
"DPWProgramCostStatus.BRIDGEREPLACREHABDISCH06 0, DPWProgramCostStatus.HIGHWAYINFRATERRITORIESZ160, DPWProgramCostStatus.FY19HIGHWAYINFRASTRUCTUREPROG RAMSZ169, DPWProgramCostStatus.TRANSPORTATIONIMPPROJLY30, DPWProgramCostStatus.ER2004HURRICANESADDLFUND09J0, DPWProgramCostStatus.EMERRELIEFFEDAIDOTHER09V0, DPWProgramCostStatus.FY17OJTSSZ49A," & _
"DPWProgramCostStatus.FY17NSTIAllocationZ49B, DPWProgramCostStatus.FY16NSTIAllocationZ49S, DPWProgramCostStatus.FY17DARFUNDS74YF, DPWProgramCostStatus.NAVYCONSTLANDACQPROJ73P0, DPWProgramCostStatus.MILITARYCONSTRNAVYFY101473V0, DPWProgramCostStatus.GUAMIMPACTSTUDYUSMC24C0, DPWProgramCostStatus.WILDLIFEREFUGEROADSTEA214190, DPWProgramCostStatus.Notes" & _
"FROM DPWProgramCostStatus" & _
"WHERE (((DPWProgramCostStatus.FY)=[Forms]![FedAidStatusQueryFYSelection]![FYComBo]) AND ((DPWProgramCostStatus.PendingApproval)=[Forms]![FedAidStatusQueryFYSelection]![PendAppComBo]));"

[June7]

Need spaces at end of following lines so constructed statement does not 'run together':

DPWProgramCostStatus.GUAMIMPACTSTUDYUSMC24C0, DPWProgramCostStatus.WILDLIFEREFUGEROADSTEA214190, DPWProgramCostStatus.Notes " & _
"FROM DPWProgramCostStatus " & _

[ssanfu]

I'm not sure is you are having problems because of the missing space as per June7 or you are having problems if one or both of the combo boxes are empty/NULL.
Is the inline SQL in a form module or a standard module?
Are the values in the combo boxes numbers, text or dates? If Text or Dates, they need to be delimited.

Here is an example of how I would write the code

Option Compare Database  'these two lines should be at the top of EVERY module - form or standard
Option Explicit          'these two lines should be at the top of EVERY module - form or standard

Private Sub Command3_Click()
    Dim TSQL As String
    Dim tmpWHERE As String

        'generate basic SQL string
        TSQL = "SELECT DPWProgramCostStatus.DatePrepared, DPWProgramCostStatus.SubmittedFHWA, DPWProgramCostStatus.FHWAEffective,"
        TSQL = TSQL & " DPWProgramCostStatus.FY, DPWProgramCostStatus.PendingApproval, DPWProgramCostStatus.ProjectNumber, DPWProgramCostStatus.ProjectTitle,"
        TSQL = TSQL & " DPWProgramCostStatus.[1240ModNo], DPWProgramCostStatus.FinalVoucher, DPWProgramCostStatus.TOTAL,"
        TSQL = TSQL & " DPWProgramCostStatus.NATLHWYSYSTERRITORIESSTEA3HT 10, DPWProgramCostStatus.NHSTERRITORIESSLUEXTLT1E,"
        TSQL = TSQL & " DPWProgramCostStatus.NHSTERRITORIESFY06LT10, DPWProgramCostStatus.PRTERRITORIALHWYSMAP21MT10,"
        TSQL = TSQL & " DPWProgramCostStatus.PRTERRITORIALHWYSMAP21EXTMT1E , DPWProgramCostStatus.NATLHWYSYSTERRITORIESTEA21QT10,"
        TSQL = TSQL & " DPWProgramCostStatus.PRTERRITORIALHWYSFMISZT10,  DPWProgramCostStatus.BRIDGEREPLACREHABDISCH060,"
        TSQL = TSQL & " DPWProgramCostStatus.HIGHWAYINFRATERRITORIESZ160, DPWProgramCostStatus.FY19HIGHWAYINFRASTRUCTUREPROG RAMSZ169,"
        TSQL = TSQL & " DPWProgramCostStatus.TRANSPORTATIONIMPPROJLY30, DPWProgramCostStatus.ER2004HURRICANESADDLFUND09J0,"
        TSQL = TSQL & " DPWProgramCostStatus.EMERRELIEFFEDAIDOTHER09V0, DPWProgramCostStatus.FY17OJTSSZ49A,  DPWProgramCostStatus.FY17NSTIAllocationZ49B,"
        TSQL = TSQL & " DPWProgramCostStatus.FY16NSTIAllocationZ49S, DPWProgramCostStatus.FY17DARFUNDS74YF, DPWProgramCostStatus.NAVYCONSTLANDACQPROJ73P0,"
        TSQL = TSQL & " DPWProgramCostStatus.MILITARYCONSTRNAVYFY101473V0, DPWProgramCostStatus.GUAMIMPACTSTUDYUSMC24C0,"
        TSQL = TSQL & " DPWProgramCostStatus.WILDLIFEREFUGEROADSTEA214190 , DPWProgramCostStatus.Notes"
        TSQL = TSQL & " FROM DPWProgramCostStatus"

    'determine where clause
    tmpWHERE = " WHERE"
    If Len(Trim([Forms]![FedAidStatusQueryFYSelection]![FYComBo]) & "") > 0 Then
        tmpWHERE = tmpWHERE & " DPWProgramCostStatus.FY = " & [Forms]![FedAidStatusQueryFYSelection]![FYComBo] & " AND"
    End If

    If Len(Trim([Forms]![FedAidStatusQueryFYSelection]![PendAppComBo]) & "") > 0 Then
        tmpWHERE = tmpWHERE & " DPWProgramCostStatus.PendingApproval = " & [Forms]![FedAidStatusQueryFYSelection]![PendAppComBo]
    End If

    'check for " and" at the end of where string - if found remove it
    If Len(Trim(tmpWHERE & "")) > 0 Then
        If Right(tmpWHERE, 4) = " AND" Then
            tmpWHERE = Left(tmpWHERE, Len(tmpWHERE) - 4)
        End If
    End If

    'add the where clause, if any
    If Len(Trim(tmpWHERE)) > 6 Then
        TSQL = TSQL & tmpWHERE
    End If
    
    
    Debug.Print TSQL

End Sub

[CJ_London]

it is not necessary to provide the table name since you are only querying one table which would make your code easier to read, but certainly you are missing a number of spaces - for example

, DPWProgramCostStatus.FinalVoucher, DPWProgramCostStatus.TOTAL," & _
"DPWProgramCostStatus.NATLHWYSYSTERRITORIESSTEA3HT 10,

"FROM DPWProgramCostStatus" & _
"WHERE (((DPWProgramCostStatus.FY)=

and you seem to have spaces in some field names which consequently need square brackets, for example

"DPWProgramCostStatus.NATLHWYSYSTERRITORIESSTE A3HT 10,
, DPWProgramCostStatus.NATLHWYSYSTERRITORIESTEA21QT1 0,
"DPWProgramCostStatus.BRIDGEREPLACREHABDISCH06 0

you also don't need all those brackets in the WHERE statement either

[ssanfu]

@Ajax

"DPWProgramCostStatus.NATLHWYSYSTERRITORIESSTE A3HT 10,
, DPWProgramCostStatus.NATLHWYSYSTERRITORIESTEA21QT1 0,
"DPWProgramCostStatus.BRIDGEREPLACREHABDISCH06 0

I assumed that the spaces were from added by the forum because IIRC, it adds a space after 50 characters if the text is not within tags....????

[CJ_London]

could be - but funny how it is only apparent on numerical characters

[ssanfu]

oops....I missed that