Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Posts
    292

    Unanswered: code help.. thanks

    I have a query.. that I'm trying to get on my form behind a button..

    On a SQL query the code looks like so..
    Code:
    SELECT Count(tblInspections.FLT) AS CountOfFLT, Left([FLT],2) AS FaultType, tblFaultCode_FAULTCODES.Description, tblInspections.CRT
    FROM tblFaultCode_FAULTCODES INNER JOIN (tblInspections INNER JOIN tblQR ON (tblInspections.strProject = tblQR.strProject) AND (tblInspections.strArea = tblQR.strArea) AND (tblInspections.strReference = tblQR.strReference)) ON tblFaultCode_FAULTCODES.Code = tblInspections.FLT
    WHERE (((Trim([tblInspections].[FLT]) & '')<>''))
    GROUP BY Left([FLT],2), tblFaultCode_FAULTCODES.Description, tblInspections.CRT;
    I'm trying to add that code to a button on a form...

    Code:
    sqlString = "SELECT Count(tblInspections.FLT) AS CountOfFLT, Left([FLT],2) AS FaultType, tblFaultCode_FAULTCODES.Description, tblInspections.CRT FROM tblFaultCode_FAULTCODES INNER JOIN (tblInspections INNER JOIN tblQR ON (tblInspections.strProject = tblQR.strProject) AND (tblInspections.strArea = tblQR.strArea) AND (tblInspections.strReference = tblQR.strReference)) ON tblFaultCode_FAULTCODES.Code = tblInspections.FLT WHERE (trim(tblInspections.FLT) & ''<>'') " & sqlString & " GROUP BY Left([FLT],2), tblFaultCode_FAULTCODES.Description, tblInspections.CRT;"
    I get an error there.. that says..

    Syntax error missing operator in query expression '(trim(tblInspections.FLT) & "<>") tblInspections.CRT='05".

    I don't understand why im getting this error..or how to fix it..

    thanks for any suggestions..

    I tried..

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You have a poorly formed statement... try this:

    ... (trim(tblInspections.FLT) <> '" & sqlString & "') GROUP BY ..."

    Also, am I correct in assuming that prior to executing this code, sqlString has been assigned a cstring for comparison?

  3. #3
    Join Date
    Jul 2003
    Posts
    292
    I get a different error this time...

    Syntax error..
    '(trim(tblInspections.FLT) <>' tblInspections.CRT='05")'

    thanks for your help..

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    tblInspections.CRT='05

    What is that? Where does that come from?

    I think you have code prior to setting your sqlstring that is borking.
    Last edited by Teddy; 04-16-04 at 17:20.

  5. #5
    Join Date
    Jul 2003
    Posts
    292
    I'm sorry let me post the entire code...

    Option Compare Database
    Option Explicit

    Private Sub Command4_Click()
    Dim sqlString As String
    sqlString = ""

    If Not (IsNull(Me.txtStartOpen3) Or IsNull(Me.txtStartEnd3)) Then
    sqlString = "AND tblInspections.strDate Between #" & Me.txtStartOpen3 & "# And #" & Me.txtStartEnd3 & "#"
    End If
    If Not (IsNull(Me.txtCRT)) Then
    If sqlString <> "" Then sqlString = sqlString & " And"
    sqlString = sqlString & " tblInspections.CRT = '" & [Forms]![Form3]![txtCRT] & "'"
    End If
    '...

    sqlString = "SELECT Count(tblInspections.FLT) AS CountOfFLT, Left([FLT],2) AS FaultType, tblFaultCode_FAULTCODES.Description, tblInspections.CRT FROM tblFaultCode_FAULTCODES INNER JOIN (tblInspections INNER JOIN tblQR ON (tblInspections.strProject = tblQR.strProject) AND (tblInspections.strArea = tblQR.strArea) AND (tblInspections.strReference = tblQR.strReference)) ON tblFaultCode_FAULTCODES.Code = tblInspections.FLT WHERE (trim(tblInspections.FLT) <> '" & sqlString & "') GROUP BY Left([FLT],2), tblFaultCode_FAULTCODES.Description, tblInspections.CRT;"

    Debug.Print sqlString
    CurrentDb.QueryDefs.Delete "query5"
    CurrentDb.CreateQueryDef "query5", sqlString
    Me.Refresh
    Me.Requery

    End Sub

    thanks for your help

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    well that would be your problem.

    Take a look at the possible values for sqlString before you try to set the SELECT statement. There's no way you can pass a good value to the statement.

    You are comparing:

    trim(tblInspections.FLT) <> sqlString.

    so if sqlString = tblInspections.CRT = '5' then that translates to:

    trim(tblInspections.FLT) <> 'tblInspections.CRT = '5'

    which is obviously bad syntax.

    I don't get what you're trying to do here... none of the possible values for sqlString after your If statement will work in that query..

  7. #7
    Join Date
    Jul 2003
    Posts
    292
    Im sorry im new to this.. below is my original form.. the user asked if I could add another search criteria to filter out my CRT field.. I added the code inside my query and tried modifying it to work on my form...

    I think some how i mixed in the trim function to my CRT field..

    Do you have any ideas how to do this.. I'm at a total lost..

    thanks!


    Private Sub Command4_Click()
    Dim sqlString As String
    sqlString = ""

    If Not (IsNull(Me.txtStartOpen3) Or IsNull(Me.txtStartEnd3)) Then
    sqlString = "AND tblInspections.strDate Between #" & Me.txtStartOpen3 & "# And #" & Me.txtStartEnd3 & "#"
    End If
    '...

    sqlString = "SELECT Count(tblInspections.FLT) AS CountOfFLT, Left([FLT],2) AS FaultType, tblFaultCode_FAULTCODES.Description FROM tblFaultCode_FAULTCODES INNER JOIN (tblInspections INNER JOIN tblQR ON (tblInspections.strProject = tblQR.strProject) AND (tblInspections.strArea = tblQR.strArea) AND (tblInspections.strReference = tblQR.strReference)) ON tblFaultCode_FAULTCODES.Code = tblInspections.FLT WHERE (trim(tblInspections.FLT) & ''<>'') " & sqlString & " GROUP BY Left([FLT],2), tblFaultCode_FAULTCODES.Description;"

    Debug.Print sqlString
    CurrentDb.QueryDefs.Delete "query5"
    CurrentDb.CreateQueryDef "query5", sqlString
    Me.Refresh
    Me.Requery

    DoCmd.OpenForm "Form3a", acNormal
    DoCmd.Close acForm, "form3"
    End Sub

Posting Permissions

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