Multiple parameter query will not return null values

[ps40life]

I have several multiple parameter queries that ask a Request #, Date, Type or a combination thereof. The problem is if, in this case, the Type field is null, the record is dropped out. I used:

LIKE [Enter "fieldname" or click OK for ALL] & "*"

on all the different fields, so a user can choose all or none of the parameter options for criteria. I also have some that use popup dialog forms in the same way. I tried Access help that said to enter the parameter on the OR line with Is Null after it, but then if I set a parameter on THAT field, besides getting the indicated parameter, I get any record with a null value in that field. It works fine if I search on any other the other parameters.

I hope this makes sense, because I am already confused enough! Does anyone have any ideas??
Thanks! : )

 

[hermanlaksko]

I think you solution could be useing a recordset created from the parameters some thing like this:


Sub AddToWhere(Field_Value As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer, Argument As String)
Dim ChrStr

If IsNull(Field_Value) Then Exit Sub
If Field_Value = "" Then Exit Sub

ChrStr = Left(Field_Value, 1)

If (ChrStr = &quot;'&quot Or (ChrStr = &quot;#&quot Then If Len(Field_Value) < 3 Then Exit Sub

If Field_Value <> &quot;&quot; Then
If ArgCount > 0 Then
MyCriteria = MyCriteria & &quot; and &quot;
End If
If Argument = &quot;Like&quot; Then
MyCriteria = (MyCriteria & FieldName & &quot; Like &quot; & Chr(39) & Field_Value & Chr(42) & Chr(42) & Chr(39))
Else
MyCriteria = (MyCriteria & FieldName & &quot; &quot; & Argument & &quot; &quot; & Field_Value)
End If
ArgCount = ArgCount + 1
End If

End Sub

AddToWhere F_Kasse, &quot;[KasseNr]&quot;, MyCriteria, ArgCount, &quot;Like&quot;
AddToWhere F_FraBeloeb, &quot;[Betalt]&quot;, MyCriteria, ArgCount, &quot;>=&quot;

Herman

 

[ps40life]

Herman,
Thanks for the reply. Sorry it has taken me so long to reply. To be honest, I don't really understand or know what to do with the code you wrote! I am just starting with VBA and SQL, so can you tell where I would put it? Is there any other way to do it within the QBE or SQL window?
Thanks for your help. I really do appreciate it.
: )

 

[hermanlaksko]

Hi Ps40Life
These are subs and functions not to be use in the SQL window.

The Above Sub could be placed in a module.
The code after the &quot;end sub&quot; are examples of how you could call this sub.
This is the function that I use to call this sub:

Function Vis_Betalinger(F_Dato, T_Dato, F_BetalID, F_Kasse, F_FraBeloeb, F_TilBeloeb)
Dim MySql As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
Dim Tmp As Variant
On Error GoTo Err_Vis_Betalinger

ArgCount = 0
MySql = &quot;SELECT * FROM YrTbl WHERE &quot;
MyCriteria = &quot;&quot;

If Not IsNull(F_Dato) Then AddToWhere &quot;#&quot; & Format(F_Dato, &quot;mm-dd-yyyy&quot & &quot;#&quot;, &quot;[Dato]&quot;, MyCriteria, ArgCount, &quot;>=&quot;
If Not IsNull(T_Dato) Then AddToWhere &quot;#&quot; & Format(T_Dato, &quot;mm-dd-yyyy&quot & &quot;#&quot;, &quot;[Dato]&quot;, MyCriteria, ArgCount, &quot;<=&quot;

AddToWhere F_BetalID, &quot;[BetalingID]&quot;, MyCriteria, ArgCount, &quot;Like&quot;
AddToWhere F_Kasse, &quot;[KasseNr]&quot;, MyCriteria, ArgCount, &quot;Like&quot;
AddToWhere F_FraBeloeb, &quot;[Betalt]&quot;, MyCriteria, ArgCount, &quot;>=&quot;
AddToWhere F_TilBeloeb, &quot;[Betalt]&quot;, MyCriteria, ArgCount, &quot;<=&quot;

If MyCriteria = &quot;&quot; Then MyCriteria = &quot;True&quot;
MyRecordSource = MySql & MyCriteria & &quot; ORDER BY BilagsNr DESC&quot;

If Not IsLoaded(&quot;YrForm&quot Then
If IsNull(F_Dato) Then MyCriteria = &quot; False&quot;
DoCmd.OpenForm &quot;YrForm&quot;, A_NORMAL, , , A_EDIT, A_NORMAL, MyRecordSource
Else
Forms!YrForm.RecordSource = MyRecordSource
End If

Exit_Vis_Betalinger:
Exit Function

Err_Vis_Betalinger:
MsgBox Error$, , GetMsg(&quot;Sys_101&quot
Resume Exit_Vis_Betalinger:

End Function

And this is what I use to call this function:
(This would be on a field or an button)

Private Sub F_BetalID_AfterUpdate()
Svar = Vis_Betalinger(Me!F_Dato, Me!T_Dato, Me!F_BetalID, Me!F_Kasse, Me!F_FraBeloeb, Me!F_TilBeloeb)
Me!F_BetalID.Requery
End Sub
Hope this would do the trick for you