Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

30/12/99 date problem

Discussion in 'Information Technology' started by Jon Lewis, Jul 28, 2006.

Tags:
  1. Jon Lewis

    Jon Lewis
    Expand Collapse
    Guest

    I know that the above represents a date of 0 as Access stores this type of
    data but why is the Date() function failing when used as follows?

    A data entry form has a textbox (locked = yes, enabled = no) with the
    default value set to "=Date()" (in the textbox control not the field) and
    Format "dd/mm/yy". The text box is bound to a field Date/Time data type,
    "dd/mm/yy" format.

    Works as it should most of the time but suddenly fails and displays 30/12/99
    then persits this on subequent new records.

    TIA
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Very strange behavior, Jon.

    The setup looks fine. Presumably the quotes around =Date() were just for the
    post and not in the property. The format should not be a problem (though I
    do prefer the yyyy.)

    One has to conclude that something else is going on here.
    Is there anything else in the form named Date?
    Perhaps a control? A field? A variable? A sub or function?

    Is there anything that could be assigning a value to the control?
    The BeforeInsert or Current event of the form?
    Or an event of another control?

    As always, you will want to make sure that the Name AutoCorrect boxes are
    unchecked under:
    Tools | Options | General

    A compact would be a good idea.

    When it fails, it might be worth checking your references (Tools |
    References from a code window.)

    Not sure any of those will pin it down for you, but it is a very odd one.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Jon Lewis" <jon.lewis@btinternet.com> wrote in message
    news:uvk5lDBrGHA.4864@TK2MSFTNGP03.phx.gbl...
    >I know that the above represents a date of 0 as Access stores this type of
    >data but why is the Date() function failing when used as follows?
    >
    > A data entry form has a textbox (locked = yes, enabled = no) with the
    > default value set to "=Date()" (in the textbox control not the field) and
    > Format "dd/mm/yy". The text box is bound to a field Date/Time data type,
    > "dd/mm/yy" format.
    >
    > Works as it should most of the time but suddenly fails and displays
    > 30/12/99 then persits this on subequent new records.
    >
    > TIA
     
  4. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    Its 30 December 1899 which is day-zero in Access in fact. I suspect the
    problem in your case might be do to with the fact that the DefaultValue
    property of a control is a string expression regardless of the data type in
    question. The Date() function returns the current date formatted in the
    local short date format. What I think might be happening here is that the
    date in this format is being interpreted as an arithmetical expression, so
    today (in European format) would be the arithmetical expression 20/7/6 which
    = 0.476190476190476. This represents a date/time value of 30 December 1899
    11:25:43, which when formatted as a short date is 30/12/99.

    Try setting the default value property to Format(Date(),"dd/mm/yy")

    This will return a string expression so should set it correctly.

    If setting the DefaultValue property of a control in code it should always
    be wrapped in literal quotes. A common requirement is to set the
    DefaultValue for a date to the value entered in the last record entered since
    the form was opened. This can be done in the form's AfterInsert event
    procedure like so:

    Me.txtDate.DefautValue = """" & Me.txtDate & """"

    If the literal quotes were omitted the same error as you are getting would
    arise.

    Ken Sheridan
    Stafford, England

    "Jon Lewis" wrote:

    > I know that the above represents a date of 0 as Access stores this type of
    > data but why is the Date() function failing when used as follows?
    >
    > A data entry form has a textbox (locked = yes, enabled = no) with the
    > default value set to "=Date()" (in the textbox control not the field) and
    > Format "dd/mm/yy". The text box is bound to a field Date/Time data type,
    > "dd/mm/yy" format.
    >
    > Works as it should most of the time but suddenly fails and displays 30/12/99
    > then persits this on subequent new records.
    >
    > TIA
    >
    >
    >
     
  5. Jon Lewis

    Jon Lewis
    Expand Collapse
    Guest

    Very strange indeed Allen!

    There IS other code that assigns a value to the control:
    Me.txtDateAdded.DefaultValue = "" and Me.txtDateAdded.DefaultValue = Date in
    the Filter and ApplyFilter events of the Form (it's a data edit as well as a
    data entry form) during a customised filter by form routine to remove the
    default Date() from the control as it's not used as a filter field and in
    fact that's what's causing the problem (should have deduced that before!).
    The 30/12/99 default only occurs after Filter by Form has been used.

    Stupid mistake! Should be Me.txtDateAdded.DefaultValue = "=Date()"

    Thanks for the help


    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:O2Ld0bBrGHA.2180@TK2MSFTNGP05.phx.gbl...
    > Very strange behavior, Jon.
    >
    > The setup looks fine. Presumably the quotes around =Date() were just for
    > the post and not in the property. The format should not be a problem
    > (though I do prefer the yyyy.)
    >
    > One has to conclude that something else is going on here.
    > Is there anything else in the form named Date?
    > Perhaps a control? A field? A variable? A sub or function?
    >
    > Is there anything that could be assigning a value to the control?
    > The BeforeInsert or Current event of the form?
    > Or an event of another control?
    >
    > As always, you will want to make sure that the Name AutoCorrect boxes are
    > unchecked under:
    > Tools | Options | General
    >
    > A compact would be a good idea.
    >
    > When it fails, it might be worth checking your references (Tools |
    > References from a code window.)
    >
    > Not sure any of those will pin it down for you, but it is a very odd one.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Jon Lewis" <jon.lewis@btinternet.com> wrote in message
    > news:uvk5lDBrGHA.4864@TK2MSFTNGP03.phx.gbl...
    >>I know that the above represents a date of 0 as Access stores this type of
    >>data but why is the Date() function failing when used as follows?
    >>
    >> A data entry form has a textbox (locked = yes, enabled = no) with the
    >> default value set to "=Date()" (in the textbox control not the field) and
    >> Format "dd/mm/yy". The text box is bound to a field Date/Time data
    >> type, "dd/mm/yy" format.
    >>
    >> Works as it should most of the time but suddenly fails and displays
    >> 30/12/99 then persits this on subequent new records.
    >>
    >> TIA

    >
    >
     
  6. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Jon,

    I am wondering about the use of the '=' in your Default Value setting.
    To be honest, I don't know how this works. Just that I have many times
    done similar functionailty, and never a problem, but I have always
    entered the Default Value simply like this...
    Date()
    No ""s, no =, just Date(). I would try it.

    --
    Steve Schapel, Microsoft Access MVP

    Jon Lewis wrote:
    > I know that the above represents a date of 0 as Access stores this type of
    > data but why is the Date() function failing when used as follows?
    >
    > A data entry form has a textbox (locked = yes, enabled = no) with the
    > default value set to "=Date()" (in the textbox control not the field) and
    > Format "dd/mm/yy". The text box is bound to a field Date/Time data type,
    > "dd/mm/yy" format.
    >
    > Works as it should most of the time but suddenly fails and displays 30/12/99
    > then persits this on subequent new records.
    >
    > TIA
    >
    >
     
  7. Jon Lewis

    Jon Lewis
    Expand Collapse
    Guest

    Hi Allen

    I replied to your post yesterday but have just noticed that for some reason
    my reply doesn't seem to be displayed.

    There was indeed some code re-setting the DefaultValue after removing it
    during a custom filter by form procedure.

    Me.txtDateAdded.DefaultValue = Date

    Should of course be:

    Me.txtDateAdded.DefaultValue = "=Date()"

    Should have spotted it myself.

    Thanks fo you help.
    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:O2Ld0bBrGHA.2180@TK2MSFTNGP05.phx.gbl...
    > Very strange behavior, Jon.
    >
    > The setup looks fine. Presumably the quotes around =Date() were just for
    > the post and not in the property. The format should not be a problem
    > (though I do prefer the yyyy.)
    >
    > One has to conclude that something else is going on here.
    > Is there anything else in the form named Date?
    > Perhaps a control? A field? A variable? A sub or function?
    >
    > Is there anything that could be assigning a value to the control?
    > The BeforeInsert or Current event of the form?
    > Or an event of another control?
    >
    > As always, you will want to make sure that the Name AutoCorrect boxes are
    > unchecked under:
    > Tools | Options | General
    >
    > A compact would be a good idea.
    >
    > When it fails, it might be worth checking your references (Tools |
    > References from a code window.)
    >
    > Not sure any of those will pin it down for you, but it is a very odd one.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Jon Lewis" <jon.lewis@btinternet.com> wrote in message
    > news:uvk5lDBrGHA.4864@TK2MSFTNGP03.phx.gbl...
    >>I know that the above represents a date of 0 as Access stores this type of
    >>data but why is the Date() function failing when used as follows?
    >>
    >> A data entry form has a textbox (locked = yes, enabled = no) with the
    >> default value set to "=Date()" (in the textbox control not the field) and
    >> Format "dd/mm/yy". The text box is bound to a field Date/Time data
    >> type, "dd/mm/yy" format.
    >>
    >> Works as it should most of the time but suddenly fails and displays
    >> 30/12/99 then persits this on subequent new records.
    >>
    >> TIA

    >
    >
     
  8. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Good news, Jon.

    I did see your earlier reply. However, I am not seeing many of my won posts
    at present, so you're not alone there.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Jon Lewis" <jon.lewis@btinternet.com> wrote in message
    news:OQWv1hKrGHA.4632@TK2MSFTNGP05.phx.gbl...
    > Hi Allen
    >
    > I replied to your post yesterday but have just noticed that for some
    > reason my reply doesn't seem to be displayed.
    >
    > There was indeed some code re-setting the DefaultValue after removing it
    > during a custom filter by form procedure.
    >
    > Me.txtDateAdded.DefaultValue = Date
    >
    > Should of course be:
    >
    > Me.txtDateAdded.DefaultValue = "=Date()"
    >
    > Should have spotted it myself.
    >
    > Thanks fo you help.
    > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    > news:O2Ld0bBrGHA.2180@TK2MSFTNGP05.phx.gbl...
    >> Very strange behavior, Jon.
    >>
    >> The setup looks fine. Presumably the quotes around =Date() were just for
    >> the post and not in the property. The format should not be a problem
    >> (though I do prefer the yyyy.)
    >>
    >> One has to conclude that something else is going on here.
    >> Is there anything else in the form named Date?
    >> Perhaps a control? A field? A variable? A sub or function?
    >>
    >> Is there anything that could be assigning a value to the control?
    >> The BeforeInsert or Current event of the form?
    >> Or an event of another control?
    >>
    >> As always, you will want to make sure that the Name AutoCorrect boxes are
    >> unchecked under:
    >> Tools | Options | General
    >>
    >> A compact would be a good idea.
    >>
    >> When it fails, it might be worth checking your references (Tools |
    >> References from a code window.)
    >>
    >> Not sure any of those will pin it down for you, but it is a very odd one.
     

Share This Page