Table Field Value Contains Field Name To Use (1 Viewer)

[LGDGlen]

this may not be the best way to do what i want to achieve but i'll detail it out here and we'll see where we go.

I have a process that generates a CSV from a query but there is a need to override the default behaviour of a calculation within the query on occasion. the table has 3 fields which can be used in the calculation so i have a field that specifies which to use so:

Field1 - first possible data to use in calculation
Field2 - second possible data to use in calculation
Field3 - third possible data in calculation
Field4 - stores either Field1, Field2 or Field3 names
Field5 - calculates based on the following:
if Field4 is not blank - get the name of which Field to use in the calculation
if Field4 is blank - if field 3 has a value use it, else if field 2 has a value use that, otherwise use field 1

Fields 1-4 are on the table, Field5 is part of the query that generates the CSV

So
if the value of Field4 = "Field2" then Field5 will use the value stored in Field2 in the calculation
if the value of Field4 = (Null) and Field3 = (Null) and Field2 = (Null) then Field5 will use the value stored in Field1 in the calculation

now i could i know use a very large iif statement but i was hoping that i could specify which field name i need to get the data from in Field4. If it is not possible then i'll just the long way round, was just trying to be sneaky and use a field to store the fieldname of a field in the same table i wanted to use but again if thats just not possible then thats ok

hope that all makes sense

rgds

G

 

[jdraw]

Would it be less cumbersome and error prone to use a form, and some options to identify the process to be executed on this run?
That is have the calculation set up behind the scenes and have the options clearly marked.
Your calculation options may be a simple as Weekly/Monthly/Yearly or similar.
You know your requirements better than readers.

 

[LGDGlen]

@jdraw thank you for your response and yes i do know my requirements better than the readers so i will try to elaborate a bit more

the users have a form where there is a drop down selection that they can use, if they use it that can specify the override to the calculation by fixing which Value to use. The values are for Cases Sold and are

Original Cases Sent To Client
Cases Kept By Client
If Client Had To Repackage What Cases Did They End Up With

The values are a number of cases and are based on what happened with a delivery. This impacts the invoice that will be generated, sometimes we invoice and then credit back the difference between Original and Kept cases.

Some clients have an agreement that we will send a final invoice for the amount they kept.

Sometimes the number of cases documented as being sent ends up being less than what arrived so when Kept cases are more that Original Cases we use kept cases.

What this process is trying to tackle is the "Final Invoice" situation where the user will set a specific field to use, be it Kept, Sold or Original and that overrides the normal calcualtion process.

The form uses a drop down to set a field to state which other field to use and this drop down is from a table that has
Original -> Field1
Kept -> Field2
Sold ->Field3

So when the query comes to run i want to use the value of this to specify which field to use in the calculation by using the value to reference a field name

i hope that makes sense

 

[ebs17]

hope that all makes sense

Such if-then cascades are familiar from Excel formulas. This is irritating in a database query. This can often be resolved very well with well-planned tables.

Instead of talking about calculation, you should show what exactly is calculated and what is used for calculation, possibly also the goal of the calculation.

 

[MajP]

That is a horrible design, but this works. Doing this in pure sql with iif
would be mind numbing, but doable.

Public Function GetValueToUse(Field1 As Variant, Field2 As Variant, Field3 As Variant, Field4 As Variant) As Variant
    Dim rtn As Variant
    Select Case Field4 & ""
        Case ""
            If Not IsNull(Field3) Then
                rtn = Field3
            ElseIf Not IsNull(Field2) Then
                rtn = Field2
            ElseIf Not IsNull(Field1) Then
                rtn = Field1
            End If
        Case "FieldOne"
            rtn = Field1
        Case "FieldTwo"
            rtn = Field2
        Case "FieldThree"
            rtn = "FieldThree"
    End Select
    GetValueToUse = rtn
End Function

SELECT tblOne.FieldOne, tblOne.FieldTwo, tblOne.FieldThree, tblOne.FieldToUse, GetValueToUse([fieldone],[FieldTwo],[FieldThree],[FieldToUse]) AS ValueToUse
FROM tblOne;

Query1 Query1

FieldOne	FieldTwo	FieldThree	FieldToUse	ValueToUse
Dog				Dog
Dog	Cat			Cat
Dog	Cat	Bird		Bird
Fish	Frog	Monkey	FieldOne	Fish
Airplane	Ship	Tractor	FieldTwo	Ship
Apple	Orange	Bananna	FieldThree	FieldThree

If field 4 is not blank it returns the value from the Named field in field 4.
If field 4 is blank it takes field 3 value, if field three is blank it takes field two, if fieldtwo is blan it takes fieldone.

 

[CJ_London]

If your selection was a number ranging 1-3 for the options you could use the choose function

Chosen: choose([fieldchoice],[original],[kept],[sold])

the description of the calculation is unclear - where does sent and arrived come into it? Which is the sold value?

as ebs17 suggests provide some example data and the result of the calculation- are all 3 fields populated? Can they be null or 0?