How to get Access to check for a condition in a table

[jimtimber]

Hello,

Apologies if this is in the wrong thread, I'm relatively new to Access 2010 and not sure if this is a table question.

I want Access to look in one table for a certain condition, and if it is true to autofill text in a different column but I've never done this before.

Specifically, I have a column in a table that is named "Letter Received" and another named "Letter Received Unsigned" which is in date format. I want Access to write "N/A" in the "Letter Received Unsigned" column if there is a date entered in the "Letter Received" column.

Any help would be greatly appreciated!

Thanks,

Jim.

 

[pr2-eugin]

Hello jimtimber, Welcome to AWF

It is an illusion that you think you need this value to be stored in the table, but in reality you do not need to store this value. This is called as Calculated values, and it is not good to store this info. Infact this is breaking one of the fundamental principal of DB Design.

If you need this info to be presented, create an unbound control on a Form or a Query column, again no need to save this info.

 

[jimtimber]

Thanks for the welcome pr2-eugin! I'm currently learning Access so some of my Qs might seem obvious.

I run a report each week and the manager has asked that in the report for any blanks "N/A" be added. I'll have a read up on unbound controls as I've never heard of them!

Thanks again!

 

[gemma-the-husky]

all you need to do is base your report on a query, and add a column to the query that evaluates as N/A

something like this

=iif(is null([confirmedfield],"N/A",[confirmedfield])

then the boss sees what he wants to see.

 

[pr2-eugin]

Or

= [URL="http://www.techonthenet.com/access/functions/advanced/nz.php"]Nz[/URL]([theFieldName], "N/A")

 

[jimtimber]

Hi again!

PR2_eugin, The Nz function was really useful, thank you. Out of interest, if these an expression that does the opposite to Nz (i.e. lets you return a value when a variant is not null)

Gemma, I couldn't get the "=iif(is null([confirmedfield],"N/A",[confirmedfield])
" expression to work, it kept saying "the expression entered contained the wrong number of arguments". What am I doing wrong?

Thanks everyone!

 

[pr2-eugin]

Out of interest, if these an expression that does the opposite to Nz (i.e. lets you return a value when a variant is not null)

In that case you need to use Dave's code.

[B]= IIF(IsNull([confirmedfield]) = True,"N/A", "Whatever the value you want")[/B]

 

[jimtimber]

Thank you!