Link to home
Start Free TrialLog in
Avatar of gdunn59
gdunn59

asked on

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

Avatar of kingjely
kingjely
Flag of Australia image
In the select on the first line, you have a simicolin ;  which ends the select.
Avatar of Rajkumar Gs
Rajkumar Gs
Flag of India image
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)
Avatar of kingjely
kingjely
Flag of Australia image
aww sorry,
Is this mysql or mssql?
Avatar of kingjely
kingjely
Flag of Australia image
im getting dumber
Avatar of Pratima
Pratima
Flag of India image
try this

   myString = "select [Quality_Review_Criteria] from tblEnrollment_Dept_Criteria_Codes (for IMPORT) where [Audit_Type] = 'BAE'"
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image
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
Avatar of Rajkumar Gs
Rajkumar Gs
Flag of India image
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" & "';"
ASKER CERTIFIED SOLUTION
Avatar of Rajkumar Gs
Rajkumar Gs
Flag of India image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of VRGultom
VRGultom
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
Avatar of gdunn59
gdunn59

ASKER

I have tried several of the solutions and with everyone I get a "Syntax error in From Clause".
Avatar of Pratima
Pratima
Flag of India image
try this

   myString = "select [Quality_Review_Criteria] from tblEnrollment_Dept_Criteria_Codes where [Audit_Type] = 'BAE'"
Avatar of Rajkumar Gs
Rajkumar Gs
Flag of India image
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 ?

SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image
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
Avatar of gdunn59
gdunn59

ASKER

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
Avatar of gdunn59
gdunn59

ASKER

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

Avatar of gdunn59
gdunn59

ASKER

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

Thanks,
gdunn59
Avatar of Rajkumar Gs
Rajkumar Gs
Flag of India image
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
Avatar of gdunn59
gdunn59

ASKER

Raj,

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

Avatar of Rajkumar Gs
Rajkumar Gs
Flag of India image
Is the 'if' condition is satisfying ?
Raj
Avatar of gdunn59
gdunn59

ASKER

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
Avatar of gdunn59
gdunn59

ASKER

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
Avatar of gdunn59
gdunn59

ASKER

Ok, I got it to work.

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