Update blanks to zeros

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created update queries to change the blanks in several tables to
zeros. At first I put multiple tables in one query and it didn't like that
because the tables were not related. So I made a query just using one table
and it worked perfectly. Then I created the exact same type of query for all
the other tables and they do not work. I even went back to the first table
where the query had worked...I changed some zeros back to blanks....and now
the same query doesn't work for that either. It's a simple query. What is
going on?

- I have double checked that all the fields to be updated are long integers
- Update to 0
- Criteris is null

Any ideas?

Thanks for your help!!
Kristine
 
If you start with nothing there (a Null) and convert it to a zero, you have
zero. If you delete the zero, you have ... either a null or a zero-length
string, depending on HOW you deleted the zero.

A zero-length string is NOT a Null.

In your query, try Is Null or = "" (two quote marks, denoting a zero-length
string).

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I get a 'Data type mismatch in criteria expression' error when I put the
criteria as ="". I have not deleted any zeros. This is imported data which
has some blank fields. When I import the data these records are set to be
long integers.
 
Sorry if I obscured my point. A "blank" ("I can't see anything") could be a
Null, or could be a zero-length string.

I'm not sure why you're running into this issue. Can you confirm the
accuracy of your import data? Are you using Access import, or are you
copying/pasting?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I understood what you meant, but still, using ="" as a criteria gave me that
error. Wouldn't that apply more to a text field anyway? I am using Access
import to import my data. The files I import are text files but as part of
the import it changes the field type on these numeric fields to long
integers. I don't know. I'm sure it's just something goofy I have over looked
so I'll keep playing with it. Thanks for your help!
Kristine
 
Kristine

Yes, the "" would fit with text data types.

If the table created when Access imports your data gives it the wrong data
type, consider using a two-step process. The first step imports data as
Access chooses. The second step involves using queries to "parse" your data
into your (more) permanent tables, giving the data the correct type using
functions like CCur(), CDate(), etc.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top