Querying a query table in a report using VB 1

[falcon7a]

I have a report in which I would like to display "Yes" if a certain entry exists in a query table. I am using an If loop as shown below. I am getting an error at the line If qryTempData.EPCStatusDesc = Dock Door. EPCStatusDesc is a field in the query table. How can i get "Yes" to be printed if the text "Dock Door" exists in the Query Table?

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim strSQL As String
Set MyDB = CurrentDb()

strSQL = "Select EPCStatusDesc from qryItemData Where EPCStatusDesc = 'Dock Door'"
Set qdef = MyDB.CreateQueryDef("qryTempData", strSQL)

If qryTempData.EPCStatusDesc = Dock Door Then
Text19.Value = "Yes"
Else
Text19.Value = "No"
End If
End Sub

Thanks.

 

[sfreeman]

I do not believe you are referencing Dock Door correctly. Need quotes.



Sam_F
"90% of the problem is asking the right question.

 

[Golom]

The way your query is set up, you are guaranteed that EVERY record will contain "Dock Door". Note however that you have only created the query ... you have not executed it. You may need something like

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
[COLOR=red]Dim rs   As DAO.Recordset[/color]
Dim strSQL As String
Set MyDB = CurrentDb()

strSQL = "Select EPCStatusDesc from qryItemData Where EPCStatusDesc = 'Dock Door'"
Set qdef = MyDB.CreateQueryDef("qryTempData", strSQL)
[COLOR=red]Set rs = qdef.OpenRecordset(dbOpenForwardOnly, dbReadOnly)[/color]
If [COLOR=red]rs.EOF[/color]  Then
    Text19.Value = "No"
Else
    Text19.Value = "Yes"
End If

End Sub

 

[falcon7a]

Thanks for your help so far. The query table has been populated using the code that I posted. I understand the query table will only have the text 'dock door' in it. I made it that way just as a test.

I however, still don't know to look into the query table and print out "Yes" if the text "Dock Door" exists in the Query Table. Any other ideas or suggestions?

Thanks again.