updating value to null

1

111

I have a SETVALUE problem where:

item:[Forms]![Profit Tracking 2]![Sell Rate]
expression:[Forms]![Profit Tracking 2]![Profit
Tracking(Receivables)].[Form]![inv total]

This works fine aslong as there is always something in [inv total] - even 0.00
will work. but if [inv total] is null (ie the user deletes the record) the SELL
Rate field will not update to "Null" (or nothing). Is there something i can add
to the expression to allow for setvalue to be null? i can't have 0.00 in the
field as this will screw up another part of the program . if it's null it has
to show null.

any advise is greatly appreciated.
 
S

Steve Schapel

111,

Assuming I understand your situation correctly, if the user deletes the
record, then [inv total] is not null, it is non-existent.

I suggest you try the following, and post back if it doesn't work
correctly for you...
In a standard module, create a user-defined function, like this...

Public Function NexNull(InputValue As Variant) As Variant
If Not (IsNumeric(InputValue)) Then
NexNull = Null
Else
NexNull = InputValue
End If
End Function

And then, in the Expression argument of the SetValue macro action...
NexNull([Forms]![Profit Tracking 2]![Profit
Tracking(Receivables)].[Form]![inv total])
 
1

111

Thanks i'll give it a try

111,

Assuming I understand your situation correctly, if the user deletes the
record, then [inv total] is not null, it is non-existent.

I suggest you try the following, and post back if it doesn't work
correctly for you...
In a standard module, create a user-defined function, like this...

Public Function NexNull(InputValue As Variant) As Variant
If Not (IsNumeric(InputValue)) Then
NexNull = Null
Else
NexNull = InputValue
End If
End Function

And then, in the Expression argument of the SetValue macro action...
NexNull([Forms]![Profit Tracking 2]![Profit
Tracking(Receivables)].[Form]![inv total])

--
Steve Schapel, Microsoft Access MVP
I have a SETVALUE problem where:

item:[Forms]![Profit Tracking 2]![Sell Rate]
expression:[Forms]![Profit Tracking 2]![Profit
Tracking(Receivables)].[Form]![inv total]

This works fine aslong as there is always something in [inv total] - even 0.00
will work. but if [inv total] is null (ie the user deletes the record) the SELL
Rate field will not update to "Null" (or nothing). Is there something i can add
to the expression to allow for setvalue to be null? i can't have 0.00 in the
field as this will screw up another part of the program . if it's null it has
to show null.

any advise is greatly appreciated.
 
M

Michael Cheng [MSFT]

Hi,

I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top