Automate creating query with Nz function (1 Viewer)

[jaryszek]

Hi Guys,

i have a table where i have nulls.
I want to for each column where i have null use Nz function in order to have result "Null" as string.

Problem what i have is that i have to go manually for each column, set up Nz function and it takes a long time.
It is the way to automate it somehow in VBA or with different way?

So steps:
1. Take existing columns in new query
2. Add Nz function for each field from table
3. Save Query

Can you please help?
Jacek

 

[cheekybuddha]

Hi,

There is no easy way.

You can write code to build the query for you but it would take just as long as to write out all the fields with Nz().

 

[arnelgp]

edit your table and in each field at Format:

;;;"Null"


for (short/Long text):

@;"Null"

 

[jaryszek]

Thank you Guys,

hmm but i will use this function checkyybuddha all the time.

;;;"Null"

What does it mean Arnel?
This is not Nz function, it will work the same?

Jacek

 

[cheekybuddha]

Jacek,

See this post/thread for explanation of ;;;"Null"

 

[jaryszek]

thank you,

i wrote this topic but still i do not catch it. they are changing colors there.
How this format is better than nz function?

Jacek

 

[cheekybuddha]

See the part:

for numbers, there are 4 sections to the format property (not the function)

positive; negative; zero; null

If you enter some text in the fourth section (ie after the third semi-colon) it will display that text when the field value is null.

It is a very neat solution from Arnel to your problem.

 

[jaryszek]

hmm it is better than Nz?
Safe?

And what about strings, dates and so on?

Jacek

 

[jaryszek]

And how this is working? It will be treated as string or it is just display info. In Excel when i am changing format this is only for displaying...

 

[cheekybuddha]

Test it out and let us know!

 

[jaryszek]

cheekybudda just follow this topic. Anybody knows and can explain in simple technical English how this format is working what Is the diilfference between nz function and if this work together with joins?

 

[Minty]

It's a display format, not data.

 

[jaryszek]

thank you Minty,

this is explaining a lot. This is not what i want to do.
Maybe i could have the function for writing query or something like that?

Best,
Jacek

 

[Minty]

If this is for exporting to Excel there are better ways of doing it. (A bit of VBA from Access to the Excel export can easily apply this.)

If not I have to question its value as you will no longer be able to add those columns up in a report etc. Perhaps you can explain its end purpose?

 

[jaryszek]

thank you Minty.

This is for importing raw data into tables from csv without primary key. I have to check what was added, deleted and updated.
If you have nulls join just will not work. But if you are using Nz function for nulls - join is working without any issues.

So i can take null columns, change them to "null" and join easily.
Best wishes for you,
Jacek

 

[Isaac]

Pardon me for jumping in, but this topic is of interest to me, as I'd never seen that trick used either.

But @jaryszek , am I correct that what you are wanting to do is actually UPDATE the VALUES/DATA in the tables, replacing all Null values with Zero length string? ("") ? That is what you want to do right? I have been reading this trying to understand the problem vs. the solution, as the whole thing is very curious--starting from the desire of the OP.

 

[jaryszek]

Hi pisorsisaac@gmail.co,

i want to replace all nulls values from table columns and create query which will change them not to "" but to string "Null".

Jacek

 

[Isaac]

I see. Thanks for clarifying.

 

[Minty]

Rather than adjusting all your queries, I would simply have a function that looped through all the fields in a given table and replaced Null values.
This means you only do it once after import, rather running a million NZ() functions every time you query your data.

 

[Isaac]

Rather than adjusting all your queries, I would simply have a function that looped through all the fields in a given table and replaced Null values.
This means you only do it once after import, rather running a million NZ() functions every time you query your data.

I agree. I was thinking of suggesting this but, as an optimization student, I felt embarrassed to suggest an inefficient loop. Ha ha. But you might as well get it done once and for all. Loop through the tabledef and all fields.