Newbie - Using the IIF Command

  • Thread starter Thread starter Ed Rawley
  • Start date Start date
E

Ed Rawley

I have a simple form that I want to look up a part number
on. All the part numbers are 7 digits and there are alot
of them, over 100K. All I want to do is input the number
in a unbound field, hit a search button and have it
requery the data, then display, in a text box, that either
the number is there or it is not there. I am using a
query, with the "Like IIF([dblPartNum = Form![FormName]!
[Field], "Yes", "Not Listed") in the conditions. But it
keeps giving me an error. Any help would be appreciated.
I know this should be easy, I can do it with DLookup, but
with so many numbers I was hoping I could speed things up
using the IIF command
 
Use the DCount function instead of the DLookUp. Then just
use the integer result for your if statement. I'd put it
in the AfterUpdate event for your TextBox

Private Sub txtFindMe_AfterUpdate()

' stop if a value has not been entered
If IsNull([txtFindMe]) or [txtFindMe]="" Then
Exit Sub
End If

'otherwise...

' declare variables
Dim strCrit as String
Dim i As Integer
Dim strMsg as String

strCrit = [txtFindMe]
i = DCount("[FieldName]", "tblTableName", _
"[FieldName]='" & strCrit & "'")

' set message based on whether the value is
' already in the table
If i = 0 Then
strMsg = "Item is not listed"
Else
strMsg = "Item is already listed " & i & " times."
End If

' display message in either a control or a messagebox
[lblMessage] = strMsg
MsgBox strMsg

End Sub

Hope this helps!

Howard Brody
 
I have a simple form that I want to look up a part number
on. All the part numbers are 7 digits and there are alot
of them, over 100K. All I want to do is input the number
in a unbound field, hit a search button and have it
requery the data, then display, in a text box, that either
the number is there or it is not there. I am using a
query, with the "Like IIF([dblPartNum = Form![FormName]!
[Field], "Yes", "Not Listed") in the conditions. But it
keeps giving me an error. Any help would be appreciated.
I know this should be easy, I can do it with DLookup, but
with so many numbers I was hoping I could speed things up
using the IIF command
Click to expand...

IIF isn't going to help here; the time spent requerying and populating
the form will be as much or more than the DLookUp itself, since you're
still searching the data in the same table. Is the field indexed? If
it has a unique Index (as it should), the DLookUp should be quite
speedy.
 
Back
Top