Results 1 to 10 of 10

Thread: SQL help !

  1. #1
    Join Date
    Jul 2003
    Posts
    292

    Unanswered: SQL help !

    I have a form which searches through a query - for dates and another txt field called CRT

    The user enters in dates of records they want to see and or CRT they want to see or both hit a button and those records show up...

    or

    If the user leaves the fields blank.. then all records show up..

    All data is in a query called query5c.. and the results are to be placed in Query5

    When I run the code with dates and CRT's that I want to see only on my form It returns everything in the Query5c..

    Heres my entire code on my form:

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

    If Not (IsNull(Me.StartDate) Or IsNull(Me.StartDate2)) Then
    sqlString = " Query5c.strDate Between #" & [Forms]![Form3c]![StartDate] & "# And #" & [Forms]![Form3c]![StartDate2] & "# "
    End If
    If Not (IsNull(Me.txtCRT)) Then
    If sqlString <> "" Then sqlString = sqlString & " And"
    sqlString = sqlString & " Query5c.CRT = '" & [Forms]![Form3c]![txtCRT] & "'"
    End If
    '...

    If sqlString <> "" Then sqlString = " Where" & sqlString

    sqlString = "SELECT Count(tblInspections.FLT) AS CountOfFLT, Left([FLT],2) AS FaultType, tblFaultCode_FAULTCODES.Description, tblInspections.CRT, tblInspections.strDate " & _
    "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, tblInspections.strDate;"

    Debug.Print sqlString
    CurrentDb.QueryDefs.Delete "Query5"
    CurrentDb.CreateQueryDef "Query5", sqlString

    Me.Refresh
    Me.Requery

    DoCmd.OpenForm "Form3a", acNormal
    DoCmd.Close acForm, "form3c"


    End Sub


    Thanks for your help!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    this
    "WHERE (trim(tblInspections.FLT) & '' <>'') "
    is horrifying.

    can you consider:
    "WHERE " & len(trim(tblInspections.FLT)) & " > 0) "

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jul 2003
    Posts
    292
    I'll consider anything that work.. ! -=)

    I changed the code..

    I get a varibable not defined error.. to the line we just changed

    thanks for your help

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    OUCH!

    i screwed up the parenthesis!

    try:
    "WHERE (" & len(trim(tblInspections.FLT)) & " > 0) "

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Jul 2003
    Posts
    292
    I'm getting a syntax error now...


    sqlString = "SELECT Count(tblInspections.FLT) AS CountOfFLT, Left([FLT],2) AS FaultType, tblFaultCode_FAULTCODES.Description, tblInspections.CRT, tblInspections.strDate " & _
    "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 (" & Len(Trim(tblInspections.FLT)) & " > 0) "" & _
    "GROUP BY Left([FLT],2), tblFaultCode_FAULTCODES.Description, tblInspections.CRT, tblInspections.strDate;"

    thanks for the help

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    one too many " in
    "WHERE (" & Len(Trim(tblInspections.FLT)) & " > 0) "" & _
    at the end

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jul 2003
    Posts
    292
    Izy,

    I removed the " to

    "WHERE (" & Len(Trim(tblInspections.FLT)) & " > 0) " & _

    as suggested.. and im getting an variable not definded again..

    thanks

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i don't see it - sorry.

    (but a speed-up suggestion if it does start to work: use left$() in place of left() whenever you are playing with strings - it's much faster)

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    MERDE!

    sorry my friend: the answer is too stupid - i've been trying to fix your WHERE to a form field, when it is even called tblInspections.FLT - too too too stupid!

    try this one:
    "WHERE (Len(Trim(tblInspections.FLT)) > 0) " & _


    (humbled!) izy



    currently using SS 2008R2

  10. #10
    Join Date
    Jul 2003
    Posts
    292
    I did as you suggested.. and yes! it got rid of the error...

    But I have a dilemma My form is not returning what I want..

    On my form I have three fields.. Two to which the user can enter in dates of records they want to see.. and a third text field to which the user can filter out the result even more ..

    for example..

    the user enters in 03/01/2004 thru 03/30/2004
    and CRT(Contractor Code) of 11

    This would bring back all records between march of 2004 with a CRT of 11

    My form now continues to return all records from the orginal source..


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

    If Not (IsNull(Me.StartDate) Or IsNull(Me.StartDate2)) Then
    sqlString = " Query5c.strDate Between #" & [Forms]![Form3c]![StartDate] & "# And #" & [Forms]![Form3c]![StartDate2] & "# "
    End If
    If Not (IsNull(Me.txtCRT)) Then
    If sqlString <> "" Then sqlString = sqlString & " And"
    sqlString = sqlString & " Query5c.CRT = '" & [Forms]![Form3c]![txtCRT] & "'"
    End If
    '...

    If sqlString <> "" Then sqlString = " Where" & sqlString

    sqlString = "SELECT Count(tblInspections.FLT) AS CountOfFLT, Left$([FLT],2) AS FaultType, tblFaultCode_FAULTCODES.Description, tblInspections.CRT, tblInspections.strDate " & _
    "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 (Len(Trim(tblInspections.FLT)) > 0) " & _
    "GROUP BY Left$([FLT],2), tblFaultCode_FAULTCODES.Description, tblInspections.CRT, tblInspections.strDate;"

    Debug.Print sqlString
    CurrentDb.QueryDefs.Delete "Query5"
    CurrentDb.CreateQueryDef "Query5", sqlString

    Me.Refresh
    Me.Requery

    DoCmd.OpenForm "Form3a", acNormal
    DoCmd.Close acForm, "form3c"


    End Sub


    Any ideas?
    thanks!

Posting Permissions

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