Single query, Multiple critera, User customing?

[StooPot]

Hi All,

I have a table which contains expense details with the following fields :
Filed Example
------------------------
4thLevelCode - 300505210
5thLevelCode - 01D04
6thLevelCode - BUK
7thLevelCode - 1EC
8thLevelCode - 012
Decription - Faulty Goods
Amount - 1.50

All records have a 4thLevel code but don't always have a 5,6,7 or 8th Level Code.

I am trying to write a form which will allow the user to customise a query to display the data. ie The 5 diffent levels. I am able to do this using combo box's and one query, but it does not show any records that have null values in any of the Level fields.

I don't want the records with Null values to always show only if the user does not select a value from the combo box.

Not too sure which angle to attack this problem, I have tried writing code and having hidden text box's but still hit the same brick wall.
Willing to buy the person who solves this one a pint if you live or work nearby )

Cheers Stoo...

 

[dhookom]

I always use code to build a "where" clause:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cbo4thLevelCode) Then
   strWhere = strWhere & " And [4thLevelCode]='" & _
        Me.cbo4thLevelCode & "' "
End If
If Not IsNull(Me.cbo5thLevelCode) Then
   strWhere = strWhere & " And [5thLevelCode]='" & _
        Me.cbo5thLevelCode & "' "
End If
'etc
DoCmd.OpenReport "rptYours", acPreview, , strWhere

Duane
MS Access MVP
Find out how to get great answers faq219-2884.