Hi,

I'm writing a function to create some fairly comlplex SQL
dynamically. I have several optional arguments that will
make up the WHERE clause eg SQLfunction(optional arg1,
optinal arg2, optional arg3,....) - I am trying to find a
way to work out which ones are present and then include
them in the WHERE clause, which will be different
depending on the number of arguments.

I thought it might be possible to use a for each...
statement, similar to for each arguement(?) in
SQLfunction, but I'm uncertain as to the element and
group names to use.

I hope this is clear, and any suggestions at all would be
welcome,

Richard

If you declare the optional arguments as Variants, you can use IsMissing()
to determine if they were supplied.

Public Function MyFunc(Optional Arg1 As Variant)
If Not IsMissing(Arg1) Then
Debug.Print "Arg1 was supplied"
End If
End Sub

If they are declared as anything other than variants, they will be
initialized to their default value (zero for numbers, etc), so IsMissing()
will always return False.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Richard Black" <(E-Mail Removed)> wrote in message
news:1820301c44a1a$8b9290e0$(E-Mail Removed)...
>
> I'm writing a function to create some fairly comlplex SQL
> dynamically. I have several optional arguments that will
> make up the WHERE clause eg SQLfunction(optional arg1,
> optinal arg2, optional arg3,....) - I am trying to find a
> way to work out which ones are present and then include
> them in the WHERE clause, which will be different
> depending on the number of arguments.
>
> I thought it might be possible to use a for each...
> statement, similar to for each arguement(?) in
> SQLfunction, but I'm uncertain as to the element and
> group names to use.
>
> I hope this is clear, and any suggestions at all would be
> welcome,
>
> Richard

Also, you might consider using a parameter array instead of multiple Arg1, arg2,
arg3. Then you can loop through the array and grab the values.

That advice is valid only if the arg1, arg2, etc are all for the same
functionality. Here is an old snippet of code that I used as an example.

'------------- Code Starts --------------
Public Function fGetMaxNumber(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the Maximum Number of a group of values passed to it.
'Sample call: myMax = GetMaxNumber("-21","TEST","2","3",4,5,6,"7",0)
'returns 7
'Ignores values that cannot be treated as numbers.

Dim i As Integer, vMax As Variant,
Dim tfFound As Boolean, dblCompare As Double

vMax = -1E+308 'very large negative number
For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then
dblCompare = CDbl(Values(i))
If dblCompare > vMax Then
vMax = dblCompare
tfFound = True
End If
End If
Next

If tfFound Then
fGetMaxNumber = vMax
Else
fGetMaxNumber = Null
End If

End Function
Allen Browne wrote:
>
> If you declare the optional arguments as Variants, you can use IsMissing()
> to determine if they were supplied.
>
> Public Function MyFunc(Optional Arg1 As Variant)
> If Not IsMissing(Arg1) Then
> Debug.Print "Arg1 was supplied"
> End If
> End Sub
>
> If they are declared as anything other than variants, they will be
> initialized to their default value (zero for numbers, etc), so IsMissing()
> will always return False.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Richard Black" <(E-Mail Removed)> wrote in message
> news:1820301c44a1a$8b9290e0$(E-Mail Removed)...
> >
> > I'm writing a function to create some fairly comlplex SQL
> > dynamically. I have several optional arguments that will
> > make up the WHERE clause eg SQLfunction(optional arg1,
> > optinal arg2, optional arg3,....) - I am trying to find a
> > way to work out which ones are present and then include
> > them in the WHERE clause, which will be different
> > depending on the number of arguments.
> >
> > I thought it might be possible to use a for each...
> > statement, similar to for each arguement(?) in
> > SQLfunction, but I'm uncertain as to the element and
> > group names to use.
> >
> > I hope this is clear, and any suggestions at all would be
> > welcome,
> >
> > Richard

Richard,

There problem with using Optional Parameters is that they cannot be type
checked if using the IsMissing function because they must be declared as
Variants and then you must do type checking in your function.

In addition using a parameter array you will have similar problems.

Of course the two methods suggested have there place but if you know exactly
how many parameters you need and their datatypes I would suggest using the
Optional syntax but with a default value for instance:

SQLFunction(Optional ByVal arg1 As String = "", Optional ByVal arg2 As
Integer = 0, Optional ByVal arg3 As Date = #01/01/1901#, ...)

Now you allow the compiler to do type checking just make sure you pick
default values that do not make sense for the client.

to check to see if arg1 is present you would simply use the functions you
are familar with for instance

If Len(arg1) > 0 Then
WHERE = "[Field1]='" & arg1 & "'" '<-also note there is no need to do a
cast here since you can define the data type in the funciton signature. The
IsMissing funciton will not work when the datatype is anything other then
variant.

Dan


"Richard Black" <(E-Mail Removed)> wrote in message
news:1820301c44a1a$8b9290e0$(E-Mail Removed)...
> Hi,
>
> I'm writing a function to create some fairly comlplex SQL
> dynamically. I have several optional arguments that will
> make up the WHERE clause eg SQLfunction(optional arg1,
> optinal arg2, optional arg3,....) - I am trying to find a
> way to work out which ones are present and then include
> them in the WHERE clause, which will be different
> depending on the number of arguments.
>
> I thought it might be possible to use a for each...
> statement, similar to for each arguement(?) in
> SQLfunction, but I'm uncertain as to the element and
> group names to use.
>
> I hope this is clear, and any suggestions at all would be
> welcome,
>
> Richard