Access Query Fill Empty Cells

In Table A, I want to update this table so that if Column B is empty, I will replace the value for that row with the value in Column A. If Columnb B is not empty, then the value in Column B will remain.


Example 1:

Row 1      Column A = "Dog"        Colmn B = " " (empty cell)
then this is updated to:
Row 1      Column A = "Dog"        Column B = "Dog"

Example 2;

Row 2     column A  = "Dog"         Column B = "Cat"
then this row will remain as it is since B was initially filled.

I prefer a non-SQL solution (i.w. how would I arrange this in the Query Design window using criterias and expressions.

Thanks.

tahirihAsked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
note, with the isnull, you can do this also

update mytable
set colb = cola
where colb is null
0
 
rockiroadsCommented:
how about this

update mytable
set colb = cola
where isnull(colb) = true
0
 
rockiroadsCommented:
given your example, what is actually in colb, is it totally empty? or is there a space or something? to handle empty strings or space in it

update mytable
set colb = cola
where trim$(nz(colb,"")) = ""

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
greyhumanConnect With a Mentor Commented:
Simple USE

IF ISNULL(B) Then B=A
ELSE B=B
0
 
tahirihAuthor Commented:
Please offer how I can create Table C table from Table A, and leave Table A unchanged, but the new Table C will have the udpates. The following did not work:

UPDATE A INTO C SET Div = Dept
WHERE trim$(nz(Div,""))="";

Thanks.
0
 
rockiroadsCommented:
You cannot do a update when creating new, it will have to be a insert

this is an example of a make table query, it will create tablec for you

select cola, nz(colb,cola)
into tablec
from tablea


to use existng table

insert into tablec (cola, colb) select cola, nz(colb, cola) from tablea


the above statements copies all rows including those where colb is not null but it will contain cola value in it if null

0
 
rockiroadsConnect With a Mentor Commented:
if u just want the records with nulls in it, just extend the select so you add in where colb is null
0
 
tahirihAuthor Commented:
Thanks.
0
All Courses

From novice to tech pro — start learning today.