If/Then/Else Query

[shm135]

Hi,

I have five date columns in my table:

Date 1, 2 ,3 4, 5

I'm not sure how to do this in access but I want to create a query
that show all the records that does not meet this criteria:

IF Date3 is not blank THEN Date4 must be less than or equal to Date2.
ELSE IF Date3 AND Date2 are BLANK THEN Date4 must be less than or
equal to Date1.

Please, if someone can help me write this query. Thank you.

 

[Ken Snell MVP]

Hi,

I have five date columns in my table:

Date 1, 2 ,3 4, 5

I'm not sure how to do this in access but I want to create a query
that show all the records that does not meet this criteria:

IF Date3 is not blank THEN Date4 must be less than or equal to Date2.
ELSE IF Date3 AND Date2 are BLANK THEN Date4 must be less than or
equal to Date1.

Please, if someone can help me write this query. Thank you.

 

[Ken Snell MVP]

I'd be inclined to use this query:

SELECT *
FROM TableName
WHERE (Date3 IS NOT NULL AND Date4 <= Date2)
OR (Date3 IS NULL AND Date2 IS NULL AND Date4 <= Date1);

 

[Jeff Boyce]

Access is optimized for well-normalized data. "Five date columns in [your]
table" is NOT well-normalized.

Expecting Access to be able to handle data structured like this is a little
like trying to drive nails with a chainsaw ... it can be done, but...

You'd get a lot more use out of Access if you'd start out by normalizing
your data, THEN work on your comparison...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[vanderghast]

NOT (
(Date3 IS NULL AND Date4<= Date2)
OR
(Date3 IS NULL AND Date2 IS NULL AND Date4 <= Date1)
)


or, if you prefer


NOT (
Date3 IS NULL AND Date4 <= Nz(Date2, Date1)
)


Vanderghast, Access MVP

 

[shm135]

NOT  (
        (Date3 IS NULL AND  Date4<= Date2)
        OR
        (Date3 IS NULL AND Date2 IS NULL AND Date4 <=  Date1)
        )

or, if you prefer

NOT (
          Date3 IS NULL AND Date4 <= Nz(Date2, Date1)
          )

Vanderghast, Access MVP











- Show quoted text -

Thank you both so much for your help. Worked perfectly. I understand
that my data isn't exactly organized best for Access, but I would
really like to get this working the way I want. I have another
question:

I have the following fields:
Location- (this field can be duplicated)
Price1
Price2

Currently the query returns 3 results per location- 1 out of those 3
usually has price1 and price2 matching. If there is a match, I only
want to see that record for that location.

So, if price1=price2 then only show that result for that location. If
none of the three results match, I want to see all three.

Please let me know how to accomplish this in an access query. Thanks!

 

[Ken Snell MVP]

Create this query (I'm using generic name for your table) -- give it a name
such as qryPriceEqual:

SELECT Location, Price1, Price2
FROM TableName
WHERE Price2 = Price1


Then use this query to show the desired results:

SELECT qryPriceEqual.Location, qryPriceEqual.Price1,
qryPriceEqual.Price2
FROM qryPriceEqual
UNION ALL
SELECT TableName.Location, TableName.Price1,
TableName.Price2
FROM TableName LEFT JOIN
qryPriceEqual
ON Table2Name.Location = qryPriceEqual.Location
WHERE qryPriceEqual.Location IS NULL


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

NOT (
(Date3 IS NULL AND Date4<= Date2)
OR
(Date3 IS NULL AND Date2 IS NULL AND Date4 <= Date1)
)

or, if you prefer

NOT (
Date3 IS NULL AND Date4 <= Nz(Date2, Date1)
)

Vanderghast, Access MVP











- Show quoted text -

Thank you both so much for your help. Worked perfectly. I understand
that my data isn't exactly organized best for Access, but I would
really like to get this working the way I want. I have another
question:

I have the following fields:
Location- (this field can be duplicated)
Price1
Price2

Currently the query returns 3 results per location- 1 out of those 3
usually has price1 and price2 matching. If there is a match, I only
want to see that record for that location.

So, if price1=price2 then only show that result for that location. If
none of the three results match, I want to see all three.

Please let me know how to accomplish this in an access query. Thanks!