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.