Steve Schapel
steve at datamanagementsolutions.biz
Thu Sep 22 20:46:46 CDT 2011
Sorry, Charlotte, on this occasion I have to disagree with you. "Assumptions" doesn't come into it at all. I don't make assumptions about this stuff. I know my applications. I know whether there will be a time component in a Date/Time field, or in a criteria entered in an unbound textbox. No assumption. And if there is or could be a time component, I also know whether and how I need to deal with it. The approach to date criteria that I have mentioned to Rocky here has never come back to haunt me ... and I have used it a lot - hundreds of times. There might be a application development style consideration here. Except in very exceptional circumstances, if there is a time value, I normally have it in a separate field. Very seldom do I have a date and time combined in a single field. So the issue you raise seldom arises for me anyway. Regards Steve -----Original Message----- From: Charlotte Foust Sent: Friday, September 23, 2011 1:25 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Filtering with International Dates One thing I learned the hard way is that there is ALWAYS a time component, even if it's midnight. Using it on a consistent basis is a good idea because assumptions can come back to haunt you. Charlotte Foust On Thu, Sep 22, 2011 at 2:36 PM, Steve Schapel < steve at datamanagementsolutions.biz> wrote: > That's correct, Chartlotte. If there is a time component, I use CDbl. > > Regards > Steve > > -----Original Message----- From: Charlotte Foust > Sent: Friday, September 23, 2011 9:16 AM > > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Filtering with International Dates > > 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 <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@**databaseadvi**sors.com<http://databaseadvisors.com> >> >> <accessd-bounces@**databaseadvisors.com<accessd-bounces at databaseadvisors.com> >> > >> [mailto:accessd-bounces@**data**baseadvisors.com<http://databaseadvisors.com> >> >> <accessd-**bounces at databaseadvisors.com<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<http://databaseadvisors.com/**mailman/listinfo/accessd> >>> >>> <http:**//databaseadvisors.com/**mailman/listinfo/accessd<http://databaseadvisors.com/mailman/listinfo/accessd> >>> >>> > >>> >>> >>> >>> >>> Website: http://www.databaseadvisors. >>> >>> ****com<http://www.**databaseadvisors.com<http://www.databaseadvisors.com> >>> >>> > >>> >>> >>> >>> >>> >>> -- >>> >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/****mailman/listinfo/accessd<http://databaseadvisors.com/**mailman/listinfo/accessd> >> >> <http:**//databaseadvisors.com/**mailman/listinfo/accessd<http://databaseadvisors.com/mailman/listinfo/accessd> >> >> > >> >> >> Website: http://www.databaseadvisors. >> >> ****com<http://www.**databaseadvisors.com<http://www.databaseadvisors.com> >> >> > >> >> >> >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/****mailman/listinfo/accessd<http://databaseadvisors.com/**mailman/listinfo/accessd> >> >> <http:**//databaseadvisors.com/**mailman/listinfo/accessd<http://databaseadvisors.com/mailman/listinfo/accessd> >> >> > >> >> >> Website: http://www.databaseadvisors. >> >> ****com<http://www.**databaseadvisors.com<http://www.databaseadvisors.com> >> >> > >> >> >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/****mailman/listinfo/accessd<http://databaseadvisors.com/**mailman/listinfo/accessd> >> >> <http:**//databaseadvisors.com/**mailman/listinfo/accessd<http://databaseadvisors.com/mailman/listinfo/accessd> >> >> > >> >> >> Website: http://www.databaseadvisors. >> >> ****com<http://www.**databaseadvisors.com<http://www.databaseadvisors.com> >> >> > >> >> >> >> -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/**mailman/listinfo/accessd<http://databaseadvisors.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://databaseadvisors.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