Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inserting a date from a variable (2000 (9.0.4402 SR-1))

    I'm having a problem with the way dates are stored when inserting into a date field from a variable.

    For instance the date field has been set up as a UK format, dd/mm/yyyy, but when I use a variable which has been set to 05/07/2004, the date when viewed has been switched to 07/05/2004. I'm somewhat puzzled, because if I have a date which is say 19/06/2004 it inserts and views correctly. Also if I manually type this in as 06/19/2004 the field switches it round to what it should be - 19/06/2004.

    My regional settings in the control panel are set to English (United Kingdom)

    Does anyone have any pointers as to what's going on, or any settings I'm missing?

    Cheers,

    Niven

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting a date from a variable (2000 (9.0.4402 SR-1))

    This is due to (a) the way Windows handles dates and ([img]/forums/images/smilies/cool.gif[/img] the US-centricity of VBA.

    If you enter a date such as 19/06/2004 or 06/19/2004, it will always be interpreted correctly, whether you use UK or US date settings. If you enter the date the "wrong" way for your date system, Windows will correct it automatically, because there is only one way it can be seen as a valid date.

    However, if you enter a date such as 05/07/2004, it is valid both in US and in UK settings, but with a different date. If you enter a literal date in VBA code, you must use US format:

    Dim datSaleDate As Date
    datSaleDate = #07/05/2004#

    results in datSaleDate being set to the 5th of July. The same holds if you use dates in an SQL string:

    strSQL = "SELECT * FROM tblSales WHERE SaleDate = #07/05/2004#"

    If you have a date value on a form, it may be handled correctly in code, but it is wise to be explicit:

    strSQL = "SELECT * FROM tblSales WHERE SaleDate = #" & Format(Forms!frmInput!txtSaleDate, "mm/dd/yyyy") & "#"

  3. #3
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting a date from a variable (2000 (9.0.4402 SR-1))

    Cheers for that, thought I was going mad! <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>

    Niven

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •