Solved

SQL Syntax for where with a string and a numeric value

Posted on 2008-10-07
13
252 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
  • 7
  • 6
13 Comments
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility

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
Comment Utility
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.
Comment Utility
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
 

Author Comment

by:BobRosas
Comment Utility
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.
Comment Utility
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
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
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
Comment Utility
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 350 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks so much!
0
 
LVL 38

Expert Comment

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now