SQL statement creating query doesn't seem to be working correctly 1

[kjv1611]

I have tried tinkering with these formats a little, and can't seem to get the data to pull out. Does anyone see any contextual/format type problems here? The query is created and runs, but the data (which I know is there) is not showing up:

Private Sub CreateListSource()
    Dim strQueryName As String
    Dim myQuery As QueryDef
    strQueryName = "ListSource"
    Dim strSQL As String
    
    strSQL = "SELECT A.[Status] " & _
        "FROM tblCompletedStatuses  A " & _
        "WHERE ((A.[TeamLeader]) = Forms!frmAuditReviewSelection!cmbTeamLeader) " & _
        "AND ((A.[Year]) = Forms!frmAuditReviewSelection!cmbYear) " & _
        "AND ((A.[Month]) = Forms!frmAuditReviewSelection.cmbMonth) " & _
        "AND ((A.[AuditType]) = Forms!frmAuditReviewSelection.frameAuditType);"
    Set myQuery = CurrentDb.CreateQueryDef([strQueryName], strSQL)
    DoCmd.OpenQuery strQueryName
End Sub

Any suggestions would be greatly appreciated.. if need more detail, please ask.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[sfreeman]

Try this...

DoCmd.OpenQuery myQuery

 

[lespaul]

try this:

Private Sub CreateListSource()
   Dim strQueryName As String
   Dim myQuery As QueryDef
   strQueryName = "ListSource"
   Dim strSQL As String
    
   strSQL = "SELECT A.[Status] " & _
    "FROM tblCompletedStatuses  A " & _
    "WHERE A.[TeamLeader] = '" & Forms!frmAuditReviewSelection!cmbTeamLeader & "' "& _
    "AND A.[Year] = " & Forms!frmAuditReviewSelection!cmbYear & _
    "AND A.[Month] = " & Forms!frmAuditReviewSelection.cmbMonth & _
    "AND A.[AuditType] = '" & Forms!frmAuditReviewSelection.frameAuditType & "';"
    Set myQuery = CurrentDb.CreateQueryDef([strQueryName], strSQL)
    DoCmd.OpenQuery strQueryName
End Sub

Some of this depends on what the field types are in the table. But if AuditType isn't a string, remove the ' marks from it, the same for TeamLeader. Are the Year and Month fields dates? You should rename them something more meaningful since those are key words.

Leslie

 

[kjv1611]

Okay, based on the suggestion for 's added in, I changed it to this, it works, but again, no data comming up in the query...

Private Sub CreateListSource()
    Dim strQueryName As String
    Dim myQuery As QueryDef
    strQueryName = "ListSource"
    Dim strSQL As String

    strSQL = "SELECT A.[Status] " & _
        "FROM tblCompletedStatuses  A " & _
        "WHERE ((A.[TeamLeader]) = 'Forms!frmAuditReviewSelection!cmbTeamLeader') " & _
        "AND ((A.[Year]) = 'Forms!frmAuditReviewSelection!cmbYear') " & _
        "AND ((A.[Month]) = 'Forms!frmAuditReviewSelection.cmbMonth') " & _
        "AND ((A.[AuditType]) = 'Forms!frmAuditReviewSelection.frameAuditType');"
    Set myQuery = CurrentDb.CreateQueryDef([strQueryName], strSQL)
    DoCmd.OpenQuery strQueryName
End Sub

and all applicapable criteria for this query are in text format. The Year and Month are best shown as text, not date/time, b/c they are not specific dates - I have other fields for that. The month takes these type values:
January
February
1st Quarter
April
May

and so forth...

also,
sfreeman,
Thanks for the effort with..
DoCmd.OpenQuery myQuery
But that would be incorrect format, as the OpenQuery method needs the string name of the query to run, not the actual query object. Believe me, I tried that way before, and got the appropriate error...

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Oops, I stand corrected on one point: the data type WAS incorrect in the table - I was looking at the wrong table. I fixed that, and now it seems to be correct with the code as I posted in the last posting. So a star to lespaul for pointing out the tiny but extremely important detail!


Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

I do need to point out, though, that the code from lespaul's post is not what worked - tried, but many errors; but rather the suggestion regarding the datatypes - I had AuditType set to Number in that table, but it should have been Text - I noticed this when compared with another table that stores the same value.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Um, oops again - I was looking at the table, not the query. no wonder... too bad we don't have an undo-post option here to remove oopses. lol I'll keep working on it and post back for sure. I still am having the same results, but the data type was incorrect for AuditType.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[dhookom]

Did you try go to your database window and open the query in design view? This will give you better information than we can provide. Come back with the full sql view.
My preference would be to not store text values for year and month etc. These have very good and consistent numeric values that should be used.

If the TeamLeader, Year, Month, and AuditType

strSQL = "SELECT A.[Status] " & _
  "FROM tblCompletedStatuses  A " & _
  "WHERE [TeamLeader] = '" & Forms!frmAuditReviewSelection!cmbTeamLeader & "' " & _
  "AND [Year] = '" & Forms!frmAuditReviewSelection!cmbYear & "' " & _
  "AND [Month] = '" & Forms!frmAuditReviewSelection.cmbMonth & "' " & _
  "AND [AuditType] = '" & Forms!frmAuditReviewSelection.frameAuditType & "';"
   'see what's going on
   DoCmd.OpenQuery strQueryName, acViewDesign

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kjv1611]

Hmm.. I believe I've narrowed down the problem. I created a separate query comparing everything by the AuditType, and it showed the data correctly. When I added in the data type comparison, the query returned no results. The value in the table is stored as a text, but I am pulling the data from a frame (option group) that returns values 1 or 2. Could that be my problem? I've tried both Number and Text formats for the AuditType in the table. Any ideas?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[dhookom]

You can substitute any value for the control's numeric value with syntax like:
Choose(Forms!frmAuditReviewSelection.frameAuditType, "value if 1", "value if 2")
This all depends on the actual value stored in your table in the AuditType field (which you haven't provided).

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kjv1611]

well, the values are either 1 or 2, that's it

If one toggle button is selected, the table stores it as 1
If the second toggle button, then the table stores a 2

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[dhookom]

Did you look at your SQL view of the query as suggested? I expect that you may be trying to match " 1" with "1" which won't work. Try:
Trim(Str(Forms!frmAuditReviewSelection.frameAuditType))


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[lespaul]

and again, from your table design, what kind of data are you storing in AuditType. Yes it's a 1 or a 2, but are those the numbers one and two or strings?

Leslie

 

[kjv1611]

Tried that, still no luck. Quite frankly, it doesn't make sense to me. "1" should equal "1", right?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

For a test, I did the following with the Form value only:
pcode]
Private Sub TestOptionValues()
Dim intAudit As Integer
Dim strAudit As Integer
Dim intSum As Integer

intAudit = Forms!frmAuditReviewSelection.FrameAuditType
strAudit = Forms!frmAuditReviewSelection.FrameAuditType

Debug.Print "Audit type = " & intAudit
Debug.Print "Audit type = " & strAudit

intSum = intAudit * 2
Debug.Print intSum
intSum = strAudit * 2
Debug.Print intSum
End Sub
[/code]
The immediate window showed this:

Audit type = 1
Audit type = 1
 2 
 2

Now I will try with the table values..

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[dhookom]

Note the space before the 2s. That is why I suggested you view the SQL view of your saved query. Can you possibly post the sql view so that we can see it?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[lespaul]

Ok, stephen, this shouldn't be this hard.

if you are trying to store strings, surround them with quotes.

if you are trying to store numbers, do not surround them with quotes.

Check the table design. If the field is a string in the table design, it needs quotes in the query, if the field is a number you don't need the quotes in the query.





SELECT * FROM TABLE WHERE AuditType = "1"

will work if in the table design AuditType is a string field.








SELECT * FROM TABLE WHERE AuditType = 1

will work if in the table design AuditType is a number field.





If you are passing a variable name:

string (puts the ' around the variable name):

strSQL = "SELECT * FROM TABLE WHERE AuditType = ' " & variableName & " ' "

(I separated the " ' for easy reading above, you should remove the extra space for actual use:
strSQL = "SELECT * FROM TABLE WHERE AuditType = '" & variableName & "'")




integer (no ' around variable name):

strSQL = "SELECT * FROM TABLE WHERE AuditType = " & variablename

Leslie

 

[kjv1611]

I agree.. it SHOULD not be that hard.. and actually, it isn't. I believe I found the solution - basically was an oversite on my part. The table did not have any data in the Audit Type field (boy do I hate to admit it). I had a typo in my code in a different form, where I put AuditType instead of Audit Type, but that table stores it as Audit Type (with a space), and I think that was causing the problems.. I have fixed that code now, and just need to redo the entries into the table -for testing of this query, I will just manually update the table - only 5 or 6 entries anyways.. for testing.

Thanks to all efforts from everyone, but I did find the problem. it had nothing to do with formats and such, but rather to do with a typo on a different form. Yuck!
[BLUSH]


Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Yep, as suspected.. all that hairpulling, etc for nothing! lol..

Boy, it's scary how a small oversite such as adding one space or taking away one space in only one location can reak so much havok!!

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV