Reference A Query Column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good evening all,

I need to reference a column from a query in another column and I don't know
how, or even if it is possible.

Basically, say a field in the query from the table is birth date, I create
one column in my query to say 'Days Old: [Birth_Date] - Date()' so I get how
many days old the person is, but then in another column I want to say 'Old or
Young: Iif([Days Old]<10000, "Young","Old")'.

Any ideas would be greatly appreciated, as currently it says 'Enter Days
Old' when I try to run the query.

Yours sincerely,

William Foster
 
Good evening all,

I need to reference a column from a query in another column and I don't know
how, or even if it is possible.

Basically, say a field in the query from the table is birth date, I create
one column in my query to say 'Days Old: [Birth_Date] - Date()' so I get how
many days old the person is, but then in another column I want to say 'Old or
Young: Iif([Days Old]<10000, "Young","Old")'.

Any ideas would be greatly appreciated, as currently it says 'Enter Days
Old' when I try to run the query.

Yours sincerely,

William Foster
Click to expand...

You can't usually reuse a calculated field in a further calculation.
Just repeat the expression:

Iif([Birth Date] - Date() < 10000, "Young", "Old")

John W. Vinson[MVP]
 
John,

Thanks for your feedback, however, I have one question. In your reply you
used the word usually, does this mean there are times when you can do it? If
so can you let me know, I am running a number of database queries and the use
of this facility in any one of a number of circumstances would be helpful.

Yours sincerely,

William Foster
 
William said:
I need to reference a column from a query in another column and I don't know
how, or even if it is possible.
Click to expand...

It's possible by using an alias as a "variable" in your query. Switch over
to sql view so it's easier to compare the algebraic terms of each calculated
value. View -> SQL View. Notice on the second line of the query below, I
used "DaysOld" as if it were a variable in the calculation of Age:

SELECT (Date() - Birth_Date) AS DaysOld,
IIF(DaysOld < 10000, "Young","Old") AS Age
FROM Employees;
 
Back
Top