Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

"IF" statements in calculated fields

Discussion in 'Information Technology' started by Jennifer Cali, Jul 28, 2006.

  1. Jennifer Cali

    Jennifer Cali
    Expand Collapse
    Guest

    I have two fields in my table dtmDateVacant & dtmDateFilled, and one
    calculation on the form that calculates the difference between those two
    dates (field: txtTimeToFill). I want the calculation to be such that if there
    is a date in the date filled then the calculated box will show the difference
    - which it does - but if there is NO date in the dtmDateFilled field then it
    will subtract using TODAY's date. How can I set this up? I can do it in Excel
    like this: =IF(L8>0,L8-K8,IF(K8>0,TODAY()-K8,"")) - how do I do this in
    Access?
    --
    Thank you! - Jennifer
     
  2. Loading...

    Similar Threads Forum Date
    1984 Court resumes recording of statements in ’84 Sikh riots History of Sikhism Sep 30, 2010
    Institutes Of Sikh Scholars To Avoid Irresponsible Statements By Ignorants/ Lured By Spiritual Articles Mar 22, 2006
    India Reading Calculated Banis Breaking News Mar 6, 2013

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    IIf(IsNull(Me.dtmDateFilled), DateDiff("d", Me.dtmDateVacant, Date()),
    DateDiff("d", Me,dtmDateVacant, Me.dtmDateFilled))

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Jennifer Cali" <JenniferCali@discussions.microsoft.com> wrote in message
    news:66D6EC9A-ED02-45C8-BB2F-BF84D536E65C@microsoft.com...
    >I have two fields in my table dtmDateVacant & dtmDateFilled, and one
    > calculation on the form that calculates the difference between those two
    > dates (field: txtTimeToFill). I want the calculation to be such that if
    > there
    > is a date in the date filled then the calculated box will show the
    > difference
    > - which it does - but if there is NO date in the dtmDateFilled field then
    > it
    > will subtract using TODAY's date. How can I set this up? I can do it in
    > Excel
    > like this: =IF(L8>0,L8-K8,IF(K8>0,TODAY()-K8,"")) - how do I do this in
    > Access?
    > --
    > Thank you! - Jennifer
     
  4. Jennifer Cali

    Jennifer Cali
    Expand Collapse
    Guest

    Doug, you have been my knight in shining armor lately! Thank you for
    answering my questions so well.

    I was playing around with the VB and had the value calc'd upon field update
    and it went something like this (see below). It worked if there was a date in
    the filled date but not if it was blank. Was I close?

    If dtmDateFilled = Null Then
    clcTimeToFill = Date - dtmDateVacant
    ElseIf dtmDateFilled <> 0 Then
    clcTimeToFill = dtmDateFilled - dtmDateVacant
    End If
    --
    Thank you! - Jennifer


    "Douglas J. Steele" wrote:

    > IIf(IsNull(Me.dtmDateFilled), DateDiff("d", Me.dtmDateVacant, Date()),
    > DateDiff("d", Me,dtmDateVacant, Me.dtmDateFilled))
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "Jennifer Cali" <JenniferCali@discussions.microsoft.com> wrote in message
    > news:66D6EC9A-ED02-45C8-BB2F-BF84D536E65C@microsoft.com...
    > >I have two fields in my table dtmDateVacant & dtmDateFilled, and one
    > > calculation on the form that calculates the difference between those two
    > > dates (field: txtTimeToFill). I want the calculation to be such that if
    > > there
    > > is a date in the date filled then the calculated box will show the
    > > difference
    > > - which it does - but if there is NO date in the dtmDateFilled field then
    > > it
    > > will subtract using TODAY's date. How can I set this up? I can do it in
    > > Excel
    > > like this: =IF(L8>0,L8-K8,IF(K8>0,TODAY()-K8,"")) - how do I do this in
    > > Access?
    > > --
    > > Thank you! - Jennifer

    >
    >
    >
     
  5. Jennifer Cali

    Jennifer Cali
    Expand Collapse
    Guest

    Uh-oh, I did something wrong. I pasted your formula into the Control Source,
    but I'm getting the #Name? error. Do I need to be substituting any variables
    into the formula? What does the "d" signify?
    --
    Thank you! - Jennifer


    "Douglas J. Steele" wrote:

    > IIf(IsNull(Me.dtmDateFilled), DateDiff("d", Me.dtmDateVacant, Date()),
    > DateDiff("d", Me,dtmDateVacant, Me.dtmDateFilled))
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "Jennifer Cali" <JenniferCali@discussions.microsoft.com> wrote in message
    > news:66D6EC9A-ED02-45C8-BB2F-BF84D536E65C@microsoft.com...
    > >I have two fields in my table dtmDateVacant & dtmDateFilled, and one
    > > calculation on the form that calculates the difference between those two
    > > dates (field: txtTimeToFill). I want the calculation to be such that if
    > > there
    > > is a date in the date filled then the calculated box will show the
    > > difference
    > > - which it does - but if there is NO date in the dtmDateFilled field then
    > > it
    > > will subtract using TODAY's date. How can I set this up? I can do it in
    > > Excel
    > > like this: =IF(L8>0,L8-K8,IF(K8>0,TODAY()-K8,"")) - how do I do this in
    > > Access?
    > > --
    > > Thank you! - Jennifer

    >
    >
    >
     
  6. Jennifer Cali

    Jennifer Cali
    Expand Collapse
    Guest

    Hey, this works!

    If IsNull(dtmDateFilled) Then
    clcTimeToFill = Date - dtmDateVacant
    Else
    clcTimeToFill = dtmDateFilled - dtmDateVacant
    End If
    --
    Thank you! - Jennifer


    "Jennifer Cali" wrote:

    > Doug, you have been my knight in shining armor lately! Thank you for
    > answering my questions so well.
    >
    > I was playing around with the VB and had the value calc'd upon field update
    > and it went something like this (see below). It worked if there was a date in
    > the filled date but not if it was blank. Was I close?
    >
    > If dtmDateFilled = Null Then
    > clcTimeToFill = Date - dtmDateVacant
    > ElseIf dtmDateFilled <> 0 Then
    > clcTimeToFill = dtmDateFilled - dtmDateVacant
    > End If
    > --
    > Thank you! - Jennifer
    >
    >
    > "Douglas J. Steele" wrote:
    >
    > > IIf(IsNull(Me.dtmDateFilled), DateDiff("d", Me.dtmDateVacant, Date()),
    > > DateDiff("d", Me,dtmDateVacant, Me.dtmDateFilled))
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no private e-mails, please)
    > >
    > >
    > > "Jennifer Cali" <JenniferCali@discussions.microsoft.com> wrote in message
    > > news:66D6EC9A-ED02-45C8-BB2F-BF84D536E65C@microsoft.com...
    > > >I have two fields in my table dtmDateVacant & dtmDateFilled, and one
    > > > calculation on the form that calculates the difference between those two
    > > > dates (field: txtTimeToFill). I want the calculation to be such that if
    > > > there
    > > > is a date in the date filled then the calculated box will show the
    > > > difference
    > > > - which it does - but if there is NO date in the dtmDateFilled field then
    > > > it
    > > > will subtract using TODAY's date. How can I set this up? I can do it in
    > > > Excel
    > > > like this: =IF(L8>0,L8-K8,IF(K8>0,TODAY()-K8,"")) - how do I do this in
    > > > Access?
    > > > --
    > > > Thank you! - Jennifer

    > >
    > >
    > >
     
  7. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Jennifer,

    One possibility here is that the name of the textbox is the same as the
    name as a field in the form's Record Source table/query.

    More likely cause of the trouble, though, is that there is a typo in
    Doug's expression, there is a , instead of a . and also the use of the
    Me is not correct in the case of an expression going into the Control
    Source of a textbox. Also you would need a = in front of the expression.

    To translate Doug's idea, then, try it like this...

    =IIf(IsNull([dtmDateFilled]),DateDiff("d",[dtmDateVacant],Date()),DateDiff("d",[dtmDateVacant],[dtmDateFilled]))

    Another way of looking at the same thing would be...
    =Nz([dtmDateFilled],Date())-[dtmDateVacant]

    --
    Steve Schapel, Microsoft Access MVP

    Jennifer Cali wrote:
    > Uh-oh, I did something wrong. I pasted your formula into the Control Source,
    > but I'm getting the #Name? error. Do I need to be substituting any variables
    > into the formula? What does the "d" signify?
     
  8. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Thanks, Steve.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Steve Schapel" <schapel@mvps.org.ns> wrote in message
    news:u7H6eZLqGHA.2180@TK2MSFTNGP04.phx.gbl...
    > Jennifer,
    >
    > One possibility here is that the name of the textbox is the same as the
    > name as a field in the form's Record Source table/query.
    >
    > More likely cause of the trouble, though, is that there is a typo in
    > Doug's expression, there is a , instead of a . and also the use of the Me
    > is not correct in the case of an expression going into the Control Source
    > of a textbox. Also you would need a = in front of the expression.
    >
    > To translate Doug's idea, then, try it like this...
    >
    > =IIf(IsNull([dtmDateFilled]),DateDiff("d",[dtmDateVacant],Date()),DateDiff("d",[dtmDateVacant],[dtmDateFilled]))
    >
    > Another way of looking at the same thing would be...
    > =Nz([dtmDateFilled],Date())-[dtmDateVacant]
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > Jennifer Cali wrote:
    >> Uh-oh, I did something wrong. I pasted your formula into the Control
    >> Source, but I'm getting the #Name? error. Do I need to be substituting
    >> any variables into the formula? What does the "d" signify?
     
  9. Jennifer Cali

    Jennifer Cali
    Expand Collapse
    Guest

    Thanks - that works great. Now to add another level: I'm trying to track the
    type of positions we have at work and associate that with the reason the
    position is vacant. For example, we have cashiers, managers, and clerks. Each
    can be vacant for one of four reasons (quit, fired, on leave, in training). I
    need to come up with some way to do a count of each vacancy reason for each
    position, and then combine that data into one report. I've been able to
    create queries (16 of them) that each do a count but now I can't combine
    them. What would you suggest? I'm at a loss on this one.
    --
    Thank you! - Jennifer


    "Steve Schapel" wrote:

    > Jennifer,
    >
    > One possibility here is that the name of the textbox is the same as the
    > name as a field in the form's Record Source table/query.
    >
    > More likely cause of the trouble, though, is that there is a typo in
    > Doug's expression, there is a , instead of a . and also the use of the
    > Me is not correct in the case of an expression going into the Control
    > Source of a textbox. Also you would need a = in front of the expression.
    >
    > To translate Doug's idea, then, try it like this...
    >
    > =IIf(IsNull([dtmDateFilled]),DateDiff("d",[dtmDateVacant],Date()),DateDiff("d",[dtmDateVacant],[dtmDateFilled]))
    >
    > Another way of looking at the same thing would be...
    > =Nz([dtmDateFilled],Date())-[dtmDateVacant]
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > Jennifer Cali wrote:
    > > Uh-oh, I did something wrong. I pasted your formula into the Control Source,
    > > but I'm getting the #Name? error. Do I need to be substituting any variables
    > > into the formula? What does the "d" signify?

    >
     
  10. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Something Like
    SELECT A.POSITION, A.REASON, COUNT(*) AS CNT FROM POSITIONS A
    WHERE dtmDateFilled IS NULL
    GROUP BY A.POSITION, A.REASON

    HTH

    Pieter

    "Jennifer Cali" <JenniferCali@discussions.microsoft.com> wrote in message
    news:BBB5F012-D327-40DF-A0F3-9B8248AA5169@microsoft.com...
    > Thanks - that works great. Now to add another level: I'm trying to track
    > the
    > type of positions we have at work and associate that with the reason the
    > position is vacant. For example, we have cashiers, managers, and clerks.
    > Each
    > can be vacant for one of four reasons (quit, fired, on leave, in
    > training). I
    > need to come up with some way to do a count of each vacancy reason for
    > each
    > position, and then combine that data into one report. I've been able to
    > create queries (16 of them) that each do a count but now I can't combine
    > them. What would you suggest? I'm at a loss on this one.
    > --
    > Thank you! - Jennifer
    >
    >
    > "Steve Schapel" wrote:
    >
    >> Jennifer,
    >>
    >> One possibility here is that the name of the textbox is the same as the
    >> name as a field in the form's Record Source table/query.
    >>
    >> More likely cause of the trouble, though, is that there is a typo in
    >> Doug's expression, there is a , instead of a . and also the use of the
    >> Me is not correct in the case of an expression going into the Control
    >> Source of a textbox. Also you would need a = in front of the expression.
    >>
    >> To translate Doug's idea, then, try it like this...
    >>
    >> =IIf(IsNull([dtmDateFilled]),DateDiff("d",[dtmDateVacant],Date()),DateDiff("d",[dtmDateVacant],[dtmDateFilled]))
    >>
    >> Another way of looking at the same thing would be...
    >> =Nz([dtmDateFilled],Date())-[dtmDateVacant]
    >>
    >> --
    >> Steve Schapel, Microsoft Access MVP
    >>
    >> Jennifer Cali wrote:
    >> > Uh-oh, I did something wrong. I pasted your formula into the Control
    >> > Source,
    >> > but I'm getting the #Name? error. Do I need to be substituting any
    >> > variables
    >> > into the formula? What does the "d" signify?

    >>
     
  11. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Something Like
    SELECT A.POSITION, A.REASON, COUNT(*) AS CNT FROM POSITIONS A
    WHERE dtmDateFilled IS NULL
    GROUP BY A.POSITION, A.REASON

    HTH

    Pieter

    "Jennifer Cali" <JenniferCali@discussions.microsoft.com> wrote in message
    news:BBB5F012-D327-40DF-A0F3-9B8248AA5169@microsoft.com...
    > Thanks - that works great. Now to add another level: I'm trying to track
    > the
    > type of positions we have at work and associate that with the reason the
    > position is vacant. For example, we have cashiers, managers, and clerks.
    > Each
    > can be vacant for one of four reasons (quit, fired, on leave, in
    > training). I
    > need to come up with some way to do a count of each vacancy reason for
    > each
    > position, and then combine that data into one report. I've been able to
    > create queries (16 of them) that each do a count but now I can't combine
    > them. What would you suggest? I'm at a loss on this one.
    > --
    > Thank you! - Jennifer
    >
    >
    > "Steve Schapel" wrote:
    >
    >> Jennifer,
    >>
    >> One possibility here is that the name of the textbox is the same as the
    >> name as a field in the form's Record Source table/query.
    >>
    >> More likely cause of the trouble, though, is that there is a typo in
    >> Doug's expression, there is a , instead of a . and also the use of the
    >> Me is not correct in the case of an expression going into the Control
    >> Source of a textbox. Also you would need a = in front of the expression.
    >>
    >> To translate Doug's idea, then, try it like this...
    >>
    >> =IIf(IsNull([dtmDateFilled]),DateDiff("d",[dtmDateVacant],Date()),DateDiff("d",[dtmDateVacant],[dtmDateFilled]))
    >>
    >> Another way of looking at the same thing would be...
    >> =Nz([dtmDateFilled],Date())-[dtmDateVacant]
    >>
    >> --
    >> Steve Schapel, Microsoft Access MVP
    >>
    >> Jennifer Cali wrote:
    >> > Uh-oh, I did something wrong. I pasted your formula into the Control
    >> > Source,
    >> > but I'm getting the #Name? error. Do I need to be substituting any
    >> > variables
    >> > into the formula? What does the "d" signify?

    >>




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4231 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     

Share This Page