If Then Statements Not Working

Discussion in 'Microsoft Access Reports' started by Matt Pierringer, Mar 16, 2007.

  1. I am not sure why, but after I change two fields two a calculated
    expression from a query, not a calculation on the report itself, it
    stopped working as it normally did. Let me try to be more clear, by
    giving examples of my code:

    'Calculated Field from query not working
    If Me.txtTotal Then
    Me.Line48.Visible = True
    Me.Line46.Visible = True
    End If
    'Toggles boxes... this code is currently working
    If Me.tgbNoList Or Me.tgbDeleted = True Then
    Me.Detail.Visible = False
    Else
    Me.Detail.Visible = True
    End If

    'Calculated two fields added together in query(number)
    'lblCallForPricing brings up a label saying "Request Price"
    'Not Working
    If IsNull(Me.txtTotal) Then
    Me!lblCallForPricing.Visible = True
    Else
    Me!lblCallForPricing.Visible = False
    End If


    txtTotal is on my report and does show up as the total of two numbers.
    Here is the equation from txtTotal in the Query:
    ttlCost: IIf(IsNull([tblProducts].[Cost]) Or IsNull([tblProducts_1].
    [Cost]),"",IIf(IsNull([tblProducts].[Cost]),"",Ceiling([tblProducts].
    [Cost]*[tblProducts].[GMR]+[tblProducts].[Cost],0.25))
    +IIf(IsNull([tblProducts_1].[Cost]),"",Ceiling([tblProducts_1].
    [Cost]*[tblProducts_1].[GMR]+[tblProducts_1].[Cost],0.25)))

    tblProducts is my main table I am joining the table on itself with a
    number used to identify that a product requires another product, which
    is why I need to display both prices then join them.

    GMR-Gross Margin Rate(The percentage that the product is being
    multiplied by)
    Ceiling-The same function from excel, to round to the nearest .25

    Public Function Ceiling(ByVal X As Double, Optional ByVal Factor As
    Double = 1) As Double
    ' X is the value you want to round
    ' is the multiple to which you want to round
    Ceiling = (Int(X / Factor) - (X / Factor - Int(X / Factor) > 0)) *
    Factor
    End Function

    Anything I missed, I am sure... but thanks for your help!

    -Matt Pierringer
     
    Matt Pierringer, Mar 16, 2007
    #1
    1. Advertisements

  2. Matt Pierringer

    BruceM Guest

    "Matt Pierringer" <> wrote in message
    news:...
    >I am not sure why, but after I change two fields two a calculated
    > expression from a query, not a calculation on the report itself, it
    > stopped working as it normally did. Let me try to be more clear, by
    > giving examples of my code:
    >
    > 'Calculated Field from query not working
    > If Me.txtTotal Then
    > Me.Line48.Visible = True
    > Me.Line46.Visible = True
    > End If


    If Me.txtTotal what? What condition of Me.txtTotal will cause the lines to
    be visible?

    > 'Toggles boxes... this code is currently working
    > If Me.tgbNoList Or Me.tgbDeleted = True Then
    > Me.Detail.Visible = False
    > Else
    > Me.Detail.Visible = True
    > End If


    Be aware that if you want to check the "toggle box" (I assume you mean
    toggle button) to see if it's False, you would need to specify:
    If Me.tgbNoList = False Or Me.tgbDeleted = False Then
    etc.

    On the other hand, your statement could be:
    If Me.tgbNoList Or Me.tgbDeleted Then
    etc.
    with the same result as you are getting now. True is assumed (if I
    understand correctly just how this works), but you need to make the test for
    each control in any other situation.


    >
    > 'Calculated two fields added together in query(number)
    > 'lblCallForPricing brings up a label saying "Request Price"
    > 'Not Working
    > If IsNull(Me.txtTotal) Then
    > Me!lblCallForPricing.Visible = True
    > Else
    > Me!lblCallForPricing.Visible = False
    > End If


    Use the actual calculation rather than the control containing the
    calculation.
    If IsNull (Me.Field1 + Me.Field2) Then
    etc.
    If you base the form on a query and perform the calculation there, you can
    test for the value of that calculated field:
    If IsNull(Me.CalculatedField) Then
    etc.

    >
    >
    > txtTotal is on my report and does show up as the total of two numbers.
    > Here is the equation from txtTotal in the Query:
    > ttlCost: IIf(IsNull([tblProducts].[Cost]) Or IsNull([tblProducts_1].
    > [Cost]),"",IIf(IsNull([tblProducts].[Cost]),"",Ceiling([tblProducts].
    > [Cost]*[tblProducts].[GMR]+[tblProducts].[Cost],0.25))
    > +IIf(IsNull([tblProducts_1].[Cost]),"",Ceiling([tblProducts_1].
    > [Cost]*[tblProducts_1].[GMR]+[tblProducts_1].[Cost],0.25)))
    >
    > tblProducts is my main table I am joining the table on itself with a
    > number used to identify that a product requires another product, which
    > is why I need to display both prices then join them.


    Use Is Not Null in an expression. I'm not following the self-join, but it
    seems to me that if either Cost field is null, the expression will return an
    empty string and will go no further. If both are true, the second time you
    test to see if Cost is null is irrelevant. You are in effect testing to see
    if both fields are true

    IIf([tblProducts].[Cost] Is Not Null Or [tblProducts_1]. [Cost]) Is Not
    Null,{do something}.{do something else})

    I can't quite follow what else is going on, or what results you expect to
    see if certain conditions are met, but as I said, I don't really understand
    self-joins.

    >
    > GMR-Gross Margin Rate(The percentage that the product is being
    > multiplied by)
    > Ceiling-The same function from excel, to round to the nearest .25
    >
    > Public Function Ceiling(ByVal X As Double, Optional ByVal Factor As
    > Double = 1) As Double
    > ' X is the value you want to round
    > ' is the multiple to which you want to round
    > Ceiling = (Int(X / Factor) - (X / Factor - Int(X / Factor) > 0)) *
    > Factor
    > End Function
    >
    > Anything I missed, I am sure... but thanks for your help!
    >
    > -Matt Pierringer
    >
     
    BruceM, Mar 16, 2007
    #2
    1. Advertisements

  3. On Mar 16, 12:35 pm, "BruceM" <> wrote:
    > "Matt Pierringer" <> wrote in message
    >
    > news:...
    >
    > >I am not sure why, but after I change two fields two a calculated
    > > expression from a query, not a calculation on the report itself, it
    > > stopped working as it normally did. Let me try to be more clear, by
    > > giving examples of my code:

    >
    > > 'Calculated Field from query not working
    > > If Me.txtTotal Then
    > > Me.Line48.Visible = True
    > > Me.Line46.Visible = True
    > > End If

    >
    > If Me.txtTotal what? What condition of Me.txtTotal will cause the lines to
    > be visible?
    >
    > > 'Toggles boxes... this code is currently working
    > > If Me.tgbNoList Or Me.tgbDeleted = True Then
    > > Me.Detail.Visible = False
    > > Else
    > > Me.Detail.Visible = True
    > > End If

    >
    > Be aware that if you want to check the "toggle box" (I assume you mean
    > toggle button) to see if it's False, you would need to specify:
    > If Me.tgbNoList = False Or Me.tgbDeleted = False Then
    > etc.
    >
    > On the other hand, your statement could be:
    > If Me.tgbNoList Or Me.tgbDeleted Then
    > etc.
    > with the same result as you are getting now. True is assumed (if I
    > understand correctly just how this works), but you need to make the test for
    > each control in any other situation.
    >
    >
    >
    > > 'Calculated two fields added together in query(number)
    > > 'lblCallForPricing brings up a label saying "Request Price"
    > > 'Not Working
    > > If IsNull(Me.txtTotal) Then
    > > Me!lblCallForPricing.Visible = True
    > > Else
    > > Me!lblCallForPricing.Visible = False
    > > End If

    >
    > Use the actual calculation rather than the control containing the
    > calculation.
    > If IsNull (Me.Field1 + Me.Field2) Then
    > etc.
    > If you base the form on a query and perform the calculation there, you can
    > test for the value of that calculated field:
    > If IsNull(Me.CalculatedField) Then
    > etc.
    >
    >
    >
    > > txtTotal is on my report and does show up as the total of two numbers.
    > > Here is the equation from txtTotal in the Query:
    > > ttlCost: IIf(IsNull([tblProducts].[Cost]) Or IsNull([tblProducts_1].
    > > [Cost]),"",IIf(IsNull([tblProducts].[Cost]),"",Ceiling([tblProducts].
    > > [Cost]*[tblProducts].[GMR]+[tblProducts].[Cost],0.25))
    > > +IIf(IsNull([tblProducts_1].[Cost]),"",Ceiling([tblProducts_1].
    > > [Cost]*[tblProducts_1].[GMR]+[tblProducts_1].[Cost],0.25)))

    >
    > > tblProducts is my main table I am joining the table on itself with a
    > > number used to identify that a product requires another product, which
    > > is why I need to display both prices then join them.

    >
    > Use Is Not Null in an expression. I'm not following the self-join, but it
    > seems to me that if either Cost field is null, the expression will return an
    > empty string and will go no further. If both are true, the second time you
    > test to see if Cost is null is irrelevant. You are in effect testing to see
    > if both fields are true
    >
    > IIf([tblProducts].[Cost] Is Not Null Or [tblProducts_1]. [Cost]) Is Not
    > Null,{do something}.{do something else})
    >
    > I can't quite follow what else is going on, or what results you expect to
    > see if certain conditions are met, but as I said, I don't really understand
    > self-joins.
    >
    >
    >
    > > GMR-Gross Margin Rate(The percentage that the product is being
    > > multiplied by)
    > > Ceiling-The same function from excel, to round to the nearest .25

    >
    > > Public Function Ceiling(ByVal X As Double, Optional ByVal Factor As
    > > Double = 1) As Double
    > > ' X is the value you want to round
    > > ' is the multiple to which you want to round
    > > Ceiling = (Int(X / Factor) - (X / Factor - Int(X / Factor) > 0)) *
    > > Factor
    > > End Function

    >
    > > Anything I missed, I am sure... but thanks for your help!

    >
    > > -Matt Pierringer


    I am still going through your answer, but I'll try to answer your
    questions before I read deeper.

    > > 'Calculated Field from query not working
    > > If Me.txtTotal Then
    > > Me.Line48.Visible = True

    I have been changing the code a lot and I guess I missed making it a
    "Not IsNull(Me.txtTotal)"


    Here is an example of my table:
    MaterialNumber
    Catalog MaterialNumber Required Cost
    PD7 22007258 $15
    7VC 22007259 $23
    7HC 22007260 22007258 $9
    MVEL17 22010023 $80

    So 7HC would get joined with PD7, txtTotal would be $24 (9+15)


    > Be aware that if you want to check the "toggle box" (I assume you mean
    > toggle button) to see if it's False, you would need to specify:
    > If Me.tgbNoList = False Or Me.tgbDeleted = False Then


    Yeah I meant toggle button, but the default for all of my tb's are
    blank which I would assume would be False.... anyways this is one of
    only things I currently have working in the Detail On Format section.

    "If both are true, the second time you
    > test to see if Cost is null is irrelevant. You are in effect testing to see
    > if both fields are true"


    I think the previous example gives you a better understanding on why I
    did 2 tests, on the same on-joined field.

    Thanks for your reply, it is giving me some ideas to look more at the
    root of the problem... i.e. before it happened.
     
    Matt Pierringer, Mar 16, 2007
    #3
  4. On Mar 16, 1:31 pm, "Matt Pierringer" <> wrote:
    > On Mar 16, 12:35 pm, "BruceM" <> wrote:
    >
    >
    >
    > > "Matt Pierringer" <> wrote in message

    >
    > >news:...

    >
    > > >I am not sure why, but after I change two fields two a calculated
    > > > expression from a query, not a calculation on the report itself, it
    > > > stopped working as it normally did. Let me try to be more clear, by
    > > > giving examples of my code:

    >
    > > > 'Calculated Field from query not working
    > > > If Me.txtTotal Then
    > > > Me.Line48.Visible = True
    > > > Me.Line46.Visible = True
    > > > End If

    >
    > > If Me.txtTotal what? What condition of Me.txtTotal will cause the lines to
    > > be visible?

    >
    > > > 'Toggles boxes... this code is currently working
    > > > If Me.tgbNoList Or Me.tgbDeleted = True Then
    > > > Me.Detail.Visible = False
    > > > Else
    > > > Me.Detail.Visible = True
    > > > End If

    >
    > > Be aware that if you want to check the "toggle box" (I assume you mean
    > > toggle button) to see if it's False, you would need to specify:
    > > If Me.tgbNoList = False Or Me.tgbDeleted = False Then
    > > etc.

    >
    > > On the other hand, your statement could be:
    > > If Me.tgbNoList Or Me.tgbDeleted Then
    > > etc.
    > > with the same result as you are getting now. True is assumed (if I
    > > understand correctly just how this works), but you need to make the test for
    > > each control in any other situation.

    >
    > > > 'Calculated two fields added together in query(number)
    > > > 'lblCallForPricing brings up a label saying "Request Price"
    > > > 'Not Working
    > > > If IsNull(Me.txtTotal) Then
    > > > Me!lblCallForPricing.Visible = True
    > > > Else
    > > > Me!lblCallForPricing.Visible = False
    > > > End If

    >
    > > Use the actual calculation rather than the control containing the
    > > calculation.
    > > If IsNull (Me.Field1 + Me.Field2) Then
    > > etc.
    > > If you base the form on a query and perform the calculation there, you can
    > > test for the value of that calculated field:
    > > If IsNull(Me.CalculatedField) Then
    > > etc.

    >
    > > > txtTotal is on my report and does show up as the total of two numbers.
    > > > Here is the equation from txtTotal in the Query:
    > > > ttlCost: IIf(IsNull([tblProducts].[Cost]) Or IsNull([tblProducts_1].
    > > > [Cost]),"",IIf(IsNull([tblProducts].[Cost]),"",Ceiling([tblProducts].
    > > > [Cost]*[tblProducts].[GMR]+[tblProducts].[Cost],0.25))
    > > > +IIf(IsNull([tblProducts_1].[Cost]),"",Ceiling([tblProducts_1].
    > > > [Cost]*[tblProducts_1].[GMR]+[tblProducts_1].[Cost],0.25)))

    >
    > > > tblProducts is my main table I am joining the table on itself with a
    > > > number used to identify that a product requires another product, which
    > > > is why I need to display both prices then join them.

    >
    > > Use Is Not Null in an expression. I'm not following the self-join, but it
    > > seems to me that if either Cost field is null, the expression will return an
    > > empty string and will go no further. If both are true, the second time you
    > > test to see if Cost is null is irrelevant. You are in effect testing to see
    > > if both fields are true

    >
    > > IIf([tblProducts].[Cost] Is Not Null Or [tblProducts_1]. [Cost]) Is Not
    > > Null,{do something}.{do something else})

    >
    > > I can't quite follow what else is going on, or what results you expect to
    > > see if certain conditions are met, but as I said, I don't really understand
    > > self-joins.

    >
    > > > GMR-Gross Margin Rate(The percentage that the product is being
    > > > multiplied by)
    > > > Ceiling-The same function from excel, to round to the nearest .25

    >
    > > > Public Function Ceiling(ByVal X As Double, Optional ByVal Factor As
    > > > Double = 1) As Double
    > > > ' X is the value you want to round
    > > > ' is the multiple to which you want to round
    > > > Ceiling = (Int(X / Factor) - (X / Factor - Int(X / Factor) > 0)) *
    > > > Factor
    > > > End Function

    >
    > > > Anything I missed, I am sure... but thanks for your help!

    >
    > > > -Matt Pierringer

    >
    > I am still going through your answer, but I'll try to answer your
    > questions before I read deeper.
    >
    > > > 'Calculated Field from query not working
    > > > If Me.txtTotal Then
    > > > Me.Line48.Visible = True

    >
    > I have been changing the code a lot and I guess I missed making it a
    > "Not IsNull(Me.txtTotal)"
    >
    > Here is an example of my table:
    > MaterialNumber
    > Catalog MaterialNumber Required Cost
    > PD7 22007258 $15
    > 7VC 22007259 $23
    > 7HC 22007260 22007258 $9
    > MVEL17 22010023 $80
    >
    > So 7HC would get joined with PD7, txtTotal would be $24 (9+15)
    >
    > > Be aware that if you want to check the "toggle box" (I assume you mean
    > > toggle button) to see if it's False, you would need to specify:
    > > If Me.tgbNoList = False Or Me.tgbDeleted = False Then

    >
    > Yeah I meant toggle button, but the default for all of my tb's are
    > blank which I would assume would be False.... anyways this is one of
    > only things I currently have working in the Detail On Format section.
    >
    > "If both are true, the second time you
    >
    > > test to see if Cost is null is irrelevant. You are in effect testing to see
    > > if both fields are true"

    >
    > I think the previous example gives you a better understanding on why I
    > did 2 tests, on the same on-joined field.
    >
    > Thanks for your reply, it is giving me some ideas to look more at the
    > root of the problem... i.e. before it happened.


    Ha after > 3 hours I finally got IT! I didn't realize that the actual
    fields have to be on the report and it can't JUST be the calculated
    ones from the query. So know I have both the calculated and calculated
    on the report... the latter is invisible.

    Thanks for your help!
    I greatly appreciate it... here is my final code, with the added
    controls as txtCost and txtCost1
    If Me.tgbNoList = True Or Me.tgbDeleted = True Then
    Me.Detail.Visible = False
    Else
    Me.Detail.Visible = True
    End If
    If IsNull(Me.txtCost) = True Then
    Me.lblCallForPricing.Visible = True
    Else
    Me.lblCallForPricing.Visible = False
    End If
    If Not IsNull(Me.txtCost1) = True Then
    Me.Line48.Visible = True
    Me.Line46.Visible = True
    Else
    Me.Line48.Visible = False
    Me.Line46.Visible = False
    End If
     
    Matt Pierringer, Mar 16, 2007
    #4
  5. Matt Pierringer

    BruceM Guest

    "Matt Pierringer" <> wrote in message
    news:...
    > On Mar 16, 1:31 pm, "Matt Pierringer" <> wrote:
    >> On Mar 16, 12:35 pm, "BruceM" <> wrote:
    >>
    >>
    >>
    >> > "Matt Pierringer" <> wrote in message

    >>
    >> >news:...

    >>
    >> > >I am not sure why, but after I change two fields two a calculated
    >> > > expression from a query, not a calculation on the report itself, it
    >> > > stopped working as it normally did. Let me try to be more clear, by
    >> > > giving examples of my code:

    >>
    >> > > 'Calculated Field from query not working
    >> > > If Me.txtTotal Then
    >> > > Me.Line48.Visible = True
    >> > > Me.Line46.Visible = True
    >> > > End If

    >>
    >> > If Me.txtTotal what? What condition of Me.txtTotal will cause the
    >> > lines to
    >> > be visible?

    >>
    >> > > 'Toggles boxes... this code is currently working
    >> > > If Me.tgbNoList Or Me.tgbDeleted = True Then
    >> > > Me.Detail.Visible = False
    >> > > Else
    >> > > Me.Detail.Visible = True
    >> > > End If

    >>
    >> > Be aware that if you want to check the "toggle box" (I assume you mean
    >> > toggle button) to see if it's False, you would need to specify:
    >> > If Me.tgbNoList = False Or Me.tgbDeleted = False Then
    >> > etc.

    >>
    >> > On the other hand, your statement could be:
    >> > If Me.tgbNoList Or Me.tgbDeleted Then
    >> > etc.
    >> > with the same result as you are getting now. True is assumed (if I
    >> > understand correctly just how this works), but you need to make the
    >> > test for
    >> > each control in any other situation.

    >>
    >> > > 'Calculated two fields added together in query(number)
    >> > > 'lblCallForPricing brings up a label saying "Request Price"
    >> > > 'Not Working
    >> > > If IsNull(Me.txtTotal) Then
    >> > > Me!lblCallForPricing.Visible = True
    >> > > Else
    >> > > Me!lblCallForPricing.Visible = False
    >> > > End If

    >>
    >> > Use the actual calculation rather than the control containing the
    >> > calculation.
    >> > If IsNull (Me.Field1 + Me.Field2) Then
    >> > etc.
    >> > If you base the form on a query and perform the calculation there, you
    >> > can
    >> > test for the value of that calculated field:
    >> > If IsNull(Me.CalculatedField) Then
    >> > etc.

    >>
    >> > > txtTotal is on my report and does show up as the total of two
    >> > > numbers.
    >> > > Here is the equation from txtTotal in the Query:
    >> > > ttlCost: IIf(IsNull([tblProducts].[Cost]) Or IsNull([tblProducts_1].
    >> > > [Cost]),"",IIf(IsNull([tblProducts].[Cost]),"",Ceiling([tblProducts].
    >> > > [Cost]*[tblProducts].[GMR]+[tblProducts].[Cost],0.25))
    >> > > +IIf(IsNull([tblProducts_1].[Cost]),"",Ceiling([tblProducts_1].
    >> > > [Cost]*[tblProducts_1].[GMR]+[tblProducts_1].[Cost],0.25)))

    >>
    >> > > tblProducts is my main table I am joining the table on itself with a
    >> > > number used to identify that a product requires another product,
    >> > > which
    >> > > is why I need to display both prices then join them.

    >>
    >> > Use Is Not Null in an expression. I'm not following the self-join, but
    >> > it
    >> > seems to me that if either Cost field is null, the expression will
    >> > return an
    >> > empty string and will go no further. If both are true, the second time
    >> > you
    >> > test to see if Cost is null is irrelevant. You are in effect testing
    >> > to see
    >> > if both fields are true

    >>
    >> > IIf([tblProducts].[Cost] Is Not Null Or [tblProducts_1]. [Cost]) Is Not
    >> > Null,{do something}.{do something else})

    >>
    >> > I can't quite follow what else is going on, or what results you expect
    >> > to
    >> > see if certain conditions are met, but as I said, I don't really
    >> > understand
    >> > self-joins.

    >>
    >> > > GMR-Gross Margin Rate(The percentage that the product is being
    >> > > multiplied by)
    >> > > Ceiling-The same function from excel, to round to the nearest .25

    >>
    >> > > Public Function Ceiling(ByVal X As Double, Optional ByVal Factor As
    >> > > Double = 1) As Double
    >> > > ' X is the value you want to round
    >> > > ' is the multiple to which you want to round
    >> > > Ceiling = (Int(X / Factor) - (X / Factor - Int(X / Factor) > 0)) *
    >> > > Factor
    >> > > End Function

    >>
    >> > > Anything I missed, I am sure... but thanks for your help!

    >>
    >> > > -Matt Pierringer

    >>
    >> I am still going through your answer, but I'll try to answer your
    >> questions before I read deeper.
    >>
    >> > > 'Calculated Field from query not working
    >> > > If Me.txtTotal Then
    >> > > Me.Line48.Visible = True

    >>
    >> I have been changing the code a lot and I guess I missed making it a
    >> "Not IsNull(Me.txtTotal)"
    >>
    >> Here is an example of my table:
    >> MaterialNumber
    >> Catalog MaterialNumber Required Cost
    >> PD7 22007258 $15
    >> 7VC 22007259 $23
    >> 7HC 22007260 22007258 $9
    >> MVEL17 22010023 $80
    >>
    >> So 7HC would get joined with PD7, txtTotal would be $24 (9+15)
    >>
    >> > Be aware that if you want to check the "toggle box" (I assume you mean
    >> > toggle button) to see if it's False, you would need to specify:
    >> > If Me.tgbNoList = False Or Me.tgbDeleted = False Then

    >>
    >> Yeah I meant toggle button, but the default for all of my tb's are
    >> blank which I would assume would be False.... anyways this is one of
    >> only things I currently have working in the Detail On Format section.
    >>
    >> "If both are true, the second time you
    >>
    >> > test to see if Cost is null is irrelevant. You are in effect testing
    >> > to see
    >> > if both fields are true"

    >>
    >> I think the previous example gives you a better understanding on why I
    >> did 2 tests, on the same on-joined field.
    >>
    >> Thanks for your reply, it is giving me some ideas to look more at the
    >> root of the problem... i.e. before it happened.

    >
    > Ha after > 3 hours I finally got IT! I didn't realize that the actual
    > fields have to be on the report and it can't JUST be the calculated
    > ones from the query. So know I have both the calculated and calculated
    > on the report... the latter is invisible.
    >
    > Thanks for your help!
    > I greatly appreciate it... here is my final code, with the added
    > controls as txtCost and txtCost1
    > If Me.tgbNoList = True Or Me.tgbDeleted = True Then
    > Me.Detail.Visible = False
    > Else
    > Me.Detail.Visible = True
    > End If
    > If IsNull(Me.txtCost) = True Then
    > Me.lblCallForPricing.Visible = True
    > Else
    > Me.lblCallForPricing.Visible = False
    > End If
    > If Not IsNull(Me.txtCost1) = True Then
    > Me.Line48.Visible = True
    > Me.Line46.Visible = True
    > Else
    > Me.Line48.Visible = False
    > Me.Line46.Visible = False
    > End If
    >


    I had thought the calculations were in the SQL, but it appears they are
    happening in VBA, maybe when you click a command button or something. When
    you refer to a calculated query field people will take that to mean a
    calculation that is part of the SQL, not a calculation performed with VBA in
    a report that uses a query as the record source.

    "I think the previous example gives you a better understanding on why I did
    2 tests, on the same on-joined field."

    I wasn't suggesting against performing two tests. Rather, I was pointing
    out that this:
    If Me.tgbNoList = True Or Me.tgbDeleted = True Then
    is the same as:
    If Me.tgbNoList Or Me.tgbDeleted Then
    However, this:
    If Me.tgbNoList Or Me.tgbDeleted = False Then
    is equivalent to:
    If Me.tgbNoList = True Or Me.tgbDeleted = False Then
    In other words, you need to specify each field or control along with the
    True or False value for which you are testing. You cannot list all of the
    fields, then perform a single True or False test on all of them.

    At least that's the way I understand it. I specify True or False because
    it's easier for me to think about it that way. Also, I'm not completely
    certain that leaving out =True will produce the intended result in all
    cases.

    Note that:
    If Not IsNull(Me.txtCost1) = True Then
    is more than you need.
    If Not IsNull(Me.txtCost1) Then
    takes care of it.
     
    BruceM, Mar 19, 2007
    #5
  6. On Mar 19, 5:57 am, "BruceM" <> wrote:
    > "Matt Pierringer" <> wrote in message
    >
    > news:...
    >
    >
    >
    > > On Mar 16, 1:31 pm, "Matt Pierringer" <> wrote:
    > >> On Mar 16, 12:35 pm, "BruceM" <> wrote:

    >
    > >> > "Matt Pierringer" <> wrote in message

    >
    > >> >news:...

    >
    > >> > >I am not sure why, but after I change two fields two a calculated
    > >> > > expression from a query, not a calculation on the report itself, it
    > >> > > stopped working as it normally did. Let me try to be more clear, by
    > >> > > giving examples of my code:

    >
    > >> > > 'Calculated Field from query not working
    > >> > > If Me.txtTotal Then
    > >> > > Me.Line48.Visible = True
    > >> > > Me.Line46.Visible = True
    > >> > > End If

    >
    > >> > If Me.txtTotal what? What condition of Me.txtTotal will cause the
    > >> > lines to
    > >> > be visible?

    >
    > >> > > 'Toggles boxes... this code is currently working
    > >> > > If Me.tgbNoList Or Me.tgbDeleted = True Then
    > >> > > Me.Detail.Visible = False
    > >> > > Else
    > >> > > Me.Detail.Visible = True
    > >> > > End If

    >
    > >> > Be aware that if you want to check the "toggle box" (I assume you mean
    > >> > toggle button) to see if it's False, you would need to specify:
    > >> > If Me.tgbNoList = False Or Me.tgbDeleted = False Then
    > >> > etc.

    >
    > >> > On the other hand, your statement could be:
    > >> > If Me.tgbNoList Or Me.tgbDeleted Then
    > >> > etc.
    > >> > with the same result as you are getting now. True is assumed (if I
    > >> > understand correctly just how this works), but you need to make the
    > >> > test for
    > >> > each control in any other situation.

    >
    > >> > > 'Calculated two fields added together in query(number)
    > >> > > 'lblCallForPricing brings up a label saying "Request Price"
    > >> > > 'Not Working
    > >> > > If IsNull(Me.txtTotal) Then
    > >> > > Me!lblCallForPricing.Visible = True
    > >> > > Else
    > >> > > Me!lblCallForPricing.Visible = False
    > >> > > End If

    >
    > >> > Use the actual calculation rather than the control containing the
    > >> > calculation.
    > >> > If IsNull (Me.Field1 + Me.Field2) Then
    > >> > etc.
    > >> > If you base the form on a query and perform the calculation there, you
    > >> > can
    > >> > test for the value of that calculated field:
    > >> > If IsNull(Me.CalculatedField) Then
    > >> > etc.

    >
    > >> > > txtTotal is on my report and does show up as the total of two
    > >> > > numbers.
    > >> > > Here is the equation from txtTotal in the Query:
    > >> > > ttlCost: IIf(IsNull([tblProducts].[Cost]) Or IsNull([tblProducts_1].
    > >> > > [Cost]),"",IIf(IsNull([tblProducts].[Cost]),"",Ceiling([tblProducts].
    > >> > > [Cost]*[tblProducts].[GMR]+[tblProducts].[Cost],0.25))
    > >> > > +IIf(IsNull([tblProducts_1].[Cost]),"",Ceiling([tblProducts_1].
    > >> > > [Cost]*[tblProducts_1].[GMR]+[tblProducts_1].[Cost],0.25)))

    >
    > >> > > tblProducts is my main table I am joining the table on itself with a
    > >> > > number used to identify that a product requires another product,
    > >> > > which
    > >> > > is why I need to display both prices then join them.

    >
    > >> > Use Is Not Null in an expression. I'm not following the self-join, but
    > >> > it
    > >> > seems to me that if either Cost field is null, the expression will
    > >> > return an
    > >> > empty string and will go no further. If both are true, the second time
    > >> > you
    > >> > test to see if Cost is null is irrelevant. You are in effect testing
    > >> > to see
    > >> > if both fields are true

    >
    > >> > IIf([tblProducts].[Cost] Is Not Null Or [tblProducts_1]. [Cost]) Is Not
    > >> > Null,{do something}.{do something else})

    >
    > >> > I can't quite follow what else is going on, or what results you expect
    > >> > to
    > >> > see if certain conditions are met, but as I said, I don't really
    > >> > understand
    > >> > self-joins.

    >
    > >> > > GMR-Gross Margin Rate(The percentage that the product is being
    > >> > > multiplied by)
    > >> > > Ceiling-The same function from excel, to round to the nearest .25

    >
    > >> > > Public Function Ceiling(ByVal X As Double, Optional ByVal Factor As
    > >> > > Double = 1) As Double
    > >> > > ' X is the value you want to round
    > >> > > ' is the multiple to which you want to round
    > >> > > Ceiling = (Int(X / Factor) - (X / Factor - Int(X / Factor) > 0)) *
    > >> > > Factor
    > >> > > End Function

    >
    > >> > > Anything I missed, I am sure... but thanks for your help!

    >
    > >> > > -Matt Pierringer

    >
    > >> I am still going through your answer, but I'll try to answer your
    > >> questions before I read deeper.

    >
    > >> > > 'Calculated Field from query not working
    > >> > > If Me.txtTotal Then
    > >> > > Me.Line48.Visible = True

    >
    > >> I have been changing the code a lot and I guess I missed making it a
    > >> "Not IsNull(Me.txtTotal)"

    >
    > >> Here is an example of my table:
    > >> MaterialNumber
    > >> Catalog MaterialNumber Required Cost
    > >> PD7 22007258 $15
    > >> 7VC 22007259 $23
    > >> 7HC 22007260 22007258 $9
    > >> MVEL17 22010023 $80

    >
    > >> So 7HC would get joined with PD7, txtTotal would be $24 (9+15)

    >
    > >> > Be aware that if you want to check the "toggle box" (I assume you mean
    > >> > toggle button) to see if it's False, you would need to specify:
    > >> > If Me.tgbNoList = False Or Me.tgbDeleted = False Then

    >
    > >> Yeah I meant toggle button, but the default for all of my tb's are
    > >> blank which I would assume would be False.... anyways this is one of
    > >> only things I currently have working in the Detail On Format section.

    >
    > >> "If both are true, the second time you

    >
    > >> > test to see if Cost is null is irrelevant. You are in effect testing
    > >> > to see
    > >> > if both fields are true"

    >
    > >> I think the previous example gives you a better understanding on why I
    > >> did 2 tests, on the same on-joined field.

    >
    > >> Thanks for your reply, it is giving me some ideas to look more at the
    > >> root of the problem... i.e. before it happened.

    >
    > > Ha after > 3 hours I finally got IT! I didn't realize that the actual
    > > fields have to be on the report and it can't JUST be the calculated
    > > ones from the query. So know I have both the calculated and calculated
    > > on the report... the latter is invisible.

    >
    > > Thanks for your help!
    > > I greatly appreciate it... here is my final code, with the added
    > > controls as txtCost and txtCost1
    > > If Me.tgbNoList = True Or Me.tgbDeleted = True Then
    > > Me.Detail.Visible = False
    > > Else
    > > Me.Detail.Visible = True
    > > End If
    > > If IsNull(Me.txtCost) = True Then
    > > Me.lblCallForPricing.Visible = True
    > > Else
    > > Me.lblCallForPricing.Visible = False
    > > End If
    > > If Not IsNull(Me.txtCost1) = True Then
    > > Me.Line48.Visible = True
    > > Me.Line46.Visible = True
    > > Else
    > > Me.Line48.Visible = False
    > > Me.Line46.Visible = False
    > > End If

    >
    > I had thought the calculations were in the SQL, but it appears they are
    > happening in VBA, maybe when you click a command button or something. When
    > you refer to a calculated query field people will take that to mean a
    > calculation that is part of the SQL, not a calculation performed with VBA in
    > a report that uses a query as the record source.
    >
    > "I think the previous example gives you a better understanding on why I did
    > 2 tests, on the same on-joined field."
    >
    > I wasn't suggesting against performing two tests. Rather, I was pointing
    > out that this:
    > If Me.tgbNoList = True Or Me.tgbDeleted = True Then
    > is the same as:
    > If Me.tgbNoList Or Me.tgbDeleted Then
    > However, this:
    > If Me.tgbNoList Or Me.tgbDeleted = False Then
    > is equivalent to:
    > If Me.tgbNoList = True Or Me.tgbDeleted = False Then
    > In other words, you need to specify each field or control along with the
    > True or False value for which you are testing. You cannot list all of the
    > fields, then perform a single True or False test on all of them.
    >
    > At least that's the way I understand it. I specify True or False because
    > it's easier for me to think about it that way. Also, I'm not completely
    > certain that leaving out =True will produce the intended result in all
    > cases.
    >
    > Note that:
    > If Not IsNull(Me.txtCost1) = True Then
    > is more than you need.
    > If Not IsNull(Me.txtCost1) Then
    > takes care of it.


    Yeah, thanks I wasn't suggesting that you wanted me to take out
    testing. I realized my faults after you pointed them out to me and I
    was just showing you how I fixed them. They were calculated SQL
    queries and not in VBA. The last part you said I didn't need is
    true... I didn't need it, but I think it safe guarded me against me
    setting it up wrong with it being visible or not(when something
    doesn't go as planned).

    Thanks for help,
    Matt Pierringer
     
    Matt Pierringer, Mar 19, 2007
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John Barnes

    IIf statements using AND

    John Barnes, Jul 17, 2003, in forum: Microsoft Access Reports
    Replies:
    1
    Views:
    3,656
    Duane Hookom
    Jul 17, 2003
  2. Donna

    If statements in Reports

    Donna, Aug 7, 2003, in forum: Microsoft Access Reports
    Replies:
    1
    Views:
    176
    Marshall Barton
    Aug 7, 2003
  3. Angelo

    If then if statements

    Angelo, Aug 28, 2003, in forum: Microsoft Access Reports
    Replies:
    1
    Views:
    137
    Rick Brandt
    Aug 28, 2003
  4. Guest

    If..then else statements help

    Guest, Apr 3, 2007, in forum: Microsoft Access Reports
    Replies:
    5
    Views:
    146
    Guest
    Apr 7, 2007
  5. Karl

    Add up if then Statements

    Karl, Mar 4, 2009, in forum: Microsoft Access Reports
    Replies:
    3
    Views:
    225
Loading...

Share This Page