update query

[yamy]

UPDATE UsersTable INNER JOIN StatusTable ON UsersTable.uID=StatusTable.uID SET StatusTable.sStatusLookup=0 WHERE (((UsersTable.uDateAdded) Is Not Null) and (( StatusTable.sID) is Null));

This query is "...about to update 0 row(s)...."

Huh?

I have 600+ records that should be updated through this query.
I have tried a variety of conditions (one or more at a time) on different fields; at best I get one record to update.

I suspect I first have to create a record in the StatusTable for each uID before I can update, but I sure can't figure out how except one by one manually - aaaaggggg!

All comments and ideas welcome!

Yamy

 

[cmmrfrds]

Try rearranging the syntax.
Tell it what table to update and then what field(s) in that table and then do any joins.

UPDATE StatusTable
SET StatusTable.sStatusLookup=0
From StatusTable
INNER JOIN UsersTable ON UsersTable.uID=StatusTable.uID WHERE (((UsersTable.uDateAdded) Is Not Null) and (( StatusTable.sID) is Null));

 

[yamy]

thanks, I gave that a shot but get the following:

Syntax error (missing operator) in query expression '0 From Status Table INNER JOIN UsersTable ON UsersTable.uID=StatusTable.uID'.

The word 'From' is what get highlighted after closing the error message.

Any other ideas?

Yamy

 

[cmmrfrds]

Sorry the syntax you have is correct for Access. I was mixing it with SQL Server.

UPDATE UsersTable INNER JOIN StatusTable ON UsersTable.uID=StatusTable.uID SET StatusTable.sStatusLookup=0 WHERE (((UsersTable.uDateAdded) Is Not Null) and (( StatusTable.sID) is Null));

Try running just a select with your WHERE criteria.

Select UsersTable.* From UsersTable INNER JOIN StatusTable ON UsersTable.uID=StatusTable.uID WHERE (((UsersTable.uDateAdded) Is Not Null) and (( StatusTable.sID) is Null));

Check the fields sID and UDateAdded for something other than null, maybe spaces?

 

[yamy]

your comment about spaces and other 'not nulls' underlines my earlier suspicion that the absence of an existing Status record for each User record is what is really causing the problem.

So really the question should be:

how do i (can I?) write a query that creates a Status record for each and every User record, filling in the sStatus field as it goes?

although, pretty soon i will have spent enough time on this that i could have created each record by hand. (horrid thought)

thanks for your comments and ideas
yamy

 

[yamy]

added note -

as I continue to prowl through my various reference books I am quickly coming to the conclusion that VBA is the answer - I Think I can manually add the new records faster than I can crash course how to add new records using VBA,

since it is a one time procedure on the existing records. Subsequent adds will be dealt with by using properly designed forms to collect all the data.

whew this stuff can be exhausting!

of course, if any of you have a ready made -fill in the blanks' VBA code module lying around that might do the job, I sure wouldn't say no!

thanks again,
yamy

 

[cmmrfrds]

Here is the basic flow in updating a recordset.

Function Atest()
'-- need a reference set to the ADO library either 2.6 or 2.7
Dim cn As New ADODB.Connection, sql1 As String
Dim rs As New ADODB.Recordset

Set cn = CurrentProject.Connection
cn.Open

'--- create sql to bring back the records you want to update
'-- select records from the table you want to update only bring back records you need with where clause.

sql1 = "Select UsersTable.* From UsersTable WHERE (((UsersTable.uDateAdded) Is Not Null);"

rs.Open sql1, cn, adOpenStatic, adLockOptimistic

rs.MoveFirst

While Not rs.EOF
rs!sStatusLookup = 0
rs.Update
rs.MoveNext
Wend

rs.Close
Set rs = Nothing

End Function