Aarch,i hate regional settings

[Nirious]

I'm going nuts here.

I have a sqlstring in code that acts as a search function.
I can search for all and everything, but there is something wrong with my date-search, and I can't get it right.

This is what I have:

SELECT bestelbonnr, klantnr, datum, 'Leveringsbon' AS EXPR1 FROM Bestelbonnen WHERE (((Bestelbonnen.datum)>=# & Format("4/11/2004", "dd\/mm\/yyyy") & "#))"

I have the following dates in 'Bestelbonnen'. 23/10/2004, 28/10/2004, 4/11/2004

When I run the code above, It results in giving me everything!
When I change the date to "10/11/2004" it still gives me everything!
But when I change it to "11/11/2004" it showsme nothing!


The stupid thing always thinks its working with mm/dd/yyyy iso dd/mm/yyyy
Though the latter appeares to be set in the regional settings ??????

PLease help
Nirious

 

[fedupfedman]

I think you have to use the # character around a date in SQL to let the system know that it's a date.

e.g. #12/11/1995#

 

[fedupfedman]

Sorry,
I didn't see that you had the # characters because of the way your messae appeared on my screen.

 

[The_Doc_Man]

If what you sent was a true cut-and-paste, then you have a quoting problem after the first # sign.

 

[Rich]

Use the US date format, and why are your dates surrounded with quotes?

 

[Nirious]

Yeah sorry about that. The string I copy pasted came from different parts of my code. The querystring is built dynamically based on what the user wants to search for. the pieces I copy pasted don't really fit very well.

I tried it entering the dates in US format being mm/dd/yyyy. And all goes just fine now. Problem is, I don't need that format, I need dd/mm/yyyy

So let's say the user enters 29/05/2004, I would need to change it to 05/29/2004 to use it in the sql query. Is there a function to do that, or do I need to do it myself?


BTW, is it a rule or something that sql date comparisons use the US format? Because the records that appear again use the dd/mm/yyyy format. So it's only the sql string itself that needs the US-format(mm/dd/yyy) here.

 

[ChrisO]

This should help…2. Wrong Formatting in Code

Regards,
Chris.

 

[john471]

That's a comprehensive explanation, ChrisO.

Having had troubles with this in the past, I always force dates into an "unambiguous" format; dd-mmm-yyyy. I presume Access or JET translates this into American when required for SQL purposes. Using dd-mmm-yyyy, any user would interpret the display as the same date. When my users enter dates into a form, I typically use the AfterUpdate event, to redisplay the interpretation in the above format, so there is no doubt around how their input has been interpreted.

In Nirious's case, I would use SQL as per the following, and this seems to be effective for me.

SELECT bestelbonnr, klantnr, datum, 'Leveringsbon' AS EXPR1 FROM Bestelbonnen WHERE (((Bestelbonnen.datum)>=#" & Format(dteUserInput, "dd-mmm-yyyy") & "#))"

IMHO the sooner the world starts using an unambiguous date format, the better. Ever eaten a frozen curried prawn dinner on the 1st of December when the useby date (shown as 2/12) made you think it was still fine, when it actually "expired" about 10 months before ?????? uke:

HTH

 

[ChrisO]

Yes the general idea is to not rely on what is set in regional settings but force the code into just what is correct.

Format using "dd-mmm-yyyy" might work but SQL strings require it in US Format i.e. "mm/dd/yyyy" or "mm-dd-yyyy".

The problem with the Format function is that Format "mm/dd/yyyy" uses the / as a replacement marker and proceeds to pick up whatever is set as the date separator in regional settings. Whatever the date separator is set to in regional settings is up to the gofingerpoker and the code could fail.

Using Format "mm\/dd\/yyyy", as Allen Browne suggests in the article, uses the \ to interpret the / as a literal as forces the Format function to bypass regional settings and return a /. It just so happens that, in the process, it also bypasses the efforts of the gofingerpokers.

Regards,
Chris.

 

[Rich]

)>=# & Format([your date field], "mm/dd/yyyy")

 

[ChrisO]

G’day Rich.

Allen Browne doesn’t stuff around.

Please build an SQL string using Format([your date field], "mm/dd/yyyy").
Test it.

Then go to regional settings and change the date separator to a . (period)
(Or change Local to French (Switzerland) or any of a number of other settings.)
Test it again.

Your code is in the hands of the gofingerpoker.

Regards,
Chris.

 

[Rich]

Yes, but I don't think Nirious is either French or Swiss, so there will be no finger poking

 

[ChrisO]

“Yes, but I don't think Nirious is either French or Swiss, so there will be no finger poking”

I’d almost put money on it or some other regional problem.

Hindsight could prove us both wrong.

Nirious does not state a Local.
The local does not need to be using just French (Switzerland) but could also be one of many settings. Or it could be simply some local gofingerpoker.

You should know this already because of this thread in which you brought me to task for a “slight exageration”… which I fixed.

If people read it all again they should see that it depends on context.
In the original thread, a link to which I posted above, you seemed to take it out of context.
Now you would seem to want this latest thread to be taken in context even though, at this stage, the context of Nirious is not known.

Unless the context is known for sure “Force Dates To US Format” and don’t rely on regional settings.

Regards,
Chris.

 

[Rich]

Have a beer there Chris, and chill out

 

[ChrisO]

I will.

 

[Rich]

throw a leg of lamb on the barbi too. It's too bloody cold here now to have one

 

[ChrisO]

Wouldn’t mind throwing a leg on barbi at the moment, not too cold here but hmmmm the rain on the roof…