Solved

What is wrong with my SQL String

Posted on 2011-02-24
23
351 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:gdunn59
  • 8
  • 6
  • 3
  • +3
23 Comments
 
LVL 8

Expert Comment

by:kingjely
ID: 34977338
In the select on the first line, you have a simicolin ;  which ends the select.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34977342
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
 
LVL 8

Expert Comment

by:kingjely
ID: 34977343
aww sorry,
Is this mysql or mssql?
0
 
LVL 8

Expert Comment

by:kingjely
ID: 34977347
im getting dumber
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 34977351
try this

   myString = "select [Quality_Review_Criteria] from tblEnrollment_Dept_Criteria_Codes (for IMPORT) where [Audit_Type] = 'BAE'"
0
 
LVL 75
ID: 34977355
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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34977360
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
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 300 total points
ID: 34977369
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
 
LVL 4

Expert Comment

by:VRGultom
ID: 34977375
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
 

Author Comment

by:gdunn59
ID: 34977397
I have tried several of the solutions and with everyone I get a "Syntax error in From Clause".
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 34977402
try this

   myString = "select [Quality_Review_Criteria] from tblEnrollment_Dept_Criteria_Codes where [Audit_Type] = 'BAE'"
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34977405
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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 200 total points
ID: 34977406
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
 
LVL 75
ID: 34977418
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
 

Author Comment

by:gdunn59
ID: 34977498
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
 

Author Comment

by:gdunn59
ID: 34993414
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
 

Author Comment

by:gdunn59
ID: 34993937
I think the list isn't correct  because of something with my if statement.

Thanks,
gdunn59
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34994413
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
 

Author Comment

by:gdunn59
ID: 34994653
Raj,

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

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34994665
Is the 'if' condition is satisfying ?
Raj
0
 

Author Comment

by:gdunn59
ID: 34998964
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
 

Author Comment

by:gdunn59
ID: 35000039
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
 

Author Comment

by:gdunn59
ID: 35000095
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

920 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

16 Experts available now in Live!

Get 1:1 Help Now