Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 542
  • Last Modified:

Date Formats in BuildCriteria

Dear Experts

I'm using the BuildCriteria method to build SQL WHERE clauses for filtering forms, based on criteria entered in textboxes.  My system and my client's system are both set up with UK regional settings (Locale and Date Formatting).  This is what happens:

1) On my system it works fine:
I enter 'Between 1/7/2004 And 1/8/2004'
BuildCriteria returns 'Between #07/01/2004# And #08/01/2004#' (interprets the dates as UK dates - dd/mm/yyyy - and puts them into the SQL as US dates - mm/dd/yyyy)

2) On the user's system however:
He enters 'Between 1/7/2004 And 1/8/2004'
BuildCriteria returns 'Between #01/07/2004# And #01/08/2004#' (interprets the dates as US dates and puts them into the SQL)

I'm currently assuming that my user's regional settings are somehow corrupted (perhaps a factory-default US setting is overriding the UK setting in his profile).  His IT support people are looking into this possibility, but I'd be very interested if anyone else has an alternative suggestion.

Thanks

James
0
James Bendall
Asked:
James Bendall
  • 2
  • 2
  • 2
  • +2
4 Solutions
 
nico5038Commented:
I always use the format statement to be sure it works like:
'Between format(fieldname_start,"yyyy/mm/dd") And format(fieldname_end,"yyyy/mm/dd")'

Nic;o)
0
 
peter57rCommented:
Hi jb79uk,
 I would have the same suspicions as you in this situation.
What happens if he types the 'Between 1/7/2004 And 1/8/2004 ' directly into a query criteria cell?  Does it get interpreted as UK format there?


Pete
0
 
Kelvin SparksCommented:
Code always treats dates in US format unless you tell it otherwise (using the format statement). This means that any value you pass from a control that is in dd/mm/yyyy is interpreted as mm/dd/yyyy. So when you enter 01/08/2005 for 1 August 2005, VBA will read it as 8 January 2005.

I always encase dates in VBA in the format statement - and generally ensure it is totally foolproof by using Format(me.controlname,"dd-mmmm-yyyy"). VBA can only intrep that as 1-August-2005.

MS knowledge base article http://support.microsoft.com/default.aspx?scid=kb;en-us;210069 will help explain
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Stephen_PerrettCommented:
Hi, I've found the same situation and it seems to occur quite often. I think it is hardware related but can't say for sure. I have two different computers and the interpretation is one way on one and the other on the other. And I think that this occurs consistently even when booted on different hard drives. The best way around it is as Nico suggests.

Steve
0
 
peter57rCommented:
'Code always treats dates in US format unless you tell it otherwise '

Using Buildcriteria counts as telling it otherwise.  Buildcriteria uses regional settings to interpret the date entry (well it should and does on all my computers which have UK settings).

Example:
?buildcriteria("orderdate",dbdate,"between 01/02/2003 and 03/04/2004")
orderdate Between #2/1/2003# And #4/3/2004#

Pete

0
 
James BendallAuthor Commented:
Thanks everyone for some very useful comments.  I'm still waiting for feedback from the user's IT support re their environment and will get back to you when I know more.  As per Pete and Steve's comments I think there are system issues behind this, but if they can't be solved I will need to work around them..

I have tried explicit formatting of the dates, e.g. dd-mmm-yyyy (01-Feb-2004), but it hasn't helped.

Thanks again, I will come back to this one later for points-splitting

James

0
 
nico5038Commented:
Only the yyyy/mm/dd format will be 100% as there's no day/month switch possible that way.

Nic;o)
0
 
James BendallAuthor Commented:
Thanks for all comments.  In the end it turned out that they were using the database via Citrix and their Citrix accounts had US regional settings.  Doh!

Still, I've got plenty of useful info here and will split points accordingly.

Thanks

James
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now