Obtaining and using data from a query table using VB 2

[falcon7a]

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? I understand that qryTempData only contains the text 'Dock Door'.

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

 

[PHV]

And what about this ?
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If DLookUp("EPCStatusDesc", "qryItemData", "EPCStatusDesc='Dock Door'") = "Dock Door" Then
Text19.Value = "Yes"
Else
Text19.Value = "No"
End If
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[HitechUser]

falcon7a,

A simple way to do it is to use the iif function in your query or use it on the control. .

Syntax.. See help for more details.

iif ( condition, value_if_true, value_if_false )

Good Luck..