Brianwarnock
Retired
- Local time
- Today, 02:27
- Joined
- Jun 2, 2003
- Messages
- 12,701
Think of it like this , if the criteria is null then the test Is Null is correct against every record, so the record is selected.
Brian
Multi-field query not working when some fields are null value
- Thread starter Erik777
- Start date
Think of it like this , if the criteria is null then the test Is Null is correct against every record, so the record is selected.
Brian
I think I misunderstood the question because Erik was talking about Nulls in "fields" when he really meant "controls" (the combos).
In Access the fields are in tables, queries and recordsets. The boxes on forms are called controls.
Hi Galaxiom,
Actually, I was talking about nulls in the fields (within the table) and not in the controls on the form. Which is why I was (maybe still am a little) confused as to how Brian's suggested changes are actually working because it doesn't ever seem to test the fields in the table for null value.
I don't understand it well enough to explain it though.
Erik
Actually, I was talking about nulls in the fields (within the table) and not in the controls on the form. Which is why I was (maybe still am a little) confused as to how Brian's suggested changes are actually working because it doesn't ever seem to test the fields in the table for null value.
I don't understand it well enough to explain it though.
Erik
Brianwarnock
Retired
- Local time
- Today, 02:27
- Joined
- Jun 2, 2003
- Messages
- 12,701
How do you want table fields that are null handled?
Do you want to be able to actively search for them?
If all 3 table fields against which you apply criteria are null do you want that record selected when selecting all records? This will happen with the approach I suggested, as I advised at the time, but not when using Like.
Is there another requirement?
Brian.
Do you want to be able to actively search for them?
If all 3 table fields against which you apply criteria are null do you want that record selected when selecting all records? This will happen with the approach I suggested, as I advised at the time, but not when using Like.
Is there another requirement?
Brian.
Hi Brian,
If I have the following data in my table:
Name Office Title
Erik Phoenix ITGuy
Bill Las Vegas Accountant
Jim Salt Lake Engineer
John null Accountant
Fred Phoenix null
Using my form with 3 controls, if I leave Title and Name blank and only select Phoenix in the office combobox control, I want to see the records for Erik and Fred returned. Same scenario but leaving Office and Name blank and only selecting Accountant in the Title combobox field, I want to see the records for Bill and John in the results. If I leave all the fields in the form blank and just hit the query button, I want to see ALL records returned, which it is doing. No other criteria.
In my case, there would (should) not ever be a record entered with all null values. I guess I never specified that but there should not be a record in there for a person with no name.
And that is the case for the real database I'm working on. There are about 10 fields that (with corresponding controls on the form) that need to be searchable in this way. Of those 10, 8 were required fields as they were being input into the Sharepoint list where this data originated. 2 were not so they are the fields with null data in them. A record with all null values does not and will not exist in this database.
I hope that makes sense or clears things up. Sorry for any confusion I may have caused.
As I said, this little example database I created is producing the desired results (as far as I can tell) after using your last suggestion. It's problem solved as far as I'm concerned.
If I have the following data in my table:
Name Office Title
Erik Phoenix ITGuy
Bill Las Vegas Accountant
Jim Salt Lake Engineer
John null Accountant
Fred Phoenix null
Using my form with 3 controls, if I leave Title and Name blank and only select Phoenix in the office combobox control, I want to see the records for Erik and Fred returned. Same scenario but leaving Office and Name blank and only selecting Accountant in the Title combobox field, I want to see the records for Bill and John in the results. If I leave all the fields in the form blank and just hit the query button, I want to see ALL records returned, which it is doing. No other criteria.
In my case, there would (should) not ever be a record entered with all null values. I guess I never specified that but there should not be a record in there for a person with no name.
And that is the case for the real database I'm working on. There are about 10 fields that (with corresponding controls on the form) that need to be searchable in this way. Of those 10, 8 were required fields as they were being input into the Sharepoint list where this data originated. 2 were not so they are the fields with null data in them. A record with all null values does not and will not exist in this database.
I hope that makes sense or clears things up. Sorry for any confusion I may have caused.
As I said, this little example database I created is producing the desired results (as far as I can tell) after using your last suggestion. It's problem solved as far as I'm concerned.
Brianwarnock
Retired
- Local time
- Today, 02:27
- Joined
- Jun 2, 2003
- Messages
- 12,701
Thanks for the feedback, the situation is what I expected.
Glad to see somebody using a test database that they can control the scenarios in, too many users these days seem to go straight to the live data, not only does this mean that they cannot attach their DB here but how the hell do they know that they have covered all the options?
Just an old timer
Brian
Glad to see somebody using a test database that they can control the scenarios in, too many users these days seem to go straight to the live data, not only does this mean that they cannot attach their DB here but how the hell do they know that they have covered all the options?
Just an old timer
Brian
Similar threads
