Full Version: Sigh... multiple criteria forms
UtterAccess Forums > Microsoft® Access > Access Forms
acidtrash
I really should have this down by now but I'm having a problem with a filter style form.
It's a bunch of combo boxes along the top and the after update event requeries the subform. The criteria in the query being...
like"*"&[forms]![frm_tracker]![field1]&"*"
The criteria fields have a default * in them.
Ive got it so that it works if there is more than one record fitting that criteria however, it doesn't return anything if only one record exists matching the defined criteria.
It also pulls up only one record when there's 3 matching records. I know I'm being an idiot. The question is why?
acidtrash
I think it has something to do with other fields not having data in so I need some way of getting the * to also bring up blanks. Am I making sense?
If a record had every field filled in it would probably work ok but it doesn't.
acidtrash
Ive had this before and my solution was to put a * in everyy blank field in every record which was a terrible solution.
PaulBrand
Try using Or as well:
FOr IsNull([forms]![frm_tracker]![field1]) Or [forms]![frm_tracker]![field1] = ""
Untested fly code:-)
acidtrash
Thats what I thought but I still get the same.
ts no good anyway as I'm also potentially searching on those other fields.
acidtrash
This might give you a better idea of what im trying to do...
Larry Larsen
Hi
Also check out some of these options:
Like Forms!FormName!ControlName & "*" Or _
Forms!FormName!ControlName Is Null
This criteria statement is the same as the QBF sample above, except that you can query by using a wildcard. For example, if you enter "Jo" in a field by using this criteria statement, the query returns every record in the field that begins with "Jo." This includes Johnson, Jones, Johanna, and so on.
Between Forms!FormName!StartDate And Forms!FormName!EndDate Or _
Forms!FormName!StartDate Is Null You can use this criteria statement to query a date field by using Start Date and End Date text boxes on the query form. Records whose start and end dates fall between the values that you specify on the query form are returned. If you omit a Start Date value on the form, however, the query returns all records, regardless of the End Date value.
Like Forms!FormName!ControlName & "*" Or Is Null
This criteria statement returns both records that match the criteria and records that are null. If the criteria are null, all the records are returned. Note that the asterisk (*) is considered a parameter because it is part of a larger Like expression. Because the asterisk is a hard-coded criteria value (for example, Like "*"), records with null values are returned.
Like IIf(IsNull(Forms!FormName![ControlName]),"*",[Forms]![FormName]![ControlName])
This criteria statement returns all the records that match the criteria. If no criteria are specified in the query form, all records that are not null are returned.
IIf(IsNull(Forms!FormName![ControlName]),[FieldName],[Forms]![FormName]![Control
Name])
This returns all the records that match the criteria. If no criteria are specified in the query form, all records that are not null are returned (the same result as in the example above).
thumbup.gif
acidtrash
I had a better idea!! Thanks
CODE
Function buildsqlfilter()
Dim sqlsrting As String
Dim Sqlcriteria As String
   sqlstring = ""
   Sqlcriteria = ""
      
   If Me![Airdoc] <> "" Then
       If Sqlcriteria = "" Then
           Sqlcriteria = "(tbl_isqtracker.airdoc)=" & "'" & Me![Airdoc] & "'" & ""
       Else
           Sqlcriteria = "(" & Sqlcriteria & ") AND ((tbl_isqtracker.airdoc)=" & "'" & Me![Airdoc] & "'" & ")"
       End If
   End If
  
   If Me![ACType] <> "" Then
       If Sqlcriteria = "" Then
           Sqlcriteria = "(tbl_isqtracker.actype)=" & "'" & Me![ACType] & "'" & ""
       Else
           Sqlcriteria = "(" & Sqlcriteria & ") AND ((tbl_isqtracker.actype)=" & "'" & Me![ACType] & "'" & ")"
       End If
   End If
  
   sqlstring = "SELECT tbl_isqtracker.Airdoc, tbl_isqtracker.RMT, tbl_isqtracker.[ACType], tbl_isqtracker.MSN, tbl_isqtracker.Description, tbl_isqtracker.Status, tbl_isqtracker.Design, tbl_isqtracker.Stress, tbl_isqtracker.Fatigue, tbl_isqtracker.Due, tbl_isqtracker.Time, tbl_isqtracker.Status2, tbl_isqtracker.ISQID FROM tbl_isqtracker where (" & Sqlcriteria & ")" & ";"
   Me![isqsub].Form.RecordSource = sqlstring
  
End Function
acidtrash
This does present the problem that if I set a field to nothing then there is still a where clause for that field
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.