VBA Function Question 2

[Ray1127]

Is it possible in Microsoft Access 2000 To have a public Function with 3 optional parameters and Any combination of the 3 parameters could be passed?

For Example:

cc = current_count(,mtgdate,)

Where Current_Count is the name of the function. The first parameter is for the location, second parameter is for the date of the meeting and the 3rd parameter is for confirmed guests. In my sample above I'm counting all guests for a particular date but there could be any combination of those 3.

Any help would be appreciated.

 

[Remou]

You can use Optional:

Test ,,"def"


Function Test(Optional p1, Optional p2 = 0, Optional p3 = "abc")
If IsMissing(p1) Then
    MsgBox p2 & p3
Else
    MsgBox p1
End If

End Function

http://lessthandot.com

 

[Ray1127]

Sorry I guess I should have included the function. I already have the parameters as Optional

Public Function Current_Count(Optional ByRef Loc As Long, Optional mtdate As Date, Optional src As String) As Long
Dim strsql As String, rs As New ADODB.Recordset, lngguests As Long, lngmbrs As Long
strsql = "SELECT Count(tblData.MBR_Key_Id) AS guests " & _
"FROM tblData INNER JOIN tbl_Guests ON tblData.MBR_Key_Id = tbl_Guests.Mbr "
If IsNumeric(Loc) Then
strsql = strsql & "Where Locationid = " & Loc
End If
If IsDate(mtdate) Then
strsql = strsql & " AND mtgdate = #" & mtdate & "# "
End If
If Len(src) > 0 Then
strsql = strsql & " AND confirmed = True "
End If
rs.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If rs.EOF Then lngguests = 0 Else lngguests = rs!Guests
rs.Close
strsql = "SELECT Count(tblData.MBR_Key_Id) AS mbrs " & _
"FROM tblData "
If IsNumeric(Loc) Then
strsql = strsql & "Where Locationid = " & Loc
End If
If IsDate(mtdate) Then
strsql = strsql & " AND mtgdate = #" & mtdate & "# "
End If


If Len(src) > 0 Then
strsql = strsql & " AND confirmed = True "
End If
rs.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If rs.EOF Then lngmbrs = 0 Else lngmbrs = rs!mbrs
rs.Close
Current_Count = lngguests + lngmbrs
Set rs = Nothing


End Function

 

[Remou]

You have assigned data types to the optional arguments, so loc will always be numeric and equal to zero, even if no argument is passed, similarly date will always be a date. Is this the problem?

http://lessthandot.com

 

[MajP]

One way is to dimension the parameters as variant and then use the ismissing function
I believe this is deprecated. You are better defining a default value and checking for that.

Optional ByRef Loc As Long = 0,
if Loc <> 0 then...

 

[Remou]

@MajP "... the ismissing function. I believe this is deprecated ..."

Sez who? It is very useful for dates for example, where nearly any default value could be valid.

http://office.microsoft.com/en-us/access-help/ismissing-function-HA001228866.aspx

http://lessthandot.com

 

[MajP]

@Remou,
Microsoft says so. I did not say that it was not useful, I just said is a deprecated function that will likely not be supported in any future vb versions. .net has already done away with is.

http://msdn.microsoft.com/en-us/library/6ck7y3ye(VS.71).aspx

 

[Remou]

@MajP The link I provided is for Access 2007, the help files for Access 2010 also include the IsMissing function with nothing about deprecation. The VB->VB.Net path is not quite the same as the VBA path, AFAIK.

http://lessthandot.com

 

[MajP]

I never got back to this, but I fully discourage anyone from using the ismissing function and that is why I believe Microsoft has shown at least some interest in deprecating it. The problem is the ismissing function is going to get you in trouble. Even someone as skilled as Remou demonstrated the problem

It is very useful for dates for example, where nearly any default value could be valid.

In contrast it has no utility here and will get you in trouble. Ismissing ONLY works with VARIANTS. It is only the variant that carries the missing bit. It is confusing in what it will provide, and here is where you will get in trouble.

Public Sub badIsMissing(Optional dtmDate As Date)
  MsgBox IsMissing(dtmDate)

  If Not IsMissing(dtmDate) Then
    'your code here
  End If
End Sub

Public Sub testBadIsMissing()
  Call badIsMissing
End Sub

What does the message box display? True or false? I did not pass any parameter so you would think True. But as Remou pointed out since I declared it as a date it defaults to 0 or (12:00AM 31Dec 1899). isMissing returns FALSE. That is obviously not what was intended, and likely to cause really bad things if used in a query.

So to use it correctly the parameter needs to be declared as a variant, and now the second problem. Why waste your time checking if it is missing? You need to check if it is a date. (Unless if it is missing you do one thing, and if it is a non date something else)

Public Sub badIsMissing2(Optional dtmDate As Variant)
  Dim x As Date
  If Not IsMissing(dtmDate) Then
    x = dtmDate
  End If
End Sub
Public Sub testBadIsMissing2()
  Call badIsMissing2("XYX")
End Sub

It sees that it is not missing so that means I have passed in a valid date. Wrong it crashes

So bottom line ismissing has really no utility and eventually it will bite you. I strongly believe in the following for any function used in a query.

1) If the function is going to be used in a query ALWAYS declare the parameters as variants, contrary to what you learned in coding. Why? Because eventually you are going to pass in a null value, and it will crash your query. Nothing is more frustrating then 10,000 records with error messages. Either you will use the function with a new record, or you will forget to assign a value. Even if in your table you have default values and required values, some day you will do an outer join and return null values. I am not suggesting this for all functions only those used by queries. For other programs you should declare the most specific data type.

2) Since you should always declare the parameters as variants for functions called by queries, you should then ALWAYS assign a default for all optional parameters.

3) Then in the function if it is supposed to be a date check that, if it is supposed to be numeric check that, if it is supposed to be a string check not null.

The following construct always works and is never confusing.

Public Sub goodConstruct(Optional dtmValue As Variant = Null, Optional strValue As Variant = Null, Optional numValue As Variant = Null)
  If IsDate(dtmValue) Then
     
  End If
  If IsNumeric(numValue) Then
  
  End If
  If Not IsNull(strValue) Then
  
  End If
End Sub

So the above construct never bombs out when your query passes in a null which it will eventually do. If you pass in a real date it handles that, it handles no value passed in, and it handles a non date correctly. Same for numeric and string.
And if you want to provide other defaults then null you can handle that as well

optional numValue as variant = 0

if numValue <> 0

Bottom line. If using optional paramters ALWAYS declare as variants and always provide default values. Avoid ismissing.

 

[Ray1127]

Thank you both. What I did was by setting the value as MajP suggested I then check for that value. The value selected would never be passed if that parameter was used. For example there are 3 parameters. LocationID, mtgdate and src LocationID is numeric, mtdate is a date and src is a string. If LocationID is passed it will be a number from 0 to 5 so If LocationID is > 5 the parameter is not passed. Since More locations could be added in the future I've set LocationID = 255. The max value it could ever be is < 20. The Date field was easy. The date will always be a future date so If that field is null it defaults to 1/1/1900. Finally the src I used a string as there are 2 types of places this function could be called from either a form or a report. If it is called from a form that field is blank as it is irrelevant. If it is called from a report the value is report.

Although just at this moment I realized this should actually be a boolean. Can't believe I didn't realize that sooner.

This works perfectly.

Thank you both for your help.

 

[Remou]

You might like to consider -1 for location, it is just conceivable, I think, with deleted records and errors, that location will hit 255, but -1 is less likely, though not impossible.

http://lessthandot.com

 

[Remou]

I have just thought - if you set location to double, you can use -0.1, which is not going to happen in an autokey.

http://lessthandot.com

 

[Remou]

Aside @MajP "It is very useful for dates for example, where nearly any default value could be valid." I did not mean declare the type as date, because once you do, you have a value, I said that earlier, and you have further demonstrated it. What would you suggest as a default value for a date? Also consider objects, what would you suggest for objects?

http://lessthandot.com

 

[MajP]

@Remou,
Not sure what your question is, so I assume you missed my point. My premise is simply that there is no good reason to use ismissing. I showed that it can be constructed much better, and more importantly the use of ismissing can very easily give you results oppositte from what you expect. Even an experienced user may forget that this has meaning only with variant declared parameters. It has nothing to do with dates and definetly nothing to do with objects.

So my point is that when using optional parameters always state default values, unless variable type default values are good enough.

1) so if using variant and you do not have a natural default you want to use, then the default should be null

(opional dtmDate as variant = null)
your check is then
if isdate(dtmDate)

If you have some better natural default then use that.

2) If using a parameter declared as an actual date then isMissing has no meaning. So I am not sure of your question. You are going to get a default anyways (12:00 Am 30Dec1899) or you are going to have to declare something else.

so either (Optional dtmDate as date)
your check (assuming 12:00 am 30 dec 1899) is not valid
if dtmDate <> 0
If 12:00 Am 30Dec1899 is a valid choice in your database, you have to define a value outside any possible range.
I can not suggest anything, that is up to the user, but this has absolutely nothing to do with using the function isMissing.

3) If using an object, there is absolutely no meaning with the function ismissing. The construct should be

(optional obj as object = nothing)
and the check
if not obj is nothing then


Maybe we are talking the same thing, but I do not understand your point.

 

[Ray1127]

Good Suggestion Remou no matter how unlikely it is to hit 255 it would be possible however it could never be less then zero. I'll do that.

Thanks again