Name field with variable?

[mgp]

Is it possible to have the name of the field change
dynamically?

For example, if I want my field name dynamically change
based on the date, how would I change the following
select stmt to have a date as the name instead
of "ThisWeek" and "4WeeksAgo";

SELECT Contacts.CompanyName, Sum(IIf(DatePart("ww",
[fdate])=DatePart("ww",Now()-28),[uprice]*[ftotal])) AS
4WeeksAgo, Sum(IIf(DatePart("ww",[fdate])=DatePart
("ww",Now()),[uprice]*[ftotal])) AS ThisWeek
FROM Contacts INNER JOIN Orders ON Contacts.ContactID =
Orders.ContactID
WHERE (((Orders.FDate)>Now()-35))
GROUP BY Contacts.CompanyName
HAVING (((Sum(IIf(DatePart("ww",[fdate])=DatePart("ww",Now
()-28),[uprice]*[ftotal])))>0)) OR (((Sum(IIf(DatePart
("ww",[fdate])=DatePart("ww",Now()),[uprice]*[ftotal])))

0))

ORDER BY Contacts.CompanyName, Sum(IIf(DatePart("ww",
[fdate])=DatePart("ww",Now()-28),[uprice]*[ftotal]))
DESC , Sum(IIf(DatePart("ww",[fdate])=DatePart("ww",Now
()),[uprice]*[ftotal])) DESC;

Thx

a2k

 

[John Vinson]

Is it possible to have the name of the field change
dynamically?

Only by constructing the entire SQL string in VBA code.

For example, if I want my field name dynamically change
based on the date, how would I change the following
select stmt to have a date as the name instead
of "ThisWeek" and "4WeeksAgo";

Storing data - such as a date - in a fieldname is EXTREMELY BAD
DESIGN, for exactly the reason you're seeing. This query is difficult
because your table is not properly normalized, I fear!

What's the actual structure of your table? Can it be changed so that
you have data *stored in fields*, rather than in fieldnames?