Link to home
Start Free TrialLog in
Avatar of Aiysha
AiyshaFlag for United States of America

asked on

if else statement in access query.

How can I use if else statement in access query

API10       API14


If API14 is empty then set API14= API10 & "000"

if API10 is empty then set API10= left (API14, 10)

I would really appreciate any help.

Thank you.
Avatar of Daniel Pineault
Daniel Pineault

Something along the lines of:

UPDATE YourTableName SET YourTableName.API14 = [API10] & "000"
WHERE (((YourTableName.API14) Is Null Or (YourTableName.API14)=""));

Open in new window


UPDATE YourTableName SET YourTableName.API10 = Left([API14],10)
WHERE (((YourTableName.API10) Is Null Or (YourTableName.API10)=""));

Open in new window

you didnt indicate you want to update those fields, so be careful of doing that.

so, do you want two separate values or a single value?  I'd start with something like this:

SELECT [API14], [API10] & "000" as [API14Alt], [API10], LEFT([API15] & "", 10) as [API10Alt] FROM yourTable

It would be helpful if you would provide a couple of rows of data and what you want the query to return.
Avatar of Aiysha

ASKER

Thank you Daniel,

I was wondering if there is anyway to first find if the row (cell) is empty and them update date it based on the other row (cell), instead of doing straight update.
Avatar of Aiysha

ASKER

API10                  API14
1234564551                                                         <----- update API14 = 12345645510000
                            78600112220100                    <------- update API10 =7860011222
5656445566      56564455660000                     <------- dont do anything with this since both columns have values.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I was wondering if there is anyway to first find if the row (cell) is empty and them update date it based on the other row (cell), instead of doing straight update.
That is effectively what the WHERE clause is doing, finding only the matching records and leaving the rest alone.  Make a copy of your backend and try it out for yourself.
Avatar of Aiysha

ASKER

Thank you.