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
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