Using text formulae as field names in a query (1 Viewer)

[nharrison]

I'm attempting to use a text formula as a field name in a query.

The scenario is this:
I have a field named Design Phase, and also three named GMP Owner Bid, DD Owner Bid, and SD Owner Bid. Design Phase is a lookup field, with three possible values (GMP, DD, and SD). See where I'm going with this?

In the query, if a job is in the GMP design phase, I want the query to return the value GMP Owner Bid under the alias Bid Date.

I've tried these SQL statements, but none are working

Bid Date: [Design Phase] & " Owner Bid"
Bid Date: [[Design Phase] & " Owner Bid"]

A push in the right direction would be greatly appreciated.

 

[MSAccessRookie]

I'm attempting to use a text formula as a field name in a query.

The scenario is this:
I have a field named Design Phase, and also three named GMP Owner Bid, DD Owner Bid, and SD Owner Bid. Design Phase is a lookup field, with three possible values (GMP, DD, and SD). See where I'm going with this?

In the query, if a job is in the GMP design phase, I want the query to return the value GMP Owner Bid under the alias Bid Date.

I've tried these SQL statements, but none are working

Bid Date: [Design Phase] & " Owner Bid"
Bid Date: [[Design Phase] & " Owner Bid"]

A push in the right direction would be greatly appreciated.

A compound IIf() Statement should give you what you are looking for. Something like:

[Bid Date]:IIf([Design Phase]="GMP", [GMP Owner Bid], IIf([Design Phase]="DD", [DD Owner Bid], [SD Owner Bid]))]
​

NOTE: It is a good Idea to avoid Spaces in Column/Table Names. ([Bid Date becomes BidDate, [Design Phase] becomes DesignPhase, etc).

 

[nharrison]

Ahh, really don't know why I didn't think of that before. I was just so set on using the field names as a text formula. Thanks a bunch, that should work fine.