Add computed column to table DateTime datatype

[krishnak]

Hi All,

I have an Access 2007 database with two columns - RefYear and RefMonth - with datatype as Integer. I want to add a computed column to this table of datatype DateTime, in the format 'RefYear/RefMonth'.

That is, if the RefMonth is 6 (not 06) and RefYear is 2010, I want the value in the new column to be '2010/6' and recognized as DateTime format.

I am finding it difficult to write queries with BETWEEN .. AND with integer values for RefYear and RefMonth.

Date is not important, but if the format requires it, the column value can be modified to '2010/6/1'.

If a computed column cannot be added, I can make a query with the same data.

Will someone please advise me how to do it?

[krishnak]

I found the solution by adding a DateTime data type column to the existing table. I included a new column manually (I could have done it by the ALTER TABLE .... statement also) name DateCol. Then I ran the following query:

UPDATE TblPerformanceData
SET DatCol = RefMonth & '/1' & RefYear

This has populated the new column with the dates.
Then I added the part for the existing query:

WHERE pd.DateCol BETWEEN (#12/1/2009#) AND (##11/1/2010#)

The dates are now hard-coded; but I plan to set up a dialog box for them.

[hansup]

You shouldn't need to add a column to store the derived date value; simply derive it as needed with a query:

SELECT RefYear, RefMonth, CDate(RefYear & "/" & RefMonth & "/1") AS DateCol
FROM YourTable;

Then you can use that query as the data source in your other query which includes your WHERE clause:

WHERE YourNewQuery.DateCol BETWEEN #2009/12/01# And #2010/11/01#

[krishnak]

Thanks for the suggestion.