If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
If/Then/Else Query
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. |
#2
|
|||
|
|||
If/Then/Else Query
"shm135" wrote in message ... 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. |
#3
|
|||
|
|||
If/Then/Else Query
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); -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "shm135" wrote in message ... 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. |
#4
|
|||
|
|||
If/Then/Else Query
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 "shm135" wrote in message ... 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. |
#5
|
|||
|
|||
If/Then/Else Query
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" wrote in message ... 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. |
#6
|
|||
|
|||
If/Then/Else Query
On Sep 22, 2:43*pm, "vanderghast" vanderghast@com wrote:
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" wrote in message ... 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.- Hide quoted text - - 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! |
#7
|
|||
|
|||
If/Then/Else Query
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/ "shm135" wrote in message ... On Sep 22, 2:43 pm, "vanderghast" vanderghast@com wrote: 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" wrote in message ... 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.- Hide quoted text - - 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! |
Thread Tools | |
Display Modes | |
|
|