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
>