Function problems

[Sooty10101]

I am trying to get a function to work
The function has two arguments which take a meter serial number and a date and takes the form of an IF , Else If , Else structure

I want to check for a null value in the first passed argument and if null to pass the statement
" No Meters Fitted"
I then want to if there is a value in the meter serial number field check the date against todays date and return a value if the date is within 20 days of todays date and a different value if its over 20 later than todays date.
This is my function but when i run in a query i get ERROR. I did try If IsNull instead of Len but that didn't work either.
Can anyone out there please help me

Public Function mySiteStatusUpdate(ByVal prmElecMsn As String, ByVal prmHoDate As Date) As String


If Len(Nz(prmElecMsn, 0)) < 4 Then
mySiteStatusUpdate = " Not Metered"
ElseIf Not IsNull(prmElecMsn) And prmHoDate > DateAdd("d", 20, Date) Then
mySiteStatusUpdate = "Metered and pending"
Else
mySiteStatusUpdate = " Metered and Urgent"



End If

 

[goodfellow]

Only thing that will cause error in your code is undeclared Nz function in line:

If Len(Nz(prmElecMsn, 0)) < 4 Then

Try to replace it with this:

If Len(prmElecMsn) < 4 Then

For me works like charm

 

[Sooty10101]

Hello
Thanks for your reply

I tried loads of stuff . I started off with

If IsNull( prmElecMsn) Then etc etc
" statment"

I tried using optional in the augments but i just can't get the statement " No meters fitted"

The other two statements do work ie where the date is 20 days later than todays date and where is is within the next 20 days

 

[goodfellow]

But now everything works, right? I'm not sure if I follow you

 

[Sooty10101]

Hello

No . I tried If Len(prmElecMsn) < 4 . Because it didn't work i added the NZ and that didn't work either. Watching the code in the immediate window if i enter a msn with less than 4 characters or more than 4 characters it still skips over the first statement and goes to one of the other statements depending on the date field
I tried this because If(IsNull wasn't working which is what i would have thought of as the obvious value to check for and act upon

IIF(IsNull certainly works if you just write the expression as a calculated field in the query grid but not in the visual basic window
Thanks for your help anyway

 

[Sooty10101]

Hello


You know what i'm mistaken, it does work. Could have swore i tried that and it wasn't working , now i tried it again and it worked. Can you tell me why If(IsNull(elecmsn) does not work and also if forces me to make the arguments ByVal . When i've tried out exercises from books i've never had to use Byval. Am i using the wrong datatypes for the auguments?

 

[Sooty10101]

Further update
It actually passes "Not Metered" even if the characters are more than 4. That must have been why i started experimenting
So i'm back at square one how do i get it to only pass " Not Metered" when there is a null value argument passed...

 

[goodfellow]

I modified your code a bit. Now it works like it suppose to.

Public Function mySiteStatusUpdate(prmElecMsn As String, prmHoDate As Date) As String
If prmElecMsn = "" Then
mySiteStatusUpdate = " Not Metered"
ElseIf prmHoDate > DateAdd("d", 20, Date) Then
mySiteStatusUpdate = "Metered and pending"
Else
mySiteStatusUpdate = " Metered and Urgent"
End If
End Function

IsNull will not work because you are passing this variable as String and empty string is automatically initialized with "". So you can see that "" is not equal to null.
You don't need to pass arguments by value in this case too. And you don't have to check if prmElecMsn = "" in ElseIf statement like it was in your previous code, because if it will be empty (in the first if statement) then this statement will not be considered.
Cheers.

 

[Sooty10101]

Hello

Yes it works now. That's a relief.. Seems so obvious when someone points it out. Thanks cos that was really frustrating

 

[Sooty10101]

What it doesn't let me do is get rid of the byVal. When i get rid of that i get the error message
" by ref argument type mismatch " Also when i run the function as a query with the passed arguments as fields i'm still getting error messages in the fields where there is not a msn - the fields are of the datatypes text and date - its not saying "no meters fitted" as intended. However, When i run in the immediate window it does say this. Sorry to be nuisance but any ideas?