asked on

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

ASKER CERTIFIED SOLUTION

nico5038

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kelvin Sparks

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

SOLUTION

Stephen_Perrett

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

James Bendall

ASKER

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

nico5038

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

Nic;o)

James Bendall

ASKER

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