Dates format changing mid way through code?

A

adh123

Registered User.
Local time
Today, 23:42
Joined
Jan 14, 2015
Messages
77
Trying to create a record in a table (tblToday) for any dates in the past five days which do not already exist. This needs to include the previous 5 days for other forms/reports to function correctly.

The code below is the simplest way I could work out how to insert multiple rows (on a row by row basis) however I am having trouble with strNowDateThree...

I found out quickly that it wanted to post dates in MM/DD/YYYY format (not the UK DD/MM/YYYY format) so amended this for all Dates as you can see below.

However strNowDateThree posts in the format DD/MM/YYYY still (hence the msgbox text halfway through so I could work out what it is doing), have I missed something glaringly obvious or is there a better way to handle this?

Code: 
Private Sub cmdLogin_Click()

Dim strNowDate As Date
strNowDate = Format(Date, "MM/DD/YYYY")

Dim strNowDateOne As Date
strNowDateOne = Format(Date - 1, "MM/DD/YYYY")

Dim strNowDateTwo As Date
strNowDateTwo = Format(Date - 2, "MM/DD/YYYY")

Dim strNowDateThree As Date
strNowDateThree = Format(Date - 3, "MM/DD/YYYY")

Dim strNowDateFour As Date
strNowDateFour = Format(Date - 4, "MM/DD/YYYY")

Dim strNowDateFive As Date
strNowDateFive = Format(Date - 5, "MM/DD/YYYY")

Dim strLastDate As Date
strLastDate = Nz(DMax("t_date", "tblToday"))


MsgBox "strNowDateFive = " & strNowDateFive
MsgBox "strLastDate = " & strLastDate
MsgBox "strNowDateThree = " & strNowDateThree


If strNowDateFive > strLastDate Then
    DoCmd.RunSQL "INSERT INTO tblToday ([t_date], [t_comments])" & " VALUES " & _
        "(" & "#" & strNowDateFive & "#, " & _
        "'" & "No notes available" & "')"
    Else
    MsgBox "strNowDateFive is LESS"
End If

If strNowDateFour > strLastDate Then
    DoCmd.RunSQL "INSERT INTO tblToday ([t_date], [t_comments])" & " VALUES " & _
        "(" & "#" & strNowDateFour & "#, " & _
        "'" & "No notes available" & "')"
    Else
    MsgBox "strNowDateFour is LESS"
End If

If strNowDateThree > strLastDate Then
    DoCmd.RunSQL "INSERT INTO tblToday ([t_date], [t_comments])" & " VALUES " & _
        "(" & "#" & strNowDateThree & "#, " & _
        "'" & "No notes available" & "')"
    Else
    MsgBox "strNowDateThree is LESS"
End If

If strNowDateTwo > strLastDate Then
    DoCmd.RunSQL "INSERT INTO tblToday ([t_date], [t_comments])" & " VALUES " & _
        "(" & "#" & strNowDateTwo & "#, " & _
        "'" & "No notes available" & "')"
    Else
    MsgBox "strNowDateTwo is LESS"
End If

If strNowDateOne > strLastDate Then
    DoCmd.RunSQL "INSERT INTO tblToday ([t_date], [t_comments])" & " VALUES " & _
        "(" & "#" & strNowDateOne & "#, " & _
        "'" & "No notes available" & "')"
    Else
    MsgBox "strNowDateOne is LESS"
End If

If strNowDate > strLastDate Then
    DoCmd.RunSQL "INSERT INTO tblToday ([t_date], [t_comments])" & " VALUES " & _
        "(" & "#" & strNowDate & "#, " & _
        "'" & "No notes available" & "')"
    Else
    MsgBox "strNowDate is LESS"
End If

DoCmd.OpenForm "frmMaster"

DoCmd.Close acForm, "frmWelcome", acSaveNo

End Sub

So running this today gives me the following:
StrNowDateFive = 01/30/2015 (30th jan 15)
StrNowDateFour = 01/31/2015 (31st jan 15)
StrNowDateThree = 01/02/2015 (2nd jan 15) - so doesnt post as is before strLastDate
StrNowDateTwo = 02/02/2015 (2nd feb 15)
StrNowDateOne = 02/03/2015 (3rd feb 15)
StrNowDate = 02/04/2015 (4th feb 15)

I have changed table field to long date so I can see exactly what is posting which is how I first found the error.
 
Last edited:
Is that when it hits, 01/31/2015?

Try;
Code: 
Format(Date -3, "\#mm\/dd\/yyyy\#")
 
mattkorguk said:
Is that when it hits, 01/31/2015?

Try;
Code: 
Format(Date -3, "\#mm\/dd\/yyyy\#")
Click to expand...


This gives me a Run-time error '13':
Type mismatch
 
hmm..that auto formats itself back to
strNowDateThree = Format(Date - 3, "\#MM\/DD\/YYYY\#")

which gives the same error?

Ive put in a msgbox to show each date line individually (and the date posted to the table) and get the following:

strLastDate = 30/01/2015 (30th jan as per last date in table)
strNowDateFive = 30/01/2015 (30th jan)
strNowDateFour = 31/01/2015 (31st jan)
strNowDateThree = 02/01/2015 (2nd jan)
strNowDateTwo = 02/02/2015 (2nd feb)
strNowDateOne = 02/03/2015 (3rd feb)
strNowDate = 02/04/2015 (4th feb)

Part way through the msgbox switches from UK dates to US dates, yet all formatted to mm/dd/yyyy.

In the table it posts correctly (except for strNowDateThree). Very confused!
 
Last edited:
Since you are adding as a date, there is no need to convert to a string which is then reinterpreted as a date using the #

all your code relating to dates can be replaced with
Code: 
Dim LastDate as Date
Dim i as integer
 LastDate=Nz(DMax("t_date", "tblToday"))

 I=5
 while Date()-i> lastDate 
     DoCmd.RunSQL "INSERT INTO tblToday ([t_date], [t_comments]) VALUES (" & Date()-i ", 'No notes available')"
    i=i-1
 wend
 
Thanks for the code there CJLondon, that works!

Is there a way of it stopping once it has added todays date, as it appears to just keep going into the future otherwise?
 
Is there a way of it stopping
Click to expand...
Ah, might of got the loop going in the wrong direction

think the loop should be

Code: 
I=0
while date()-I>lastdate
    DoCmd.RunSQL "INSERT ....
    I=I+1
wend
example data

Date=10/1/2015
lastdate=8/1/2015

so for loop
1 date-0=10/1/2015>8/1/2015 - run
2 date-1=09/1/2015>8/1/2015 - run
3 date-2=08/1/2015>8/1/2015 - stop
 
It isnt changing formats, it always requires US date formats, however 1/30 obviously doesnt exist and Access is "being kind" by converting it for you.

The fix is easy, you basicaly have it already, however in the wrong place
Code: 
....
Dim strNowDateFive As Date
strNowDateFive = Format(Date - 5, "MM/DD/YYYY")
....
Here you are forcing US dates, but feeding this into a date field, basicaly undoing the formatting back into a default "state"
Code: 
strNowDateFive = Date - 5
Would do the exact same, but you do need the format but not in the above....
Code: 
...
If strNowDateFive > strLastDate Then
    DoCmd.RunSQL "INSERT INTO tblToday ([t_date], [t_comments])" & " VALUES " & _
        "(" & "#" & strNowDateFive & "#, " & _
        "'" & "No notes available" & "')"
    Else
    MsgBox "strNowDateFive is LESS"
End If
...
You want it in the insert SQL
Code: 
...
If strNowDateFive > strLastDate Then
    DoCmd.RunSQL "INSERT INTO tblToday ([t_date], [t_comments])" & " VALUES " & _
        "(" & "#" & [U]Format([/U]strNowDateFive[U], "MM/DD/YYYY")[/U] & "#, " & _
        "'" & "No notes available" & "')"
    Else
    MsgBox "strNowDateFive is LESS"
End If

I wont say much about your naming convention (str... as Date?) or your non-standard way of declaring variables (it is mostly good practice to put all your Dim's on top of the module)
 
CJ_London said:
Ah, might of got the loop going in the wrong direction

think the loop should be

Code: 
I=0
while date()-I>lastdate
    DoCmd.RunSQL "INSERT ....
    I=I+1
wend
example data

Date=10/1/2015
lastdate=8/1/2015

so for loop
1 date-0=10/1/2015>8/1/2015 - run
2 date-1=09/1/2015>8/1/2015 - run
3 date-2=08/1/2015>8/1/2015 - stop
Click to expand...

This works really nicely. The only problem with it from here is that dates are added the other way around (i.e. insert todays date, they yesterdays, then day before etc) so in combo boxes on other forms and reports the order does not flow. I have edited the code to this which seems to work well :)

Code: 
Dim LastDate As Date
Dim i As Integer
    LastDate = Nz(DMax("t_date", "tblToday"))
    
i = 1
While LastDate + i <= Date
    DoCmd.RunSQL "INSERT INTO tblToday ([t_date], [t_comments])" & " VALUES " & _
        "(" & "'" & LastDate + i & "', " & _
        "'" & "No notes available" & "')"
    i = i + 1
Wend

I wont say much about your naming convention (str... as Date?) or your non-standard way of declaring variables (it is mostly good practice to put all your Dim's on top of the module)
Click to expand...

Namliam, thanks for your help as well. This being the first vba project (my knowledge has all come from searches on google and here!) I have taken on it is a steep learning curve although I am pretty pleased with whats been achieved so far!
 
adh123 said:
This works really nicely. The only problem with it from here is that dates are added the other way around (i.e. insert todays date, they yesterdays, then day before etc) so in combo boxes on other forms and reports the order does not flow. I have edited the code to this which seems to work well :)
Click to expand...
The order data is input into tables should be Irrelevant.
If you need data represented in your forms in a particular order, you should enforce this by adding an "Order by" into the select

DoCmd.RunSQL "INSERT INTO tblToday ([t_date], [t_comments])" & " VALUES " & _
"(" & "'" & LastDate + i & "', " & _
"'" & "No notes available" & "')"
Click to expand...
No no no. You are using ' around a date field? This will eventually cause problems... Dates you wrap in # and force the MM/DD/YYYY format to ensure thing go the way you want them too.

Though having a tblToday, kindoff negates its purpose.... If you need the past week always as your dates pickable from a combobox...
Simply make a table that holds values 0,1,2,3,4,5,6, lets call it tbl7Days in its single Column myDay
Now all you need to do is
Code: 
Select Date() - myDay as DatePicker 
from tbl7Days
Order by Date() - myDay
To generate a list of dates for you.
 
Thanks

It is more than just the past 7 days. The users are able to add in notes/comments on a day to day basis about their current tasks. We will need to store a record of these and so it needs to add a new row each time. On another form they can amend the 'no notes available' to whatever is required on that day.

Noted about using # instead of '!

This should look a bit better and seems to do the trick!

Code: 
Private Sub btnLogin_Click()

DoCmd.SetWarnings False

Dim LastDate As Date
Dim i As Integer
    LastDate = Nz(DMax("t_date", "tblToday"))
    
i = 1
While LastDate + i <= Date
    DoCmd.RunSQL "INSERT INTO tblToday ([t_date], [t_comments])" & " VALUES " & _
        "(" & "#" & Format(LastDate + i, "MM/DD/YYYY") & "#, " & _
        "'" & "No notes available" & "')"
    i = i + 1
Wend

DoCmd.SetWarnings True

DoCmd.OpenForm "frmMaster"

DoCmd.Close acForm, "frmWelcome", acSaveNo

End Sub
 
adh, why make your code more complicated than it needs to be?

"(" & "#" & Format(LastDate + i, "MM/DD/YYYY") & "#, "

can be simplified to

"(#" & Format(LastDate + i, "MM/DD/YYYY") & "#, "

and since lastdate is a date, you do not need the # or format. Read up on what # does - it translates a string to a date, so you are taking a date, formatting it into a string and then converting it back to a date. So simplifies to

LastDate+i,

In case you don't know, the date type is a special type of decimal number - today (5th Feb) is 42040.0, so 02/05/2015, 05/02/2015, 5th Feb 2015, 05 February 2015 are all formats of 42040.0. The time element (e.g. derived from Now() has a value after the decimal point being number of seconds as a percentage of 24hrs * 60 minutes * 60 seconds so 12 noon would be 0.5, 11:30 would be 0.46 etc

This is why Date()-1 gives you yesterday, because you are subtracting a number from a number.
 
When I tried it without the # or format it reverted back to the issue I had before, where the day was staying as the 2nd, but the month was increasing (feb/mar/apr etc).

I do think that I need to go back through previous forms code and make sure that what I have picked up from you guys is applied to other forms to make sure that I do not get tripped up when the tool goes live with the users, so thanks for the advice :)
 
CJ_London said:
and since lastdate is a date, you do not need the # or format.
Click to expand...
You may not, however when you are in a setting where your regional settings set dates as DD/MM/YYYY then you do need to use the format...

Otherwize you dont get 1/2/2015 to be 1/Feb/2015, but rather Jan/2/2015 since access sql requires the US Format vs the euro format from the regional settings that is used in VBA
 
You must log in or register to reply here.

Similar threads

B
Avoid duplicate records (overlapping dates)
Replies
18
Views
1,129
arnelgp
arnelgp
D
Solved Problem with dates in sql
Replies
7
Views
410
disgracept
D
B
Overlapping dates with some null data
Replies
7
Views
857
bilakos93
B
zelarra821
Solved Detect the active field and its value
2
Replies
25
Views
1,285
zelarra821
zelarra821
R
Date format
Replies
5
Views
820
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom