CODEWITHSUNDEEP
ms-access
paul steven
paul steven

Reputation: 51

access iif query do nothing when false

My situation/problem:

TableA
id, postalcode, region

Criteria for the field i want to add value in (postalcode):

=IIf([tableA.Region]="Chicago","60064",[postalcode])

What i need to accomplish is an iif-query where:

In the field postalcode I check in the criteria if the field region equals Chicago. If so fill up the field with postalcode 60064 if not DO NOTHING

For the do nothing part I'm using the fields name, this is wrong? am i using the criteria in the right field (the field i want to add?) I'm using a selection query?

As you can see i'm a noob in access queries...

Can somebody give me the right iff statement? Thx in advance, D

Upvotes: 3

Views: 13971

Answers (5)

Choxmi
Choxmi

Reputation: 1664

As far as I can understand,

if not DO NOTHING

You need to return nothing if the condition is false.

What you need is the WHERE clause instead of IIF

Upvotes: 1

Faisal Anwer
Faisal Anwer

Reputation: 1

Please add another IIF query as OR

IIf([Forms]![Run_Macro Form]![ReturnType_DrpDwn]="Monthly",[Forms]![Run_Macro Form]![Cmb_Per_Ending],#1/1/2010#)

IIf([Forms]![Run_Macro Form]![ReturnType_DrpDwn]="Quarterly",#1/1/2010#,#1/1/2025#)

Upvotes: 0

Rhdr
Rhdr

Reputation: 387

This is a dead old post but I think this was what you wanted:

=Like IIf([tableA.Region]="Chicago","60064",'*')

Upvotes: 1

Johnny Bones
Johnny Bones

Reputation: 8402

If I'm understanding your question correctly, you want a NULL value in Postal Code if it's not Chicago?

=IIf([tableA].[Region]="Chicago","60064","")

Upvotes: 0

HansUp
HansUp

Reputation: 97101

I'll suggest you bracket table.Region differently, or eliminate the brackets there entirely.

=IIf([tableA].[Region]="Chicago","60064",[postalcode])
=IIf(tableA.Region="Chicago","60064",[postalcode])

If that doesn't fix the problem you're trying to solve, tell us more about the problem. If you're getting an error message, tell us what it says.

Taking a wild guess, that code as a field expression in a query will not give you an editable column in the query result set. If you want to pre-load a column value based on your criteria, but then allow the user to change the value, use a form. In the form's On Current event, load the value as you wish.

Upvotes: 1

Related Questions