Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

NEED IF EXPRESSION (ACCESS 2003)

Status
Not open for further replies.

Dausa67

Technical User
Oct 1, 2008
96
US
Good morning,
I have a Crystal Report that works great but my management has requested that I create the same query in Access 2003 for multiple users. I have created the query in Access 2003 but I can't figure out how to re-write the formula in the Expression Builder.
[ul][li]I have used the Expression Builder for simple functions but I have not done anything in VBA.[/li][/ul]
[ul][li]I would like create an extra column in the query design view and use the Expression Builder to add the formula below. [/li][/ul]

Code:
if {'Purchase_Order_Rescheduling_'.IMS Need Date} IN cdate (2014, 01, 01) to cdate (2014, 01, 31)
AND {@PO DUE DATE} IN currentdate to cdate (2013, 12, 31)  
or {@PO DUE DATE} IN AllDatesToYesterday then "Consider Pushing out to Jan 2014" else
if {'Purchase_Order_Rescheduling_'.IMS Need Date} IN AllDatesToYesterday 
AND {@PO DUE DATE} <= {'Purchase_Order_Rescheduling_'.IMS Need Date} then "Review for Expediting";

Can someone tell me the correct way to write the above formula?
Thank you in advance for your help.

Clay
 
In English....

Those were the actual table and field names shown in my original post....

RULE 1
If the IMS date is between (01/01/2014 to 01/31/2014)
and the PO Date is between (today() to 12/31/2013)
OR the PO Date is =< yesterday then put message "Consider Pushing out to Jan 2014" ELSE

RULE 2
If the IMS Date is =< yesterday
and the PO Date is between (today() to 12/31/2013)
OR the PO Date =< IMS Date then put message "Review for Expediting"

I honestly don't know if I can use IIf() in place of If {}.

Clay
 
I never can remember the ‘order of importance’ of AND and OR, so I would suggest parenthesis:
Either:
If [red]([/red]IMS date between (01/01/2014 and 01/31/2014)
and PO Date between (today() and 12/31/2013) [red])[/red]
[blue]OR[/blue] PO Date is =< yesterday then

Or
If IMS date between (01/01/2014 and 01/31/2014)[blue]
and[/blue] [red]([/red]PO Date between (today() and 12/31/2013)
OR PO Date is =< yesterday[red])[/red] then


Have fun.

---- Andy
 
Since this seems to be a business calculation which might be used in several places in your application, I would not use an expression. I would create a small user-defined function that can be called from anywhere and modified in one place.

Create a new, blank module and paste this code into it. Save the module with the name "modBusinessCalcs".
Code:
Public Function DateReview(datIMS as Date, datPO as Date) as String
    If ( Format(datIMS,"YYYYMM") = "201401" AND _
            datPO Between Date AND #12/31/2013# ) OR _
            datPO <= Date -1 Then
        DateReview = "Consider Pushing out to Jan 2014"
      Else
        If (datIMS <= Date()-1 AND datPO Between Date AND #12/31/2013#) OR _
             datPO <= datIMS Then
            DateReview = "Review for Expediting"
        End If
    End If
End Function

You can then use the function in your query.
Code:
YourColumn: DateReview([IMS Date Field Name],[PO Date Field Name])

This assumes the two columns always contain legitimate date values. There is also an assumption of order of precedence.

I typically hate to hard code values like 201401 etc. What happens next year? Would you need to change the code? I would replace any hard coded value with a calculated value.




Duane
Hook'D on Access
MS Access MVP
 
And if you insist on IIf:
YourColumn: IIf(([IMS Need Date] Between #2014-01-01# And #2014-01-31# AND [PO DUE DATE] Between Date() And #2013-12-31#) OR [PO DUE DATE]<Date(), 'Consider Pushing out to Jan 2014', IIf(([IMS Need Date]<Date() AND [PO DUE DATE] Between Date() And #2013-12-31#) OR [PO DUE DATE]<=[IMS Need Date], 'Review for Expediting', '?'))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
PHV,
Thank you... that worked perfectly.

All,
Thank you for the attempts to try to help.
Have a great day.

Clay
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top