Dynamic Field Selection in a Query?

[Xx_TownDawg_xX]

Greetings.

I have an expression that works fine, but only for a specific field [ShipTPF] within the table. What I’d like to be able to pick which field to use depending on the situation, through the use of a Switch() function to change out the [ShipTPF] to [ShipFab] and [ShipMir].

Here is the expression that works fine:

=IIf([Forms]![frmMainMenu]![qFiscal]>0,DLookUp("[ShipTPF]","qryFiscalCalendar","[Fiscal_ID] = #" & [Forms]![frmMainMenu]![qFiscal] & "#"),0)

The logic used to change out the [ShipTPF] to [ShipFab] and [ShipMir] is based on the value of [Forms]![frmMainMenu]![qDepartment]. I can see where I could paste the expression literally into the respective values, and what I have so far is this:

Switch( [Forms]![frmMainMenu]![qDepartment] = “Fabrication”, value-1, [Forms]![frmMainMenu]![qDepartment] = “Mirrors”, value-2, [Forms]![frmMainMenu]![qDepartment] = “Partitions”, value-3)

Please note that: Value-1 relates to [ShipFab], Value-2 relates to [ShipMir], and Value-3 relates to [ShipTPF].

However, that sort of code would be sloppy of course, and maintenance of that code would be tricky, so I’m asking for your help to use some sort of string substitution maneuver. Although I have not seen a specific example, I’m assuming that code might look something like this?

Private Sub SomeModule
 
Dim deptstr As String
 
Switch( [Forms]![frmMainMenu]![qDepartment] = “Fabrication”, deptstr=[ShipFab], [Forms]![frmMainMenu]![qDepartment] = “Mirrors”, deptstr=[ShipMir], [Forms]![frmMainMenu]![qDepartment] = “Partitions”, deptstr=[ShipTPF])

IIf([Forms]![frmMainMenu]![qFiscal]>0,DLookUp("deptstr","qryFiscalCalendar","[Fiscal_ID] = #" & [Forms]![frmMainMenu]![qFiscal] & "#"),0)

End Sub

I wanted to get your suggestions before I attempted to code/paste all that. I also realize that my use of the DLookup() function is not preferred as well, so I would gladly accept any expertise anyone might care to provide. Thanks.

 

[allan57]

Try removing the double quotes from around the variable:

IIf([Forms]![frmMainMenu]![qFiscal]>0,DLookUp(deptstr,"qryFiscalCalendar","[Fiscal_ID] = #" & [Forms]![frmMainMenu]![qFiscal] & "#"),0)

 

[Xx_TownDawg_xX]

It can be done, and it was pretty easy actually. Here's what I did.

I used a macro on my OnClick() event to SetTempVar. The Variable's name was deptstr, and the expression was:

Switch([Forms]![frmMainMenu]![qDepartment]="Fabrication","[ShipFab]",[Forms]![frmMainMenu]![qDepartment]="Mirrors","[ShipMir]",[Forms]![frmMainMenu]![qDepartment]="Partitions","[ShipTPF]")

I checked to make sure it was working by placing this on a form to test it out:

=TempVars("deptstr")

Pleased with the result, I modified a DLookup using the call for the TempVars that I declared previously.

=IIf([Forms]![frmMainMenu]![qFiscal]>0,DLookUp(TempVars("deptstr"),"qryFiscalCalendar","[Fiscal_ID] = #" & [Forms]![frmMainMenu]![qFiscal] & "#"),0)

Works like a charm.