Multiple selects including Null's in a query

[damonreid]

Hi,
I want to select multiple variables from a form and pass them to a query, however I don't mind how many of the variables are used.

I am currently using the syntax;

Like [Forms]![Form Name]![Combo Box Name] & "*"

On each of the fields in the query I want to be able to filter on for my report. However it is ignoring my null fields and given I have 10+ variables and none of them are required fields I am getting very few results. Is there any way to do this but to include nulls as well?

I am using XP and Access 2003.



Very long winded code below...

SELECT PunchListDetails.Ref, PunchListDetails.[Entered On], PunchListDetails.[Associated System], PunchListDetails.[Location/Area], PunchListDetails.PunchDetails, PunchListDetails.[Additional Comments], PunchListDetails.[Item Open], PunchListDetails.[Item Closed], PunchListDetails.[Item Raised At], PunchListDetails.[Date Closed Out], PunchListDetails.[Due On], PunchListDetails.[Revised Due Date], PunchListDetails.Discipline, PunchListDetails.[Punch Owner], PunchListDetails.[Responsible Contractor], PunchListDetails.OwnerSignOff, PunchListDetails.OwnerSignOffLogIn, PunchListDetails.OwnerSignOffDate, PunchListDetails.EngineerSignOff, PunchListDetails.EngineerSignOffLogIn, PunchListDetails.EngineerSignOffDate, PunchListDetails.ClientSignOff, PunchListDetails.ClientSignOffLogIn, PunchListDetails.ClientSignOffDate, PunchListDetails.[Project Number], PunchListDetails.Impact
FROM PunchListDetails
WHERE (((PunchListDetails.[Associated System]) Like [Forms]![ReportsForm]![cmbSystemCode] & "*") AND ((PunchListDetails.[Location/Area]) Like [Forms]![ReportsForm]![cmbArea] & "*") AND ((PunchListDetails.[Item Raised At]) Like [Forms]![ReportsForm]![cmbRaised] & "*") AND ((PunchListDetails.Discipline) Like [Forms]![ReportsForm]![cmbDiscipline] & "*") AND ((PunchListDetails.[Punch Owner]) Like [Forms]![ReportsForm]![cmbOwner] & "*") AND ((PunchListDetails.[Responsible Contractor]) Like [Forms]![ReportsForm]![cmbContractor] & "*") AND ((PunchListDetails.Impact) Like [Forms]![ReportsForm]![cmbImpact] & "*") AND ((PunchListDetails.[Entered By]) Like [Forms]![ReportsForm]![cmbRaisedBy] & "*") AND ((PunchListDetails.[Item Type]) Like [Forms]![ReportsForm]![cmbItemType] & "*"));

[kepston]

Try using OR instead of AND in your WHERE clause.

[damonreid]

That does work but it now returns values for rows not intended.

For example if we wanted raised by Damon Reid and Mechanical items it now returns all items raised by Damon Reid and all Mechanical items instead of the Mechanical items raised by Damon Reid.

I need to keep it as AND but I need to be able to include nulls in the "*" search.

Thanks for the try though.

[kepston]

I thought it was too easy!
Let me think about it, and I'll get back to you.

[kepston]

You need to build the criteria 'on the fly'.
Do this in an appropriate event.
Do you have a search button on your [ReportsForm]? - build criteria in OnClick event
Is this query the basis for a report/form? - build criteria in OnLoad event

Dim strCriteria As String
strCriteria=""
If Not IsNull([Forms]![ReportsForm]![cmbSystemCode]) Then
    strCriteria=strCriteria & "PunchListDetails.[Associated System]) Like [Forms]![ReportsForm]![cmbSystemCode] & '*'" & " AND "
End if
If Not IsNull([Forms]![ReportsForm]![cmbArea]) Then
    strCriteria=strCriteria & "(PunchListDetails.[Location/Area]) Like [Forms]![ReportsForm]![cmbArea] & '*'" & " AND "
End if
 

etc...
Finish with

strCriteria=Left(strCriteria, Len(strCriteria)-5)

to remove the last " AND "

Let me know if you get stuck

[NeoPa]

I would recommend the same course :)
Add only the items that are used to the WHERE clause in the string.

[damonreid]

Thank you both... this was driving me crazy, I can't belive you are not able to do it in the query itself, would be so much easier.

One last question once I have my nice new string strCriteria how do I pass this to a report. I have opened the report and gone to the vb code but I am not sure how to pass a filter to it on open?

Option Compare Database
 
Private Sub Report_Open(Cancel As Integer)
 
End Sub

Is the complete code for the command button (in case anyone is interested).

Private Sub cmdPrePunchDetails_Click()
On Error GoTo Err_cmdPrePunchDetails_Click
 
        Dim strCriteria As String
        strCriteria = ""
        If Not IsNull([Forms]![ReportsForm]![cmbSystemCode]) Then
            strCriteria = strCriteria & "PunchListDetails.[Associated System]) Like [Forms]![ReportsForm]![cmbSystemCode] & '*'" & " AND "
        End If
        If Not IsNull([Forms]![ReportsForm]![cmbArea]) Then
            strCriteria = strCriteria & "(PunchListDetails.[Location/Area]) Like [Forms]![ReportsForm]![cmbArea] & '*'" & " AND "
        End If
        If Not IsNull([Forms]![ReportsForm]![cmbRaisedBy]) Then
            strCriteria = strCriteria & "(PunchListDetails.[Entered By]) Like [Forms]![ReportsForm]![cmbRaisedBy] & '*'" & " AND "
        End If
        If Not IsNull([Forms]![ReportsForm]![cmbRaised]) Then
            strCriteria = strCriteria & "(PunchListDetails.[Item Raised At]) Like [Forms]![ReportsForm]![cmbRaised] & '*'" & " AND "
        End If
        If Not IsNull([Forms]![ReportsForm]![cmbDiscipline]) Then
            strCriteria = strCriteria & "(PunchListDetails.[Discipline]) Like [Forms]![ReportsForm]![cmbDiscipline] & '*'" & " AND "
        End If
        If Not IsNull([Forms]![ReportsForm]![cmbOwner]) Then
            strCriteria = strCriteria & "(PunchListDetails.[Punch Owner]) Like [Forms]![ReportsForm]![cmbOwner] & '*'" & " AND "
        End If
        If Not IsNull([Forms]![ReportsForm]![cmbImpact]) Then
            strCriteria = strCriteria & "(PunchListDetails.[Impact]) Like [Forms]![ReportsForm]![cmbImpact] & '*'" & " AND "
        End If
        If Not IsNull([Forms]![ReportsForm]![cmbContractor]) Then
            strCriteria = strCriteria & "(PunchListDetails.[Responsible Contractor]) Like [Forms]![ReportsForm]![cmbContractor] & '*'" & " AND "
        End If
        If Not IsNull([Forms]![ReportsForm]![cmbItemType]) Then
            strCriteria = strCriteria & "(PunchListDetails.[Item Type]) Like [Forms]![ReportsForm]![cmbItemType] & '*'" & " AND "
        End If
 
        strCriteria = Left(strCriteria, Len(strCriteria) - 5)
 
        Dim stDocName As String
 
        stDocName = "PunchListDetails"
        DoCmd.OpenReport stDocName, acPreview
 
Exit_cmdPrePunchDetails_Click:
    Exit Sub
 
Err_cmdPrePunchDetails_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrePunchDetails_Click
 
End Sub

[kepston]

Dim stDocName As String
        stDocName = "PunchListDetails"
        DoCmd.OpenReport stDocName, acPreview, , strCriteria

[damonreid]

That works perfectly.
Only exception is when no fields are entered, but I have a separate report for that.

Thank you very much.

[NeoPa]

When no criteria are entered, you simply need to call it with :

DoCmd.OpenReport stDocName, acPreview

[damonreid]

That is far easier then having report a and report b for updating.

Thanks again.

Damon

[NeoPa]

No problem.
I'm pleased that worked for you and yes, it is easier ;)