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

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

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

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-04-07 : 15:43:10

Hi! This seems so simple, but I have yet found an answer.
It is the same old date/time data type stored in sql server. All the users care is to query base on the date part.
In this particular insidance, I have to do the comparison on the client(Access) side, so all those convert(), DateAdd() Tsql functions
are out of reach.

Here are the details:
The txtDate has mm/dd/yy as its format.
The partial sql looks like this:
strSQL = strSQL + " WHERE (((Format([EnteredDate], 'mm/dd/yyyy')) = #" & txtDate & "#)

And here are some of the results:
data entered on form: 03/31/06
txtDate value seen under break: 3/31/2006
returned: nothing, (should return data)

data entered on form: 12/31/05
txtDate value seen under break: 12/31/2005
returned data as expected.

changed to (((Format([EnteredDate], 'm/dd/yyyy')), One M instead of MM:
data entered on form: 03/31/06
txtDate value seen under break: 3/31/2006 (user A), 03/31/2006 (user B)
returned as expected( User A), returned: nothing (User B, should return data)

data entered on form: 12/31/05
txtDate value seen under break: 12/31/2005
returned data.

I hate to go => txtDate and <txtDate + 1 route just to avoid the time portion of it.

What other options do I have? I will try CDate(txtDate).

Thanks!

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-04-10 : 20:50:58

Yep - try CDate.....

That way you are comparing a date to a date, whereas before you were comparing a string to a string. That's a bit dangerous when you're playing with dates....

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-04-13 : 10:27:53

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

Here are some new results, and rewording of my original problem.

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, of cause, 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.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-04-13 : 10:44:38

Are you sure there aren't times being stored as well in your datetime column?

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-04-13 : 11:10:20

There are time parts for most of the records. Probably I should add date function to default setting for that field to take out the time parts when it is created.

Thanks!

Kristen
Test

22859 Posts

Posted - 2006-04-13 : 11:15:14

"to take out the time parts"

See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Getting%20the%20date%20portion%20of%20a%20datetime%20value

Kristen

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-04-17 : 11:18:33

Kristen,

I have used those tips under your link. They are great! But in this case I haven't found my answer there.

First, I need to take out the time parts in JET syntax instead of in T-SQL, as I have stated on my initial post. My query has to run under Jet because it links to many other tables that are not in the same sql server database, and sql linked servers is hard to configure because of different collations.

Secondly, I cannot use Format() function in Access for the task! Because it depends on client machine's lacale date setting, and I have no control over how my users set their local date.

I have set one of the date field's default to (dateadd(day,datediff(day,0,getdate()),0)), to prevent the time parts from getting into the table on the first place. BTW, this tip might came from your link, which I have to give full credit to it.

Anyway, as I am still working on the issue, any suggestion is welcome.

Hommer

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-18 : 01:05:39

In Access to omit time part use this

dateadd("d",datediff("d",0,datecol),0)

If you want to use current date then
dateadd("d",datediff("d",0,now()),0)

Madhivanan

Failing to plan is Planning to fail

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-04-18 : 09:39:35

Thank you madhivanan!

I was totally wrong of saying that Access doesn't have dateadd and datediff. I guess I just don't know how to put them together in the way you did, so I did not specifically look out for them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-18 : 09:47:50

Which version of Access do you use?

Madhivanan

Failing to plan is Planning to fail

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-04-18 : 10:56:17

I have 97, 2000, and 2003. This particular app is in 97.

Kristen
Test

22859 Posts

Posted - 2006-04-19 : 01:03:06

"in this case I haven't found my answer there"

That's because I'm a Muppet and hadn't realised I was posting in the access forum - sorry!

Kristen