How do I do this query?

  • Thread starter Thread starter William Gates
  • Start date Start date
W

William Gates

I am working in Access 97:

Any field that is "null" is my criteria.

I need to have an "n" put in any field that is "null."

How do I do this?

Thanks for any help.
 
You can use the null-to-zero (NZ) function:

SELECT NZ([Field1], "n") As FirstField, NZ([Field2], "n") As SecondField ...
FROM MyTable

Caution: Because you're asking NZ to return a character string if the field
is null, all fields will be returned as text - including any that are
originally numbers.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Start by making a simple select query on the table, and type "Is Null"
(without the quotes) in the criteria line below the field in question. Run
the query to verify that it returns the correct records, and when happy
return to the design view and from the menu go Query > Update to change it
to an update query. Note that now there is a new line in the grid, reading
Update To on the left. In that line type "n" under the field in question and
run the query (click on the red exclamation mark button in the toolbar).
This will do the job.

HTH,
Nikos
 
Where do I enter this?

Is it in the query screen where the fields are lined up?

There is Field, Table, Sort, Show and Criteria. Do I set
up the [SELECT NZ([Field1]] in the Criteria line?

Sorry for being so brainless here.
 
Where do I enter this?

Is it in the query screen where the fields are lined up?

There is Field, Table, Sort, Show and Criteria. Do I set
up the [SELECT NZ([Field1]] in the Criteria line?

Sorry for being so brainless here.
 
If you're doing this on the query grid, enter the NZ expression for each
field on the Field line. You can give each expression a name by entering
the name, a colon, and the expression. Like this:

SomeField: NZ([Field1], "n")

Note that "Field1" is the name of one of the fields from your table that you
want to show up as "n" if null.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top