Solved

What is wrong with my SQL String

Posted on 2011-02-24
23
355 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

628 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