What is wrong with my SQL String

I have a SQL String that should query this table and select the Audit_Type "BAE" and place the results in the combo box drop down (cboQualRevCriteria) on the subform (frmQuality_Review_Subform), but it is giving me everything in the table.

What is wrong with my SQL String and/or Code?

Thanks,

gdunn59
Select Case Me.cboAuditType.Text
  Case "BAE"
   myString = "select [Quality_Review_Criteria] from tblEnrollment_Dept_Criteria_Codes (for IMPORT) where [Audit_Type] = " & "BAE"";"
   Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
   Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
   Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
   DoCmd.GoToControl "cboSystem"

Open in new window

LVL 1
gdunn59Asked:
Who is Participating?
 
Rajkumar GsSoftware EngineerCommented:
In MS Access, if double quotes is used instead of single quotes, you can try this way
Dim myString As String = "select [Quality_Review_Criteria] from tblEnrollment_Dept_Criteria_Codes (for IMPORT) where [Audit_Type] = """ & "BAE" & """;"
0
 
kingjelyCommented:
In the select on the first line, you have a simicolin ;  which ends the select.
0
 
Rajkumar GsSoftware EngineerCommented:
When you execute these two queries any difference in result. ?
I mean whether all the records in table are related to Audit_Type = "BAE" ?

select [Quality_Review_Criteria] from tblEnrollment_Dept_Criteria_Codes (for IMPORT) where [Audit_Type] = "BAE"

select [Quality_Review_Criteria] from tblEnrollment_Dept_Criteria_Codes (for IMPORT)
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
kingjelyCommented:
aww sorry,
Is this mysql or mssql?
0
 
kingjelyCommented:
im getting dumber
0
 
Pratima PharandeCommented:
try this

   myString = "select [Quality_Review_Criteria] from tblEnrollment_Dept_Criteria_Codes (for IMPORT) where [Audit_Type] = 'BAE'"
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
See if this works for

   myString = "select [Quality_Review_Criteria] from tblEnrollment_Dept_Criteria_Codes (for IMPORT) where [Audit_Type] = " & Chr(34) & "BAE" & Chr(34)


mx
0
 
Rajkumar GsSoftware EngineerCommented:
I think, something wrong with syntax - missed single quotes ?

Try modify first line to this
Dim myString As String = "select [Quality_Review_Criteria] from tblEnrollment_Dept_Criteria_Codes (for IMPORT) where [Audit_Type] = '" & "BAE" & "';"
0
 
VRGultomCommented:
DoCmd.GoToControl "cboSystem"


I think you have to set in the combo box component, what value to be display there.

Database query is only query data from database, but to put it in component, you have to set it in the component itself.

Regards
0
 
gdunn59Author Commented:
I have tried several of the solutions and with everyone I get a "Syntax error in From Clause".
0
 
Pratima PharandeCommented:
try this

   myString = "select [Quality_Review_Criteria] from tblEnrollment_Dept_Criteria_Codes where [Audit_Type] = 'BAE'"
0
 
Rajkumar GsSoftware EngineerCommented:
Dim myString As String = "select [Quality_Review_Criteria] from tblEnrollment_Dept_Criteria_Codes (for IMPORT) where [Audit_Type] = """ & "BAE" & """;"

I am not confident in MS Access SQL - What's this ? - (for IMPORT)
Try remove ?

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Try with Brackets around Table Name

   myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes (for IMPORT)] where [Audit_Type] = " & Chr(34) & "BAE" & Chr(34)
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
gdunn59
You would be a lot further ahead if ... you do not have *any* spaces OR special characters in object names. For example ... change:

tblEnrollment_Dept_Criteria_Codes (for IMPORT)

to

tblEnrollmentDeptCriteriaCodesForIMPORT

mx
0
 
gdunn59Author Commented:
Ok, putting the brackets around it seemed to get rid of the error.  I just need to play around with it some more tomorrow to make sure it is doing what I want, and then I will respond and accept a solution.

Thanks everyone,

gdunn59
0
 
gdunn59Author Commented:
Ok, the solution that RajkumarGS provided worked as far as no errors, but the list that should be appearing still isn't pulling the correct data.

I have pasted all of the code to see if someone can figure out why it is not pulling the correct data in the list.

Dim myString As String

If cboRegion = "East" Or cboRegion = "Central" AND Me.cboDept = "Enrollment" Then
        Select Case Me.cboAuditType
            Case "BAE"
myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "BAE" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case "COB"
                myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "COB" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case "Disenrollment"
                myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "Disenrollment" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case "EFT"
                myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "EFT" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case "Enrollment"
                myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "Enrollment" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case "LEP"
                myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "LEP" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case "Maintenance"
                myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "Maintenance" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case "MiscRptEmail"
                myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "MiscRptEmail" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case "OOA"
                myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "OOA" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case "Plan Change"
                myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "Plan Change" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case "POA"
                myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "POA" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case "Reinstatement"
                myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "Reinstatement" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case "Status Reports"
                myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "Status Reports" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case Else
                MsgBox "Subform Can Not Be Updated From This Value"
        End Select
ElseIf cboRegion = "West" And Me.cboDept = "Enrollment" Then
        Select Case Me.cboAuditType
            Case "Enrollment"
                'myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "BAE" & """ group by [Code_Sort];"
                myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "Enrollment" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case "COB"
                myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "COB" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case "Disenrollment"
                myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "Disenrollment" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case "Plan Change"
                myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "Plan Change" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case "OOA Address Change"
                myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "OOA Address Change" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case "Maintenance"
                myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "Maintenance" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case "Reinstatements"
                myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "Reinstatements" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case "ACS"
                myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "ACS" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case "POA"
                myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Audit_Type] = """ & "POA" & """;"
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = myString
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
                Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Enabled = True
                DoCmd.GoToControl "cboSystem"
            Case Else
                MsgBox "Subform Can Not Be Updated From This Value"
        End Select
    End If
End If
   
End Sub

Open in new window

0
 
gdunn59Author Commented:
I think the list isn't correct  because of something with my if statement.

Thanks,
gdunn59
0
 
Rajkumar GsSoftware EngineerCommented:
Change this line
   If cboRegion = "East" Or cboRegion = "Central" And Me.cboDept = "Enrollment" Then
to
   If (cboRegion = "East" Or cboRegion = "Central") And Me.cboDept = "Enrollment" Then

Raj
0
 
gdunn59Author Commented:
Raj,

I tried that and it didn't seem to make a difference.

0
 
Rajkumar GsSoftware EngineerCommented:
Is the 'if' condition is satisfying ?
Raj
0
 
gdunn59Author Commented:
I'm picking everything that is in the IF Statement and then choosing one of the Audit_Types (ie COB, Disenrollments, etc), and it should pull up the Audit_Type list that is for the East and/or Central Region (which the list is the same for these two Regions), and then "Enrollment" for the Dept.

So it should pull up the list for just these.

Thanks,
gdunn59
0
 
gdunn59Author Commented:
Ok, I figured out the problem, I forgot when i first created this the other day I didn't have all the data in the table, now I do.

So I needed to add criteria for the Region to distinguish between East-Central and West.

But something is still wrong in my string.  I have the following:

myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Region] like """ & "East*" AND [Audit_Type] = """ & "COB" & """;"

What is wrong with the syntax?

Thanks,
gdunn59
0
 
gdunn59Author Commented:
Ok, I got it to work.

myString = "select [Quality_Review_Criteria] from [tblEnrollment_Dept_Criteria_Codes] where [Region] like """ & "East*" & """ AND [Audit_Type] = """ & "COB" & """;"

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.