Use a period between the table name and the field name, not an exclamation point.

Kevin - I get the same error.

I just noticed the mistake. You need to reference the field on both sides of the comparison.

IIF([tbl_Charge Codes].[PoP End]>Date()+60,"Active",IIF([tbl_Charge Codes].[PoP End]>Date()+30 and [tbl_Charge Codes].[PoP End]=Date() and [tbl_Charge Codes].[PoP End]

The post is incomplete (don't know why). Uploading a screenshot.

Kevin - That worked.  Thank you very much.  Now that it works, it seems my thought process isn't correct.  I'm building a search for with combo boxes and one of them is Status with the options of Active, D-30, D-60, and Expired.  I used the above expression and then built another names Status_ID with IIF expresion taking the Values from the Status expression and converting it to the equivalent Status_ID from the combo box.  I added the Status_ID  where clause to the VBA and the form with requery and reload, but it doesn't filter the by the selected Status.  The SQL String is:

Private Sub RequeryForm()

    Dim SQL As String, WhereStr As String
    
    WhereStr = ""
    If StrategicInititive_ID <> "" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "StrategicInitiative=" & StrategicInititive_ID
    End If
    If Directorate_ID <> "" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Directora

is StrategicInitiative a Short Text Field?
Is StrategicInititive_ID the name of your Combo Box?

Probably a Double Double Quotes problem.

If StrategicInititive_ID is your Combo Box, you should also check for Null as well as an empty string..

Kevin - It looks like my SQL statement was cut off.  The StrategicInitiative  string works.  

I'm having issues with the Status_ID combo box filtering.  Thate part of the where statement s:

If Status_ID <> "" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Status_ID=" & Status_ID
    End If

There is probably a way to do this all in the Where statement instead of adding it to the SQL string.  I just don't know how to do it.  I keep trying different things.

What Data Type is Status_ID?

Walter, again if you have an active post still going that's discussing the same topic, please don't make a second post (even in a different forum section, it all looks the same to us). It makes it super confusing on our end to keep things together, thank you. I deleted my response on that post to try to keep things together here.

Like Kevin said, if that's a short text field you need to use the double double quotes.

Second Post

Adam - Thanks and I understand.  Please forgive the duplicate posts.

I tried the double double quotes and it's still not working.

Everything else is working great, I just can't get the Status to filter.

Can I use an IIF statement in the where string?

My current IIF statement is:

"IIf([tbl_Charge Codes].[PoP End]>Date()+60,""Active"",IIf([tbl_Charge Codes].[PoP End]>Date()+30 And [tbl_Charge Codes].[PoP End]<Date()+60,""D-60"",IIf([tbl_Charge Codes].[PoP End]>=Date() And [tbl_Charge Codes].[PoP End]<Date()+30,""D-30"",""Expired""))) AS Status, " _

Can I wrap all that into the WhereStr for the Status_ID lookup?  That currently looks like:


If Status_ID <> "" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Status_ID=" & Status_ID
    End If

Where is that IIF statement? Is it just an unbound field with that calculation in it? Or is it a bound field that gets set to that by an event? Or is it a field in a query?

So you tried this and it didn't work?
WhereStr = WhereStr & "Status_ID=""" & Status_ID & """"

I tried

WhereStr = WhereStr & "Status_ID=""" & Status_ID & """"

and it didn't work.

The IIF statement is contained in the data source behind the subform.  I've also added the following to the data source:

& "IIf([Status]=""Active"",""1"",IIf([Status]=""D-30"",""2"",IIf([Status]=""D-60"",""3"",IIf([Status]=""Expired"",""4"")))) AS Status_ID " _

I should've just assigned the Status_ID numbers in the first IIF, but I didn't want to get rid of it since it was working.

I have a Status_ID combo box in the main form that will allow the user to select: Active, D-30, D-60, Expired.

Ideally I would like everything with the [PoP End] >=Date() display when they select Active
Only the records with a [PoP End] between Date() and Date()+30 when they select D-30
Only the records with a [PoP End] between Date()+30 and Date()+60 when they select D-60
Show all records with a [PoP End] <Date() when they select Expired.

Well if you have just an unbound combo box on your search form, Just hard code it for simplicity, there's only a few options.

Make it's row source
"Active";"D-30";"D-60";"Expired"

Then you can do
If Status_ID<>"" Then
If Status_ID="Active" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "[PoP End]>=#" & Date() & "#"
        End If
ElseIf Status_ID="D-30" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "[PoP End]>=#" & Date() & "# And [PoP End]<=#" & Date()+30 & "#"
        End If
ElseIf Status_ID="D-60" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "[PoP End]>=#" & Date()+30 & "# And [PoP End]<=#" & Date()+60 & "#"
        End If
ElseIf Status_ID="Expired" Then
         'ran outve space, same idea
End If
End If

I made the recommended changes and get the following error when trying to filter by Status_ID.

Cimpile error:
Else without If

Here's the statement:

If Status_ID <> "" Then
    If Status_ID = "Active" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "[PoP End]>=#" & Date & "#"
        End If
    ElseIf Status_ID = "D-30" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "[PoP End]>=#" & Date & "# And [PoP End]<=#" & Date + 30 & "#"
        End If
    ElseIf Status_ID = "D-60" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "[PoP End]>=#" & Date + 30 & "# And [PoP End]<=#" & Date + 60 & "#"
        End If
    ElseIf Status_ID = "Expired" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "[PoP End]<#" & Date & "#"
        End If
    End If
    End If

If you push debug does it show where that is? There's an If missing somewhere, could be in the entire function if you deleted stuff to put that in. Just need to find it. Can you upload an image of the entire function now?

The above screenshot is the entire Where string.

Try taking out those 4 End Ifs inside of the different statuses

If Status_ID <> "" Then
    If Status_ID = "Active" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "[PoP End]>=#" & Date & "#"
        
    ElseIf Status_ID = "D-30" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "[PoP End]>=#" & Date & "# And [PoP End]<=#" & Date + 30 & "#"
        
    ElseIf Status_ID = "D-60" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "[PoP End]>=#" & Date + 30 & "# And [PoP End]<=#" & Date + 60 & "#"
        
    ElseIf Status_ID = "Expired" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "[PoP End]<#" & Date & "#"
        
    End If
    End If

Adam - I was doing that as you sent this and it works flawlessly.  Thank you so much for your assistance.  I've been working on this for more than a week.

Yea I put them there by habit, didn't need them in this case. If you put them there you need to rewrite a lot more on those 1 line if statements for it to make sense.

Glad you finally got it working.

Adam - One more piece I'm struggling with that is in the same vein.  I have another Search Form that has Stage_ID as an option.  The data source behind the subform has Stage_ID in it as well.  I need to have the Where clause show all records with a Stage_ID of 1,2,3,4,5,7 if the Stage_ID in the Main form is Blank.  If the Stage_ID in the Main form has a selection than it needs to be:

WhereStr = WhereStr & "Stage_ID=" & Stage_ID

I don't know if it's a double double quotes issue or I'm just not doing something correct.  I'm getting a type mismatch error.

That part of the function is:

If Stage_ID = "" Then
        If WhereStr = "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Stage_ID=" & "1" Or "2" Or "3" Or "4" Or "5" Or "7"
    ElseIf Stage_ID <> "" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Stage_ID=" & Stage_ID
    End If

I'm definitely pushing the limits of my knowledge at this point.

Yea that first one is too many quotations, we're not entering a variable or field value so we don't need to quote them all.
This assumes they are numbers
        WhereStr = WhereStr & "Stage_ID=1 Or Stage_ID=2 Or Stage_ID=3 Or Stage_ID=4 Or Stage_ID=5 Or Stage_ID=7"
If They are strings (short text) (those are single quotes ' it does the same thing as 2 of the double quotes "")
        WhereStr = WhereStr & "Stage_ID='1' Or Stage_ID='2' Or Stage_ID='3' Or Stage_ID='4' Or Stage_ID='5' Or Stage_ID='7'"

The second part should be OK if it's a number, if its short text
        WhereStr = WhereStr & "Stage_ID=""" & Stage_ID & """"

Actually, the ORs in that might give you trouble in the full SQL statement, maybe it's better to just do this if its basically "show all"
        WhereStr = WhereStr & "Stage_ID LIKE '*'"

You could also throw the whole thing in parenthesis I believe,
        WhereStr = WhereStr & "(Stage_ID=1 Or Stage_ID=2 Or Stage_ID=3 Or Stage_ID=4 Or Stage_ID=5 Or Stage_ID=7)"

It's missing the line with the AND before that
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
Then the stuff I posted

Adam - I tried all those options and I get the above error each time.  It looks to me like I messed something up when building the entire Where argument.  I'll upload a screen shot of the entire function.

Pretty sure you'll have to use the LIKE or the parenthesis as well, otherwise the OR will overwrite everything and say just show me everything that has a stage_ID of 2 regardless of anything else. So the second or third example

You have If WhereStr = "" instead of If WhereStr <> "" on the line above my code

You also might want to do If Stage_ID="" Or IsNull(Stage_ID)

Adam - I need it to be "Stage_ID=" & Stage_ID if the combo box has a value in it.

But when it's null I need to only show Stage_ID 1,2,3,4,5

I accidently listed 7 before, but that doesn't need to show unless they specifically search for it.

I switching it back to WhereStr <> "" and the data reloads but it doesn't filter it.  It just shows every Stage.

Did the one with the parenthesis work? Just take out the 7 then. That should show if 1 2 3 4 or 5

Adam - I've tried all the options you sent.  I don't get an error with the following syntax, but it also doesn't filter anything out:

It does filter when I select a Stage_ID in the main form.

If Stage_ID <> "" Then
    If Stage_ID = "" Or IsNull(Stage_ID) Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "(Stage_ID=1 Or Stage_ID=2 Or Stage_ID=3 Or Stage_ID=4 Or Stage_ID=5)"
    ElseIf Stage_ID <> "" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Stage_ID=" & Stage_ID
    End If
    End If

Try wrapping each of the numbers in single quotes
Stage_ID='1' or Stage_ID='2' etc might as well check if text.

Also what is the rowsource and bound column of Stage_ID? We might need to use the .column property

wrapping each number in single quotes didn't do anything.  No errors, but it also didn't filter.

Stage_ID, and Stage from tbl_Stages.  The Bound Column is Stage_ID which is an Autonumber.

Adam - I just got it working.  I did change the Stage_ID combo box on the main form to a value list and then user the following:

If Stage_ID <> "" Then
    ElseIf Stage_ID = "" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "(tbl_Tasks.Stage_ID) = 1 Or (tbl_Tasks.Stage_ID) = 2 Or (tbl_Tasks.Stage_ID) = 3 Or (tbl_Tasks.Stage_ID) = 4 Or (tbl_Tasks.Stage_ID) = 5 "
    If Stage_ID = "Stage 0" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Stage_ID=1"
    ElseIf Stage_ID = "Stage 1" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Stage_ID=2"
    ElseIf Stage_ID = "Stage 2" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "Stage_ID=3"

Etc.......

I spoke to soon.  I can select any of the other options an leave Stage_ID blank and only records with Stage 0 - Stage 4 display, but the form doesn't filter from any of the other selections any long.  If I replace the ElseIf Stage_ID = "" Then with If Stage_ID = "" then the other selections filter correctly but when leaving Stage_ID NULL on the main form all the records show up event those in Stage 5 and Stage 6.

I'm sure this is just a syntax issue, but I can't seem to figure it out.

Yea that's what I was saying, so if that first part runs        
WhereStr = WhereStr & "(tbl_Tasks.Stage_ID) = 1 Or (tbl_Tasks.Stage_ID) = 2 Or (tbl_Tasks.Stage_ID) = 3 Or (tbl_Tasks.Stage_ID) = 4 Or (tbl_Tasks.Stage_ID) = 5 "

That is going to ruin your SQL statement when anything else is involved, your saying i want X AND Y AND Z normally, then you throw OR into it and it ruins it, then your saying I want X AND Y AND Z OR Stage1, then it will show you X Y Z and ANYTHING that is stage 1 regardless if X,Y,Z match to it. Throwing all those ORs there is just basically like show me all the records.

I think what you need to do is

If Stage_ID <> "" Then
  If Stage_ID = "Stage 0" Then
    If WhereStr <> "" Then WhereStr = WhereStr & " AND "
    WhereStr = WhereStr & "Stage_ID=1"
  ElseIf Stage_ID = "Stage 1" Then
    If WhereStr <> "" Then WhereStr = WhereStr & " AND "
    WhereStr = WhereStr & "Stage_ID=2"
    'etc all the numbered ones
  End If
ElseIf Stage_ID="" or IsNull(Stage_ID) Then
  If WhereStr <> "" Then WhereStr = WhereStr & " AND "
  WhereStr = WhereStr & "((tbl_Tasks.Stage_ID) = 1 Or (tbl_Tasks.Stage_ID) = 2 Or (tbl_Tasks.Stage_ID) = 3 Or (tbl_Tasks.Stage_ID) = 4 Or (tbl_Tasks.Stage_ID) = 5)"
End If

Putting that whole section inside of Parenthesis like I did, I'm still not sure that syntax is correct but worth a try.

I wonder if you couldn't just do this for simplicity in the second section

ElseIf Stage_ID="" or IsNull(Stage_ID) Then
  If WhereStr <> "" Then WhereStr = WhereStr & " AND "
  WhereStr = WhereStr & "Stage_ID<=5"
End If

Adam - You rock sir!!!!!!!

Thank you so much for your assistance.  I knew it was a syntax issue, but I just don't understand it enough.

Thanks again for all of your assistance!