Variable Data Type in Custom Function?

[herbivorous]

I am trying to write a function that, depending on the arguments passed to it, returns different types of data about a financial transaction -- the date, the amount, or a string containing the date, amount, and a transaction code. The function is running well enough with GIFTINFO, the function name, defined as Variant and then set to the value of the appropriate (and appropriatedly DIMmed to start the function call) string, date, or amount of money. But while the date info seems to get passed in the date data type, displaying as short date without any extra formating and allowing date comparisons, the currency format does not display correctly -- I get no $ and no ,

I am using a select case to evaluate the formating arguments and assign the proper value to GIFTINFO. Can I just stick a DIM GIFTINFO into each one of these to force the proper data type? Or will it choke since the first line of the module already DIMs GIFTINFO(blah as long, blah as string, optional blah as string) AS VARIANT?

 

[Steve101]

Why not define different functions, each defined with the appropriate data types: for example:
[tt]
Function YourFunction1 (p1, p2, p3) As Date
...
...
YourFunction1 = YourFunctionGeneric (p1, p2, p3)
End Function

Function YourFunction2 (p1, p2, p3) As Currency
...
...
YourFunction2 = YourFunctionGeneric (p1, p2, p3)
End Function

Function YourFunctionGeneric (p1, p2, p3)
...
...
YourFunctionGeneric = whatever
[tt]
This way, you have explicit control of the data type returned by the function(s). You obviously name the functions appropriately as well, to reflect what they are returning.

Hope this helps,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[herbivorous]

If there is no other option forthcoming, I can certainly do it that way. I had consulted with my other primary user and decided that having one function with a variety of parameters was actually easier to remember and use. So I remain hopeful of another solution. I'm always trying to do something screwy that Access isn't quite built for. . .

 

[Steve101]

OK. Dont necessarily agree with your reasons; good function naming conventions could certainly mean that you could make things easy to remember (eg. having a single name prefix plus a name suffix, instead of, say, the first parameter).

Anyway, here's another option, which you might be able to adapt for your purposes. Its a pretty powerful technique anyway:

Define your own custom compound data type; then, make the type of your function this type. The function will then return ALL of the components; pick of choose the ones you need; For example:
[tt]
'-----------------------------
'define employeetype in module
'-----------------------------
Type EmployeeType
DOB As Date
Surname As String
Salary As Currency
End Type

'------------------------------------------
'define function, determining return values
'------------------------------------------
Function getEmployee() As EmployeeType
getEmployee.DOB = #1/1/2001#
getEmployee.Surname = "Smith"
getEmployee.Salary = 10000
End Function

'----------------------------
'Call function from somewhere
'----------------------------
debug.? getEmployee.DOB
[/tt]

I havnt bothered with passing arguments into the function, but presumably you'd do this. Nothing different here. Just adapt as required. Then use as required. A couple of other points.

(a) Its better to assign the results of the function to a defined variable of the same type. This way the function only gets invoked once, but the assigned variable can be interrogated / used as many times as you like.

(b) Downside of this approach, that I've found, is that the function cannot be called from within SQL. I think it gets its proverbials in a knot as it get confused with the dot notation.

Adapt as required, and let me know if this does it for you,

Regards,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)