query problem, null values

[Lemmy Winks]

Hi, im developing a database. I have a form with several combo boxes on it that drop down to display options. What im working on now is a combo box with serial numbers for items. I want to display a report for all items, all items with a serial number, and a specific item chosen from the combo box with the chosen serial number. Problem is alot of items dont have serial numbers, so the fields are null. As you can see in my function in the query you have the FAS (serial number), and if that is the option in the combo box then the FAS field in the query will contain all the FAS values from the main table. Access leaves out all the rows with null vales in the field automatically, which makes things convient for the FAS only query, but causes a problem during my ALL query for all records in the database. You can see i have [XRAY - Main]![FAS] Or Is Null down for the ALL "True" value, and this works to get all records with and without a FAS number if i plug it striaght into criteria, but it does nothing in the function, becasue it itself is techniqually not a value. Ive tried putting it outside of this Iff function as seen below the first line of code, and it works for FAS and single serials but still not for ALL. Is there anything i can do to make this work? I basically need something that will select all values null or not in the FAS field, but i cant find anything that does that, i know in SQL you can just type * to get that but not here.I also have another thing im working on dunno if it could go anywhere, any help be appreciated. Also im thinking of making a custom function to do this, but cant find the existing Iff function anywhere to copy/learn from that code.

IIf([Forms]![testformLB]![FAS CB]=" ALL",[XRAY - Main]![FAS] Or Is Null,IIf([Forms]![testformLB]![FAS CB]=" FAS",[XRAY - Main]![FAS],[Forms]![testformLB]![FAS CB]))
([XRAY - Main]![FAS] Or Is Null) And IIf([Forms]![testformLB]![FAS CB]=" FAS",[XRAY - Main]![FAS],[Forms]![testformLB]![FAS CB])
([XRAY - Main]![FAS] Or Is Null) And IIf([Forms]![testformLB]![FAS CB]=" FAS",[XRAY - Main]![FAS],IIf([Forms]![testformLB]![FAS CB]<>" FAS",[Forms]![testformLB]![FAS CB]))

 

[Guus2005]

To Nz() or not to Nz() that's the question.

There is a function called Nz which converts a NULL value into a default.

strText = Nz(rst.Fields("Initials"),"")
intRoomnumber = Nz(rst.fields("roomnr"),0)

When each of the recordset values are NULL, the alternative is returned. For the text field it is an empty string, for the integer it is 0.

This answer is based on the topic name "query problem, null values" and the absence of the Nz command in the IIF statement.

I didn't read the text you wrote. You might want to add some structure in there.

The IIF statement doesn't make any sense to me.

First you are comparing if [Forms]![testformLB]![FAS CB] equals the text " ALL". Which is not only strange because of the space before " ALL" but also when the criteria is met the retun value is "[XRAY - Main]![FAS] Or Is Null". Is Null is not a proper value to compare to a boolean.
I am not sure why you do that.

Take a look at the Switch and Choose commands perhaps they are better suited for your purpose.

Share & Enjoy!

 

[Lemmy Winks]

I figured this out a little while after i made this thread, basically had to create another query to act as this query's parent query. In that query i converted all field to be either there default value if not null, and if they were null convert them to zero length empty string "". Once i did that the fields and values were usable in the main query.