Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Syntax for where with a string and a numeric value

Posted on 2008-10-07
13
Medium Priority
?
269 Views
Last Modified: 2012-05-05
I have an access front end and I'm linked to a SQL backend.
The following syntax worked until I tried adding from 'AND' on.
ReportNo is a numeric field and I also need to filter by TestDescription which is a string.
Thanks in advance.

MySQL = "UPDATE tblMTTestRooms Set RoomNo = '" & Left$(strRoomNo, Len(strRoomNo) - 2) & "' "
                MySQL = MySQL & "WHERE " & strQCReportNo & " = QCReportNo AND & strTestDescription = " & rs2!TestDescription & "'"
                DoCmd.RunSQL (MySQL)
0
Comment
Question by:BobRosas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 22664859

MySQL = "UPDATE tblMTTestRooms Set RoomNo = '" & Left$(strRoomNo, Len(strRoomNo) - 2) & "' "
MySQL = MySQL & "WHERE " & strQCReportNo & " = QCReportNo & " AND & strTestDescription = " & rs2!TestDescription & "'"
DoCmd.RunSQL (MySQL)

Open in new window

0
 

Author Comment

by:BobRosas
ID: 22664889
Thanks for your quick response.
Maybe it's because I'm working in access but the 2nd line has a syntax error and I can't compile.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22664917
You were right. I rushed it. Try this one.

Future note to make it easier you can use an ampersand and an underscore > & _  < to do line continuations.


MySQL = "UPDATE tblMTTestRooms Set RoomNo = '" & Left$(strRoomNo, Len(strRoomNo) - 2) & "' " & _
      "WHERE strQCReportNo = "& QCReportNo & " AND strTestDescription = '" & rs2!TestDescription & "'"
DoCmd.RunSQL (MySQL)

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:BobRosas
ID: 22670925
When I compiled I got variable not defined for QCReportNo.  So I added rs2! in front of the field and it compiled (I'm unclear why since it worked before) but anyway when I run the query it now asks for parameters for both strQCReportNo and strTestDescription.  Another thing it didn't do when my code only contained one variable.  Can you explain what I'm doing wrong.  If I step through the code the str variables show the correct values.  
Thanks again!

MySQL = "UPDATE tblMTTestRooms Set RoomNo = '" & Left$(strRoomNo, Len(strRoomNo) - 2) & "' " & _
                          "WHERE strQCReportNo = " & rs2!QCReportNo & " AND strTestDescription = '" & rs2!TestDescription & "'"
                    DoCmd.RunSQL (MySQL)

Open in new window

0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22671014
Can I see the rest of your code?

The  rs2 is generally meaning that you are pulling the data from a recordset. If the recordset isn't open then it will ask for them thinking they are parameters.

Where is the  rs2!QCReportNo and  rs2!TestDescription values coming from -- f orm, some other part of the code? Once we figure that out the rest should be easy.
0
 

Author Comment

by:BobRosas
ID: 22671115
Depending on user input a different query may be run but all the querys create the same make table (rs2) and then fall thru my code.  I've attached my code.
Thanks
Private Sub CalcRoomNo()
On Error GoTo Err_Report_Load
'Each Test Performed can be done on more than 1 room.  The following code cycles through the code
'and saves all room numbers that correspond to a test performed into 1 record, then saves it to a make table.
'This make table is the source for fsubDailyTests
    strTestDescription = ""
    strTestRoomDesc = ""
    strRoomNo = ""
    DoCmd.SetWarnings False
    'Date Only
    If IsNull(cboReleaseNo) And IsNull(cboQCRptNo) Then
        Set qdf = CurrentDb.QueryDefs("qryTestRoomsDate")
        qdf.Parameters("[Forms]![frmPrevDailyDFT]![cboFmDate]") = [Forms]![frmPrevDailyDFT]![cboFmDate]
        qdf.Parameters("[Forms]![frmPrevDailyDFT]![cboToDate]") = [Forms]![frmPrevDailyDFT]![cboToDate]
        DoCmd.OpenQuery "qryMTTestRoomsDaily"
    Else
        'QCRptNo Only
        If IsNull(cboReleaseNo) Then
            Set qdf = CurrentDb.QueryDefs("qryTestRooms")
            qdf.Parameters("[Forms]![frmPrevDailyDFT]![cboQCRptNo]") = [Forms]![frmPrevDailyDFT]![cboQCRptNo]
            DoCmd.OpenQuery "qryMTTestRooms"
        Else
            'ReleaseNo Only
            If IsNull(cboFmDate) And IsNull(cboToDate) Then
                Set qdf = CurrentDb.QueryDefs("qryTestRoomsRel")
                qdf.Parameters("[Forms]![frmPrevDailyDFT]![txtReleaseNo]") = [Forms]![frmPrevDailyDFT]![txtReleaseNo]
                DoCmd.OpenQuery "qryMTTestRoomsRel"
            Else
                'ReleaseNo & Date
                Set qdf = CurrentDb.QueryDefs("qryTestRoomsRelDate")
                qdf.Parameters("[Forms]![frmPrevDailyDFT]![txtReleaseNo]") = [Forms]![frmPrevDailyDFT]![txtReleaseNo]
                qdf.Parameters("[Forms]![frmPrevDailyDFT]![cboFmDate]") = [Forms]![frmPrevDailyDFT]![cboFmDate]
                qdf.Parameters("[Forms]![frmPrevDailyDFT]![cboToDate]") = [Forms]![frmPrevDailyDFT]![cboToDate]
                DoCmd.OpenQuery "qryMTTestRoomsRelDate"
            End If
        End If
    End If
    DoCmd.SetWarnings True
    Set rs = qdf.OpenRecordset
    Set rs2 = CurrentDb.OpenRecordset("tblMTTestRooms", DB_OPEN_DYNASET)
    With rs
        strQCReportNo = rs!QCReportNo
        strQCReportNoCur = rs!QCReportNo
        strTestDescription = rs!TestDescription  'this saves off test description
        strTestDescriptionCur = rs!TestDescription 'this keeps field so when eof code still works
        Do While Not .EOF
        If strTestDescription = rs!TestDescription And strQCReportNo = rs!QCReportNo Then
            strRoomNo = strRoomNo & rs!RoomNo & ", "
            .MoveNext
        Else
            If strTestDescription = strTestDescriptionCur Or strQCReportNo = strQCReportNoCur Then
                With rs2
                    DoCmd.SetWarnings False
                    MySQL = "UPDATE tblMTTestRooms Set RoomNo = '" & Left$(strRoomNo, Len(strRoomNo) - 2) & "' " & _
                          "WHERE strQCReportNo = " & rs2!QCReportNo & " AND strTestDescription = '" & rs2!TestDescription & "'"
                    DoCmd.RunSQL (MySQL)
                    DoCmd.SetWarnings True
                    rs2.MoveNext
                    strQCReportNo = rs!QCReportNo
                    strQCReportNoCur = rs!QCReportNo
                    strRoomNo = "" 'reset value to null for each version Id
                End With
            Else
            End If
        End If
        Loop
    End With
    rs.Close
    rs2.Close
Err_Report_Load:
    If Err = 0 Then 'if table is empty code jumps here from "Do While Not .eof" so skip message
        Exit Sub
    End If
    If Err = 3021 Then 'No Current Record"
        With rs2
            DoCmd.SetWarnings False
            MySQL = "UPDATE tblMTTestRooms Set RoomNo = '" & Left$(strRoomNo, Len(strRoomNo) - 2) & "' " & _
                    "WHERE strQCReportNo = " & rs2!QCReportNo & " AND strTestDescription = '" & rs2!TestDescription & "'"
            DoCmd.RunSQL (MySQL)
            DoCmd.SetWarnings True
            strRoomNo = "" 'reset value to null for each version Id
        End With
        rs.Close
        rs2.Close
    Else
        MsgBox Err.Description
    End If
End Sub

Open in new window

0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22671659
Try this on:
Private Sub CalcRoomNo()
'On Error GoTo Err_Report_Load
'Each Test Performed can be done on more than 1 room.  The following code _
 cycles through the code and saves all room numbers that correspond to a _
 test performed into 1 record, then saves it to a make table.
 
'This make table is the source for fsubDailyTests
    strTestDescription = ""
    strTestRoomDesc = ""
    strRoomNo = ""
    DoCmd.SetWarnings False
    'Date Only
    If IsNull(cboReleaseNo) And IsNull(cboQCRptNo) Then
        Set QDF = CurrentDb.QueryDefs("qryTestRoomsDate")
        QDF.Parameters("[Forms]![frmPrevDailyDFT]![cboFmDate]") = [Forms]![frmPrevDailyDFT]![cboFmDate]
        QDF.Parameters("[Forms]![frmPrevDailyDFT]![cboToDate]") = [Forms]![frmPrevDailyDFT]![cboToDate]
        DoCmd.OpenQuery "qryMTTestRoomsDaily"
    Else
        'QCRptNo Only
        If IsNull(cboReleaseNo) Then
            Set QDF = CurrentDb.QueryDefs("qryTestRooms")
            QDF.Parameters("[Forms]![frmPrevDailyDFT]![cboQCRptNo]") = [Forms]![frmPrevDailyDFT]![cboQCRptNo]
            DoCmd.OpenQuery "qryMTTestRooms"
        Else
            'ReleaseNo Only
            If IsNull(cboFmDate) And IsNull(cboToDate) Then
                Set QDF = CurrentDb.QueryDefs("qryTestRoomsRel")
                QDF.Parameters("[Forms]![frmPrevDailyDFT]![txtReleaseNo]") = [Forms]![frmPrevDailyDFT]![txtReleaseNo]
                DoCmd.OpenQuery "qryMTTestRoomsRel"
            Else
                'ReleaseNo & Date
                Set QDF = CurrentDb.QueryDefs("qryTestRoomsRelDate")
                QDF.Parameters("[Forms]![frmPrevDailyDFT]![txtReleaseNo]") = [Forms]![frmPrevDailyDFT]![txtReleaseNo]
                QDF.Parameters("[Forms]![frmPrevDailyDFT]![cboFmDate]") = [Forms]![frmPrevDailyDFT]![cboFmDate]
                QDF.Parameters("[Forms]![frmPrevDailyDFT]![cboToDate]") = [Forms]![frmPrevDailyDFT]![cboToDate]
                DoCmd.OpenQuery "qryMTTestRoomsRelDate"
            End If
        End If
    End If
    DoCmd.SetWarnings True
    Set RS = QDF.OpenRecordset
    Set rs2 = CurrentDb.OpenRecordset("tblMTTestRooms", DB_OPEN_DYNASET)
    
If RS.EOF = False Then
    RS.MoveFirst
    strQCReportNo = RS!QCReportNo
    strTestDescription = RS!TestDescription  'this saves off test description
Else
    MsgBox "No Data", vbExclamation, "Exiting Fuction"
    Set RS = Nothing
    Exit Sub
End If
    
Do Until RS.EOF = True
    If strTestDescription = RS!TestDescription And strQCReportNo = RS!QCReportNo Then
        strRoomNo = strRoomNo & RS!RoomNo & ", "
    Else
        mySQL = "UPDATE tblMTTestRooms Set RoomNo = '" & Left(strRoomNo, Len(strRoomNo) - 2) & "' " & _
              "WHERE strQCReportNo = " & strQCReportNo & " " & _
              "AND strTestDescription = '" & strTestDescription & "'"
        DoCmd.SetWarnings False
        DoCmd.RunSQL mySQL, True
        DoCmd.SetWarnings True
        strQCReportNo = RS!QCReportNo
        strTestDescription = RS!TestDescription
        strRoomNo = "" 'reset value to null for each version Id
    End If
    RS.MoveNext
Loop
 
Set RS = Nothing
    
Exit Sub
 
Err_Report_Load:
    If Err = 0 Then 'if table is empty code jumps here from "Do While Not .eof" so skip message
        Exit Sub
    Else
        MsgBox Err.Description
    End If
End Sub

Open in new window

0
 

Author Comment

by:BobRosas
ID: 22671775
I increased your points just because of all your extra effort.  Your code will go a long way in helping me with other code.  
Unfortunately I'm still getting the 'Enter Parameter Value" box for strQCReportNo and strTestDescription.
I just don't know how to fix it.
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 1400 total points
ID: 22671906
Try this.

The field name right after the WHERE and the AND should be fields in your  tblMTTestRooms table.
    If strTestDescription = RS!TestDescription And strQCReportNo = RS!QCReportNo Then
        strRoomNo = strRoomNo & RS!RoomNo & ", "
    Else
        mySQL = "UPDATE tblMTTestRooms Set RoomNo = '" & Left(strRoomNo, Len(strRoomNo) - 2) & "' " & _
              "WHERE QCReportNo = " & strQCReportNo & " " & _
              "AND TestDescription = '" & strTestDescription & "'"
     

Open in new window

0
 

Author Comment

by:BobRosas
ID: 22672816
I've attached more code in hopes that it will help.  I'm sure it's something I haven't explained is why I'm having this problem.
The 1st recordset (rs) collects all the room#'s but each room# is in a separate record and I need them as a string.
The 2nd recordset (rs2) is a make table with the same fields and once I gather the roomNo as a string I want to save it to this make table.  I then use the make table (tblMTTestRooms) to run the report.  
Because the fields are the same in both recordsets is that the problem?  Because the fields right after the WHERE and the AND do exist in tblMTTestRooms.
Ex data of rs
QCReportNo	TestDescription	RoomNo	 Results
2173	         Adhesion Test	2	 See attached.
2173	         Adhesion Test	Adhesion1	 See attached.
2173	         Adhesion Test	Adhesion2	 See attached.
2173	         Blotter Test	3	 See attached.
2173	         Blotter Test	Blotter1	 See attached.
2173	         Blotter Test	Blotter2	 See attached.
2173	         Dry Film Thickness	Dry Film1	 See attached.
2173	         Dry Film Thickness	Dry Film2	 See attached.
 
Ex of rs2/tblMTTestRooms  (RoomNo is blank originally and the above code is supposed to fill RoomNo with a string)
For example RoomNo for AdhesionTest should be "2, Adhesion1, Adhesion2"
based on the above data.
 
QCReportNo DailyDate  TestDescId TestDescription RoomNo Results
2173	  8/21/2008	109   Adhesion Test	 See attached.
2173	  8/21/2008	108   Blotter Test   	 See attached.
2173	  8/21/2008	86    Dry Film Thickness 	 See attached.

Open in new window

0
 

Author Comment

by:BobRosas
ID: 22680239
I've attached the code that I finally got to work.  Thank you so much for your help.  I have a great code example for future projects.
You EE guys are great!

MySQL = "UPDATE tblMTTestRooms Set RoomNo = '" & Left$(strRoomNo, Len(strRoomNo) - 2) & "' " & _
                " WHERE QCReportNo = " & strQCReportNo & _
                " AND TestDescription = '" & strTestDescription & "'"

Open in new window

0
 

Author Closing Comment

by:BobRosas
ID: 31504055
Thanks so much!
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22680643
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question