Querying Three Joined Tables with Nulls

[DHaedo]

Hi,
I have been working on a creating a form for querying records from an external database source. All three tables have a unique ID "AAA-00000", table 1 has 4000 records with no duplicate ID's, table 2 8000 records with duplicates, table 3 has only 1000 records with no duplicates. I am cannot make the unique ID a primary key, but I can use it to link between the three tables. Using outer joins to link table 2 & 3 to table 1 yield the correct number of records. Once I add criteria to the the query it then limits the records to between the tables (using [Forms]![FrmA]![cboPick] for example).



I have tried using in [Criteria] "Like IIf([Forms]![frmA]![cboPick]=" ","*",[Forms]![frmA]![cboPick])" and "Like "*" & [Forms]![frmA]![cboPick] & "*"" with wildcards (and other variations) but the criteria limits the records, and if I use "is Null" in the [Or] field it ignores the criteria.

Can anyone help with the expression that will include Nulls?

Thanks!

[John_G]

What field are you applying the criteria to?


What does the value in [cboPick] look like, i.e. what table field value does it contain?

How do you make the combo box contain a blank, rather than Null?

[DHaedo]

I am applying the criteria to a field called [StrGrade] (number field), this is in table 3 and only has the unique ID field (text) with the [StrGrade] field.

The values in [cboPick] are based off another table with just the values [ ,1,2,3,4,5] as a Number field.

To clear cboPick I have a "Clear" button that me.cboPick = "", I also have a master clear button for all the combo boxes that does the exact same function (me.cbo### = "") for all the combo boxes.

[John_G]

Based on that, your criteria expression is way too complicated.

The table field the combo box is based on contains numeric data; therefore each occurrence will contain either a number or Null - it cannot contain a blank or a zero-length string. So, that means the combo box value can only be a number or Null. Your comparisons with Like and wildcards don't mean anything.

In your query, the values in [StrGrade] will equal the value in the combo box, or they will be Null. So two criteria cells for StrGrade. They are:

[Forms]![frmA]![cboPick]
Is Null

Now, there is one big caveat to all that. The criteria cannot distinguish between a query row which has a record in table3 containing an ID value but no [strGrade] value (your data may not have any like that), and a query row which has no Table3 data at all (you're using an outer join).

This should work for you.

[DHaedo]

Thanks John, I have redesigned part of my database to join the tables first (in sql), and then query on the table. I am still having issues with zero length fields from the joined table and the query. I am unable to convert zero length cells to nulls (or anything) while joining the tables. Any thoughts on converting zero length cells using sql code?
Thanks