Calculating form

[Techknownothing]

I have a form that performs a simple calculation.
It divides field1 (SALE PRICE) by field2 (RSF) to give me field3,
(PRICE PSF)
BUT, sometimes, when entering data, I do not know one or both of the
first two fields and leave them blank. When that is the case, Access
places either "#NUM!" or "$0" in the field. How do I change the
preferences to read "TBD" in field 3 when either field1 or field2 are
empty?

Jeff

 

[Graham R Seach]

Jeff,

Me.Field3 = IIF(Nz([sale price], 0) / Nz(rsf, 0) = 0, "TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Klatuu]

I believe your code will throw a divide by 0 error.
How about
Me.Field3 = IIf(IsNull(Field1) or IsNull(Field2),"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

Jeff,

Me.Field3 = IIF(Nz([sale price], 0) / Nz(rsf, 0) = 0, "TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a form that performs a simple calculation.
It divides field1 (SALE PRICE) by field2 (RSF) to give me field3,
(PRICE PSF)
BUT, sometimes, when entering data, I do not know one or both of the
first two fields and leave them blank. When that is the case, Access
places either "#NUM!" or "$0" in the field. How do I change the
preferences to read "TBD" in field 3 when either field1 or field2 are
empty?

Jeff

 

[Techknownothing]

Where should I insert this code?

Jeff

I believe your code will throw a divide by 0 error.
How about
Me.Field3 = IIf(IsNull(Field1) or IsNull(Field2),"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

Jeff,

Me.Field3 = IIF(Nz([sale price], 0) / Nz(rsf, 0) = 0, "TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a form that performs a simple calculation.
It divides field1 (SALE PRICE) by field2 (RSF) to give me field3,
(PRICE PSF)
BUT, sometimes, when entering data, I do not know one or both of the
first two fields and leave them blank. When that is the case, Access
places either "#NUM!" or "$0" in the field. How do I change the
preferences to read "TBD" in field 3 when either field1 or field2 are
empty?

Jeff

 

[Klatuu]

I would make the Default Value property of Field3 "TBD"
I would put the code in a sub and call it from the After Update events of
Field1 and Field2.

Where should I insert this code?

Jeff

I believe your code will throw a divide by 0 error.
How about
Me.Field3 = IIf(IsNull(Field1) or IsNull(Field2),"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

Jeff,

Me.Field3 = IIF(Nz([sale price], 0) / Nz(rsf, 0) = 0, "TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a form that performs a simple calculation.
It divides field1 (SALE PRICE) by field2 (RSF) to give me field3,
(PRICE PSF)
BUT, sometimes, when entering data, I do not know one or both of the
first two fields and leave them blank. When that is the case, Access
places either "#NUM!" or "$0" in the field. How do I change the
preferences to read "TBD" in field 3 when either field1 or field2 are
empty?

Jeff

 

[Techknownothing]

I'm still not there.
I am getting the following error message when I try to put in the code:

Compile Error:
Expected: List separator or )

Also, does it matter that field1 is in the main form and and field2 is
in a subform with field3?

Jeff

I would make the Default Value property of Field3 "TBD"
I would put the code in a sub and call it from the After Update events of
Field1 and Field2.

Where should I insert this code?

Jeff

I believe your code will throw a divide by 0 error.
How about
Me.Field3 = IIf(IsNull(Field1) or IsNull(Field2),"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

:

Jeff,

Me.Field3 = IIF(Nz([sale price], 0) / Nz(rsf, 0) = 0, "TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a form that performs a simple calculation.
It divides field1 (SALE PRICE) by field2 (RSF) to give me field3,
(PRICE PSF)
BUT, sometimes, when entering data, I do not know one or both of the
first two fields and leave them blank. When that is the case, Access
places either "#NUM!" or "$0" in the field. How do I change the
preferences to read "TBD" in field 3 when either field1 or field2 are
empty?

Jeff

 

[Klatuu]

yes, it does matter. You have to qualify the reference to include the
subform contorl on the main form, note, this is not the name of the subform,
but the name of the subform control.
Me!SubFormControlName!Field2

As to the compile error, my eyesight is not so good, so I can't see what you
did from here. Posting it would be very helpful

I'm still not there.
I am getting the following error message when I try to put in the code:

Compile Error:
Expected: List separator or )

Also, does it matter that field1 is in the main form and and field2 is
in a subform with field3?

Jeff

I would make the Default Value property of Field3 "TBD"
I would put the code in a sub and call it from the After Update events of
Field1 and Field2.

Where should I insert this code?

Jeff

Klatuu wrote:
I believe your code will throw a divide by 0 error.
How about
Me.Field3 = IIf(IsNull(Field1) or IsNull(Field2),"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

:

Jeff,

Me.Field3 = IIF(Nz([sale price], 0) / Nz(rsf, 0) = 0, "TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a form that performs a simple calculation.
It divides field1 (SALE PRICE) by field2 (RSF) to give me field3,
(PRICE PSF)
BUT, sometimes, when entering data, I do not know one or both of the
first two fields and leave them blank. When that is the case, Access
places either "#NUM!" or "$0" in the field. How do I change the
preferences to read "TBD" in field 3 when either field1 or field2 are
empty?

Jeff

 

[Techknownothing]

OK
Here is where I am so far:

Private Sub PRICE_PSF_AfterUpdate()

Me.PRICE_PSF = IIf(IsNull(SALE_PRICE) or IsNull(Me!metrodcbuildings!rsf
),"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

End Sub

The default value is set to TBD and the FORMAT is set to currency.

Jeff

yes, it does matter. You have to qualify the reference to include the
subform contorl on the main form, note, this is not the name of the subform,
but the name of the subform control.
Me!SubFormControlName!Field2

As to the compile error, my eyesight is not so good, so I can't see what you
did from here. Posting it would be very helpful

I'm still not there.
I am getting the following error message when I try to put in the code:

Compile Error:
Expected: List separator or )

Also, does it matter that field1 is in the main form and and field2 is
in a subform with field3?

Jeff

I would make the Default Value property of Field3 "TBD"
I would put the code in a sub and call it from the After Update events of
Field1 and Field2.

:

Where should I insert this code?

Jeff

Klatuu wrote:
I believe your code will throw a divide by 0 error.
How about
Me.Field3 = IIf(IsNull(Field1) or IsNull(Field2),"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

:

Jeff,

Me.Field3 = IIF(Nz([sale price], 0) / Nz(rsf, 0) = 0, "TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a form that performs a simple calculation.
It divides field1 (SALE PRICE) by field2 (RSF) to give me field3,
(PRICE PSF)
BUT, sometimes, when entering data, I do not know one or both of the
first two fields and leave them blank. When that is the case, Access
places either "#NUM!" or "$0" in the field. How do I change the
preferences to read "TBD" in field 3 when either field1 or field2 are
empty?

Jeff

 

[Klatuu]

I don't really see the problem, the syntax appears to be correct.

OK
Here is where I am so far:

Private Sub PRICE_PSF_AfterUpdate()

Me.PRICE_PSF = IIf(IsNull(SALE_PRICE) or IsNull(Me!metrodcbuildings!rsf
),"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

End Sub

The default value is set to TBD and the FORMAT is set to currency.

Jeff

yes, it does matter. You have to qualify the reference to include the
subform contorl on the main form, note, this is not the name of the subform,
but the name of the subform control.
Me!SubFormControlName!Field2

As to the compile error, my eyesight is not so good, so I can't see what you
did from here. Posting it would be very helpful

I'm still not there.
I am getting the following error message when I try to put in the code:

Compile Error:
Expected: List separator or )

Also, does it matter that field1 is in the main form and and field2 is
in a subform with field3?

Jeff


Klatuu wrote:
I would make the Default Value property of Field3 "TBD"
I would put the code in a sub and call it from the After Update events of
Field1 and Field2.

:

Where should I insert this code?

Jeff

Klatuu wrote:
I believe your code will throw a divide by 0 error.
How about
Me.Field3 = IIf(IsNull(Field1) or IsNull(Field2),"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

:

Jeff,

Me.Field3 = IIF(Nz([sale price], 0) / Nz(rsf, 0) = 0, "TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a form that performs a simple calculation.
It divides field1 (SALE PRICE) by field2 (RSF) to give me field3,
(PRICE PSF)
BUT, sometimes, when entering data, I do not know one or both of the
first two fields and leave them blank. When that is the case, Access
places either "#NUM!" or "$0" in the field. How do I change the
preferences to read "TBD" in field 3 when either field1 or field2 are
empty?

Jeff

 

[Techknownothing]

It is calculating correctly but still no "TBD."
Perhaps it is something else.
Could the problem be that the FORMAT for the field is set to currency
but the DEFAULT VALUE is set to "TBD" ?

Jeff

I don't really see the problem, the syntax appears to be correct.

OK
Here is where I am so far:

Private Sub PRICE_PSF_AfterUpdate()

Me.PRICE_PSF = IIf(IsNull(SALE_PRICE) or IsNull(Me!metrodcbuildings!rsf
),"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

End Sub

The default value is set to TBD and the FORMAT is set to currency.

Jeff

yes, it does matter. You have to qualify the reference to include the
subform contorl on the main form, note, this is not the name of the subform,
but the name of the subform control.
Me!SubFormControlName!Field2

As to the compile error, my eyesight is not so good, so I can't see what you
did from here. Posting it would be very helpful

:

I'm still not there.
I am getting the following error message when I try to put in the code:

Compile Error:
Expected: List separator or )

Also, does it matter that field1 is in the main form and and field2 is
in a subform with field3?

Jeff


Klatuu wrote:
I would make the Default Value property of Field3 "TBD"
I would put the code in a sub and call it from the After Update events of
Field1 and Field2.

:

Where should I insert this code?

Jeff

Klatuu wrote:
I believe your code will throw a divide by 0 error.
How about
Me.Field3 = IIf(IsNull(Field1) or IsNull(Field2),"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

:

Jeff,

Me.Field3 = IIF(Nz([sale price], 0) / Nz(rsf, 0) = 0, "TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a form that performs a simple calculation.
It divides field1 (SALE PRICE) by field2 (RSF) to give me field3,
(PRICE PSF)
BUT, sometimes, when entering data, I do not know one or both of the
first two fields and leave them blank. When that is the case, Access
places either "#NUM!" or "$0" in the field. How do I change the
preferences to read "TBD" in field 3 when either field1 or field2 are
empty?

Jeff

 

[Klatuu]

If the Default Value is set in the form, no. If your table field is
currency, I am suprised it is not thowing an error if it tries to update the
table with TBD. That will create an error.

It is calculating correctly but still no "TBD."
Perhaps it is something else.
Could the problem be that the FORMAT for the field is set to currency
but the DEFAULT VALUE is set to "TBD" ?

Jeff

I don't really see the problem, the syntax appears to be correct.

OK
Here is where I am so far:

Private Sub PRICE_PSF_AfterUpdate()

Me.PRICE_PSF = IIf(IsNull(SALE_PRICE) or IsNull(Me!metrodcbuildings!rsf
),"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

End Sub

The default value is set to TBD and the FORMAT is set to currency.

Jeff


Klatuu wrote:
yes, it does matter. You have to qualify the reference to include the
subform contorl on the main form, note, this is not the name of the subform,
but the name of the subform control.
Me!SubFormControlName!Field2

As to the compile error, my eyesight is not so good, so I can't see what you
did from here. Posting it would be very helpful

:

I'm still not there.
I am getting the following error message when I try to put in the code:

Compile Error:
Expected: List separator or )

Also, does it matter that field1 is in the main form and and field2 is
in a subform with field3?

Jeff


Klatuu wrote:
I would make the Default Value property of Field3 "TBD"
I would put the code in a sub and call it from the After Update events of
Field1 and Field2.

:

Where should I insert this code?

Jeff

Klatuu wrote:
I believe your code will throw a divide by 0 error.
How about
Me.Field3 = IIf(IsNull(Field1) or IsNull(Field2),"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

:

Jeff,

Me.Field3 = IIF(Nz([sale price], 0) / Nz(rsf, 0) = 0, "TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a form that performs a simple calculation.
It divides field1 (SALE PRICE) by field2 (RSF) to give me field3,
(PRICE PSF)
BUT, sometimes, when entering data, I do not know one or both of the
first two fields and leave them blank. When that is the case, Access
places either "#NUM!" or "$0" in the field. How do I change the
preferences to read "TBD" in field 3 when either field1 or field2 are
empty?

Jeff

 

[Graham R Seach]

The problem is that (a) your field is a Currency datatype, which can't
contain a string value, and (b) the end of your calculation fails to fully
qualify rsf as Me!metrodcbuildings!rsf.

As for resolving the datatype problem, you can place a label control
underneath (using Format | Send to Back) the textbox, set it's Caption
property = "TBD", and set the textbox's BackStyle property to Transparent if
the calculation returns "TBD", otherwise put the value into the textbox, and
set its BackStyle property = Normal.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

It is calculating correctly but still no "TBD."
Perhaps it is something else.
Could the problem be that the FORMAT for the field is set to currency
but the DEFAULT VALUE is set to "TBD" ?

Jeff

I don't really see the problem, the syntax appears to be correct.

OK
Here is where I am so far:

Private Sub PRICE_PSF_AfterUpdate()

Me.PRICE_PSF = IIf(IsNull(SALE_PRICE) or IsNull(Me!metrodcbuildings!rsf
),"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

End Sub

The default value is set to TBD and the FORMAT is set to currency.

Jeff


Klatuu wrote:
yes, it does matter. You have to qualify the reference to include
the
subform contorl on the main form, note, this is not the name of the
subform,
but the name of the subform control.
Me!SubFormControlName!Field2

As to the compile error, my eyesight is not so good, so I can't see
what you
did from here. Posting it would be very helpful

:

I'm still not there.
I am getting the following error message when I try to put in the
code:

Compile Error:
Expected: List separator or )

Also, does it matter that field1 is in the main form and and field2
is
in a subform with field3?

Jeff


Klatuu wrote:
I would make the Default Value property of Field3 "TBD"
I would put the code in a sub and call it from the After Update
events of
Field1 and Field2.

:

Where should I insert this code?

Jeff

Klatuu wrote:
I believe your code will throw a divide by 0 error.
How about
Me.Field3 = IIf(IsNull(Field1) or IsNull(Field2),"TBD",
CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

:

Jeff,

Me.Field3 = IIF(Nz([sale price], 0) / Nz(rsf, 0) = 0,
"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a form that performs a simple calculation.
It divides field1 (SALE PRICE) by field2 (RSF) to give me
field3,
(PRICE PSF)
BUT, sometimes, when entering data, I do not know one or
both of the
first two fields and leave them blank. When that is the
case, Access
places either "#NUM!" or "$0" in the field. How do I
change the
preferences to read "TBD" in field 3 when either field1
or field2 are
empty?

Jeff

 

[Techknownothing]

Thanks so much for your help
A couple more questions.

Here is the code I have now:

Private Sub PRICE_PSF_AfterUpdate()

Me.PRICE_PSF = IIf(IsNull(SALE_PRICE) Or
IsNull(Me!METRODCBUILDINGS!RSF), "TBD", CCur(Nz([SALE_PRICE], 0) /
Nz(RSF, 0)))

End Sub

Any suggestions on how to fully qualify RSF as Me!METRODCBUILDINGS!RSF
?

Also, how do I set the textbox's BackStyle property to transparent if
the calculation returns "TBD"? I know where the field property is but
all it has is a pulldown menu for normal and transparent.
Sorry, I'm a newbie

Jeff

The problem is that (a) your field is a Currency datatype, which can't
contain a string value, and (b) the end of your calculation fails to fully
qualify rsf as Me!metrodcbuildings!rsf.

As for resolving the datatype problem, you can place a label control
underneath (using Format | Send to Back) the textbox, set it's Caption
property = "TBD", and set the textbox's BackStyle property to Transparent if
the calculation returns "TBD", otherwise put the value into the textbox, and
set its BackStyle property = Normal.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

It is calculating correctly but still no "TBD."
Perhaps it is something else.
Could the problem be that the FORMAT for the field is set to currency
but the DEFAULT VALUE is set to "TBD" ?

Jeff

I don't really see the problem, the syntax appears to be correct.

:

OK
Here is where I am so far:

Private Sub PRICE_PSF_AfterUpdate()

Me.PRICE_PSF = IIf(IsNull(SALE_PRICE) or IsNull(Me!metrodcbuildings!rsf
),"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

End Sub

The default value is set to TBD and the FORMAT is set to currency.

Jeff


Klatuu wrote:
yes, it does matter. You have to qualify the reference to include
the
subform contorl on the main form, note, this is not the name of the
subform,
but the name of the subform control.
Me!SubFormControlName!Field2

As to the compile error, my eyesight is not so good, so I can't see
what you
did from here. Posting it would be very helpful

:

I'm still not there.
I am getting the following error message when I try to put in the
code:

Compile Error:
Expected: List separator or )

Also, does it matter that field1 is in the main form and and field2
is
in a subform with field3?

Jeff


Klatuu wrote:
I would make the Default Value property of Field3 "TBD"
I would put the code in a sub and call it from the After Update
events of
Field1 and Field2.

:

Where should I insert this code?

Jeff

Klatuu wrote:
I believe your code will throw a divide by 0 error.
How about
Me.Field3 = IIf(IsNull(Field1) or IsNull(Field2),"TBD",
CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

:

Jeff,

Me.Field3 = IIF(Nz([sale price], 0) / Nz(rsf, 0) = 0,
"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a form that performs a simple calculation.
It divides field1 (SALE PRICE) by field2 (RSF) to give me
field3,
(PRICE PSF)
BUT, sometimes, when entering data, I do not know one or
both of the
first two fields and leave them blank. When that is the
case, Access
places either "#NUM!" or "$0" in the field. How do I
change the
preferences to read "TBD" in field 3 when either field1
or field2 are
empty?

Jeff

 

[Graham R Seach]

<<Any suggestions on how to fully qualify RSF as Me!METRODCBUILDINGS!RSF>>
Er, am I missing something?
Me!METRODCBUILDINGS!RSF

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Thanks so much for your help
A couple more questions.

Here is the code I have now:

Private Sub PRICE_PSF_AfterUpdate()

Me.PRICE_PSF = IIf(IsNull(SALE_PRICE) Or
IsNull(Me!METRODCBUILDINGS!RSF), "TBD", CCur(Nz([SALE_PRICE], 0) /
Nz(RSF, 0)))

End Sub

Any suggestions on how to fully qualify RSF as Me!METRODCBUILDINGS!RSF
?

Also, how do I set the textbox's BackStyle property to transparent if
the calculation returns "TBD"? I know where the field property is but
all it has is a pulldown menu for normal and transparent.
Sorry, I'm a newbie

Jeff

The problem is that (a) your field is a Currency datatype, which can't
contain a string value, and (b) the end of your calculation fails to
fully
qualify rsf as Me!metrodcbuildings!rsf.

As for resolving the datatype problem, you can place a label control
underneath (using Format | Send to Back) the textbox, set it's Caption
property = "TBD", and set the textbox's BackStyle property to Transparent
if
the calculation returns "TBD", otherwise put the value into the textbox,
and
set its BackStyle property = Normal.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

It is calculating correctly but still no "TBD."
Perhaps it is something else.
Could the problem be that the FORMAT for the field is set to currency
but the DEFAULT VALUE is set to "TBD" ?

Jeff

Klatuu wrote:
I don't really see the problem, the syntax appears to be correct.

:

OK
Here is where I am so far:

Private Sub PRICE_PSF_AfterUpdate()

Me.PRICE_PSF = IIf(IsNull(SALE_PRICE) or
IsNull(Me!metrodcbuildings!rsf
),"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

End Sub

The default value is set to TBD and the FORMAT is set to currency.

Jeff


Klatuu wrote:
yes, it does matter. You have to qualify the reference to include
the
subform contorl on the main form, note, this is not the name of
the
subform,
but the name of the subform control.
Me!SubFormControlName!Field2

As to the compile error, my eyesight is not so good, so I can't
see
what you
did from here. Posting it would be very helpful

:

I'm still not there.
I am getting the following error message when I try to put in
the
code:

Compile Error:
Expected: List separator or )

Also, does it matter that field1 is in the main form and and
field2
is
in a subform with field3?

Jeff


Klatuu wrote:
I would make the Default Value property of Field3 "TBD"
I would put the code in a sub and call it from the After
Update
events of
Field1 and Field2.

:

Where should I insert this code?

Jeff

Klatuu wrote:
I believe your code will throw a divide by 0 error.
How about
Me.Field3 = IIf(IsNull(Field1) or IsNull(Field2),"TBD",
CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

:

Jeff,

Me.Field3 = IIF(Nz([sale price], 0) / Nz(rsf, 0) = 0,
"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

message
I have a form that performs a simple calculation.
It divides field1 (SALE PRICE) by field2 (RSF) to give
me
field3,
(PRICE PSF)
BUT, sometimes, when entering data, I do not know one
or
both of the
first two fields and leave them blank. When that is
the
case, Access
places either "#NUM!" or "$0" in the field. How do I
change the
preferences to read "TBD" in field 3 when either
field1
or field2 are
empty?

Jeff

 

[Techknownothing]

You wrote:

Sorry...that was unclear.
How do I write the expression to set the textbox's BackStyle property
to transparent if
the calculation returns "TBD"? I know where the field property is but
all it has is a pulldown menu for normal and transparent...but I think
it calls for an expression.
Will the pulldown field accept an expression and if so, what should it
be?

Also, you wrote:
"The problem is that (a) your field is a Currency datatype, which can't

contain a string value, and (b) the end of your calculation fails to
fully
qualify rsf as Me!metrodcbuildings!rsf."

I was wondering if you had a suggestion for solving (b)?
thanks again

Jeff

<<Any suggestions on how to fully qualify RSF as Me!METRODCBUILDINGS!RSF>>
Er, am I missing something?
Me!METRODCBUILDINGS!RSF

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Thanks so much for your help
A couple more questions.

Here is the code I have now:

Private Sub PRICE_PSF_AfterUpdate()

Me.PRICE_PSF = IIf(IsNull(SALE_PRICE) Or
IsNull(Me!METRODCBUILDINGS!RSF), "TBD", CCur(Nz([SALE_PRICE], 0) /
Nz(RSF, 0)))

End Sub

Any suggestions on how to fully qualify RSF as Me!METRODCBUILDINGS!RSF
?

Also, how do I set the textbox's BackStyle property to transparent if
the calculation returns "TBD"? I know where the field property is but
all it has is a pulldown menu for normal and transparent.
Sorry, I'm a newbie

Jeff

The problem is that (a) your field is a Currency datatype, which can't
contain a string value, and (b) the end of your calculation fails to
fully
qualify rsf as Me!metrodcbuildings!rsf.

As for resolving the datatype problem, you can place a label control
underneath (using Format | Send to Back) the textbox, set it's Caption
property = "TBD", and set the textbox's BackStyle property to Transparent
if
the calculation returns "TBD", otherwise put the value into the textbox,
and
set its BackStyle property = Normal.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

It is calculating correctly but still no "TBD."
Perhaps it is something else.
Could the problem be that the FORMAT for the field is set to currency
but the DEFAULT VALUE is set to "TBD" ?

Jeff

Klatuu wrote:
I don't really see the problem, the syntax appears to be correct.

:

OK
Here is where I am so far:

Private Sub PRICE_PSF_AfterUpdate()

Me.PRICE_PSF = IIf(IsNull(SALE_PRICE) or
IsNull(Me!metrodcbuildings!rsf
),"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

End Sub

The default value is set to TBD and the FORMAT is set to currency.

Jeff


Klatuu wrote:
yes, it does matter. You have to qualify the reference to include
the
subform contorl on the main form, note, this is not the name of
the
subform,
but the name of the subform control.
Me!SubFormControlName!Field2

As to the compile error, my eyesight is not so good, so I can't
see
what you
did from here. Posting it would be very helpful

:

I'm still not there.
I am getting the following error message when I try to put in
the
code:

Compile Error:
Expected: List separator or )

Also, does it matter that field1 is in the main form and and
field2
is
in a subform with field3?

Jeff


Klatuu wrote:
I would make the Default Value property of Field3 "TBD"
I would put the code in a sub and call it from the After
Update
events of
Field1 and Field2.

:

Where should I insert this code?

Jeff

Klatuu wrote:
I believe your code will throw a divide by 0 error.
How about
Me.Field3 = IIf(IsNull(Field1) or IsNull(Field2),"TBD",
CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

:

Jeff,

Me.Field3 = IIF(Nz([sale price], 0) / Nz(rsf, 0) = 0,
"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

message
I have a form that performs a simple calculation.
It divides field1 (SALE PRICE) by field2 (RSF) to give
me
field3,
(PRICE PSF)
BUT, sometimes, when entering data, I do not know one
or
both of the
first two fields and leave them blank. When that is
the
case, Access
places either "#NUM!" or "$0" in the field. How do I
change the
preferences to read "TBD" in field 3 when either
field1
or field2 are
empty?

Jeff

 

[Graham R Seach]

1. Add your combo to the form. Let's call it cboCombo.
2. Add a Label to the form. Let's call it lblTBD.
3. Set the Label's dimensions to be the same as the combo.
4. Set the Label's Caption property = "TBD", and centre the text.
5. Set the Label's BackColor property to be the same as the combo.
6. Set the Label's ForeColor property to grey.
7. Place the Label directly beneath the combo. If necessary, use Format |
Send to Back.
8. Place the following code in the shown events:

Private Sub cboCombo_LostFocus()
Me.cboCombo.BackStyle = Abs(Len("" & Me.cboCombo) <> 0)
End Sub

Private Sub Form_Current()
Me.cboCombo.BackStyle = Abs(Len("" & Me.cboCombo) <> 0)
End Sub

If you want to use this technique on more than one control, create a custom
Sub as follows:
Private Sub TransparentControl(strControlName As String)
Me!(strControlName).BackStyle = Abs(Len("" &
Me!(strControlName).Value) <> 0)
End Sub

....then the form's Current event, and the combo's LostFocus event would look
like this:

Private Sub cboCombo_LostFocus()
TransparentControl "cboCombo"
End Sub

Private Sub Form_Current()
TransparentControl "cboCombo"
End Sub

Use the same construct on any control that needs it.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

You wrote:

Sorry...that was unclear.
How do I write the expression to set the textbox's BackStyle property
to transparent if
the calculation returns "TBD"? I know where the field property is but
all it has is a pulldown menu for normal and transparent...but I think
it calls for an expression.
Will the pulldown field accept an expression and if so, what should it
be?

Also, you wrote:
"The problem is that (a) your field is a Currency datatype, which can't

contain a string value, and (b) the end of your calculation fails to
fully
qualify rsf as Me!metrodcbuildings!rsf."

I was wondering if you had a suggestion for solving (b)?
thanks again

Jeff

<<Any suggestions on how to fully qualify RSF as
Me!METRODCBUILDINGS!RSF>>
Er, am I missing something?
Me!METRODCBUILDINGS!RSF

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Thanks so much for your help
A couple more questions.

Here is the code I have now:

Private Sub PRICE_PSF_AfterUpdate()

Me.PRICE_PSF = IIf(IsNull(SALE_PRICE) Or
IsNull(Me!METRODCBUILDINGS!RSF), "TBD", CCur(Nz([SALE_PRICE], 0) /
Nz(RSF, 0)))

End Sub

Any suggestions on how to fully qualify RSF as Me!METRODCBUILDINGS!RSF
?

Also, how do I set the textbox's BackStyle property to transparent if
the calculation returns "TBD"? I know where the field property is but
all it has is a pulldown menu for normal and transparent.
Sorry, I'm a newbie

Jeff

Graham R Seach wrote:
The problem is that (a) your field is a Currency datatype, which can't
contain a string value, and (b) the end of your calculation fails to
fully
qualify rsf as Me!metrodcbuildings!rsf.

As for resolving the datatype problem, you can place a label control
underneath (using Format | Send to Back) the textbox, set it's Caption
property = "TBD", and set the textbox's BackStyle property to
Transparent
if
the calculation returns "TBD", otherwise put the value into the
textbox,
and
set its BackStyle property = Normal.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

It is calculating correctly but still no "TBD."
Perhaps it is something else.
Could the problem be that the FORMAT for the field is set to
currency
but the DEFAULT VALUE is set to "TBD" ?

Jeff

Klatuu wrote:
I don't really see the problem, the syntax appears to be correct.

:

OK
Here is where I am so far:

Private Sub PRICE_PSF_AfterUpdate()

Me.PRICE_PSF = IIf(IsNull(SALE_PRICE) or
IsNull(Me!metrodcbuildings!rsf
),"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

End Sub

The default value is set to TBD and the FORMAT is set to
currency.

Jeff


Klatuu wrote:
yes, it does matter. You have to qualify the reference to
include
the
subform contorl on the main form, note, this is not the name of
the
subform,
but the name of the subform control.
Me!SubFormControlName!Field2

As to the compile error, my eyesight is not so good, so I can't
see
what you
did from here. Posting it would be very helpful

:

I'm still not there.
I am getting the following error message when I try to put in
the
code:

Compile Error:
Expected: List separator or )

Also, does it matter that field1 is in the main form and and
field2
is
in a subform with field3?

Jeff


Klatuu wrote:
I would make the Default Value property of Field3 "TBD"
I would put the code in a sub and call it from the After
Update
events of
Field1 and Field2.

:

Where should I insert this code?

Jeff

Klatuu wrote:
I believe your code will throw a divide by 0 error.
How about
Me.Field3 = IIf(IsNull(Field1) or IsNull(Field2),"TBD",
CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

:

Jeff,

Me.Field3 = IIF(Nz([sale price], 0) / Nz(rsf, 0) = 0,
"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

message
I have a form that performs a simple calculation.
It divides field1 (SALE PRICE) by field2 (RSF) to
give
me
field3,
(PRICE PSF)
BUT, sometimes, when entering data, I do not know
one
or
both of the
first two fields and leave them blank. When that is
the
case, Access
places either "#NUM!" or "$0" in the field. How do
I
change the
preferences to read "TBD" in field 3 when either
field1
or field2 are
empty?

Jeff

 

[Techknownothing]

Thank you!!!

Jeff

1. Add your combo to the form. Let's call it cboCombo.
2. Add a Label to the form. Let's call it lblTBD.
3. Set the Label's dimensions to be the same as the combo.
4. Set the Label's Caption property = "TBD", and centre the text.
5. Set the Label's BackColor property to be the same as the combo.
6. Set the Label's ForeColor property to grey.
7. Place the Label directly beneath the combo. If necessary, use Format |
Send to Back.
8. Place the following code in the shown events:

Private Sub cboCombo_LostFocus()
Me.cboCombo.BackStyle = Abs(Len("" & Me.cboCombo) <> 0)
End Sub

Private Sub Form_Current()
Me.cboCombo.BackStyle = Abs(Len("" & Me.cboCombo) <> 0)
End Sub

If you want to use this technique on more than one control, create a custom
Sub as follows:
Private Sub TransparentControl(strControlName As String)
Me!(strControlName).BackStyle = Abs(Len("" &
Me!(strControlName).Value) <> 0)
End Sub

...then the form's Current event, and the combo's LostFocus event would look
like this:

Private Sub cboCombo_LostFocus()
TransparentControl "cboCombo"
End Sub

Private Sub Form_Current()
TransparentControl "cboCombo"
End Sub

Use the same construct on any control that needs it.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

You wrote:

Sorry...that was unclear.
How do I write the expression to set the textbox's BackStyle property
to transparent if
the calculation returns "TBD"? I know where the field property is but
all it has is a pulldown menu for normal and transparent...but I think
it calls for an expression.
Will the pulldown field accept an expression and if so, what should it
be?

Also, you wrote:
"The problem is that (a) your field is a Currency datatype, which can't

contain a string value, and (b) the end of your calculation fails to
fully
qualify rsf as Me!metrodcbuildings!rsf."

I was wondering if you had a suggestion for solving (b)?
thanks again

Jeff

<<Any suggestions on how to fully qualify RSF as
Me!METRODCBUILDINGS!RSF>>
Er, am I missing something?
Me!METRODCBUILDINGS!RSF

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Thanks so much for your help
A couple more questions.

Here is the code I have now:

Private Sub PRICE_PSF_AfterUpdate()

Me.PRICE_PSF = IIf(IsNull(SALE_PRICE) Or
IsNull(Me!METRODCBUILDINGS!RSF), "TBD", CCur(Nz([SALE_PRICE], 0) /
Nz(RSF, 0)))

End Sub

Any suggestions on how to fully qualify RSF as Me!METRODCBUILDINGS!RSF
?

Also, how do I set the textbox's BackStyle property to transparent if
the calculation returns "TBD"? I know where the field property is but
all it has is a pulldown menu for normal and transparent.
Sorry, I'm a newbie

Jeff

Graham R Seach wrote:
The problem is that (a) your field is a Currency datatype, which can't
contain a string value, and (b) the end of your calculation fails to
fully
qualify rsf as Me!metrodcbuildings!rsf.

As for resolving the datatype problem, you can place a label control
underneath (using Format | Send to Back) the textbox, set it's Caption
property = "TBD", and set the textbox's BackStyle property to
Transparent
if
the calculation returns "TBD", otherwise put the value into the
textbox,
and
set its BackStyle property = Normal.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

It is calculating correctly but still no "TBD."
Perhaps it is something else.
Could the problem be that the FORMAT for the field is set to
currency
but the DEFAULT VALUE is set to "TBD" ?

Jeff

Klatuu wrote:
I don't really see the problem, the syntax appears to be correct.

:

OK
Here is where I am so far:

Private Sub PRICE_PSF_AfterUpdate()

Me.PRICE_PSF = IIf(IsNull(SALE_PRICE) or
IsNull(Me!metrodcbuildings!rsf
),"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

End Sub

The default value is set to TBD and the FORMAT is set to
currency.

Jeff


Klatuu wrote:
yes, it does matter. You have to qualify the reference to
include
the
subform contorl on the main form, note, this is not the name of
the
subform,
but the name of the subform control.
Me!SubFormControlName!Field2

As to the compile error, my eyesight is not so good, so I can't
see
what you
did from here. Posting it would be very helpful

:

I'm still not there.
I am getting the following error message when I try to put in
the
code:

Compile Error:
Expected: List separator or )

Also, does it matter that field1 is in the main form and and
field2
is
in a subform with field3?

Jeff


Klatuu wrote:
I would make the Default Value property of Field3 "TBD"
I would put the code in a sub and call it from the After
Update
events of
Field1 and Field2.

:

Where should I insert this code?

Jeff

Klatuu wrote:
I believe your code will throw a divide by 0 error.
How about
Me.Field3 = IIf(IsNull(Field1) or IsNull(Field2),"TBD",
CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

:

Jeff,

Me.Field3 = IIF(Nz([sale price], 0) / Nz(rsf, 0) = 0,
"TBD", CCur(Nz([sale
price], 0) / Nz(rsf, 0)))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

message
I have a form that performs a simple calculation.
It divides field1 (SALE PRICE) by field2 (RSF) to
give
me
field3,
(PRICE PSF)
BUT, sometimes, when entering data, I do not know
one
or
both of the
first two fields and leave them blank. When that is
the
case, Access
places either "#NUM!" or "$0" in the field. How do
I
change the
preferences to read "TBD" in field 3 when either
field1
or field2 are
empty?

Jeff