How to add a string to a date

[Ben_Entrew]

Hi all,

I want to add a string as year to a date.
Somehow it doesn't work out. It should extract all records with Valid_from = 01.01.2013 and valid_to = 31.12.2013. The Year assignment works.

Can somebody please help me?

Thanks in advance.

Regards,
Ben

Public Sub BEN()
Dim strSQL As String
Dim t As Date, s As Date
DoCmd.SetWarnings False

Year = Right(pricedate, 4)

t = 1 / 1 / " & Year & "
s = 12 / 31 / " & Year & "

strSQL = "SELECT TRANSFER_PRICES.* INTO [TEMP] " & _
         " FROM TRANSFER_PRICES " & _
         " Where [Valid_from] = " & Format(t, "\#mm\/dd\/yyyy\#") & _
         " AND [Valid_to] = " & Format(s, "\#mm\/dd\/yyyy\#")
              
DoCmd.RunSQL strSQL


DoCmd.SetWarnings True

End Sub

 

[pr2-eugin]

You need a DateSerial function to get Date. Also make some sensible variable names.

Public Sub BEN()
    Dim strSQL As String, yearStr As String
    Dim startDate As Date, endDate As Date
    DoCmd.SetWarnings False

    yearStr = Right(pricedate, 4)

    startDate = DateSerial(yearStr, 1, 1)
    endDate = DateSerial(yearStr, 12, 31)

    strSQL = "SELECT TRANSFER_PRICES.* INTO [TEMP] " & _
             " FROM TRANSFER_PRICES " & _
             " Where [Valid_from] = " & Format(startDate, "\#mm\/dd\/yyyy\#") & _
             " AND [Valid_to] = " & Format(endDate, "\#mm\/dd\/yyyy\#")
                  
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
End Sub

 

[Ben_Entrew]

Thanks Paul,

this works now.

Regards,
Ben

 

[pr2-eugin]

No problem ! Good Luck !