If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Filter Report by Form - VBA Code
I have a report with both text, date and Yes/No fields and I am trying to
filter the report data with a pop-up form. I have fields I want to filter the data by. I have code that works with 'Text' fields; it is For intCounter = 1 To 5 If Me("Filter" & intCounter) "" Then strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And " End If If i take out the '& Chr(34)' it works with the Yes/No fields. The date field I would like to have as either 'Is Not Null' or 'Is Null' (it is filter2) What code do I need to make it work with the three data types? Any help would be appreciated. -- Thanks Mannie G |
#2
|
|||
|
|||
Filter Report by Form - VBA Code
Mannie G wrote:
I have a report with both text, date and Yes/No fields and I am trying to filter the report data with a pop-up form. I have fields I want to filter the data by. I have code that works with 'Text' fields; it is For intCounter = 1 To 5 If Me("Filter" & intCounter) "" Then strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And " End If If i take out the '& Chr(34)' it works with the Yes/No fields. The date field I would like to have as either 'Is Not Null' or 'Is Null' (it is filter2) What code do I need to make it work with the three data types? Because the syntax is different for the three types, I think it's easier/clearer to do each field separately: If Len(Nz(Filter1,"")) 0 Then 'Text field strSQL = strSQL & " And [" & Me("Filter" & intCounter).Tag & "] = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) End If If Not IsNull(Filter2) Then 'Number field strSQL = strSQL & " And [" & Me("Filter" & intCounter).Tag & "] = " & Me("Filter" & intCounter) End If If Not IsNull(Filter3) Then 'Date/time field strSQL = strSQL & " And [" & Me("Filter" & intCounter).Tag & "] = " & Format(Me("Filter" & intCounter), "\#yyyy-m-d\#") End If .. . . strSQL = Mid(strSQL, 6) -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|