How to build a datetime column ....

[kkrishna]

Hi All,

I have an Access 2007 database with two columns - RefYear and RefMonth - with datatype as Integer. I want to add a 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'.

Will someone please advise me how to do it?

[pbaldy]

You can use the DateSerial() function with your two fields plus a day to build a date. In your query:

WHERE DateSerial(...) Between ...

[kkrishna]

Thanks for the reply.

In fact I found 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.

[pbaldy]

The downside to a field is that it is a calculated field, which you generally shouldn't save. In this case, you need to account for every new or updated record to make sure that field stays current (like if a user changes the month value for whatever reason).