A problem With NULL!!!

[Pharcyde]

Heya All!

I have a little problem trying to assign a null value.

All I need really is the ability to assign a null value to a field if no mark is entered in a box.

At the moment, If the field is empty, FinalMark is assigned the value '0'.
I would like it to be blank.

Function FinalMark(Mark, Mark2, Mark3) As Integer
    If Not IsNull(Mark3) Then
        FinalMark = Mark3
        
        Else
            If Not IsNull(Mark2) Then
                FinalMark = Mark2
                
                Else
                    If Not IsNull(Mark) Then
                    FinalMark = Mark

                    End If
            End If
    End If
           
End Function

is the function tat works properly. When I try to add:

Else If IsNull(Mark) Then
                          FinalMark = Null
                      EndIf

I get 'Invalid Use of Null'

I suspect its the 'As Integer' part thats doing this, but I do not know the way around it!!

Please help! LeeBoy

 

[maxmangion]

At the moment, If the field is empty, FinalMark is assigned the value '0'.

remove the 0 from being the default value in the property

 

[KenHigg]

I suspect this is a bad suggestion but, If you set it to a negative number instead of null would that serve your purpose?

 

[namliam]

change "as Integer" to "as Variant"

Integers return 0 when empty, Variants can return Null values

Greetz

P.S. Thanx for reminding that you got this code originaly from this board and in particularly from me

 

[Pharcyde]

Removing the 0 sounded perfect, but it didnt make any difference!
Having a negative number just makes it come up as a negative number!

I've found a way past it by altering the table manuall and printing the reports off without opening the form (So it cant change the values) and I guess I could put an 'Iif' statement in the report to pick up a 0 and hcnage it to null....

Any other suggestions??

 

[Pharcyde]

P.S. Thanx for reminding that you got this code originaly from this board and in particularly from me

Holy Sheet! Yes, everyone - how rude am I!?

Many thanks to the mailman for his help with the code originally - he is very correct in pointing out that it was him that sorted the first lot out for me!! Sorry 'bout that!

No offence intended - slightly stressed at the mo!

 

[KenHigg]

Hats off to namliam...

Now, did you try changing it to a variant? Did it work?

 

[Pharcyde]

oh yeah. I did, it did, everyones happy! Cheers all for your help! Think I'll be scouring the net for a VB reference guide or something so I dont have to keep annoying yous lot on here!!

 

[Mile-O]

Function FinalMark(Mark, Mark2, Mark3) As Integer[/QUOTE]

What data type are Mark, Mark2, and Mark3? Currently they are vaiants which, in this situation, are the worst implicit data type.

 

[Pharcyde]

Mile-O - yup, they are currently variants. So far its the only thing thats worked. Do you think this is going to cause p roblems in the future?

 

[Dan_T]

Can't you still assign the sero value but format the form field to show null?

Either condition the form control or place an iif statement in the field control.

Eg. fieldname.controlsource =iif(finalMark = 0,"",finalmark) crude but works.