[Webinar] Streamline your web hosting managementRegister Today

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

multiple criteria If-Then-Else embedded in Access query

Okay, I have a field named "Comments" that I've pulled into an access query.  I need to embed the following if-then-else statements without them overriding one another.  I tried embedding one if-then-else, then querying that query with the other if-then-else and I lose the results of the first query.

I need the If-then-else to say:

Comments: IIf([Status]="LR" And [Wash Date]<[UnitStatusDate],"Lease Returned on" & " " & [UnitStatusDate] & " " & " - Prior to Wash Date")

and

IIf([Status]="Sell" of "SellF" or "SellP" or "Sold1" or "Sold2", And [Wash Date]<[UnitStatusDate],"Truck placed on Sell List on" & " " & [UnitStatusDate] & " " & " - Prior to Wash Date")

This is urgent.  Help!



   
0
jcuniglio
Asked:
jcuniglio
  • 2
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can there be more values for Status than LR, Sell, Sellf, SellP, Sold1 or Sold2? If not, then just use the first IIF statement with this exception:

IIf([Status]="LR" And [Wash Date]<[UnitStatusDate],"Lease Returned on" & " " & [UnitStatusDate] & " " & " - Prior to Wash Date", ],"Truck placed on Sell List on" & " " & [UnitStatusDate] & " " & " - Prior to Wash Date")

If there CAN be other values, then try something like this:

IIf([Status]="LR" And [Wash Date]<[UnitStatusDate],"Lease Returned on" & " " & [UnitStatusDate] & " " & " - Prior to Wash Date",IIf([Status]="Sell" of "SellF" or "SellP" or "Sold1" or "Sold2", And [Wash Date]<[UnitStatusDate],"Truck placed on Sell List on" & " " & [UnitStatusDate] & " " & " - Prior to Wash Date"))


0
 
jcuniglioAuthor Commented:
Well, we're moving in the right direction, however, it's populating EVERY field with the second 'else', regardless of the value of 'Status'.  Yes, there CAN be other values.
0
 
Rey Obrero (Capricorn1)Commented:


IIf([Status]="LR" And [Wash Date]<[UnitStatusDate],"Lease Returned on" & " " & [UnitStatusDate] & " " & " - Prior to Wash Date",IIf([Status]="Sell" or [Status]= "SellF" or [Status]="SellP" or [Status]="Sold1" or [Status]="Sold2" And [Wash Date]<[UnitStatusDate],"Truck placed on Sell List on" & " " & [UnitStatusDate] & " " & " - Prior to Wash Date"))
0
 
Rey Obrero (Capricorn1)Commented:

IIf([Status]="LR" And [Wash Date]<[UnitStatusDate],"Lease Returned on" & " " & [UnitStatusDate] & " " & " - Prior to Wash Date",IIf([Status]="Sell" or [Status]= "SellF" or [Status]="SellP" or [Status]="Sold1" or [Status]="Sold2" And [Wash Date]<[UnitStatusDate],"Truck placed on Sell List on" & " " & [UnitStatusDate] & " " & " - Prior to Wash Date","Whatever"))
0
 
jcuniglioAuthor Commented:
You are a God and genius amongst simple men.  Thanks!!!!!!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now