[AccessD] A2003: Filter Property

Using Date Variables In A Criteria String
===========================
Certain aspects required to be kept in view
----------------------------------------------------

    Similar topic came up in one of the other discussion groups,  where it was pointed out by John Viescas, that when a date variable (in its raw state) is concatenated within a string, it will get embedded in a format governed by existing regional settings (for short date) on the particular computer.

    This can lead to unexpected results where the computer's system settings are not conforming to U.S. convention (mm-dd-yyyy).

    It was therefore concluded that as a foolproof measure for consistent results, the date variable should first be converted into a non-ambiguous string format (or into U.S style format), before being concatenated with the main statement. 

    An example is given below (TDate is a date type field in table T_Test, while StartDate & FinDate are variables holding date values)-

StrSQL = "DELETE * FROM T_Test"
StrSQL = StrSQL & " WHERE TDate Between  #" & _
                 Format(StartDate, "dd-mmm-yyyy") & "#"
StrSQL = StrSQL & " AND #" & _
                 Format(FinDate, "dd-mmm-yyyy") & "#;"

A.D.Tejpal
--------------

  ----- Original Message ----- 
  From: Darren DICK 
  To: 'Access Developers discussion and problem solving' 
  Sent: Friday, November 12, 2004 07:15
  Subject: RE: [AccessD] A2003: Filter Property


  Oops worked it out
  It's

  Me.Filter = "DateOfBilling >= DateValue('" & Me.txtStartDate & "')" _
   & "and DateOfBilling <= DateValue('" & Me.txtEndDate & "')"

  Many thanks y'all

  See ya

  Darren
  Stuart --- you da man !!!!!!!!!!!!1
   

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
  Sent: Friday, 12 November 2004 11:48 AM
  To: Access Developers discussion and problemsolving
  Subject: Re: [AccessD] A2003: Filter Property

  On 12 Nov 2004 at 11:32, Darren DICK wrote:

  > Hello all
  > The help wasn't very helpful
  > I want to apply a filter to a form using criteria from other controls 
  > like Me.Filter = "DateOfBilling = " & Me.txtStartDate Me.FilterOn = 
  > true
  >  
  >  
  > It doesn't seem to work (IE It returns no results) But I have about 20 
  > records where about 6 of those records have the same DateOfBilling 
  > date as I am typing into txtStartDate field
  >  
  > So why don't I see those 6 returned from the 20??
  >  
  Good old Internationalisation and date display formats strikes again?

  Try
  Me.Filter = "DateOfBilling = DateValue('" & Me.txtStartDate & "')"

  Stuart