Convert months and years into one end of month date

[Apple08]

Hi All

I imported an Excel table into Access with two separate year and month fields, but I need to convert them into one field with end of month date. For example: 2015, Aug need to convert into Access as 31-Aug-15.

I am unable to change the Excel content before import as that is a report provided by other source.

I would appreciate it very much if someone could help to advise is there any way to do it? Many thanks.

 

[Joe4]

Here are some calculations that will return the date that you want (not sure if you are using an American or European version of Access, at least one of these should work).

DateCalc: DateAdd("m",1,DateValue([MonthField] & " 1, " & [YearField]))-1

or

DateCalc2: DateAdd("m",1,DateValue("1-" & [MonthField] & " -" & [YearField]))-1

Being a date field, you can then format it any way you like, i.e. "dd-mmm-yy"

 

[Apple08]

Excellent Joe! It works perfectly! Many thanks.

However some of my year data are blank like below:

2013
(blank)
(blank)
2014
(blank)
(blank)
(blank)
2015
(blank)
(blank)

Is there anyway that the blank year can automatically copy from the year field above? Many thanks.

 

[Joe4]

Is there anyway that the blank year can automatically copy from the year field above?

I guess that depends on how you define "the year field above".
Remember, Access is not like Excel. The order of records in a table really has no meaning.
Someone one described it well, when they said to think of an Access table as "a bag of marbles", where all the records in mixed in there, in no real particular order.

Now, there are some complex ways to do that if you order your fields in a query, but I am not sure it will be worth the effort for you.
See: https://support.microsoft.com/en-us/kb/210504
and Get Value from Previous Record for this record - Microsoft Access / VBA

You may be better off going another route, like fixing your data.

 

[Apple08]

Thanks Joe. Yes it is more complicated than I expected. I will have a go and see if it is worth to carry on.

Many thanks for your help indeed!