MS Access 2000 VBA Code Shows Totally Incorrect Date from Controls on a Form

[Easystart]

Hi,

Sorry for my English. English is not my native tougue.

I am working in MS Access 2000 with a SQLServer 2000 Backend database.
MS Access 2000 is my GUI front end that has SQLServer linked tables in
it. One of my forms has two TEXT BOX controls formated as Short Date.
The form is binded to a linked table. The linked tables has about 7
records and one of the control is binded to a table field. These two
controls displays the date correctly as 01/07/2006 and 31/07/2006 (I am
running Windows XP, my regional setting is set to English Australia,
location is set to Australia, language for non-Unicode programs is set
to English Australia, and my date format is set to d/MM/YYYY) when the
form is running. I have some VBA code behind the form to print out
these two dates to a text file. The dates show up as 30/12/2005 and
30/01/2006. I really do not know why these dates are totally different.
Here's my VBA code:

Open "C:\Results.txt" For Output Access Write Lock Write As #1
Print #1, "GL run from "; Me.txtFromDate; " to "; Me.txtToDate
Print #1, ""
::
::
Close #1

I am hoping some one has come across with this problem.

Thanks in advance,

Easystart

[pietlinden]

oh, so you mean you're just outputting everything to a text file?

My guess is that since MS stores all it's data in US format, your have
to force it into Australian format when you write it to the file. What
happens if you use Format() and force it into the format you want?

[Allen Browne]

There are some basic things you need to do to ensure Access interprets our
d/m/y dates correctly.

Specifically:
- Set the Format property of unbound contorls, so Access knows they are
dates.
- Declare parameters in your query, so Access gets the type right.
- Typecast calculated fields, so Access gets the type right.
- Understand the difference between dates entered in the interface, and
dates entered into VBA code or SQL clauses.

Details in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

Also:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Easystart" <nh*******@hotmail.comwrote in message
news:11**********************@75g2000cwc.googlegro ups.com...

>
Sorry for my English. English is not my native tougue.

I am working in MS Access 2000 with a SQLServer 2000 Backend database.
MS Access 2000 is my GUI front end that has SQLServer linked tables in
it. One of my forms has two TEXT BOX controls formated as Short Date.
The form is binded to a linked table. The linked tables has about 7
records and one of the control is binded to a table field. These two
controls displays the date correctly as 01/07/2006 and 31/07/2006 (I am
running Windows XP, my regional setting is set to English Australia,
location is set to Australia, language for non-Unicode programs is set
to English Australia, and my date format is set to d/MM/YYYY) when the
form is running. I have some VBA code behind the form to print out
these two dates to a text file. The dates show up as 30/12/2005 and
30/01/2006. I really do not know why these dates are totally different.
Here's my VBA code:

Open "C:\Results.txt" For Output Access Write Lock Write As #1
Print #1, "GL run from "; Me.txtFromDate; " to "; Me.txtToDate
Print #1, ""
::
::
Close #1

I am hoping some one has come across with this problem.

Thanks in advance,

Easystart

[Lyle Fairfield]

pi********@hotmail.com wrote:

My guess is that since MS stores all it's data in US format

I think store is not the correct word here. Dates are stored in 8
bytes, resembling doubles. MS requires the US format (mm/dd/yyyy) or an
international format (yyyy-mm-dd) to interpret date strings correctly.

[Lyle Fairfield]

Easystart wrote:

Hi,

Sorry for my English. English is not my native tougue.

I am working in MS Access 2000 with a SQLServer 2000 Backend database.
MS Access 2000 is my GUI front end that has SQLServer linked tables in
it. One of my forms has two TEXT BOX controls formated as Short Date.
The form is binded to a linked table. The linked tables has about 7
records and one of the control is binded to a table field. These two
controls displays the date correctly as 01/07/2006 and 31/07/2006 (I am
running Windows XP, my regional setting is set to English Australia,
location is set to Australia, language for non-Unicode programs is set
to English Australia, and my date format is set to d/MM/YYYY) when the
form is running. I have some VBA code behind the form to print out
these two dates to a text file. The dates show up as 30/12/2005 and
30/01/2006. I really do not know why these dates are totally different.
Here's my VBA code:

Open "C:\Results.txt" For Output Access Write Lock Write As #1
Print #1, "GL run from "; Me.txtFromDate; " to "; Me.txtToDate
Print #1, ""
::
::
Close #1

I am hoping some one has come across with this problem.

Thanks in advance,

Easystart

I cannot duplicate your results. It seems that something obvious
(exchanging day and month positions;
allowing for SQL-Server day zero to be two days later than VBA
[1900-01-01 as opposed to 1899-12-30];
the "#" character being interpreted to mean number by the print command
rather that date-number)
isn't jumping out here.

I note that, depending upon rounding, the error dates are one half year
earlier than the correct dates. Are you doing anything with six months,
half a year in this procedure or any procedure connected with this
form?

I suggest that you run

Open "C:\Results.txt" For Output Access Write Lock Write As #1
Debug.Print "GL run from "; Me.txtFromDate; " to "; Me.txtToDate
Debug.Print "GL run from "; Clng(Me.txtFromDate); " to ";
CLng(Me.txtToDate)
Print #1, "GL run from "; Me.txtFromDate; " to "; Me.txtToDate
.....

to see if the values appearing in the Immediate Window will shed some
light on what's going wrong. I would also be more explicit as in
Me.txtFromDate.Value.
or
Format(Me.txtFromDate.Value, "yyyy-mm-dd")

[Easystart]

Hi,

It is Easystart again.

After reading your replies, I have made a small change. Here's my
change:

r_reportdata.FromDate = CVDate(Me.txtFromDate.Value)
r_reportdata.ToDate = CVDate(Me.txtToDate.Value)
lstrTemp = "GL run from " & Format(r_reportdata.FromDate, "short date")
& " to " & Format(r_reportdata.ToDate, "short date")
::
::
Print #1, lstrTemp

The two controls on my form are already formated as short date.

Let's wait for my Client to try it out and I will post the result.

Regards,

Easystart.


Lyle Fairfield wrote:

Easystart wrote:

Hi,

Sorry for my English. English is not my native tougue.

I am working in MS Access 2000 with a SQLServer 2000 Backend database.
MS Access 2000 is my GUI front end that has SQLServer linked tables in
it. One of my forms has two TEXT BOX controls formated as Short Date.
The form is binded to a linked table. The linked tables has about 7
records and one of the control is binded to a table field. These two
controls displays the date correctly as 01/07/2006 and 31/07/2006 (I am
running Windows XP, my regional setting is set to English Australia,
location is set to Australia, language for non-Unicode programs is set
to English Australia, and my date format is set to d/MM/YYYY) when the
form is running. I have some VBA code behind the form to print out
these two dates to a text file. The dates show up as 30/12/2005 and
30/01/2006. I really do not know why these dates are totally different.
Here's my VBA code:

Open "C:\Results.txt" For Output Access Write Lock Write As #1
Print #1, "GL run from "; Me.txtFromDate; " to "; Me.txtToDate
Print #1, ""
::
::
Close #1

I am hoping some one has come across with this problem.

Thanks in advance,

Easystart

I cannot duplicate your results. It seems that something obvious
(exchanging day and month positions;
allowing for SQL-Server day zero to be two days later than VBA
[1900-01-01 as opposed to 1899-12-30];
the "#" character being interpreted to mean number by the print command
rather that date-number)
isn't jumping out here.

I note that, depending upon rounding, the error dates are one half year
earlier than the correct dates. Are you doing anything with six months,
half a year in this procedure or any procedure connected with this
form?

I suggest that you run

Open "C:\Results.txt" For Output Access Write Lock Write As #1
Debug.Print "GL run from "; Me.txtFromDate; " to "; Me.txtToDate
Debug.Print "GL run from "; Clng(Me.txtFromDate); " to ";
CLng(Me.txtToDate)
Print #1, "GL run from "; Me.txtFromDate; " to "; Me.txtToDate
....

to see if the values appearing in the Immediate Window will shed some
light on what's going wrong. I would also be more explicit as in
Me.txtFromDate.Value.
or
Format(Me.txtFromDate.Value, "yyyy-mm-dd")