format(EnterDate, "mm/dd/yyyy")

[Guest]

Hi!
I have run into a wired situation where an ac97 report shows data from one
machine but no from others, and when I made a change, other machines fetched
the report fine but not the one in question.

I have the solution, depending who is using, the code adjusts itself. It is
ugly, and I would like to find out why it’s acting this way.

First, the mdb file is on a network drive. Users are running directly
against it, and there is no concurrency issue.

Here are the other details:
TxtDate is a text box on a form, it has mm/dd/yy as its format.
User inputs a date then click a button.

The button click event dynamically builds a query. Part of it looks like this:
strSQL = strSQL + " WHERE (((Format([EnteredDate], 'mm/dd/yyyy')) = #" &
txtDate & "#)

And here are some of the results under two scenarios:

Scenario One: every body’s working but not this particular “power userâ€.*

(Note: every body = two win 2k machines and one xp pro v2002 sp2
power user= xp pro v2002 sp2)

data entered on form: 03/31/06
txtDate value seen under break: 3/31/2006 (everyone), 03/31/06 (power user)
srtSQL Format as ‘m/dd/yyyy’
returned data as expected for everyone but “power userâ€.

data entered on form: 12/31/05
txtDate value seen under break: 12/31/2005 (every one), 12/31/05 (power user)
srtSQL Format as ‘m/dd/yyyy’
returned data as expected for everyone but “power userâ€.

However, there is a sub-scenario, if I changed srtSQL Format to ‘mm/dd/yyyy’
instead of ‘m/dd/yyyy’, for every one, only 12/31/05 returns data, 03/31/06
does not, and for power user, nothing returns on either case.

Scenario Two: every body broke but “power user†working.

Changed srtSQL Format to ‘mm/dd/yy’

Same test data entered as above, and same value under break mode as above.

I have also tried
dim dtDate as date
dtDate=CDate(txtDate) then pass dtDate to sql, but nothing has changed.

I hope I have provided enough details.

Thanks!

 

[Douglas J. Steele]

You do not need to (nor want to) apply the Format function to the field in
the table. The Format function changes whatever's in it to a string, so the
best case is that you're forcing Access to convert the date field to a
string and then back to a date.

It's just the value of txtDate that you have to worry about being in
mm/dd/yyyy format. Dates aren't actually stored with any particular format:
they're 8 byte floating point numbers, where the integer portion represents
the date as the number of days relative to 30 Dec, 1899, and the decimal
portion represents the time as a fraction of a day.

 

[Guest]

Ok, I kind of understand what you are saying, but...
A text box should have the data type started as text, I was thinking maybe
the format property will force it into date type if I used a valid
"mm/dd/yy". Your explaination sounds to me that if I typed a date in a
textbox, then access will treat that as date type.
I will take format out and test again.
Thanks!

You do not need to (nor want to) apply the Format function to the field in
the table. The Format function changes whatever's in it to a string, so the
best case is that you're forcing Access to convert the date field to a
string and then back to a date.

It's just the value of txtDate that you have to worry about being in
mm/dd/yyyy format. Dates aren't actually stored with any particular format:
they're 8 byte floating point numbers, where the integer portion represents
the date as the number of days relative to 30 Dec, 1899, and the decimal
portion represents the time as a fraction of a day.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi!
I have run into a wired situation where an ac97 report shows data from one
machine but no from others, and when I made a change, other machines
fetched
the report fine but not the one in question.

I have the solution, depending who is using, the code adjusts itself. It
is
ugly, and I would like to find out why it's acting this way.

First, the mdb file is on a network drive. Users are running directly
against it, and there is no concurrency issue.

Here are the other details:
TxtDate is a text box on a form, it has mm/dd/yy as its format.
User inputs a date then click a button.

The button click event dynamically builds a query. Part of it looks like
this:
strSQL = strSQL + " WHERE (((Format([EnteredDate], 'mm/dd/yyyy')) = #" &
txtDate & "#)

And here are some of the results under two scenarios:

Scenario One: every body's working but not this particular "power user".*

(Note: every body = two win 2k machines and one xp pro v2002 sp2
power user= xp pro v2002 sp2)

data entered on form: 03/31/06
txtDate value seen under break: 3/31/2006 (everyone), 03/31/06 (power
user)
srtSQL Format as 'm/dd/yyyy'
returned data as expected for everyone but "power user".

data entered on form: 12/31/05
txtDate value seen under break: 12/31/2005 (every one), 12/31/05 (power
user)
srtSQL Format as 'm/dd/yyyy'
returned data as expected for everyone but "power user".

However, there is a sub-scenario, if I changed srtSQL Format to
'mm/dd/yyyy'
instead of 'm/dd/yyyy', for every one, only 12/31/05 returns data,
03/31/06
does not, and for power user, nothing returns on either case.

Scenario Two: every body broke but "power user" working.

Changed srtSQL Format to 'mm/dd/yy'

Same test data entered as above, and same value under break mode as above.

I have also tried
dim dtDate as date
dtDate=CDate(txtDate) then pass dtDate to sql, but nothing has changed.

I hope I have provided enough details.

Thanks!

 

[Douglas J. Steele]

You may have misinterpretted me. If you type a date into a text box, Access
is going to interpret it as a text string. If you want Access to convert it
to a date, you surround it with # delimiters (as you have).

You appear to be trying to do two different things simultaneously: have
Access interpret your text input (the contents of txtDate) as a Date AND
convert the date field in your table to a string. If you want to compare a
string to a string, you don't want the # delimiters. If you want to compare
a date to a date, you don't want the Format function.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok, I kind of understand what you are saying, but...
A text box should have the data type started as text, I was thinking maybe
the format property will force it into date type if I used a valid
"mm/dd/yy". Your explaination sounds to me that if I typed a date in a
textbox, then access will treat that as date type.
I will take format out and test again.
Thanks!

You do not need to (nor want to) apply the Format function to the field
in
the table. The Format function changes whatever's in it to a string, so
the
best case is that you're forcing Access to convert the date field to a
string and then back to a date.

It's just the value of txtDate that you have to worry about being in
mm/dd/yyyy format. Dates aren't actually stored with any particular
format:
they're 8 byte floating point numbers, where the integer portion
represents
the date as the number of days relative to 30 Dec, 1899, and the decimal
portion represents the time as a fraction of a day.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi!
I have run into a wired situation where an ac97 report shows data from
one
machine but no from others, and when I made a change, other machines
fetched
the report fine but not the one in question.

I have the solution, depending who is using, the code adjusts itself.
It
is
ugly, and I would like to find out why it's acting this way.

First, the mdb file is on a network drive. Users are running directly
against it, and there is no concurrency issue.

Here are the other details:
TxtDate is a text box on a form, it has mm/dd/yy as its format.
User inputs a date then click a button.

The button click event dynamically builds a query. Part of it looks
like
this:
strSQL = strSQL + " WHERE (((Format([EnteredDate], 'mm/dd/yyyy')) = #"
&
txtDate & "#)

And here are some of the results under two scenarios:

Scenario One: every body's working but not this particular "power
user".*

(Note: every body = two win 2k machines and one xp pro v2002 sp2
power user= xp pro v2002 sp2)

data entered on form: 03/31/06
txtDate value seen under break: 3/31/2006 (everyone), 03/31/06 (power
user)
srtSQL Format as 'm/dd/yyyy'
returned data as expected for everyone but "power user".

data entered on form: 12/31/05
txtDate value seen under break: 12/31/2005 (every one), 12/31/05 (power
user)
srtSQL Format as 'm/dd/yyyy'
returned data as expected for everyone but "power user".

However, there is a sub-scenario, if I changed srtSQL Format to
'mm/dd/yyyy'
instead of 'm/dd/yyyy', for every one, only 12/31/05 returns data,
03/31/06
does not, and for power user, nothing returns on either case.

Scenario Two: every body broke but "power user" working.

Changed srtSQL Format to 'mm/dd/yy'

Same test data entered as above, and same value under break mode as
above.

I have also tried
dim dtDate as date
dtDate=CDate(txtDate) then pass dtDate to sql, but nothing has changed.

I hope I have provided enough details.

Thanks!

 

[Guest]

First off Steele is right do not format anything just let the system comppare
dates to dates. You SHOULD test the entered text first for a valid date - use
isdate(txtDate).

As to why it is happening - a guess would be the power user has changed the
default formatting of dates in Windows - Control Panel/Regional Options/Date
Tab.

You may have misinterpretted me. If you type a date into a text box, Access
is going to interpret it as a text string. If you want Access to convert it
to a date, you surround it with # delimiters (as you have).

You appear to be trying to do two different things simultaneously: have
Access interpret your text input (the contents of txtDate) as a Date AND
convert the date field in your table to a string. If you want to compare a
string to a string, you don't want the # delimiters. If you want to compare
a date to a date, you don't want the Format function.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok, I kind of understand what you are saying, but...
A text box should have the data type started as text, I was thinking maybe
the format property will force it into date type if I used a valid
"mm/dd/yy". Your explaination sounds to me that if I typed a date in a
textbox, then access will treat that as date type.
I will take format out and test again.
Thanks!

You do not need to (nor want to) apply the Format function to the field
in
the table. The Format function changes whatever's in it to a string, so
the
best case is that you're forcing Access to convert the date field to a
string and then back to a date.

It's just the value of txtDate that you have to worry about being in
mm/dd/yyyy format. Dates aren't actually stored with any particular
format:
they're 8 byte floating point numbers, where the integer portion
represents
the date as the number of days relative to 30 Dec, 1899, and the decimal
portion represents the time as a fraction of a day.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi!
I have run into a wired situation where an ac97 report shows data from
one
machine but no from others, and when I made a change, other machines
fetched
the report fine but not the one in question.

I have the solution, depending who is using, the code adjusts itself.
It
is
ugly, and I would like to find out why it's acting this way.

First, the mdb file is on a network drive. Users are running directly
against it, and there is no concurrency issue.

Here are the other details:
TxtDate is a text box on a form, it has mm/dd/yy as its format.
User inputs a date then click a button.

The button click event dynamically builds a query. Part of it looks
like
this:
strSQL = strSQL + " WHERE (((Format([EnteredDate], 'mm/dd/yyyy')) = #"
&
txtDate & "#)

And here are some of the results under two scenarios:

Scenario One: every body's working but not this particular "power
user".*

(Note: every body = two win 2k machines and one xp pro v2002 sp2
power user= xp pro v2002 sp2)

data entered on form: 03/31/06
txtDate value seen under break: 3/31/2006 (everyone), 03/31/06 (power
user)
srtSQL Format as 'm/dd/yyyy'
returned data as expected for everyone but "power user".

data entered on form: 12/31/05
txtDate value seen under break: 12/31/2005 (every one), 12/31/05 (power
user)
srtSQL Format as 'm/dd/yyyy'
returned data as expected for everyone but "power user".

However, there is a sub-scenario, if I changed srtSQL Format to
'mm/dd/yyyy'
instead of 'm/dd/yyyy', for every one, only 12/31/05 returns data,
03/31/06
does not, and for power user, nothing returns on either case.

Scenario Two: every body broke but "power user" working.

Changed srtSQL Format to 'mm/dd/yy'

Same test data entered as above, and same value under break mode as
above.

I have also tried
dim dtDate as date
dtDate=CDate(txtDate) then pass dtDate to sql, but nothing has changed.

I hope I have provided enough details.

Thanks!

 

[Tim Ferguson]

TxtDate is a text box on a form, it has mm/dd/yy as its format.
User inputs a date then click a button.

My favourite TextBox validation routine goes something like this:

private sub tb_BeforeUpdate(Cancel as integer)

' change this to whatever you want to pin your users to...
' could use General or Short Date if you prefer to trust
' your user's regional settings
private const forceDateFormat = "dd/mm/yyyy"

if isnull(tb.value) then
' change this if it's a required input
Cancel = False

elseif not isDate(tb.Value) then
msgbox "Garbage Input, not even a date"
Cancel = True

elseif tb.Value <> Format(CDate(tb.Value),forceDateFormat) then
' convert to a date and back again; if it's different then
' the user entered a valid date in the wrong format
msgbox "Please enter date in proper format"
Cancel = True

else
' everything's okay
Cancel = False

Endif

end sub


Hope that helps


Tim F

 

[Nikos Yannacopoulos]

Hommer, Doug, Kim,

Pardon me for jumping in, I just happen to live in Europe and have some
(nasty) experience with non-US date formats.


Hommer,

To begin with, take on the advice already offered, it's good advice.

Furthermore, my guess is you are not in the US, right? Chances are
different machines have different date formats in regional settings,
which is a recipe for disaster with Access and dates. I have found the
only trouble-free way to overcome this is (a) always use date/time
fields only for dates, never text or whatever, (b) never let the user
type in a date, be it for data entry or filtering; always use an ActiveX
calendar or month control instead, so as to avoid formatting issues and
misunderstandings, and (c) only ever apply formats on dates for display
purposes (in reports, and possibly forms, on display-only controls).

Some will argue there are problems with ActiveX controls (deployment,
versioning etc) but in my experience those are far easier managed than
users changing the regional settings on their machines.

HTH,
Nikos

 

[Douglas J. Steele]

You're right, Nikos. Being developed in the States, Access tends to be a tad
ignorant of other date formats than mm/dd/yyyy.

You might want to read Allen Browne's "International Dates in Access" at
http://allenbrowne.com/ser-36.html or what I had in my September 2003 Access
Answers column for Pinnacle Publication's "Smart Access" newsletter. (The
column and accompanying database can be downloaded at
http://www.accessmvp.com/djsteele/SmartAccess.html)

 

[Guest]

Thank you everybody.
Kim,
Your guest is right. The user's regional settings/options has short date
format as mm/dd/yy while mine and others have m/dd/yyyy.

Tim,
Great validation routine!

Nikos,
We are in the US. Nevertheless, your advice is well taken.

Again thanks for everybody.

hommer

 

[Nikos Yannacopoulos]

Thanks for this, Doug!

 

[Guest]

I hate to keep bothering your folks, but my issue is still unresolved.
Previously, I did not understand what is happening behind the scene, and have
a temporally “patch†to make it work.

Now I understood the intricacy of Access’ handling of date, thanks to all
of your responses, I am looking for a better solution.

My issue actually evolved after certain things have been cleared out of the
way. Now how you enter the date and how their regional setting is, are all
under control. (I went out to Mr. Steele’s site and downloaded the SQLDate90
function).

Now my issue is on the left hand side of the equation as listed below. How
to take out the time parts of my date data?

strSQL = " WHERE (((Format([EnteredDate], 'mm/dd/yyyy')) = #" & txtDate & "#)

First, I need to take out the time parts in JET syntax instead of in T-SQL,
so those dateadd, datediff are not available.
Secondly, I cannot use Format() function in Access for the task, because it
depends on client machine's locale date setting in interpreting how to carry
out the function.

How do I make access ignore the time parts stored in sql server without
using format()?

hommer

 

[Douglas J. Steele]

Using that Format statement will remove the time from the string that's
returned.

However, you're going to be trying to compare "04/17/2006" to
"#04/17/2006#", so you're never going to have any matches.

 

[Tim Ferguson]

Now my issue is on the left hand side of the equation as listed below.
How to take out the time parts of my date data?

strSQL = " WHERE (((Format([EnteredDate], 'mm/dd/yyyy')) = #" &
txtDate & "#)

Everything you were being advised about upthread runs against this.

*Don't compare text; compare dates*.

Best way to do this is a parameterised query:

PARAMETERS ActiveDate DATETIME;
SELECT This, That, TheOther FROM OverThere
WHERE EnteredDate = ActiveDate
ORDER BY This;


and then

With QueryDefs("MyQuery")
' actually you need much much better error trapping here
' but you get the picture
.Parameters("ActiveDate") = CDate(txtDate.Value)
' any other parameters go here if necessary

' now get the data
Set rst = .OpenRecordset(dbOpenSnapshot, dbForwardOnly)

End With


If you have to use SQL insertion, then at least use Jet date literals
properly:

' this is mandatory!! Regardless of where in the world you are
Const jetDateFormat = "\#yyyy\-mm\-dd\#"
' if you prefer you can use a USA date too like "\#mm\/dd\/yyyy\#"

' now set up the command
jetCriterion = "WHERE EnteredDate = " & _
Format(CDate(txtDate.Value), jetDateFormat)

' and see it
Debug.Assert vbYes=MsgBox(jetCriterion, "Is This OK?", vbYesNo)

' and then pass it off to whatever...



Just remember what you have been told here:

*Don't compare text; compare dates*.


Hope that helps


Tim F