[AccessD] Filtering with International Dates

Stuart McLachlan stuart at lexacorp.com.pg
Thu Sep 22 17:10:57 CDT 2011


CLng returns the wrong value if the data contains a time after midday (it rounds the day part 
up to the next day).

I always use  strSQL =  ...Datevalue('" & txtDate & "')....   


-- 
Stuart


On 22 Sep 2011 at 14:16, Charlotte Foust wrote:

> But a date isn't an integer of any length, so if you have times
> included in the date field, CLng would give you the wrong result.
> 
> Charlotte Foust
> 
> On Thu, Sep 22, 2011 at 2:08 PM, Steve Schapel <
> steve at datamanagementsolutions.biz> wrote:
> 
> > 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@**databaseadvisors.com<accessd-bounces at databaseadvis
> > ors.com>
> > [mailto:accessd-bounces@**databaseadvisors.com<accessd-bounces at datab
> > aseadvisors.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<http://datab
> >> aseadvisors.com/mailman/listinfo/accessd>
> >>
> >>
> >>
> >>
> >> Website:
> >> http://www.databaseadvisors.**com<http://www.databaseadvisors.com>
> >>
> >>
> >>
> >>
> >>
> >>  --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/**mailman/listinfo/accessd<http://databa
> > seadvisors.com/mailman/listinfo/accessd>
> >
> >
> > Website:
> > http://www.databaseadvisors.**com<http://www.databaseadvisors.com>
> >
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/**mailman/listinfo/accessd<http://databa
> > seadvisors.com/mailman/listinfo/accessd>
> >
> >
> > Website:
> > http://www.databaseadvisors.**com<http://www.databaseadvisors.com>
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/**mailman/listinfo/accessd<http://databa
> > seadvisors.com/mailman/listinfo/accessd>
> >
> >
> > Website:
> > http://www.databaseadvisors.**com<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