Access bug? Not displaying query results... kind of.

[CoreyReynolds]

Hey all,

This may be the dumbest thing I've ever seen. I'm creating a queryDef in VBA for access. Here's a sample of the query it creates:

SELECT tblEquipment.equipmentID, tblSample.sampleDate, tblSampleResults.Chromium, IIf([x]=True,'X',(IIf([u]=True,'U',' '))) AS Critical
 
FROM (((tblEquipment 
INNER JOIN tblCompartment ON tblEquipment.[equipmentID] = tblCompartment.[equipmentID]) 
INNER JOIN tblSample ON tblCompartment.[compartmentAutoID] = tblSample.[compartmentSpecific]) 
INNER JOIN tblSampleResults ON tblSample.[sampleNumber] = tblSampleResults.[sampleCode]) 
LEFT JOIN tblSignificant ON tblSampleResults.sampleID = tblSignificant.sampleCode
 
WHERE (((tblEquipment.equipmentID)=31757 Or (tblEquipment.equipmentID)=31774 Or (tblEquipment.equipmentID)=39858 Or (tblEquipment.equipmentID)=39879 Or (tblEquipment.equipmentID)=52718 Or (tblEquipment.equipmentID)=52832 Or (tblEquipment.equipmentID)=52841 Or (tblEquipment.equipmentID)=52842 Or (tblEquipment.equipmentID)=52843 Or (tblEquipment.equipmentID)=81076 Or (tblEquipment.equipmentID)=89801 Or (tblEquipment.equipmentID)=102055 Or (tblEquipment.equipmentID)=102850 Or (tblEquipment.equipmentID)=109801 Or (tblEquipment.equipmentID)=109898 Or (tblEquipment.equipmentID)=109919 Or (tblEquipment.equipmentID)=109920 Or (tblEquipment.equipmentID)=111722 Or (tblEquipment.equipmentID)=121301 Or (tblEquipment.equipmentID)=190860 Or (tblEquipment.equipmentID)=190933 Or (tblEquipment.equipmentID)=239817 Or (tblEquipment.equipmentID)=372013 Or (tblEquipment.equipmentID)=372053 Or (tblEquipment.equipmentID)=372227 Or (tblEquipment.equipmentID)=379908) 
AND ((tblSample.sampleDate)>=#3/3/2007# And (tblSample.sampleDate)<=#12/11/2009#) AND ((tblCompartment.compartmentCode)=1) AND ((tblSignificant.field) Is Null Or (tblSignificant.field)='Chromium'))
ORDER BY tblEquipment.equipmentID;

Now here's the kicker, if I open the query it doesn't show any results. BUT (and this is wild) if I change anything it will display. Say I enter the query designer and show, and then rehide something, change the one column to "Critica" instead of "Critical", ANYTHING - It will display the proper results!

I have no idea why this could happen. it happened once I put in the conditional above that reads " ((tblSignifican t.field) Is Null Or (tblSignificant .field)='Chromi um'))", but I need it there. Until I put that in everything was working fine. I thought that maybe "field" was a reserved word so I just tried changing it to "fieldName" and it still doesn't work.

Help! :(

[CoreyReynolds]

Here's my no good code btw:

(won't let me edit original reply or add code tags to the below, not sure why sorry.

Private Sub btnTrend_Click()
 
'Create Query
Dim query As String
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Dim mySql As String
Dim valSelect As Variant
Dim strValue As String
Dim firstEquip As String
firstEquip = True
Dim ValSelectE As Variant
Dim strValueE As String
Dim valSel As String
 
 
 
 
Dim chartLabel As String
chartLabel = "Sample Result Trending - " & Me.lstCompChoice.Column(1) & " : " & Me.equipmentID.Value & " [" & Me.equipmentDescription.Value & "]"
'Ensure proper information is selected
If Me.lstCompChoice.ItemsSelected.Count > 0 And Me.lstTrendChoice.ItemsSelected.Count > 0 And Me.txtDateStart <> "" And Me.txtDateEnd <> "" Then
 
'Create begining of query
mySql = "SELECT tblEquipment.equipmentID, tblSample.sampleDate, "
 
 
    'This section just adds the particular component to the SQL String
    For Each valSelect In Me.lstTrendChoice.ItemsSelected
        If lstTrendChoice.Selected(valSelect) Then
            mySql = mySql & " tblSampleResults." & Me.lstTrendChoice.ItemData(valSelect) & ","
            valSel = (Me.lstTrendChoice.ItemData(valSelect))
 
        End If
    Next valSelect
mySql = mySql & " IIf([x]=True,'X',(IIf([u]=True,'U',' '))) AS Critical,"
'Clip last useless comma
mySql = Left(mySql, Len(mySql) - 1)
 
'Create query to turn into chart
mySql = mySql & " FROM (((tblEquipment INNER JOIN tblCompartment ON tblEquipment.[equipmentID] = tblCompartment.[equipmentID])" & _
" INNER JOIN tblSample ON tblCompartment.[compartmentAutoID] = tblSample.[compartmentSpecific]) INNER JOIN" & _
" tblSampleResults ON tblSample.[sampleNumber] = tblSampleResults.[sampleCode] " & _
") Left JOIN tblSignificant ON tblSampleResults.sampleID = tblSignificant.sampleCode" & _
" WHERE tblCompartment.compartmentCode=" & Me.lstCompChoice.Value
 
'Insert conditional of equipment loop
For Each valSelect In Me.Combo7.ItemsSelected
    If Me.Combo7.Selected(valSelect) Then
        If firstEquip Then
            mySql = mySql & " And (tblEquipment.equipmentID=" & Me.Combo7.ItemData(valSelect)
            firstEquip = False
        Else
            mySql = mySql & " OR tblEquipment.equipmentID=" & Me.Combo7.ItemData(valSelect)
        End If
    End If
Next valSelect
 
mySql = mySql & ") AND (tblSample.sampleDate >= #" & Me.txtDateStart.Value & "# and tblSample.sampleDate <= #" & Me.txtDateEnd.Value & "#)"
mySql = mySql & "AND (tblSignificant.field ='" & Replace(Me.lstTrendChoice.Value, " ", "") & "' OR tblSignificant.field = null)" & " ORDER BY tblEquipment.equipmentID ASC;"
 
'delete if old temp query exists
Dim qdf As Variant
For Each qdf In db.QueryDefs
If qdf.Name = "qryTemp" Then
db.QueryDefs.Delete "qryTemp"
Exit For
End If
Next
 
 
'create new query
 
db.CreateQueryDef "qryTemp", mySql
 
CreateDAOChartM "qryTemp", valSel
 
 
 
 
End If
 
'Pass Query into chart creation method
End Sub

[ADezii]

A couple of items immediately stood out, but I am not really sure if they are relevant.

1. You do not need to check the Selected Property of each Item in the ItemsSelected Collection, since these Items by nature are already Selected. This is redundant. Your For...Next Loops should read:
   

   Dim valSelect As Variant
   Dim mySQL As String
   Dim valSel As String
    
   For Each valSelect In Me.lstTrendChoice.ItemsSelected
     mySQL = mySQL & " tblSampleResults." & Me.lstTrendChoice.ItemData(valSelect) & ","
       valSel = (Me.lstTrendChoice.ItemData(valSelect))
   Next valSelect
    

   For Each valSelect In Me.Combo7.ItemsSelected
     If firstEquip Then
       mySQL = mySQL & " And (tblEquipment.equipmentID=" & Me.Combo7.ItemData(valSelect)
         firstEquip = False
     Else
       mySQL = mySQL & " OR tblEquipment.equipmentID=" & Me.Combo7.ItemData(valSelect)
     End If
   Next valSelect

2. Unless I missed something, I see no relevance as far as the valSel Variable is concerned (Code Line #7 above - first Code Block)
3. Kindly put your Code within Code Tags along with the proper indentation, and I'll be happy to look at it in more detail. Repost the Code if necessary.

[CoreyReynolds]

Sorry about the code - the option wasn't there (I'm not sure why).
I did figure out what the issue was - when I changed an option in the designer Access kind of just went "Well look at all this ugly code! Let me clean it up for you!" and did something that made it work in the process of reorganizing. That "thing" was a parenthesis issue. I just figured out what it did and changed it into my code.

I also updated it with your suggestions and everything is tip top.

In closing: Access saw and error and fixed it (wait what??? :) )

[missinglinq]

BTW, Corey, you have to click on the "Go Advanced" button below the editing screen to add code tags with an icon. From there hilite the code and click on the pound (#) sign.

Linq ;0)>