Null Value QueryDefs Problem

[sandra45]

Hi, another problem that I dont know how to solve, please help!!! I have a QUERY called qry_ALL, a FORM where the user puts in data to find records. I have 9 text boxes for user input in the FORM but of course not all text boxes will be filled in. The parameter value is null and I expect it returns the value even if it's null, but no record is being returned at all. I have put Is Null under other criteria of QUERY qry_ALL but still doesnt retrieve any records, please help. Below is the code:
Dim qryQuote As DAO.QueryDef
Dim rec As DAO.Recordset
Set qryQuote = CurrentDb.QueryDefs("qry_ALL")
If Not IsNull(Me.x) Or Me.x <> "" Then qryQuote.Parameters!test1 = Me.x
If Not IsNull(Me.y) Or Me.y <> "" Then qryQuote.Parameters!test2 = Me.y
If Not IsNull(Me.z) Or Me.z <> "" Then qryQuote.Parameters!test3 = Me.z
If Not IsNull(Me.a) Or Me.a <> "" Then qryQuote.Parameters!test4 = Me.a
If Not IsNull(Me.b) Or Me.b <> "" Then qryQuote.Parameters!test5 = Me.b
If Not IsNull(Me.c) Or Me.c <> "" Then qryQuote.Parameters!test6 = Me.c
If Not IsNull(Me.d) Or Me.d <> "" Then qryQuote.Parameters!test7 = Me.d
If Not IsNull(Me.e) Or Me.e <> "" Then qryQuote.Parameters!test8 = Me.e
If Not IsNull(Me.f) Or Me.f <> "" Then qryQuote.Parameters!test9 = Me.f

Set rec = qryQuote.OpenRecordset()
If rec.RecordCount <> 0 Then
Set Me!lstResult.Recordset = rec
End If

Regards,
Sandra

 

[JerryKlmns]

Sandra, this SQL-Statement

PARAMETERS TYP Text ( 255 );
SELECT CL93.*
FROM CL93
WHERE (((CL93.Xaraktirismos)=[TYP])) OR (((CL93.Xaraktirismos) Is Null Or (CL93.Xaraktirismos) Is Not Null) AND (([TYP]) Is Null));

retrieves records from the table CL93, using the parameter TYP. If the parameter value is not entered, then retrieves records that on field Xaraktirismos there is or there is not any value! So you get all records. Paste that on SQL View and change to Design View to see the trick! (I guess you are using MS-Access)

Although I dont know the structure of qry_ALL, you could instead, build the full sql in code and use it like

rec.ActiveConnection = AnOpenConnectionToTheDataBase
rec.source = "TheFullBuiltSQL_Statement"
rec.open

Any questions welcomed

 

[sandra45]

Hi Jerry, does it mean I have to create new parameter for each parameter on the query? For example test1 parameter in QUERY qry_ALL. Thanks a lot.

Regards,
Sandra

 

[JerryKlmns]

Well Sandra, this means that for every field you use a parameter value to filter records, you should check the parameter variable [TYP for my example] if it is null.
If parameter variable is null, then the query shows all records where on field Xaraktirismos mach the criteria expression -->(Is Null or Is Not Null) which equals to All.
Replacing criteria (Is Null or Is Not Null) with a (Like "*"), you get all non null records!(Checked it)

In my example the field Xaraktirismos, allows nulls and does contain null values. If your fields dont contain nulls, then the Where clause changes to

WHERE (((CL93.Xaraktirismos)=[TYP])) OR (((CL93.Xaraktirismos) Is Not Null) AND (([TYP]) Is Null));

I hope it 's more clear now ..... but if it s still greeks to you, feel free to ask again.

 

[PHV]

A simpler way:
WHERE Nz([Field1])=Nz([test1],[Field1])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jjlogan]

Some thoughts for consideration:
1. Original post shows OR logic. It seems AND is appropriate here since you want to assign the textbox value only if textbox is not null AND it is not "". (?)
2. I have not worked with Paramters with QueryDef object, but have used them in the stored query. Am wondering if your query criteria fits into following structure: Like "*" & [parameter] & "*"? This structure does not eliminate records when criteria parameter is blank.
Jeff

 

[sandra45]

Hi all, thank you very much for your posts. Yes I want to have records based on parameter passed by users. If no value in any textboxes, then all records should be returned.

I do use e.g. [test1] under row Criteria in QUERY qry_ALL, and this parameter [test1] will get value keyed in the textbox. Since I have 9 parameters set in the QUERY, if only 5 textboxes have values, it returns error message "...expect 9..."

Regards,
Sandra