Adding a new calculated field in Access

0
votes

I'm sure that this question has been asked many times but I did Google around and got a little lost so hoping for some help:

I'd like to create a new calculated field "accurateMatch". If [EMAIL] is populated accurateMatch should contain the contents of [EMAIL], else it should be empty. This will be a clone of [EMAIL] right now but I'll add nested IIFs if I can get this part working for now.

I have a table with hundreds of thousands of records. In some cases we have [EMAIL] in others we do not. I know from sorting the table alphabetically that there are ~ 100k records with no email address. I then opened query builder and added an expression in a new empty text field that I created called "accurateMatch":

IIf(IsNull([EMAIL]),"",[EMAIL])

But rather than produce query which results in hundreds of thousands of records, some of which have a populated [hasEmailForMatching] field, the results are an empty query. The field titles are there but nothing else?

Why is this happening and how can I resolve it?

Here is the SQL view following comment. Is there any other info I can give that could help people help me here?

SELECT someList.accurateMatch
FROM someList
WHERE (((someList.accurateMatch)=IIf(IsNull([EMAIL]),"",[EMAIL])));

I suspect my goal is unclear after reading the answers. I want to create a new field that contains EMAIL where email exists. I went down a rabbit hole of readong some MS documentation that suggested I run query wizzard and build the query, then use update. I'm a little lost since been working with SQL only for past few months and am struggling with Access.

3
ms-access
It's unclear what you're asking, because you have not posted your SQL. – Mark C.
Posting that snippet has shed a little light actually. My goal is to add a new field that is email if email is populated, else first name + last name if email is not populated. I just wanted to get the expression running first before adding the name part. It looks like this expression is filtering on an already empty field (I just created "accurateMatch". I want to create a new field that is the contents of email when email exists, else something else. @GordThompson posted this comment as you were writing yours. Do you follow? – Doug Fir
Okay, so do you want: SELECT someList.accurateMatch FROM someList WHERE IsNull([someList].[EMAIL]) ? – Mark C.
I think I've asked the wrong question. Unsure if I should delete and post again or keep with this thread but here goes: I'd like to create a new field called "accurateMatch" that contains the contents of [EMAIL] if [EMAIL] is populated, else blank "". (I know that it will basically be an exact clone of [EMAIL] but I'll add in nested conditions after I get this part working – Doug Fir
Just edit your question. No problem. If the question title reflects something different, edit that also. – Mark C.

3 Answers

4
votes

If you wanted to create a new field, you use your expression in the SELECT statement.

SELECT someList.EMAIL, IIF(IsNull(someList.EMAIL), "", someList.EMAIL) AS accurateMatch
FROM someList

What does this do? It lists, field by field :

IIF - If Then
[condition] - IsNull(someList.EMAIL) - If someList.EMAIL is null, it will fall into this condition
true_expression - "" - If someList.EMAIL is NULL, you will see nothing
false_expression - [somelist.EMAIL] - If someList.EMAIL is NOT Null, you will see the email

You can view them side by side to compare results.

1
votes

Try using Len() instead:

SELECT someList.accurateMatch
FROM someList
WHERE someList.accurateMatch = IIf(Len(EMAIL & "") <> 0, EMAIL, "")

EDIT:

SELECT IIf(Len(EMAIL & "") <> 0, EMAIL, "") AS accurateMatch
FROM someList
-3
votes

SQL Database have a special data type called DBNull please try researching this an maybe it will help you.