Urgent - Create column on query depending on another column text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fred - 22 May 2008 15:41 GMT

HI.. i need help on solving one big problem i can't get the solution..
I have a query that gets some values and also a month text from one table..
based on a form parameters.. What i want to do is to add/create a column on
the query and make that column depend on the text of another column..
Like..if the text in colum month is "january" the value should be on column
"num" should be 1, "february" - 2, December - 12....etc

the SQL code i have before creating that column i need is:

SELECT Table1.Ano, Table1.Parque, Table1.[Potência Produzida],
Table1.[Irradiância média], Table1.Temperatura, Table1.[Nº de horas de sol],
Table1.Month, Table1.Tarifa
FROM Table1
WHERE (((Table1.Ano)=[Forms]![Form3].[combo6]) AND
((Table1.Parque)=[Forms]![Form3].[combo4]));

Now i want to create column called "num" and i want to programme it
something like this code(in VBA only to exemplify):

if (query).(column_month).text = "January" then
(column_num).value = 1
else
if (query).(column_month).text = "February" then
(column_num).value = 2
else
if (query).(column_month).text = "March" then
(column_num).value = 3
else
..............................

Please help on how to do it..
Thanks!
Nuno

Reply to this Message

BruceM - 22 May 2008 16:20 GMT

If column_month is text you could use the Switch function. Help has more
information. Another option is that you may be able to use CDate (the day
and the year won't matter):
MonthNumber: Format(CDate([column_month] & " 1, 2008"),"m")

However, if you have a date field in the record, and if column_month matches
the month from that field, you shouldn't be storing the name of the month.
Instead you could use the Format function to get the name of the month:
MonthName: Format(DateField,"mmmm")

To get the number for the month:
MonthNumber: Format(DateField,"m")

In general, if you have a data field you can extract parts of that (day,
month, year, quarter, etc.) rather than storing extra information.

In any case, Month should not be used as a field name. It is the name of a
function, and Access could get confused, especially if you do not enclose
the field name in square brackets.

> HI.. i need help on solving one big problem i can't get the solution..
> I have a query that gets some values and also a month text from one
[quoted text clipped - 33 lines]
> Thanks!
> Nuno

Reply to this Message