Select Case - checking two text boxes

[scottian]

Im trying to produce a form in a database that will filter information dependant on the contents of two text boxes on the form. One box is UserID and the other is Date. I would to bebale to filter the data by using a case statement that check the boxes and decide which query to use.
i.e. Pseudo code.
Case - UserID not null and Date not null
form recordsource = qryUserAndDateFilter
refresh

Case UserID null and Date not null
form recordsource = qryDateFilter
refresh

Case - UserID not null and Date null
form recordsource = qryUserFilter
refresh

Case - UserID null and Date null
form recordsource = qryNoFilter
refresh

any ideas?


"My God! It's full of stars...

 

[jebry]

Hi!

I don't think you can run a Select - Case off of two variables so just use a nested If:

If IsNull(UserID) = False Then
If IsNull(Date) = False Then
form recordsource = qryUserAndDateFilter
Else
form recordsource = qryUserFilter
End If
Else
If IsNull(Date) = False Then
form recordsource = qryDateFilter
Else
form recordsource = qryNoFilter
End If
End If

refresh

I know that this is psuedocode but just in case I will say, make sure you field/textbox is not actually named Date since this is a reserved word.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[PWise]

Select Case True
Case not isnull(UserID) and not isnull(Date) 
form recordsource = qryUserAndDateFilter

Case isnull(UserID) and not isnull(Date)
form recordsource = qryDateFilter


Case  not isnull(UserID) and isnull(Date)
form recordsource = qryUserFilter


Case isnull(UserID) and isnull(Date)
form recordsource = qryNoFilter




End Select
refresh

 

[scottian]

Thank you both very much.
I tried the If, Then, Else first and it worked like a charm. But ive decided to you use the Case method as i really need to get my head around Case statements.

Once again, thanks very much.

"My God! It's full of stars...