Solved

What is wrong with my SQL String

Posted on 2011-02-24
23
354 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
[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
  • 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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
 
LVL 1

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
 
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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) 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
 
LVL 1

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
 
LVL 1

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
 
LVL 1

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
 
LVL 1

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
 
LVL 1

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
 
LVL 1

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
 
LVL 1

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

739 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