Update Query Help! PLEASE!!!

  • Thread starter Thread starter Paul Kinville
  • Start date Start date
P

Paul Kinville

ok, so this is working with a form that i put together. just
something quick and dirty that will date my application from the time
that it is first accessed. this will allow me to do some neat stuff
with counting days and weeks, and...whatever. so i have this little
bit of code that checks a date table and updates the date field in
that table with todays date the very first time the program is opened.
well, the first time that the form that auto-opens comes up.

Private Sub Form_Open(Cancel As Integer)
DoCmd.RunSQL ("UPDATE [date] SET [date].[date] = Now() WHERE
(((date.date) Is Null));")
End Sub

can anyone tell me why this isn't working? even when the table is
completely empty, it wont write the date to the table. anyone have
any thoughts on this? the first few times i tried to run it, it
worked fine, but then all of a sudden, it stopped working.

thanks in advance!!!
 
I would suggest you rename both the table and the field. Date is a function
in Access, and it is likely getting confused.
 
It worked for me. What do you mean by 'it won't run'. Do you get an error
message?

--
Joan Wild
Microsoft Access MVP

Paul Kinville said:
thanks you so much for your response on this but for whatever reason, even
Click to expand...
when the table is named something else, like date_open, the query just
doesn't do anything.
assume this is my table

name: date_open
field: date_open
field type: date/time

and i am just running this query...

UPDATE date_open SET date_open.date_open = Now() WHERE
Click to expand...
(((date_open.date_open) Is Null));
 
Paul Kinville said:
ok, so this is working with a form that i put together. just
something quick and dirty that will date my application from the time
that it is first accessed. this will allow me to do some neat stuff
with counting days and weeks, and...whatever. so i have this little
bit of code that checks a date table and updates the date field in
that table with todays date the very first time the program is opened.
well, the first time that the form that auto-opens comes up.

Private Sub Form_Open(Cancel As Integer)
DoCmd.RunSQL ("UPDATE [date] SET [date].[date] = Now() WHERE
(((date.date) Is Null));")
End Sub
Click to expand...

Paul Kinville,

I created a table named Date with a column named Date.

I copied the above SQL into a Query, and tried to run it.

On running it, the message returned is "You are about to 0 row(s)".

Since the table is empty, the WHERE clause criteria of (date.date Is Null)
can never be met, no rows in an empty Table equals no rows that are Null,
which explains the message.

Now, what about if we INSERT two rows, one with 01/01/04, and one that is
Null. Oops, I only had one column, Date, and I set it to be the Primary Key
of the table, so a little tinkering with the Table is necessary. I added an
extra column to act as Primary Key (AutoNumber), and an extra column for me
to use to force the creation of new rows directly in the Datasheet. I set
the Date column to have a DefaultValue of Null, and then added an extra row.

DateID, My Value, Date
1 1 01/01/2004
2 2 Null


After running the above SQL, the tables contents are:

DateID, My Value, Date
1 1 01/01/2004
2 2 03/26/2004 12:40:04 PM

I manually added another row:

DateID, My Value, Date
1 1 01/01/2004
2 2 03/26/2004 12:40:04 PM
3 3 Null

Now, to step up to using the FORM

I created a blank form named, of all things, Form.

I opened its code module, and pasted in the above VBA code.

I saved the Form, and Ran it.

I got the "You are about to update 1 row(s)" message.

Table Date's contents are now:

DateID, My Value, Date
1 1 01/01/2004
2 2 03/26/2004 12:40:04 PM
3 3 03/26/2004 12:43:49 PM


Everything seemed to work for me.


Sincerely,

Chris O.
 
Private Sub Form_Open(Cancel As Integer)
DoCmd.RunSQL ("UPDATE [date] SET [date].[date] = Now() WHERE
(((date.date) Is Null));")
End Sub

can anyone tell me why this isn't working? even when the table is
completely empty, it wont write the date to the table.
Click to expand...

I wouldn't expect it to do so.

An Update query *updates the values of fields in existing records*.

If there are no records in the table, then there is nothing to update;
and the update query will (correctly) do nothing.

Could you explain the purpose of the table and what you're trying to
accomplish?
 
Back
Top