ignore null value when filtering with multi criteria

[molen malat]

i have a query with 4 fields, and a form based on it. i put another 4 textbox to get criteria to filter the query. the query runs normally when all the textbox have a value (not null) but when one or more of them is empty (null) the query returns no records. how can i ignore the (null) in my criteria.

[MMcCarthy]

Post the full query

i have a query with 4 fields, and a form based on it. i put another 4 textbox to get criteria to filter the query. the query runs normally when all the textbox have a value (not null) but when one or more of them is empty (null) the query returns no records. how can i ignore the (null) in my criteria.

[molen malat]

Post the full query

how can i post the query?

[MMcCarthy]

Open the query in design view and then change the view button to show SQL. Then simply copy and paste the code here.

how can i post the query?

[molen malat]

the query is:

SELECT Drugs.EnName, Drugs.Cmpny, Drugs.CPrice, Drugs.NPrice
FROM Drugs
WHERE (((Drugs.EnName)=[Forms]![SbQDrg]![TextA]) AND ((Drugs.Cmpny)=[Forms]![SbQDrg]![TextB]) AND ((Drugs.CPrice)=[Forms]![SbQDrg]![TextC]) AND ((Drugs.NPrice)=[Forms]![SbQDrg]![TextD]));

thanks a lot..

[MMcCarthy]

Try pasting this instead and see if it will work:

SELECT Drugs.EnName, Drugs.Cmpny, Drugs.CPrice, Drugs.NPrice
FROM Drugs
WHERE IIf(Not IsNull([Forms]![SbQDrg]![TextA]),[Drugs].[EnName]=[Forms]![SbQDrg]![TextA] AND)
IIf(Not IsNull([Forms]![SbQDrg]![TextB]),[Drugs].[Cmpny]=[Forms]![SbQDrg]![TextB] AND)
IIf(Not IsNull([Forms]![SbQDrg]![TextC]),[Drugs].[CPrice]=[Forms]![SbQDrg]![TextC] AND)
IIf(Not IsNull([Forms]![SbQDrg]![TextD]),[Drugs].[NPrice]=[Forms]![SbQDrg]![TextD]);

[molen malat]

sorry..
but a syntax error in it because ofthe "( " can you send it again.

thanks..thanks..thanks

[molen malat]

Thanks..............

[MMcCarthy]

Are you still getting an error, if so what exactly does it say?

sorry..
but a syntax error in it because ofthe "( " can you send it again.

thanks..thanks..thanks

[molen malat]

realy i don't try it again because i found a solution using VB-code that is:

If Not IsNull(Me.txtEnName) Then
strWhere = strWhere & "([EnName] = """ & Me.txtEnName & """) AND "
End If

and so on for other criteria then:


strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True

[molen malat]

realy i don't try it again because i found a solution using VB-code that is:

If Not IsNull(Me.txtA) Then
strWhere = strWhere & "([EnName] = """ & Me.txtA & """) AND "
End If

and so on for other criteria then:
lngLen = Len(strWhere) - 5
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
thats all.