Query question

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Query question

Post by silverback »

Our users often extract data from the DB so they can play with it in Excel e.g. they extract salary data for the annnual review.
There are many records, and because it's important that there are no null fields lurking (which might not be noticed and thus, affect teh Excel calculations etc) we provide a 'validation query' as well as the actual data extraction query.
This checks that none of the fields they want in Excel are null, and if there are, lists only the invalid records. The users then have to search each listed record looking for the null field. (although there may be more than one)
I would like to make the erroneous field(s) more obvious.
I still want to retrieve only records in error.
Is there a way to find such a record (currently criterion = null) and put ERROR in the offending field? It would also be great if ERROR could be in bold, red font (say).

Can it be done - simply?

Thanks
Silverback

User avatar
HansV
Administrator
Posts: 78230
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Query question

Post by HansV »

You could use Nz([FieldName],"ERROR") in a query to display ERROR for empty (null) values.

Query results cannot be formatted - you can only format controls on forms and reports.
Best wishes,
Hans

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Re: Query question

Post by silverback »

HansV wrote:You could use Nz([FieldName],"ERROR") in a query to display ERROR for empty (null) values.
I can't get this to work. When I put that expression on the Criterion line, the query returns zero records. If I remove this, the query correctly returns 1 record.
Checking the Access help, it says that the data type of the field should be Variant. I'm checking whether a date field has a value in it.
Clearly I am not understanding (again); can someone point out what I'm doing wrong, please?

Thank you
Silverback

User avatar
HansV
Administrator
Posts: 78230
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Query question

Post by HansV »

I thought you wanted the query to display ERROR instead of a blank. The expression Nz([FieldName],"ERROR") was intended for the Field: row of the query, not for the Criteria: row.

You can still use a condition in the column with the original field to select records. If you wish, clear the Show check box of this column, so that it is not displayed in the query result.
Best wishes,
Hans

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Re: Query question

Post by silverback »

HansV wrote:I thought you wanted the query to display ERROR instead of a blank. The expression Nz([FieldName],"ERROR") was intended for the Field: row of the query, not for the Criteria: row.
Well, yes, I do. I understand now . . . :blush:

Silverback