IF Statement in MS Access

Posted on 2012-09-15
Last Modified: 2012-09-15
To whom it may concern,

I need to say the following in MS Access, but I think that I am missing smth in my SQL code. Could someone tell me how to make the following work?

Expression1 is the field in my design view and the rest is the values that I want my field to assume.

Expression1: If ((var1>0 and var2=#12/31/9999#), min(var3,var1),var4)

Question by:maroulator
    LVL 29

    Accepted Solution

    You need to use IIf inside an expression rather than IF.  
    As for min(var3,var1), that is not how the MIN function works in Access.  I'm assuming you are trying to get the lower value of the two values (var1 and var3).  If so your expression should go something like this:
    IIf ((var1>0 and var2=#12/31/9999#), IIf(var3<var1,var3,var1),var4)

    Open in new window

    LVL 10

    Expert Comment

    Disregard - IrogSinta beat me to the punch  ;)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now