?
Solved

Date Formats in BuildCriteria

Posted on 2005-03-08
8
Medium Priority
?
536 Views
Last Modified: 2007-12-19
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
Comment
Question by:James Bendall
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 54

Accepted Solution

by:
nico5038 earned 1000 total points
ID: 13484706
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
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 600 total points
ID: 13484810
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 13484894
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 11

Assisted Solution

by:Stephen_Perrett
Stephen_Perrett earned 400 total points
ID: 13484903
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
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 600 total points
ID: 13484948
'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
 
LVL 8

Author Comment

by:James Bendall
ID: 13484977
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
 
LVL 54

Expert Comment

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

Nic;o)
0
 
LVL 8

Author Comment

by:James Bendall
ID: 13487819
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question