[AccessD] Un-American Date Filter

Stuart McLachlan stuart at lexacorp.com.pg
Thu Mar 31 18:38:43 CDT 2011


Or you can replace your entire code example with:

 gstrSQL = "SELECT" &  _
                  " pk_lngFYPID" &  _
                  " FROM ITPMO_tblCalander_FYP" &  _
                  " INNER JOIN ITPMO_tblCalander_All" &  _
                   " ON (pk_lngFYPID = fk_lngFYPID)" &  _
                   " WHERE dteCalDate =  Date()" 

or if you want a variable date:

dteMyDate = Date()
gstrSQL = "SELECT" &  _
                  " pk_lngFYPID" &  _
                  " FROM ITPMO_tblCalander_FYP" &  _
                  " INNER JOIN ITPMO_tblCalander_All" &  _
                   " ON (pk_lngFYPID = fk_lngFYPID)" &  _
                   " WHERE dteCalDate =  DateValue('" & dteMyDate & "')" 


On 1 Apr 2011 at 10:20, Darryl Collins wrote:

> 
> ______________________________________________________________________
> _________________
> 
> Note: This e-mail is subject to the disclaimer contained at the bottom
> of this message.
> ______________________________________________________________________
> _________________
> 
> 
> Rocky,
> 
> Here is an example of how I use it.  If find be forcing all dates to
> align like this you get around the regional issues.
> 
> '=========================================
> 
> iY = Format(Now(), "yyyy")
> iM = Format(Now(), "mm")
> iD = Format(Now(), "dd")
> 
> dteNow = DateSerial(iY, iM, iD)
> 
> gstrSQL = vbnullstring
> gstrSQL = gstrSQL & "SELECT"
> gstrSQL = gstrSQL & " pk_lngFYPID"
> gstrSQL = gstrSQL & " FROM ITPMO_tblCalander_FYP"
> gstrSQL = gstrSQL & " INNER JOIN ITPMO_tblCalander_All"
> gstrSQL = gstrSQL & " ON (pk_lngFYPID = fk_lngFYPID)"
> gstrSQL = gstrSQL & " WHERE (((dteCalDate) = #" & dteNow & "#))" 
> 
> ' do what ever here with gstrSQL
> 
> '=========================================
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky
> Smolin Sent: Friday, 1 April 2011 10:06 AM To: 'Access Developers
> discussion and problem solving' Subject: Re: [AccessD] Un-American
> Date Filter
> 
> Shamil:
> 
> I'm not using Date Serial because the date is already formatted in the
> text box.  Is there a reason to parse it out and then use Date Serial?
> 
> Rocky
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
> Salakhetdinov Sent: Thursday, March 31, 2011 3:42 PM To: 'Access
> Developers discussion and problem solving' Subject: Re: [AccessD]
> Un-American Date Filter
> 
> Rocky --
> 
> <<<CDate(...) is "guessing" (sometimes wrongly)>>> I should have
> written that CDate(...) uses MS Windows system locale date format to
> convert String date representation to its Date value. And doing so it
> could produce results not expected/intended to be used by a developer
> as in this case:
> 
> ?Format(CDate(Format(DateSerial(2011,4,1),
> "dd/mm/yyyy")),"dd-mmm-yyyy") 01-apr-2011
> ?Format(CDate(Format(DateSerial(2011,4,1), "mm/dd/yyyy")),
> "dd-mmm-yyyy") 04-jan-2011
> 
> when system locale date format was 'dd/mm/yyyy' and so first result
> was correct while the second... was correct also ... but didn't
> conform developer's intention as they got January 4th, 2011 instead of
> April 1st, 2011...
> 
> And here system locale date format is also 'dd/mm/yyyy' and both
> results are "correct":
> 
> ?Format(CDate(Format(DateSerial(2011,4,21),
> "dd/mm/yyyy")),"dd-mmm-yyyy") 21-apr-2011
> ?Format(CDate(Format(DateSerial(2011,4,21), "mm/dd/yyyy")),
> "dd-mmm-yyyy") 21-apr-2011 
> 
> I quoted word correct because both results produce 21-April-2011 which
> is equal to the source date DateSerial(2011,4,21) but CDate(...)
> should better(?) produce runtime error for CDate("4/21/2011")  on
> systems with "dd/mm/yyyy" system locale date format... (and on your
> system with 'mm/dd/yyyy' system locale date format you'll get
> CDate('21/4/2011') - 21-April-2011, which is confusing if you don't
> know what happens "behind the curtains"
> 
> On your system with 'mm/dd/yyyy' date format set in the system locale
> you'll get opposite to my first sample results:
> 
> ?Format(CDate(Format(DateSerial(2011,4,1),
> "dd/mm/yyyy")),"dd-mmm-yyyy") 01-jan-2011
> ?Format(CDate(Format(DateSerial(2011,4,1), "mm/dd/yyyy")),
> "dd-mmm-yyyy") 04-apr-2011
> 
> And in MS Access Query Designer (QBE Grid) dates are presented using
> system locale date/time format as well as in MS Access form's
> textboxes if you use "Short Date" format....
> 
> Hope that helps.
> 
> Thank you.
> 
> --
> Shamil
> 
> -----Original Message-----
> From: Shamil Salakhetdinov [mailto:shamil at smsconsulting.spb.ru]
> Sent: 1 ?????? 2011 ?. 1:58
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [AccessD] Un-American Date Filter
> 
> Hi Rocky -- 
> 
> <<<
> IT'S WORKING!!!
> >>>
> Great!
> 
> <<<
> Oddly, the SQL still shows the date as dd/mm/yyyy.
> >>>
> Do you mean QBE (MS Access Query Designer/Query By Example) grid's
> criteria values presented in dd/mm/yyyy format while SQL expression
> string has date values in American mm/dd/yyyy format?
> 
> I haven't seen your code but from your description using
> 
> Format(Me.txtGEDueDate, "mm/dd/yyyy")
> 
> should be enough, while using
> 
> CDate(Format(Me.txtGEDueDate, "mm/dd/yyyy"))
> 
> could result in wrong SQL where criteria - CDate(...) is "guessing"
> (sometimes wrongly) what Date value should be while converting it from
> its string representation - have a look/try to type in Immediate
> window:
> 
> 
> ?Format(CDate(Format(DateSerial(2011,4,1),
> "dd/mm/yyyy")),"dd-mmm-yyyy") 01-apr-2011
> ?Format(CDate(Format(DateSerial(2011,4,1), "mm/dd/yyyy")),
> "dd-mmm-yyyy") 04-jan-2011
> 
> ?Format(CDate(Format(DateSerial(2011,4,21),
> "dd/mm/yyyy")),"dd-mmm-yyyy") 21-apr-2011
> ?Format(CDate(Format(DateSerial(2011,4,21), "mm/dd/yyyy")),
> "dd-mmm-yyyy") 21-apr-20111 
> 
> Of course date string used in SQL expression in American format should
> be enclosed in a pair of '#' symbols - #04/01/2011# ....
> 
> Thank you.
> 
> --
> Shamil
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky
> Smolin Sent: 31 ????? 2011 ?. 22:50 To: 'Access Developers discussion
> and problem solving' Subject: Re: [AccessD] Un-American Date Filter
> 
> Shamil:
> 
> I think that's what Doug was trying to tell me?  Anyway, IT'S
> WORKING!!!
> 
> I changed the code to CDate(Format(Me.txtGEDueDate, "mm/dd/yyyy")). 
> Oddly, the SQL still shows the date as dd/mm/yyyy.
> 
> Thank you one and all.
> 
> Rocky
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
> Salakhetdinov Sent: Thursday, March 31, 2011 11:40 AM To: 'Access
> Developers discussion and problem solving' Subject: Re: [AccessD]
> Un-American Date Filter
> 
> Rocky --
> 
> SQL "knows" American mm/dd/yyyy  dates only - your constructed in code
> SQL expression should be:
> 
> INSERT INTO tblKittingToMIS ( PartNumber, OrderNumber, DueDate,
> QuantityDue ) SELECT tblDemand.PartNumber, tblDemand.OrderNumber,
> tblDemand.DueDate, tblDemand.QuantityDue FROM tblDemand  WHERE DueDate
> >= #03/31/2011# AND DueDate <= #04/07/2011#
> 
> --
> Shamil
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky
> Smolin Sent: 31 ????? 2011 ?. 21:56 To: 'Access Developers discussion
> and problem solving' Subject: [AccessD] Un-American Date Filter
> 
> Dear List:
> 
> I create a temp table using a SQL statement I construct in code to
> incorporate various filter - one of which is a date filter.  
> 
> Works well here but when I send it to the user in Bahrain who's
> regional setting is (I assume from the screen shot he sent) English
> U.K. where the date format is dd/mm/yyyy it fails.  I set my regional
> settings on my box to U.K. and sure enough it fails - returns all
> records with no date filtering.
> 
> Here's the SQL statement that creates the table:
> 
> INSERT INTO tblKittingToMIS ( PartNumber, OrderNumber, DueDate,
> QuantityDue ) SELECT tblDemand.PartNumber, tblDemand.OrderNumber,
> tblDemand.DueDate, tblDemand.QuantityDue FROM tblDemand  WHERE DueDate
> >= #31/03/2011# AND DueDate <= #07/04/2011#
> 
> which is supposed to filter out any record in tblDemand where the Due
> Date is outside of the range March 31 to April 7.  But it don't.
> 
> When I look in tblDemand, the dates are displayed properly as
> dd/mm/yyyy. 
> 
> Why doesn't this work?  Should I be using some kind of different
> syntax for this filter?
> 
> MTIA
> 
> 
> Rocky Smolin
> 
> Beach Access Software
> 
> 858-259-4334
> 
> Skype: rocky.smolin
> 
> www.e-z-mrp.com <http://www.e-z-mrp.com/> 
> 
> www.bchacc.com <http://www.bchacc.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
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> ______________________________________________________________________
> _________________
> 
> The information transmitted in this message and its attachments (if
> any) is intended only for the person or entity to which it is
> addressed. The message may contain confidential and/or privileged
> material. Any review, retransmission, dissemination or other use of,
> or taking of any action in reliance upon this information, by persons
> or entities other than the intended recipient is prohibited.
> 
> If you have received this in error, please contact the sender and
> delete this e-mail and associated material from any computer.
> 
> The intended recipient of this e-mail may only use, reproduce,
> disclose or distribute the information contained in this e-mail and
> any attached files, with the permission of the sender.
> 
> This message has been scanned for viruses.
> ______________________________________________________________________
> _________________
> 
> -- 
> 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