Concatenating parts of a date

[Adzi]

Concatenating parts of a date query [SOLVED]

Hi,

I'm currently involved in a project to manage licences and boats on a waterway. The old accesse database was archaic in the way it worked so a complete redesign is in order.

I'm stuck on a bit of VBA whereby I need to join two parts of the date. The mm/dd is set in the code however the yyyy is variable and I have used a case selection but I cant seem to concatenate the mm/dd bit and the yyyy together.

Here's what I have at the moment;

Private Sub cmd_btnShowRenewals_Click()
Dim year As Integer
Dim currentDate As Date
Dim yearDifference As Integer
year = Me.cmb_yearSelector.Value
    Select Case Me.cmb_MonthSelector.Value
        Case "1":   licenceExpiryMonth = "#1/31/""& year &#"
        Case "2":   licenceExpiryMonth = "#2/28/""& year &#"
        Case "3":   licenceExpiryMonth = "3/31/""& year &"
        Case "4":   licenceExpiryMonth = "4/30/""& year &"
        Case "5":   licenceExpiryMonth = "5/31/""& year &"
        Case "6":   licenceExpiryMonth = "6/30/""& year &"
        Case "7":   licenceExpiryMonth = "7/31/""& year &"
        Case "8":   licenceExpiryMonth = "8/31/""& year &"
        Case "9":   licenceExpiryMonth = "9/30/""& year &"
        Case "10":  licenceExpiryMonth = "10/31/""& year &"
        Case "11":  licenceExpiryMonth = "11/30/""& year &"
        Case "12":  licenceExpiryMonth = "12/31/""& year &"
    End Select
 
End Sub

Obviously I'm getting a type mismatch error. Can anyone help me with my conundrum?

 

[Brianwarnock]

Don't use Access reserved words such as year as data names.
Assuming that it is myyear for this example I would just use Dateserial to calculate the required last day of the month

myyear = Me.cmb_yearSelector.Value
mymonth = Me.cmb_MonthSelector.Value

licenceExpiryMonth =dateserial(myyear,mymonth+1,0)

This form of the dateserial gives the last day of the previous month, which is why I add 1 to your month value. It allows for leap years and year ends.

Brian

 

[Adzi]

Don't use Access reserved words such as year as data names.
Assuming that it is myyear for this example I would just use Dateserial to calculate the required last day of the month

myyear = Me.cmb_yearSelector.Value
mymonth = Me.cmb_MonthSelector.Value

licenceExpiryMonth =dateserial(myyear,mymonth+1,0)

This form of the dateserial gives the last day of the previous month, which is why I add 1 to your month value. It allows for leap years and year ends.

Brian

Many thanks for your help Brian!!