Dlookup with IIf Function - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Dlookup with IIf Function

Kristy Duncan

5 years ago

Is there anyway to us dlookup with the IIf Function for the domain? I am currently working on a database that will provide users with Medicaid and Medicare rates for whatever procedure codes they need. Medicaid is by state, and some rates are different depending on the providers licensure level and if it is a facility or non-facility setting. Medicare is by zip code, licensure level, and if it is a facility or non-facility setting.
Right now I have a lot of queries and can only give the user one rate at a time. Id like to have the user enter 20 procedure codes, if they have them, and give them all the rates at once. I tried converting the SQL in the query to VBA, but it didnt work. From what I read, its because these are just search queries. My next idea was dlookup, but I am not sure if I can enter all the criteria I need to get the correct rates.
OR is there a way to run a query against more than text box input at a time? Could I use the loop function?

Adam Schwanz @Reply

5 years ago

You can definitely run multiple if/then statements with DLOOKUPs as the results. Not sure about a super complex IIF.

If Code = 1 Then
Price=  dlookup1
If Code = 2 then
  Price = dlookup2
etc...

If it needs to be more advanced then that with multiple options, you could use a list box and a variable as well. This is covered really well in the search seminar. Then basically continue this method down adding on more and more to the variable to give you the end result with everything togethor.

Dim X As String
Dim SQL As String
X = ""
    If SearchField<> "" Then
        If X <> "" Then X = X & " " & "AND" & " "
        X = X & "SearchField LIKE '*" & SearchField & "*'"
    End If
SQL = "Select FieldName from TableName where " & X
ListBox.RowSource = SQL

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View