Updating table field

[DreamerZ]

Really stupid, newbie question, but I can't for the life of me remember how to do this:

I have a date check against a table field (Now() vs. date in table). Table has 1 record.

I want to update the table field based on whether that data is equal to Now() or not.

Simple, right? No form involved. Happens when DB first opens.

ARGH!


DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[TonyJollans]

Hi DreamerZ,

So what's the question??

The chance of a value on a record being equal to Now() (accurate at least to the second), of course, is pretty remote, so I suspect you want a different check - against Date() perhaps.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[LarryDeLaruelle]

Dreamer:

Not clear on what your intent is, but remember that Now() returns date + time. If you want date only use Date().


Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[Golom]

Now() returns the current time stamp (i.e. date and time). If you are testing [DateField] = Now() then you will succeed only when the [DateField] is the current date and time. For example, as I type this, Now() is 03/09/04 2:37:47 PM and only a date with that value will match it. If you just want to compare the date portion then use Date() instead of Now().

 

[DreamerZ]

Sorry, but that's not the problem. I'm using Now() as an example currently and might change it later on.

The problem is updating the table. If Now() or Date() or whatever equals the field record in my table, what do I need to do to update the field record with Now() or Date() or whatever?

So, not a variable issue, but an update table issue. By the way, with Format, I can make Now() equal any time/date included. Not the most elegant for sure, but I'm just testing at the moment.


DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[PHV]

And what about the Update method of the Recordset object ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

And what about the UPDATE SQL verb ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[RoyVidar]

Adding to PHV's last suggestion with the runsql method of the docmd object (or execute method of database/querydef or connection object).

Or - why not use a form, you probably have a startupform, bind it to the table and you should be able to both check current value, and or update in one of the form events (current, load...) with Date() or Now()...

Roy-Vidar

 

[TonyJollans]

Hi DreamerZ,

Are you just asking how to do an update? If so SQL is like this ..

[tt][blue]UPDATE TableName
SET TableName.FieldName = 'New Value',
TableName.NumField = 15
WHERE TableName.DateField = Date();[/blue][/tt]

.. and code something on these lines ..

[blue]DoCmd.RunSQL "UPDATE [i]TableName[/i] " & _
             "SET    [i]TableName.FieldName[/i] = 'New Value', " & _
             "       [i]TableName.NumField[/i]  = 15 " & _ 
             "WHERE  [i]TableName.DateField[/i] = Date();"[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[DreamerZ]

Thanks all for the information. I think I'll probably go the easiest route of doing the check with the opening form although I might be better off with the SQL route.

Must have been a blue moon, Friday the 13th. I couldn't remember the SQL route.

By the way, congrats Roy on the Tip Master of the Week!



DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[Apollo13a]

How about using
Currentdb.Execute "UPDATE tablename SET [fieldname]=[new value] WHERE [fieldname] {> < =} " & comparison value