Welcome to SPN

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

Sign Up Now!

Easier way to update a field in a table

Discussion in 'Information Technology' started by JethroUK©, Nov 14, 2005.

  1. JethroUK©

    JethroUK©
    Expand Collapse
    Guest

    which is the easiest way to update a value in a table (that's not in the
    source)

    i need to store a single date value (but not in the forms record source)

    i understand i can only do this in a seperate table "stats"

    how might i do this?
     
  2. Loading...

    Similar Threads Forum Date
    Grammer / Vyakarn Gurbani Made Easier Language, Arts & Culture Aug 28, 2013
    Controversial Life is Easier Without Karma - a Discussion Hard Talk Aug 5, 2012
    Arts/Society Imitating Accents Makes Them Easier to Understand Language, Arts & Culture Dec 17, 2010
    Sikh News 'Process should be made easier' (The New Straits Times) Breaking News May 9, 2008
    Sikh News 'Process should be made easier' (The New Straits Times) Breaking News May 9, 2008

  3. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    Hi Jethro

    Execute a single update query statement. For example:

    Dim sSQL as string
    sSQL = "Update [mytable] set [mydatefield]=" _
    & Format( somedatevalue, "\#mm\/dd\/yyyy\#" ) _
    & " where <some criteria>;"
    CurrentDb.Execute sSQL, dbFailOnError

    The Format is important, as SQL required dates to be in US format and does
    not take any notice of Windows regional settings.

    --
    Good Luck!

    Graham Mandeno [Access MVP]
    Auckland, New Zealand

    "JethroUK©" <reply@the.board> wrote in message
    news:pNMdf.12475$c66.12350@newsfe3-gui.ntli.net...
    > which is the easiest way to update a value in a table (that's not in the
    > source)
    >
    > i need to store a single date value (but not in the forms record source)
    >
    > i understand i can only do this in a seperate table "stats"
    >
    > how might i do this?
    >
    >
     
  4. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Sun, 13 Nov 2005 19:50:07 GMT, "JethroUK©" <reply@the.board> wrote:

    >which is the easiest way to update a value in a table (that's not in the
    >source)
    >
    >i need to store a single date value (but not in the forms record source)
    >
    >i understand i can only do this in a seperate table "stats"
    >
    >how might i do this?


    I have no idea. You can see your table structure; you know what this
    value is, and what it's used for. We do not.

    Please give us a little help here, Jethro. What is this date? What is
    it the date OF? What does it relate to? What is your Form bound to,
    and how does the current record on the form relate to this date?

    John W. Vinson[MVP]
     
  5. JethroUK©

    JethroUK©
    Expand Collapse
    Guest

    i'll give that a whirl - i notice the sql doesn't specify any particular
    record - does this mean it would update a whole recordset in one go?

    my date table only has one record - but it could be useful for next part of
    the project which i just happen to be thinking about

    i'm sending out a mailmerge and was wondering how to update 'LetterSent'
    field to reflect this activity - i guess i could use same method?

    thanx

    "Graham Mandeno" <Graham.Mandeno@nomail.please> wrote in message
    news:uptu3dJ6FHA.2600@tk2msftngp13.phx.gbl...
    > Hi Jethro
    >
    > Execute a single update query statement. For example:
    >
    > Dim sSQL as string
    > sSQL = "Update [mytable] set [mydatefield]=" _
    > & Format( somedatevalue, "\#mm\/dd\/yyyy\#" ) _
    > & " where <some criteria>;"
    > CurrentDb.Execute sSQL, dbFailOnError
    >
    > The Format is important, as SQL required dates to be in US format and does
    > not take any notice of Windows regional settings.
    >
    > --
    > Good Luck!
    >
    > Graham Mandeno [Access MVP]
    > Auckland, New Zealand
    >
    > "JethroUK©" <reply@the.board> wrote in message
    > news:pNMdf.12475$c66.12350@newsfe3-gui.ntli.net...
    > > which is the easiest way to update a value in a table (that's not in the
    > > source)
    > >
    > > i need to store a single date value (but not in the forms record source)
    > >
    > > i understand i can only do this in a seperate table "stats"
    > >
    > > how might i do this?
    > >
    > >

    >
    >
     
  6. JethroUK©

    JethroUK©
    Expand Collapse
    Guest

    "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    news:66cfn1dvfffqm8olpmrjg9du7oo41af0c7@4ax.com...
    > On Sun, 13 Nov 2005 19:50:07 GMT, "JethroUK©" <reply@the.board> wrote:
    >
    > >which is the easiest way to update a value in a table (that's not in the
    > >source)
    > >
    > >i need to store a single date value (but not in the forms record source)
    > >
    > >i understand i can only do this in a seperate table "stats"
    > >
    > >how might i do this?

    >
    > I have no idea. You can see your table structure; you know what this
    > value is, and what it's used for. We do not.
    >
    > Please give us a little help here, Jethro. What is this date? What is
    > it the date OF? What does it relate to? What is your Form bound to,
    > and how does the current record on the form relate to this date?
    >
    > John W. Vinson[MVP]


    just for the record - i have client information in the main table - i have
    produced weekly (approx) statistics based on the data - i just want to
    record the date/time i last ran statistics (not related at all to the main
    table & it's not bound), so the next time i run stats off i can use this
    'from' date
     
  7. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Sun, 13 Nov 2005 23:16:45 GMT, "JethroUK©" <reply@the.board> wrote:

    >just for the record - i have client information in the main table - i have
    >produced weekly (approx) statistics based on the data - i just want to
    >record the date/time i last ran statistics (not related at all to the main
    >table & it's not bound), so the next time i run stats off i can use this
    >'from' date
    >


    By far the simplest way to do this is to put a Date/Time field,
    default value Date() or Now() (depending on whether you want a
    datestamp or a timestamp) in the statistics table, and use a Max query
    to find the most recent record.

    John W. Vinson[MVP]
     
  8. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    Hi Jethro

    It's the " where <some criteria>;" bit that selects the record(s) that are
    to be updated. If you have no WHERE clause then it will update all records.
    --
    Good Luck!

    Graham Mandeno [Access MVP]
    Auckland, New Zealand

    "JethroUK©" <reply@the.board> wrote in message
    news:0LPdf.9022$mF5.647@newsfe4-gui.ntli.net...
    > i'll give that a whirl - i notice the sql doesn't specify any particular
    > record - does this mean it would update a whole recordset in one go?
    >
    > my date table only has one record - but it could be useful for next part
    > of
    > the project which i just happen to be thinking about
    >
    > i'm sending out a mailmerge and was wondering how to update 'LetterSent'
    > field to reflect this activity - i guess i could use same method?
    >
    > thanx
    >
    > "Graham Mandeno" <Graham.Mandeno@nomail.please> wrote in message
    > news:uptu3dJ6FHA.2600@tk2msftngp13.phx.gbl...
    >> Hi Jethro
    >>
    >> Execute a single update query statement. For example:
    >>
    >> Dim sSQL as string
    >> sSQL = "Update [mytable] set [mydatefield]=" _
    >> & Format( somedatevalue, "\#mm\/dd\/yyyy\#" ) _
    >> &
    >> CurrentDb.Execute sSQL, dbFailOnError
    >>
    >> The Format is important, as SQL required dates to be in US format and
    >> does
    >> not take any notice of Windows regional settings.
    >>
    >> --
    >> Good Luck!
    >>
    >> Graham Mandeno [Access MVP]
    >> Auckland, New Zealand
    >>
    >> "JethroUK©" <reply@the.board> wrote in message
    >> news:pNMdf.12475$c66.12350@newsfe3-gui.ntli.net...
    >> > which is the easiest way to update a value in a table (that's not in
    >> > the
    >> > source)
    >> >
    >> > i need to store a single date value (but not in the forms record
    >> > source)
    >> >
    >> > i understand i can only do this in a seperate table "stats"
    >> >
    >> > how might i do this?
    >> >
    >> >

    >>
    >>

    >
    >
     
  9. JethroUK

    JethroUK
    Expand Collapse
    Guest

    That's two birds with one stone - Thanks

    "Graham Mandeno" <Graham.Mandeno@nomail.please> wrote in message
    news:%23dr5dVW6FHA.2036@TK2MSFTNGP14.phx.gbl...
    > Hi Jethro
    >
    > It's the " where <some criteria>;" bit that selects the record(s) that
    > are to be updated. If you have no WHERE clause then it will update all
    > records.
    > --
    > Good Luck!
    >
    > Graham Mandeno [Access MVP]
    > Auckland, New Zealand
    >
    > "JethroUK©" <reply@the.board> wrote in message
    > news:0LPdf.9022$mF5.647@newsfe4-gui.ntli.net...
    >> i'll give that a whirl - i notice the sql doesn't specify any particular
    >> record - does this mean it would update a whole recordset in one go?
    >>
    >> my date table only has one record - but it could be useful for next part
    >> of
    >> the project which i just happen to be thinking about
    >>
    >> i'm sending out a mailmerge and was wondering how to update 'LetterSent'
    >> field to reflect this activity - i guess i could use same method?
    >>
    >> thanx
    >>
    >> "Graham Mandeno" <Graham.Mandeno@nomail.please> wrote in message
    >> news:uptu3dJ6FHA.2600@tk2msftngp13.phx.gbl...
    >>> Hi Jethro
    >>>
    >>> Execute a single update query statement. For example:
    >>>
    >>> Dim sSQL as string
    >>> sSQL = "Update [mytable] set [mydatefield]=" _
    >>> & Format( somedatevalue, "\#mm\/dd\/yyyy\#" ) _
    >>> &
    >>> CurrentDb.Execute sSQL, dbFailOnError
    >>>
    >>> The Format is important, as SQL required dates to be in US format and
    >>> does
    >>> not take any notice of Windows regional settings.
    >>>
    >>> --
    >>> Good Luck!
    >>>
    >>> Graham Mandeno [Access MVP]
    >>> Auckland, New Zealand
    >>>
    >>> "JethroUK©" <reply@the.board> wrote in message
    >>> news:pNMdf.12475$c66.12350@newsfe3-gui.ntli.net...
    >>> > which is the easiest way to update a value in a table (that's not in
    >>> > the
    >>> > source)
    >>> >
    >>> > i need to store a single date value (but not in the forms record
    >>> > source)
    >>> >
    >>> > i understand i can only do this in a seperate table "stats"
    >>> >
    >>> > how might i do this?
    >>> >
    >>> >
    >>>
    >>>

    >>
    >>

    >
    >
     

Share This Page