Refer to field using concatenated text 4

[simon1974]

I am concatenating several pieces of data together which will tell me which field (among several) from which to pull data. The question is, how do I refer in a formula to a field by using concatenated text. Can't figure it out, would appreciate any help.

 

[Hmadyson]

Fields(enter text here)

Ex. if field name is [A]&"1" then

Fields([A]&"!&quot

 

[shannonp]

Where are you wanting to put the formula? In a query, VB?...

 

[simon1974]

Shannon,

I need to stick it in a query. Thanks for any help you might offer.

 

[simon1974]

It appears that the "fields" function does not work in a query. Can anyone offer any further help? Thanks

 

[SgtJarrow]

If you merely want to concatenate fields in a query design window, ensure the tables that contain the fields are displayed, then in the Field line of any column, the format is something like:

[tablename].[fieldname] & [tablename].[fieldname] & "normal text can also be inserted like this" & [tablename].[fieldname]

If there are no duulpicate names from any of the tables, you can omit the [tablename]. portions from above...... Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[simon1974]

Thanks Robert. What I really need to do is this -- I have several fields that have a preset payment amount (named, for example, [Payment1], [Payment2], [Payment3]. I have another field that does a calculation to determine which period I am in and returns a 1,2, or 3. I want a final field to display the current payment, which is determined by the 1,2, or 3. So what I was hoping to do is use concatenation somehow to link "Payment" with the calculated 1,2, or 3 for the field reference so that I can get it to dynamically refer to either Payment1, Payment2, or Payment3 based on which period I am in. I looked at IIF, but I don't think that I can include multiple else statements, and there are 25 periods. Thanks again, let me know if there is anything I can do.

 

[SgtJarrow]

Have you looked at the select case function???? Although you can't put this into the query directly, you can create a module that can return the correct values you require and then caoncatenate them into your query....

I am kind of confused with your situation....you start by saying you have three periods, but finish by saying you have 25......

Can you send me the db with a brief description of what query is involved and your expected results.....Be happy to take a look-see and help you out.....if you can send it, I use Access97 and please zip to under 1MB or the exchange server will strip off the atatchment Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[JeremyNYC]

Simon,

Sooner or later someone will get sick of me saying this, but it looks a lot like you should do some work on your table scheme. There's a great article on my website (not written by me) that explains data normalization. If you follow the principles in that article you'll get around this problem quite easily.

Jeremy

PS: The article is in the developer's section, which is a little mucked up right now. At the home page, right click on the Developer's Section link and bring it up in a new window. You'll be able to get the article from there. =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[shannonp]

You have the solution...I think a nested iif statement will work:

Current Payment: Iif([period]=1,[Payment1],iif([period]=2,[Payment2],[Payment 3]))

If period is a calculated value within your query then just replace ([period]) with your calculation. One other thing to note is that the above statement assumes ONLY values 1, 2 or 3 will be inherited by [period].


ps. let me know how you get on