Return Value from Field - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Return Value from Field

Martin Hermans

6 years ago

Hello Richard,

I've been working with Access many years now end I can't solve a problem, which I know that it is possible, but how?
Let say I have a query with Rows like "CustomersID". My Columns are maybe Q1, Q2, Q3, Q4. The data could be a financial value, whatever.
My criteria could be: Like [Give customerID] and THEN I want my query to ask me "Which Q you want to see?" So depending on the answer of the second question, I should see the result of the right customer and the value of the chosen Q.
How do I do that?
I hope my question is clear enough for you, and I hope you have the answer.
Greetings
Martin Hermans

	Alex Hedley @Reply 6 years ago
Are you currently promoting for input like: Parameter Query

Richard Rost @Reply

6 years ago

Hi Martin. In Access we refer to rows as "records" and columns as "fields." Yeah, I know... it's the teacher in me. LOL

This is not something you could easily do with a simple query. However, you could do it with a DLOOKUP function on a form. Create two text boxes: one for the CustomerID, and another for the field (I assume that's quarters) you want to look up. Then with a DLOOKUP you could combine both fields to return the value you want.

	Martin Hermans OP @Reply 6 years ago
Parameter Query doesn't work the way I want it. I want to see a specific field depending on a date. Let say I have fields of a partnumber; quantity need; week1; week2; week3; week4; etc. Now if today is Week1, I want to see the quantity need of field week1. Next week I want to see the quantity need of week2 etc.

Richard Rost @Reply

6 years ago

Access really wasn't designed to work that way, but I suppose you could do it with some nested IIF functions in a query. For example, let's say I want either the Address, City, or State field from a customer. I could put this in my query:

MyField: IIf([What Field]="1",[Address],IIf([What Field]="2",[City],[ZIP]))

The user will be prompted [What Field]. If they reply with "1" then they get the Address. If "2" then the City. Anything else will return the ZIP.

You could do the same thing with your fields. Hope this helps.

	Martin Hermans OP @Reply 6 years ago
Richard, I know Access isn't designed do do it that way, but some Excel downloads are built real dumb and provides me with this topic! I will try your suggestion... THX

	Richard Rost @Reply 6 years ago
I know what you mean. Dealing with other peoples' bad data setups is my biggest annoyance. Sometimes it's easier to import the data into Access, "scrub" it so it's in a proper format, then you can get the reporting you want from it.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View