Where then Else? formula

[jarett]

I have a "WHERE" statement in my last query

WHERE (((CDate([DateCreated] & " " & [Order Time]))>DMax("dtmQueryLastRun","tblQueryLastRun")));

I need to add another parameter if the (CDate([DateCreated] & " " & [Order Time]) is < DMAX.... then WHERE (((CDate([NEWDATEFIELD])>DMax("dtmQueryLastRun","tblQueryLastRun")))

Can I add this to the same SQL or do I need to add another query?

 

[JonXL]

You didn't mention what you want the ELSE to return... Anyway, the setup can be as simple as...

WHERE IIF(CDate([DateCreated] & " " & [Order Time])>DMax("dtmQueryLastRun","tblQueryLastRun"),'CONDITION WHEN TRUE' , '... WHEN FALSE');

 

[jarett]

I guess the ELSE would be WHERE (((CDate([NEWDATEFIELD])>DMax("dtmQueryLastRun","tblQueryLastRun"))) if it would even be considered an ELSE statement.

 

[JonXL]

You want the same criterion regardless of if the condition is met or not?

 

[Micron]

maybe

WHERE CDate([DateCreated] & " " & [Order Time])>DMax("dtmQueryLastRun","tblQueryLastRun") AND
 CDate([NEWDATEFIELD])>DMax("dtmQueryLastRun","tblQueryLastRun")

I probably have the ending parentheses in the wrong place for the first CDate. Not clear what's being asked for.

 

[jarett]

You want the same criterion regardless of if the condition is met or not?

Hmm... trying to figure out how to explain it. So if the DateCreated+OrderTime is less than the tblQueryLastRun value I need to look to another field in the query "NEWFIELD". Then only allow records where the NEWFIELD is > tblQueryLastRun value.

 

[Micron]

Confusing still but take a look at the IIF function. IIF(test, result if true, result if false)
I tried to construct a sample but got balled up in figuring out which parts were just the test and which were the criteria to be used for the results of the test from the different examples given.

 

[JonXL]

Hmm... trying to figure out how to explain it. So if the DateCreated+OrderTime is less than the tblQueryLastRun value I need to look to another field in the query "NEWFIELD". Then only allow records where the NEWFIELD is > tblQueryLastRun value.

I think I'm getting it. What happens if DateCreated+OrderTime = tblQueryLastRun value? You can do this one of two ways - first using the IIF() and second using grouped AND and OR.

WHERE IIF(CDate([DateCreated] & " " & [Order Time])>DMax("dtmQueryLastRun","tblQueryLastRun"), CDate([DateCreated] & " " & [Order Time])>DMax("dtmQueryLastRun","tblQueryLastRun") ,  CDate([NEWDATEFIELD])<DMax("dtmQueryLastRun","tblQueryLastRun"));

WHERE (CDate([DateCreated] & " " & [Order Time])>DMax("dtmQueryLastRun","tblQueryLastRun") AND CDate([NEWDATEFIELD])<DMax("dtmQueryLastRun","tblQueryLastRun")) OR (CDate([DateCreated] & " " & [Order Time])<DMax("dtmQueryLastRun","tblQueryLastRun")  AND CDate([NEWDATEFIELD])>DMax("dtmQueryLastRun","tblQueryLastRun"));

Depending on which of these you want to have when DateCreated+OrderTime = the tblQueryLastRun, you will change the > or < to >= or <=, respectively. And you will want to give careful thought to that and make sure you actually make those updates so you don't miss records you might be expecting to have returned.

 

[jarett]

I think I'm getting it. What happens if DateCreated+OrderTime = tblQueryLastRun value? You can do this one of two ways - first using the IIF() and second using grouped AND and OR.

WHERE IIF(CDate([DateCreated] & " " & [Order Time])>DMax("dtmQueryLastRun","tblQueryLastRun"), CDate([DateCreated] & " " & [Order Time])>DMax("dtmQueryLastRun","tblQueryLastRun") ,  CDate([NEWDATEFIELD])<DMax("dtmQueryLastRun","tblQueryLastRun"));

WHERE (CDate([DateCreated] & " " & [Order Time])>DMax("dtmQueryLastRun","tblQueryLastRun") AND CDate([NEWDATEFIELD])<DMax("dtmQueryLastRun","tblQueryLastRun")) OR (CDate([DateCreated] & " " & [Order Time])<DMax("dtmQueryLastRun","tblQueryLastRun")  AND CDate([NEWDATEFIELD])>DMax("dtmQueryLastRun","tblQueryLastRun"));

Depending on which of these you want to have when DateCreated+OrderTime = the tblQueryLastRun, you will change the > or < to >= or <=, respectively. And you will want to give careful thought to that and make sure you actually make those updates so you don't miss records you might be expecting to have returned.

I don't think I could ever get the values to be equal, or should I say not impossible but unlikely, an order would have to be created the exact time the query was run down to the second. Thanks I will try those out.

 

[jarett]

Been trying a couple different ways with no luck, the value in the Query last run table that the formula is referencing is 5/20/2021 9:38:23 AM, my Date Created+OrderTime value is 5/20/2021 9:21:40 AM, and the DateUpdate+Ordertime1 value is 5/20/2021 9:39:49 AM. So in theory the query should filter the record out since the "DateCreated" value is < 5/20/2021 9:38:23 AM "QueryLastRun" but then look to the next criteria which should allow the record to be included in the query results because the "DateUpdate" is > 5/20/2021 9:38:23 AM "QueryLastRun". My query result is not showing any records though. This is the whole SQL, nothing else has changed expect for the WHERE line, was getting correct results before testing these options. If I change the CDate([DateUpdated] & " " & [Order Time1]))>DMax("dtmQueryLastRun","tblQueryLastRun") to a < then the query shows all results except the record that I am trying to produce (this is correct if I am reading it correctly), so not sure why it doesn't produce the correct result when I change it back to >.

SELECT po_detail2.PurchaseOrderNo, Left([UDF_UNIQUE_KEY],Len([UDF_UNIQUE_KEY])-1) AS INVENTORY_KEY, Trim([po_detail2!Qty Ordered]) AS Qty, Right([UDF_UNIQUE_KEY],1) AS SIZE_INDEX, CDate([DateCreated] & " " & [Order Time]) AS Expr1, CDate([DateUpdated] & " " & [Order Time1])
FROM po_detail2 INNER JOIN IM068_MXPUnivProdCode ON (po_detail2.LotSerialNo = IM068_MXPUnivProdCode.LotSerialNo) AND (po_detail2.ItemCode = IM068_MXPUnivProdCode.ItemCode)
WHERE IIF(CDate([DateCreated] & " " & [Order Time])>DMax("dtmQueryLastRun","tblQueryLastRun"), CDate([DateCreated] & " " & [Order Time])>DMax("dtmQueryLastRun","tblQueryLastRun"), CDate([DateUpdated] & " " & [Order Time1]))>DMax("dtmQueryLastRun","tblQueryLastRun")
ORDER BY po_detail2.PurchaseOrderNo DESC;