My date is messed up (1 Viewer)

Ziggy1

Registered User.
Local time
Today, 01:01
Joined
Feb 6, 2002
Messages
462
I set this code up to load a table based on beginning ending dates, I thought it was working...then I realized is I enter a begin date where the DAY is a single digit ( 1-9) it mixes it up as a month so where Dec 1/09 is entered it turns it into Jan 12/09?

Seems basic, and I'm being lazy by posting because it is probably staring at me in the face....


Code:
Public Sub Update()

Dim strSQL As String
Dim stDate As Date
Dim EnDate As Date


stDate = Me.txtDate


DoCmd.OpenQuery "qryDelete_tblRunDate"


    If IsNull(Me.txtEndDate) Then

        EnDate = Me.txtDate + 1

    Else

        EnDate = Me.txtEndDate

    End If

Do Until stDate = EnDate

    strSQL = "insert into tblRundate(rundate,systemID,Client)"
    strSQL = strSQL + "Values(" & "#" & stDate & "#" & "," & "'" & Me.txtComp & "'" & "," & "'" & Me.txtClient & "'" & ")"
    'strSQL = "INSERT INTO tblRunDate (RunDate) values (#25-Jun-2009#)"
    
    DoCmd.RunSQL strSQL

stDate = stDate + 1

    Loop


Me.Requery


End Sub


thanks for looking
 

HiTechCoach

Well-known member
Local time
Yesterday, 20:01
Joined
Mar 6, 2006
Messages
4,357
Are you storing the dates as a date/time data type?

Are you making sure that you enter a valid data on the form?

This seams to work:
Code:
? DateValue("Dec 1/09")
12/1/2009
 

Ziggy1

Registered User.
Local time
Today, 01:01
Joined
Feb 6, 2002
Messages
462
Thanks Coach...you rock :) date Value did the trick...but I don't understand why my setup wouldn't handle it without it?

Access 2007

Form comes with Date Picker when I add the control and choose medium date, the VB already had the date type set and I wrapped # around the variable...the final table also has the field formatted same....

oh well, it works now...thanks
 

Ziggy1

Registered User.
Local time
Today, 01:01
Joined
Feb 6, 2002
Messages
462
Wait spoke to soon, I had just come back from a meeting and saw your post...tried it worked...but that was with a 2 digit day in the date... Nov 18....I forgot to test with a single day.... arghhh...
 

MSAccessRookie

AWF VIP
Local time
Yesterday, 21:01
Joined
May 2, 2008
Messages
3,428
I set this code up to load a table based on beginning ending dates, I thought it was working...then I realized is I enter a begin date where the DAY is a single digit ( 1-9) it mixes it up as a month so where Dec 1/09 is entered it turns it into Jan 12/09?

Seems basic, and I'm being lazy by posting because it is probably staring at me in the face....

thanks for looking

Is there any possibility that your region setting is incorrect? You are expecting 12/1/2009 to be December 1. That is the North American (and others) format of a date (mm/dd/yyyy). You are getting January 12, which indicates that you might be using the European (or others) format for a date (dd/mm/yyyy).

Just a thought.
 

novtalath

Registered User.
Local time
Today, 01:01
Joined
Dec 30, 2008
Messages
19
Maybe you should set up FORMAT in actual database and/or on them textboxes you enter date to a database. Just a thought. Would sort some of your troubles.
 

namliam

The Mailman - AWF VIP
Local time
Today, 02:01
Joined
Aug 11, 2003
Messages
11,696
The problem is not related to the 1 or 2 digit date, it is related to the SQL native US format being used.

If you are using DEC 01/09 that should always work but that is data entry which gets "mutilated" into a "real" date here:
stDate = Me.txtDate

This date is then actually 01/12/2009 which is displayed into your code as such (using your regional settings) then transposed (because of the sql ##) into MM/DD/YYYY or 12 Jan.

THe solution to this is to "force" the proper format going into your sql using Format
ISO, which always works
... "Values(" & "#" & Format(stDate, "YYYY-MM-DD") & "#" ...
Or US, which works in sql:
... "Values(" & "#" & Format(stDate, "MM/DD/YYYY") & "#" ...
 

Ziggy1

Registered User.
Local time
Today, 01:01
Joined
Feb 6, 2002
Messages
462
Hi guys, sorry i didn't post back sooner...the holidays got in the way, I was off for 2 weeks and finally got caught back up to this problem.

What I found was not so much the Regional setting but I was contradicting my formatting, or confusing it. I found that if I added the extra M in the months, that helped, but I also had the "Default" values in the controls set to d/mm/yy...I forgot I put it there.

Thanks for the help.
 

Users who are viewing this thread

Top Bottom