[AccessD] International date format problem

Two different approaches:

DateValue()  results in Jet doing the conversion
Format() results in VBA  doing it before passing the SQL to JET. 

It's shorter.  Personally, I find:

...= DateValue('" & myData & "') ....

clearer than:

... = #" & Format(myData,"mmddyyyy") & "#...

-- 
Stuart

On 1 Aug 2012 at 20:31, Rocky Smolin wrote:

> Better than Format?
> 
> R 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
> Sent: 01 August 2012 17:06
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] International date format problem
> 
> I always use the following, it works on any PC regardless of what their
> local date format is.   
> 
> --
> Stuart
> 
> & "' AND DueDate = Datevalue('" _
> & rstPODetail!fldPODDateRequired & "') AND PartNumber = '" _
> 
> On 1 Aug 2012 at 16:06, Rocky Smolin wrote:
> 
> > Dear List:
> >  
> > I have the following line which works fine here with mm/dd/yyyy but 
> > does not work for my client in Mexico where the date format is dd/mm/yyyy:
> >  
> >     rstSupply.FindFirst "OrderNumber = '" & Me.txtPONumber & " - " & 
> > rstPODetail!fldPODLineNumber _
> >         & "' AND DueDate = #" _
> >         & rstPODetail!fldPODDateRequired & "# AND PartNumber = '" _
> >         & rstPODetail!fldPODPartNumber & "'"
> > 
> >  
> > where fldPODDateRequired is Date/Time data type.  This is A2K3 BTW.  
> > When date format is set in Windows to dd/mm/yyyy 
> > rstSupply.NoMatch=True, when date format is mm/dd/yyyy 
> > rstSupply.NoMatch = false (finds the record)
> >  
> > I think I may have this problem in other places in the code.  Is there 
> > a general method to avoid this problem?
> >  
> > MTIA
> >  
> > Rocky Smolin
> > Beach Access Software
> > 858-259-4334
> > www.bchacc.com <http://www.bchacc.com/> www.e-z-mrp.com 
> > <http://www.e-z-mrp.com/>
> > Skype: rocky.smolin
> >  
> > --
> > 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
>