removing #Num! from txt box

[evilcowstare via AccessMonster.com]

Hi
I have a basic calculation running in a recordsource on my form, the problem
I have is that if nothing has been entered into the other fields to which it
calculates from it displays #Num! in the txt box.

I have Net Value and Job Cost with auto calculate an answer into Margin.
I then have GP that does Margin/Netvalue and is meant to put to answer in the
GP box, this works fine unless the default value of £0.00 is showing in Net
Value or Job Cost. What I need is for it to either show 0 or nothing at all
in GP until something is added to the other boxes.

Thanks

I have heard of using an IIF statement or something but cant get it to work
for me??
Might help?

 

[Guest]

What d you have now?

Try the IIf

=IIf([NetValueFieldName]=0,0,YourCalculation)

 

[Arvin Meyer [MVP]]

Assuming single form view, use a piece of code in each of the calculated
elements to fill the result:

Sub text1_AfterUpdate

If
Len(text1 + text2) >0 Then
'Your expression here
Else
text3 = Null
End If

End Sub

 

[evilcowstare via AccessMonster.com]

Hi I tried the code but kept getting errors?
Maybe Im doing it wrong...

The calculation I want should end up in GP

My txt boxes are Netvalue, Jobcost and margin, all have £0.00 in until i
enter info into netvalue or jobcost.

Margin uses a control source of Netvalue-jobcost and puts its answer into
margin.
I then need GP to do the calculation margin-netvalue.

How should I do the code and where do I need to put it, sorry Im new to all
this )

Thank You for your help!
Jay

Assuming single form view, use a piece of code in each of the calculated
elements to fill the result:

Sub text1_AfterUpdate

If
Len(text1 + text2) >0 Then
'Your expression here
Else
text3 = Null
End If

End Sub

Hi
I have a basic calculation running in a recordsource on my form, the

[quoted text clipped - 18 lines]

for me??
Might help?

 

[Arvin Meyer [MVP]]

Hi I tried the code but kept getting errors?
Maybe Im doing it wrong...

Assuming you changed the names of the controls, what errors did you get? Did
you set a breakpoint and toggle through the code untils it errored? What
line did it error on?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

The calculation I want should end up in GP

My txt boxes are Netvalue, Jobcost and margin, all have £0.00 in until i
enter info into netvalue or jobcost.

Margin uses a control source of Netvalue-jobcost and puts its answer into
margin.
I then need GP to do the calculation margin-netvalue.

How should I do the code and where do I need to put it, sorry Im new to
all
this )

Thank You for your help!
Jay

Assuming single form view, use a piece of code in each of the calculated
elements to fill the result:

Sub text1_AfterUpdate

If
Len(text1 + text2) >0 Then
'Your expression here
Else
text3 = Null
End If

End Sub

Hi
I have a basic calculation running in a recordsource on my form, the

[quoted text clipped - 18 lines]

for me??
Might help?

 

[evilcowstare via AccessMonster.com]

Hi first of all it had a problem with len and then it just kept going through
all of it finding fault with every little bit, not really sure exactly what
the problem is, seems to not like the whole thing?

Thanks for the help!

Hi I tried the code but kept getting errors?
Maybe Im doing it wrong...

Assuming you changed the names of the controls, what errors did you get? Did
you set a breakpoint and toggle through the code untils it errored? What
line did it error on?

The calculation I want should end up in GP

[quoted text clipped - 30 lines]

 

[Arvin Meyer [MVP]]

Try changing your references (from any code window choose Tools >>>
References) to DAO. Although that's not the problem with my code there may
be some other code that has a problem. Also uncheck the reference to ADO
(Access Data Objects) or disambiguate the references by using the explicit
definition:

Dim rst As DAO.Recordset
or
Dim rst As ADO.Recordset

The Len function measure the length in characters of a string. One way of
guaranteeing that you are passing a variable is to use:

If Len(stringvariable & vbNullString) > 0 Then

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi first of all it had a problem with len and then it just kept going
through
all of it finding fault with every little bit, not really sure exactly
what
the problem is, seems to not like the whole thing?

Thanks for the help!

Hi I tried the code but kept getting errors?
Maybe Im doing it wrong...

Assuming you changed the names of the controls, what errors did you get?
Did
you set a breakpoint and toggle through the code untils it errored? What
line did it error on?

The calculation I want should end up in GP

[quoted text clipped - 30 lines]

for me??
Might help?

 

[evilcowstare via AccessMonster.com]

Hi Arvin,
thanks loads for you help, it seems to have sorted itself out now )
Thanks Again
Jay

Try changing your references (from any code window choose Tools >>>
References) to DAO. Although that's not the problem with my code there may
be some other code that has a problem. Also uncheck the reference to ADO
(Access Data Objects) or disambiguate the references by using the explicit
definition:

Dim rst As DAO.Recordset
or
Dim rst As ADO.Recordset

The Len function measure the length in characters of a string. One way of
guaranteeing that you are passing a variable is to use:

If Len(stringvariable & vbNullString) > 0 Then

Hi first of all it had a problem with len and then it just kept going
through

[quoted text clipped - 16 lines]