changing nulls to other values

[mikeder]

Hi,

I have a relatively simple question. I have a field called "proposal phase" in my projects table. Sometimes the field contains numbers that indicate the particular phase of the proposal and other times there is a blank to indicate that no proposal was written for the project. How do I do it so that when there is a blank field for proposal phase, that it shows up as N/A.

Thanks

 

[Vassago]

You could set the default value for that field to "N/A". For anything already existing you could create an update query to update that field with "N/A" if the field is Null. If you need to know how to create the query let me know.

HTH

Vassago

 

[mikeder]

sounds like a good idea

thanks. I think your suggestion will work. how would I write the Update query?

thanks for you help!

 

[Vassago]

Create a new query in design mode. Choose the table you want to update to from the list, then select OK. On the menu bar choose "Query" and "Update Query" to change the Query to an update query. Then select "View" and choose "SQL View." This will bring up the SQL code for the update. Now copy and paste this code there.

UPDATE Projectstable SET Projectstable.[proposal phase] = "N/A"
WHERE (((Projectstable.[proposal phase]) Is Null));

Make sure that the proposal phase field in the table is set to string and NOT number, or else you will get errors when trying to change it to "N/A".

Let me know if that helps,

Vassago

 

[Vassago]

Change all instances of projectstable in that sql to whatever the name of your table is. Just thought I'd let you know to do that in case you didn't know.

Vassago

 

[mikeder]

works great!

Thanks for you help and speedy reply. I have one more question though. The code you sent me changes the ProposalPhase to be N/A if there is a null value present.

Is there a way to create one update query that changes both the ProposalPhase and the JobPhase to N/A if there is a null value present. I can do this by creating two update queries as you have shown me but I would like to do it all in one step.

thanks again!

 

[Vassago]

Try this:

UPDATE Table1 SET projectstable.[proposalphase] = IIf([proposalphase] Is Null,"N/A",[proposalphase]), projectstable.[jobphase] = IIf([jobphase] Is Null,"N/A",[jobphase])
WHERE (((projectstable.[proposalphase]) Is Null)) OR (((projectstable.[jobphase]) Is Null));

Let me know if that works,

Vassago

 

[mikeder]

Awesome!

Works like a charm! Thanks for all your help!!