[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

Query in Access 2007

Field1 = Type (text field)
Field2 = DOA (Date field)
Field3 = TypeAddress (Text field)


New Field:=IIF([Type]=”92”,[DOA])

I need the NewField to show the DOA field if the Type equals 92
0
Delores_C
Asked:
Delores_C
  • 6
  • 5
  • 3
  • +1
1 Solution
 
mbizupCommented:
What should it be if Type is not 92?

This shows a blank:

IIF([Type]=”92”,[DOA], "")
0
 
Delores_CAuthor Commented:
I do not want anything to show
0
 
NorieCommented:
Are the slanted quotes a typo?

Have you tried single quotes?

NewField:Iif([Type]='92', [DOA], '')

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mbizupCommented:
Okay - the above should work for you.  Just use that as an expression for a new field:

NewField: IIF([Type]=”92”,[DOA], "")
0
 
mbizupCommented:
>> Are the slanted quotes a typo?

Good catch.  I bet they were copied in from MS Word.  I wonder if they make a difference?
0
 
Delores_CAuthor Commented:
Not working, this is an exact copy of the query I have.  

PatDOB: IIf([Reference]="PROGRESSIVE",[DateOfBirth],"")

The Reference is the Provider name field , the "Progressive" is the name of the provider and the DateOfBirth is the field I want to show only if the provider name is Progressive.
0
 
NorieCommented:
'Not working' how?

Error? Incorrect result? Crashing database?

0
 
mbizupCommented:
What is it showing?
0
 
Delores_CAuthor Commented:
I am not getting anything in the field.  Could it be because the results is a date field and the criteria is a text field?
0
 
mbizupCommented:
That shouldn't be a problem.

Double check that you have the criteria actually in your data *exactly*.

If you wan a non-exact match, use this:

PatDOB: IIf([Reference] LIKE "*PROGRESSIVE*",[DateOfBirth],"")

That will allow anything that contains 'progressive'.
0
 
NorieCommented:
The field types shouldn't matter.

Where are you using this expression?

Query? Form? Report?
0
 
peter57rCommented:
Is Reference defined as a lookup field?

If so, you have to test for its (probably numeric) ID value, not the visible value.
0
 
Delores_CAuthor Commented:
it's working, thanks alot!
0
 
mbizupCommented:
Good deal :)
0
 
Delores_CAuthor Commented:
Excellent instructions
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 6
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now