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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

On Mar 16, 12:35 pm, "BruceM" <bam...@yawhodawtcalm.not> wrote:
> "Matt Pierringer" <mat...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
> >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.

On Mar 16, 1:31 pm, "Matt Pierringer" <mat...@gmail.com> wrote:
> On Mar 16, 12:35 pm, "BruceM" <bam...@yawhodawtcalm.not> wrote:
>
>
>
> > "Matt Pierringer" <mat...@gmail.com> wrote in message
>
> >news:(E-Mail Removed)...
>
> > >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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Mar 16, 1:31 pm, "Matt Pierringer" <mat...@gmail.com> wrote:
>> On Mar 16, 12:35 pm, "BruceM" <bam...@yawhodawtcalm.not> wrote:
>>
>>
>>
>> > "Matt Pierringer" <mat...@gmail.com> wrote in message
>>
>> >news:(E-Mail Removed)...
>>
>> > >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.

On Mar 19, 5:57 am, "BruceM" <bam...@yawhodawtcalm.not> wrote:
> "Matt Pierringer" <mat...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > On Mar 16, 1:31 pm, "Matt Pierringer" <mat...@gmail.com> wrote:
> >> On Mar 16, 12:35 pm, "BruceM" <bam...@yawhodawtcalm.not> wrote:
>
> >> > "Matt Pierringer" <mat...@gmail.com> wrote in message
>
> >> >news:(E-Mail Removed)...
>
> >> > >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