Solved VBA Date Formatting Issue (1 Viewer)

[chizzy42]

Hi, Hope all is well. I was wondering if someone could help me out with a date formatting issue I have. I've been using a bit of code for date formatting and now that I'm trying to use it to find the previous date I think I'm doing something wrong.

Dim DT3 As String
DoCmd.SetWarnings False
MsgBox (Now())
DT3 = (Format(Day(Now()) + 1, "dd") & "D" & Format(Month(Now()) + 1, "dd") & Format(Now(), "yy"))
MsgBox ("DT3 " & DT3)

The above code gives the correct date but I have to add +1 to the day and month to get the correct date even though if I use now() in a message box I get the correct date, why does it lose the correct value. Also if I format the month part with "mm" (which I now think is correct it loses a month ?
Could someone tell me please what the correct format should be for todays date..and if this could be used to find the previous days date?

thanks for reading

 

[Gasman]

Use DateAdd()
Do not use string dates for any calcs, purely display
Date - 1 would give you previous day?
Dates are actually stored as numbers in Access ( and other programs).

 

[Eugene-LS]

Could someone tell me please what the correct format should be for todays date..and if this could be used to find the previous days date?

IIf I understood your wishes correctly:

Dim DT3 As String
Dim dMyDate As Date
Dim sVal As String
   
    'DoCmd.SetWarnings False
    'MsgBox (Now())
   
    dMyDate = Date
   
    dMyDate = DateAdd("d", 1, dMyDate) '+ 1 Day
    dMyDate = DateAdd("m", 1, dMyDate) '+ 1 Month
   
    DT3 = (Format(Day(Now()) + 1, "00") & "D" & Format(Month(Now()) + 1, "00") & Format(Now(), "yy"))
    sVal = Format(dMyDate, "dd\Dmmyy") 'Other way for code above
   
    MsgBox "DT3: " & DT3 & vbCrLf & "sVal: " & sVal
   
'   Debug.Print "sVal: " & sVal
'   Debug.Print "DT3: " & DT3

Much Shorter code for the same:

Dim DT3 As String
    '...  DateAdd("m", 1, Date) + 1 '+ 1 Month and + 1 Day
    DT3 = Format(DateAdd("m", 1, Date) + 1, "dd\Dmmyy")
    MsgBox "DT3: " & DT3

 

[chizzy42]

Hi Gasman and Eugene-LS for the replies. After gasman saying to try DateAdd i found this code

Dim FirstDate As Date    ' Declare variables.
Dim IntervalType As String
Dim Number As Integer
Dim Msg As String
IntervalType = "d"    ' "m" specifies months as interval.
FirstDate = InputBox("Enter a date")
Number = InputBox("Enter number of months to add")
Msg = "New date: " & DateAdd(IntervalType, Number, FirstDate)
MsgBox Msg

Which after playing about with gave me the flexibility of dates, but for some some reason the way i was formatting was still off. The code Eugene-LS supplied seems to do the trick.

thank you both