Function Reference an Arguement (1 Viewer)

[Thicko]

Hi All,

Really thought this was going to be straightforward but:

I have a function that I want to return the number of a consumable to use. The function is run through a query

There's 30 consumables C1 to C30

If the DLookup is true I want to return the corresponding consumable

e.g.

e.g. ?ConsumableToUse("Pink Needle",1,6,3,1,1,,,,,,,,,,,,,,,,,,,,,,,,,,,1)

For a "Pink Needle" which is C1 I need the output to be 6, the below function returns C1 rather than the argument C1 = 6

Public Function ConsumableToUse(DrugNameVialCurrent As String, ConsumableNumberCurrent As Integer, Optional ByVal C1 As Double, Optional C2 As Double, Optional C3 As Double, Optional C4 As Double, Optional C5 As Double, Optional C6 As Double, Optional C7 As Double, Optional C8 As Double, Optional C9 As Double, Optional C10 As Double, Optional C11 As Double, Optional C12 As Double, Optional C13 As Double, Optional C14 As Double, Optional C15 As Double, Optional C16 As Double, Optional C17 As Double, Optional C18 As Double, Optional C19 As Double, Optional C20 As Double, Optional C21 As Double, Optional C22 As Double, Optional C23 As Double, Optional C24 As Double, Optional C25 As Double, Optional C26 As Double, Optional C27 As Double, Optional C28 As Double, Optional C29 As Double, Optional C30 As Double, Optional ExtraVariable As Integer) As Double

If DLookup("DrugNameVial", "tblDrugWeightCost", "[ConsumableNumber]=" & ConsumableNumberCurrent) = DrugNameVialCurrent Then

VariableToTake = ("C" & Trim(Str(ConsumableNumberCurrent)))
ConsumableToUse = VariableToTake

End If

End Function

Any advise greatly appreciated

 

[Gasman]

Well that is what you are passing into the function. It is working as coded.
Why or why do you have all those optionals?
Plus you do not declare variable to take?
Walk through the code line by line with F8 and see what the code does and the values used.

 

[Pat Hartman]

I don't see how you get from the arguments to determining that you need "6". There is no array and no loop. You are using a dLookup() and using the number you say the function is determining from all the optional fields.

 

[Gasman]

I think I see what you are trying to do?
Try consumabletouse = eval(variabletotake)

 

[oleronesoftwares]

VariableToTake = ("C" & Trim(Str(ConsumableNumberCurrent)))

this is the reason it is returning C1 , YOU ARE CONCATENATING "C" & A NUMBER

 

[jdraw]

Please tell us in plain English what is the logic of your function.

 

[Thicko]

Previously tried the Eval option but no joy.

There's two tables, one table tblDrugListWeight contains the name of consumables up to 30 and each has a corresponding number from 1 to 30 (ConsumableNumber)

A second table has a Drug & a dose band and against each band there is the number of each of the consumables in a column designated Consumabe1, Consumable2, etc..

What I'm attempting to do is take a query that will list all the consumables associated with the band and the number required.

It was previosuly done in a way where the name of the column matched the name of the consumable without the spaces allowing for DLookup to be done for each consumable. The issue is that consumables change over time and different labs use different consumables so we need it to be flexible with the name of the consumable. I'm trying to do it in a way that least disturbes all the other code and reports that run alongside.

I used optional fields as not all sites will use 30 consumables and all bands will have some null fields where the band doesn't require any of that type of consumable.

I did toy with loops but I don't want to have to loop through upto 30 consumables for each consumable on the list. That option doesn't feel efficient.

Thanks for all the ideas

 

[Pat Hartman]

A second table has a Drug & a dose band and against each band there is the number of each of the consumables in a column designated Consumabe1, Consumable2, etc..

That is the crux of the problem. Normalize the tables and the logic becomes simple. Instead of having 30 columns, you need a separate table. There will be 0-30 entries depending on how many consumables are needed. AND, if it turns out you ever need more than 30, unlike a spreadsheet, no changes need to be made. You just add a new row and magically, it is seen everywhere.

I did toy with loops but I don't want to have to loop through up to 30 consumables for each consumable on the list. That option doesn't feel efficient.

Yep, you're right. it is inefficient. That is why we emphasize normalizing the tables. Spreadsheets, which is what you have, are wide and flat and require structure and formula changes to expand. Relational tables are tall and narrow and adding an item requires NO code, and NO query or other object changes. You just add a new row and everything just works.