[AccessD] OT: A2203: Date Format Issues

Kath Pelletti kp at sdsonline.net
Thu Jul 17 20:31:01 CDT 2008


what an aussie thread..... both "yanks" and "mongrel" in the same 
message....    :)

kath


----- Original Message ----- 
From: "Darren D" <darren at activebilling.com.au>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Sent: Friday, July 18, 2008 11:26 AM
Subject: Re: [AccessD] A2203: Date Format Issues


> Howdy
>
> Funny I did try it and yes that's what I ended up doing
> I just used date serial to get the date 7 days ago in American format 
> mm/dd/yyyy
> and then passed that to the SQL. When I used date serial to get the date 
> (less 7
> days) in to dd/mm/yyyy (Aussie format) the sql looked lovely in 
> debug.print yet
> returned no results (when I knew there were some) when I applied it as the
> form's SQL
>
> When I copied the sql from the immediate window and pasted it into the 
> Query
> Designer in SQL mode - It pasted nice - Then I'd run it and get no results 
> then
> I'd look at it in Query design mode and the mongrel had reversed the date 
> and
> month right there in the query design window
>
> So in the immediate window I'd have
> select * from tblAccount where datecreated >= #11/07/2008#  <--pretty 
> herbal
>
> So I'd paste that into the SQL Query designer - it'd paste OK - then I'd 
> run it
> - get no results - even though I know there are some - then I'd look at it 
> in
> query design mode - see the month and date had been changed then look at 
> it
> again in SQL view and see something like
>
> select * from tblAccount where tblAccount.datecreated >= #7/11/2008#
> plus a few parenthesis - SO I just gave up and formatted the date like the 
> yanks
> <sigh>
>
> Many thanks
>
> Darren
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
> Sent: Friday, 18 July 2008 11:16 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] A2203: Date Format Issues
>
> Hi Darren,
>
> Did you try Drews suggestion?
>
>>From Access Help:
>
> '========================================================
> DateSerial, Day, Month, and Year Function Examples
>
> The following example uses the DateSerial function together with the Year,
> Month, and Day functions to return the number of days in a month. You can 
> pass
> either a date or a string to the DaysInMonth function.
>
> Function DaysInMonth(dteInput As Date) As Integer
>    Dim intDays As Integer
>
>    ' Add one month, subtract dates to find difference.
>    intDays = DateSerial(Year(dteInput), _
>        Month(dteInput) + 1, Day(dteInput)) _
>        DateSerial(Year(dteInput), _
>        Month(dteInput), Day(dteInput))
>    DaysInMonth = intDays
>    Debug.Print intDays
> End Function
> The following Sub procedure shows several different ways that you might 
> call the
> DaysInMonth function.
>
> Sub CallDaysInMonth()
>    Dim intDays As Integer
>    intDays = DaysInMonth(#4/1/96#)
>    intDays = DaysInMonth("4-1-96")
>    intDays = DaysInMonth("April 1, 1996")
> End Sub
> '================================================================
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Darren D
> Sent: Friday, 18 July 2008 11:08 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] A2203: Date Format Issues
>
>
> Thanks to all who responded
>
> I have edited my regional settings and still it fails
>
> I have had to capitulate and get the SQL built on the fly to have American 
> date
> formatting
>
> arrrrggghhh
>
> Darren
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
> Sent: Saturday, 12 July 2008 2:09 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] A2203: Date Format Issues
>
> Are you creating this SQL in code?  If so, use DateSerial to 'put' the
> date into the SQL and you'll never have a problem.
>
> Drew
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren D
> Sent: Friday, July 11, 2008 1:36 AM
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] A2203: Date Format Issues
>
> Hi Team
>
> I have a new machine - SO maybe this date question is a regional setting
> issue
>
>
>
> Anyhow - I am trying to show all accounts created in a db since a
> certain
> date
>
> When I debug the recordsource SQL that is supposed to 'feed' the form I
> see
>
>
>
> select * from tblAccount where DateCreated >= #4/07/2008#
>
>
>
> Excellent - Over here in Oz we want dd/mm/yyy date formatting
>
> So to me this SQL will show me all the accounts created since the 4th
> July
> 2008
>
> But it does not
>
> It ends up showing me all the accounts created since 7th April
>
>
>
> Arrrgggghhhh
>
>
>
> I have made all the neseccary changes to regional settings
>
>
>
> Help please
>
>
>
> Darren
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> The information contained in this transmission is intended only for the 
> person
> or entity to which it is addressed and may contain II-VI Proprietary 
> and/or
> II-VI Business Sensitive material. If you are not the intended recipient, 
> please
> contact the sender immediately and destroy the material in its entirety, 
> whether
> electronic or hard copy. You are notified that any review, retransmission,
> copying, disclosure, 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.
>
>
> --
> 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
>
> This email and any attachments may contain privileged and confidential
> information and are intended for the named addressee only. If you have 
> received
> this e-mail in error, please notify the sender and delete this e-mail
> immediately. Any confidentiality, privilege or copyright is not waived or 
> lost
> because this e-mail has been sent to you in error. It is your 
> responsibility to
> check this e-mail and any attachments for viruses.  No warranty is made 
> that
> this material is free from computer virus or any other defect or error. 
> Any
> loss/damage incurred by using this material is not the sender's 
> responsibility.
> The sender's entire liability will be limited to resupplying the material.
>
> -- 
> 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
> 





More information about the AccessD mailing list