[AccessD] Filtering with International Dates

Steve Schapel steve at datamanagementsolutions.biz
Thu Sep 22 17:56:35 CDT 2011


Yes, Rocky, it always works.

A proviso I think is that if you are using an unbound textbox to gather your 
date criteria, and then referring to that textbox in your code (Me.MyTextbox 
or Forms!MyForm!MyTextbox), the textbox's Format property should be set to a 
valid date format.

Yes, if you need to cater to a time component (in my experience this is very 
rare in the type of circumstance you described), then you need to make sure 
you write your code accordingly, but that is also very easy.

Regards
Steve

-----Original Message----- 
From: Rocky Smolin
Sent: Friday, September 23, 2011 10:25 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Filtering with International Dates

Cool.  That would seem to be pretty universal then regardless of source
format?

Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steve Schapel
Sent: Thursday, September 22, 2011 2:08 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Filtering with International Dates

Rocky

As a person in a non-US zone, I have always used the CLng function in such
circumstances.  Works great.

strSQL = strSQL & "fldPOPromisedDate <= " &
CLng(Forms!frmPOReport!txtLEPromisedDate)

Regards
Steve

-----Original Message-----
From: Rocky Smolin
Sent: Friday, September 23, 2011 7:27 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Filtering with International Dates

Is that better or more reliable or more general than

Format$(varDate, "\#mm\/dd\/yyyy\#")?

Rocky

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Thursday, September 22, 2011 11:50 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Filtering with International Dates

Here's what MS suggests:

Function MakeUSDate(DateIn As Variant) As String

    ' Do nothing if the value is not a date.

    If Not IsDate(DateIn) Then Exit Function

    ' Convert the date to a U.S. Date format.

    MakeUSDate = "#" & Month(DateIn) & "/" & Day(DateIn) & "/" &
Year(DateIn) & "#"

End Function

I would suggest that if you're using SQL Server as an alternative BE you
also have a function to return the proper date delimiter.

Charlotte Foust
On Thu, Sep 22, 2011 at 11:36 AM, Rocky Smolin
<rockysmolin at bchacc.com>wrote:

> Dear List:
>
> Because I'm old and can't remember (and the archives are down), I was
> told how to filter with dates so that if a user is using international
> dates it doesn't matter.
>
> The following code:
>
>    If Nz(Forms!frmPOReport!txtGEPromisedDate) <> "" Then
>        If Len(strSQL) <> 0 Then strSQL = strSQL & " AND "
>        strSQL = strSQL & "fldPOPromisedDate >= #" &
> Forms!frmPOReport!txtGEPromisedDate & "# "
>    End If
>
>    If Nz(Forms!frmPOReport!txtLEPromisedDate) <> "" Then
>        If Len(strSQL) <> 0 Then strSQL = strSQL & " AND "
>        strSQL = strSQL & "fldPOPromisedDate <= #" &
> Forms!frmPOReport!txtLEPromisedDate & "# "
>
> strSQL eventually ends up in Me.Filter.
>
> Works good in the USA.  :) Fails in Nicaragua. :(
>
> Was it CDate I was supposed to use?
>
> Sorry for the redux.
>
> TIA
>
> Rocky
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
>
>
> Website: http://www.databaseadvisors.com
>
>
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com 




More information about the AccessD mailing list