CODEWITHSUNDEEP
ms-access
Doug Fir
Doug Fir

Reputation: 21204

Adding a new calculated field in Access

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.

Upvotes: 0

Views: 196

Answers (3)

Linger
Linger

Reputation: 15048

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

Upvotes: 1

Mark C.
Mark C.

Reputation: 6450

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.

Upvotes: 4

Lyle
Lyle

Reputation: 421

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

Upvotes: -3

Related Questions