How to combine year and month into one field

  • Thread starter Thread starter Cam
  • Start date Start date
C

Cam

Hello,

I have a year (4 numeric digits) and month (1 numeric digit [1-9] and 2
numeric digits [10-12]) field that I would like to create a field to combine
them. Keep in mind when sorting it sort by the year and month.

Sample
Yr Mth
2008 1
2008 2
2008 9
2008 10
2008 12

Would like:
Yr Mth Field
2008 1 2008, 01
2008 2 2008, 02
2008 9 2008, 09
2008 10 2008, 10
2008 12 2008, 12

Thanks
 
strYearAndMonth = Cstr(yr) + Iff(len(Cstr(Mth))=1,"0"& Cstr(Mth),
Cstr(Mth))

Regards

Kevin
 
If you are going to save the date in a field and prefer a mm/yyyy format that
you can sort, you could do the following:

In the table where you want to save the combined date, set the data type of
the field to Date/Time and set the format to mm/yyyy. Then in the update
query use

UPDATE Table1 SET Table1.CombinedDate = [mth] & "/" & [yr];

Now if you sort that field it will sort correctly by month and year.
 
Simplest expression I can think of would be

[Yr] & Format([Mth],"\, 00")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top