ADP ignoring my format code

[T.J. Bernard]

I have an MS Access ADP that is finished up and working
great. It is testing out perfect, and I am excited.
Except my London users are having errors. I traced the
error down, and it is due to the date format differences.
In the USA we use (mm/dd/yyyy) and in the UK they use
(dd/mm/yyyy). No biggie I thought, I just added the code:

Format(myfield, "mm/dd/yyyy")

This has always worked in the past. After adding this
code I tested the database again (I changed to the UK date
standard on my machine). And the weirdest thing is it
just seems to ignore the function. It doesn't give me an
error, it compiles just fine, but the format function
doesn't do anything.

I stepped through the code and the date variable stays the
same value before, during and after the format statement
(20/9/2004) thus giving me an error when I try to pass the
value to a stored procedure.

If anyone has run into this before, or has any
recommendations it would be greatly appreciated.

Thank you,

T.J.

 

[Lyle Fairfield]

In the USA we use (mm/dd/yyyy) and in the UK they use
(dd/mm/yyyy).

Most systems will recognize and interpret yyyy-mm-dd correctly, no matter the
brainless manifestation of national arrogance.

Where and how are you using Format?

 

[T.J. Bernard]

Lyle, thank you for answering. I just tried the yyyy-mm-
dd format as well and the code ignored it. Here is
where/how I am using the format statement:

I have a text box on my form displaying a date field, that
is calculated depending on what the user selects in a text
box. It displays in this format "September 20, 2004" to
prevent confusion for the London users.

I then click a button, and on the button click the value
is passed to a stored procedure. It works just fine for
me, but the London users have problems. I then switched
my Regional settings to "English (United Kingdom)" and I
was able to duplicate their errors.

So in the code I added this:

Dim Thedate as Date

Thedate = Format(txtday.value, "mm/dd/yyyy")

Then I pass the variable to the stored procedure. If I
step through the code the format statement never does
anything, the values comes in as 20/9/2004, and after the
format statement it is still equal to 20/9/2004. So it
gives me an error.

This works just fine in MS Access, but for some reason the
ADP seems to ignore the code. It does compile correctly,
so very weird. If you have any ideas, let me know.

Thank you,

T.J.

 

[Vadim Rapp]

Hello T.J.:
You wrote in conference microsoft.public.access.adp.sqlserver on Mon, 20
Sep 2004 13:48:36 -0700:

TB> Dim Thedate as Date

TB> Thedate = Format(txtday.value, "mm/dd/yyyy")

TB> Then I pass the variable to the stored procedure. If I
TB> step through the code the format statement never does
TB> anything, the values comes in as 20/9/2004, and after the
TB> format statement it is still equal to 20/9/2004. So it
TB> gives me an error.

Nice puzzle. I think the reason is that you declare TheDate as date.
Regardless of the way you assign a value to it, when you _use_ it, it's
presented according to your regional settings.

To resolve, Dim Thedate as String.

Vadim

 

[Brendan Reynolds]

What's happening here goes something like this ...

The Value property of the textbox, which is a Variant, is converted by the
Format function to a Variant of sub-type String, then implicitly converted
to a Date by the assignment to the Date variable.

VBA Date variables store dates as Double, so what is actually stored in the
variable is the value 38250.

The only effect of the whole process so far has been to lose any time part
that the original data may have contained. It looks like it isn't intended
to contain any, so that's probably not a problem in this context, but it may
still be worth noting for future reference.

If the desired end result is a string in mm/dd/yyyy format, then I agree
with Vadim that the result of the Format function needs to be assigned to a
String variable. (Better still, use the Format$ version of the function,
which returns a String, rather than a Variant of sub-type String, avoiding
the unnecessary implicit conversion from Variant to String).

What I'm trying to clarify here is that what is happening is not that "it
comes in as 20/9/2004 and is still 20/9/2004". In reality, it comes in as
38250 and remains 38250. It only *looks like* 20/9/2004.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Lyle Fairfield]

What's happening here goes something like this ...

The Value property of the textbox, which is a Variant, is converted by
the Format function to a Variant of sub-type String, then implicitly
converted to a Date by the assignment to the Date variable.

When a text box named txtBox is bound to a field of type date/time what does
one get when one asks:
TypeName(txtBox.Value)
?

 

[Brendan Reynolds]

Hi Lyle.

Dim varWhatever As Variant
varWhatever = Date
MsgBox TypeName(varWhatever)

Would you argue then, that varWhatever is not a Variant?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Lyle Fairfield]

Hi Lyle.

Dim varWhatever As Variant
varWhatever = Date
MsgBox TypeName(varWhatever)

Would you argue then, that varWhatever is not a Variant?

No. But the question of type, Access forms, Jey Data and VBA seems not so
clearly defined as it might be. In any case, the original poster reported
that his "code" worked in an MDB but not in an ADP. Why? Surely, they both
reference the same library. Did he mean sql? This is unlikely as he seems
experienced and precise. So, again, why? Perhaps if he would post the whole
procedure in which the code fails, we might be able to ascertain why.

 

[Brendan Reynolds]

I looked up the original post, Lyle, and my understanding is that what TJ
was saying was not that this particular procedure had worked in an MDB, but
that the general technique of formatting a date using US format had worked
in the past. I suspect that your suggestion of using yyyy-mm-dd format is a
better approach, but I leave that aspect of the discussion to those, like
yourself, with more experience of SQL Server than I. The point I was making
was not whether converting to US format was a good idea or not, but why the
code was not doing that.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Vadim Rapp]

Hello Brendan:
You wrote on Tue, 21 Sep 2004 11:42:27 +0100:

BR> Better still, use the Format$ version of the function, which returns a
BR> String, rather than a Variant of sub-type String, avoiding the
BR> unnecessary implicit conversion from Variant to String).

I think the proper way to address this problem is using functions like
DatePart.

I.e. for example, if I want to extract the month from the date, the bad code
will be

Month = mid(MyDate,1,2)

The good code will be

Month = datepart("m",MyDate)

This is good practice even for domestic-only applciations; for the
international ones, it's absolutely mandatory.

Actually, the whole Y2K issue was because people were using (1) instead of
(2).


Vadim

 

[Brendan Reynolds]

Hi Vadim,

Sorry about the delay in replying, I was ill for a few days last week.

If the month is all that is required, and MyDate is a Date variable, and
we're talking VBA, not SQL, then all we need is Month(MyDate).

As I remember it though, what the original poster wanted was a string
representation of a complete date in US format. That would work with Jet
SQL, which, in 32-bit versions, expects dates in that format, regardless of
the current locale.

The question I was attempting to address was the question posed in the
subject line, why was the formatting code not producing the expected result?
But there is I think another question here, which is whether this Jet SQL
technique is appropriate in an ADP/SQL Server context?

I'm not any kind of expert on SQL Server, but I see that the SQL Server
Books Online topic, "Writing International Transact-SQL Statements" lists
three solutions to this problem - use the ODBC timestamp, date, and time
escape clauses, use the unseperated numeric strings (yyyymmdd, which if I
remember correctly is what Lyle suggested) or use the CONVERT statement with
an explicit style parameter.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.