Easier way to update a field in a table

[JethroUK©]

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?

 

[Graham Mandeno]

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.

 

[John Vinson]

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]

 

[JethroUK©]

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

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

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?

 

[JethroUK©]

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

 

[John Vinson]

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]

 

[Graham Mandeno]

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

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

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

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?

 

[JethroUK]

That's two birds with one stone - Thanks

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

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

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

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?