Query wont show records with a blank field

[bignate]

Hi,

I have a query by form that has the criteria Like [Forms]![FormName]![ControlName] & "*" for each field. However the problem is that if one of the records have at least one blank field then the whole record wont show up in the query results. For example if there is a record with Fullname, and Age filled in but Address isn't filled in then if search Adam into the query by form the record wont show up because the address line is blank.

To make records that have a blank field show I know I could use Like Forms![FormName]![ControlName] & "*" Or Forms![FormName]![ControlName] Is Null but when I have used it, it has corrupted the query because I think if you use If Is Null many times in one query it becomes too complex for it to process. AlsoThe other method is to use Nz in an expression but I cant do that because I want the query results to show up in a form where you can edit the records and the error message Field is based on an expression and can not be edited comes up if you try to edit the records.

So my question is does anyone know anything else I could put into the criteria to show records that may have a blank field?

Thanks

 

[Brianwarnock]

As you have discovered Like will not return Nulls, and thus you have to use the Is Null approach. I was not aware that there was a limit to the number of times that you can do this, however it is true that if you do it in the design grid and then save the query what the GUI produces is horrendous, maybe that is where the problem arises.
Having initially designed your query with the Is Null switch to SQL view and edit the Where clause adding all of the OR ...Is Null conditions, now only save the query from the SQL view. It is worth creating new lines at each And to make the job simpler.

I have assumed that you don't want to code it all in the SQL view. Note that you can still view the query in the Design grid, just don't save it from there.

Brian

 

[JHB]

...
For example if there is a record with Fullname, and Age filled in but Address isn't filled in then if search Adam into the query by form the record wont show up because the address line is blank.

To make records that have a blank field show I know I could use Like Forms![FormName]![ControlName] & "*" Or Forms![FormName]![ControlName] Is Null ..

So my question is does anyone know anything else I could put into the criteria to show records that may have a blank field?

Thanks

Maybe I misunderstand the above, but if there is missing some data in a field, then the criteria after the 'Or' isn't correct, because as it is now you are testing if the control in the form is null.

Like Forms![FormName]![ControlName] & "*" Or Is Null ..

 

[Cronk]

When you write
... error message Field is based on an expression and can not be edited comes up if you try to edit the records

it must be due to the query having a calculated field, not in calculations in the criteria.

 

[Brianwarnock]

Maybe I misunderstand the above, but if there is missing some data in a field, then the criteria after the 'Or' isn't correct, because as it is now you are testing if the control in the form is null.

There are two distinct options
As you have it the field will always be selected if it is null, thus if he asks for Like b* then not only will Brian etc be selected but also will all nulls.

The other option is when coded to test the criteria for null, and it is, then all records match for that field and the final selection of records is based on other fields' selection.

The use of Like to enable partial string criteria causes the problem, if that is not needed then


Where (fielda= forms!srchform!controla, or forms!srchform!controa is null)
And (fieldB = etc

Is the norm, hopefully with the controla etc being combos

Brian

 

[bignate]

I will try what Brianwarnock suggested and then reply telling you if I got it to work. Cronk yeah the error message was due to having a calculated field. I understand that I am using the first option and that nulls will return but it is not much of a problem for me. I didn't fully understand the second option that brianwarnock suggested and I do have to use 'Like'.

Thanks for the responses guys

 

[bignate]

Hi Brianwarnock would you be able to elaborate on the second option because maybe it would be better than the first option for me?

I tried to do what Brianwariwcik told me to do in his first postbut I don't think I done it correctly because now every single record shows no matter what I enter into the search. This is what I put into the SQL View:

SELECT OperationalRiskEventTable.DateReported, OperationalRiskEventTable.TimeReported, OperationalRiskEventTable.[Full Name], OperationalRiskEventTable.[Event Category], OperationalRiskEventTable.[Event Description], OperationalRiskEventTable.Product, OperationalRiskEventTable.[Place of Occurence], OperationalRiskEventTable.[How Discovered], OperationalRiskEventTable.Currency, OperationalRiskEventTable.Amount, OperationalRiskEventTable.[GBP Equivalent], OperationalRiskEventTable.[Recoveries/Mitigation], OperationalRiskEventTable.[Operational/Service Impact], OperationalRiskEventTable.Status, OperationalRiskEventTable.[Department Assigned To], OperationalRiskEventTable.[Person Assigned To], OperationalRiskEventTable.[Date Closed]
FROM OperationalRiskEventTable
WHERE (((OperationalRiskEventTable.DateReported)>=Forms!SearchPage!SDateBegin And (OperationalRiskEventTable.DateReported)<=Forms!SearchPage!SDateEnd)
And ((OperationalRiskEventTable.[Full Name]) Like Forms!SearchPage!SearchFullName & "*")) Or (((Forms!SearchPage!SearchFullName) Is Null))
And ((OperationalRiskEventTable.[Event Category]) Like Forms!SearchPage! SearchEventCategory & "*")) Or (((Forms!SearchPage!SearchEventCategory) Is Null))
And ((OperationalRiskEventTable.[Place of Occurence]) Like Forms!SearchPage!SearchPlaceOfOccurence & "*")) Or (((Forms!SearchPage!SearchPlaceOfOccurence) Is Null))
And ((OperationalRiskEventTable.Currency) Like Forms!SearchPage!SearchCurrency & "*")) Or (((Forms!SearchPage!SearchCurrency) Is Null))
And ((OperationalRiskEventTable.Amount) Like Forms!SearchPage!SearchAmount & "*")) Or (((Forms!SearchPage!SearchAmount) Is Null))
And ((OperationalRiskEventTable.[Date Closed]) Like Forms!SearchPage!SearchDateClosed & "*")) Or (((Forms!SearchPage!SearchDateClosed) Is Null))

 

[Brianwarnock]

Although it looks fine the bracketing is incorrect, I think the Where clause should be

WHERE ((OperationalRiskEventTable.DateReported)>=Forms! SearchPage!SDateBegin And (OperationalRiskEventTable.DateReported)<=Forms!Se archPage!SDateEnd)
And (OperationalRiskEventTable.[Full Name] Like Forms!SearchPage!SearchFullName & "*" Or Forms!SearchPage!SearchFullName Is Null)
And (OperationalRiskEventTable.[Event Category] Like Forms!SearchPage! SearchEventCategory & "*"  Or Forms!SearchPage!SearchEventCategory Is Null)
And (OperationalRiskEventTable.[Place of Occurence] Like Forms!SearchPage!SearchPlaceOfOccurence & "*" Or Forms!SearchPage!SearchPlaceOfOccurence Is Null)
And (OperationalRiskEventTable.Currency Like Forms!SearchPage!SearchCurrency & "*" Or Forms!SearchPage!SearchCurrency Is Null)
And (OperationalRiskEventTable.Amount Like Forms!SearchPage!SearchAmount & "*" Or Forms!SearchPage!SearchAmount Is Null)
And (OperationalRiskEventTable.[Date Closed] Like Forms!SearchPage!SearchDateClosed & "*" Or Forms!SearchPage!SearchDateClosed Is Null)

Brian

 

[Brianwarnock]

I did not understand your comment about the second option as what you have coded is for the second option.

Try with the new Where clause

Brian

 

[bignate]

Thank you very much, it now works. Ignore what I said about the second option, at first I didn't realise that what you were describing was what I was doing.