run time error 3075 1

[tksy]

HI

I am gettinga run time error in´my code

Sub UpdateNulls()
Dim strSQL As String
Dim rs As DAO.Recordset
For Each tdf In CurrentDb.TableDefs
If Left(tdf.Name, 4) <> "Msys" And tdf.Name <> "Table1" Then
strSQL = "Select * From [" & tdf.Name & "] a Inner Join table1 On a.fall = table1.fall Where a.Status = 5"

Set rs = CurrentDb.OpenRecordset(strSQL)

Do While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
If IsNull(rs.Fields(i)) Then
rs.Edit
rs.Fields(i) = 111111
rs.Update
End If
Next
rs.MoveNext
Loop

End If
Next
End Sub


I am getting runtime error on operator missing
a.fall = table1.fall

 

[Remou]

I would have been grateful if you had continued your post in Stackoverflow, rather than continuing a question across two different sites. I said in

http://stackoverflow.com/posts/edit/241191

"Here is some largely untested code. Hopefully it will give you a start." because it is difficult to provide a suitable answer to an unusual request. It seems unlikely, but not impossible, that all your tables have a join field in common.

In you original Stackoverflow post, you said the field that the tables had in common was idno, has this changed to fall? Have you tried pasting the SQL into the query design window to see if it works?

 

[tksy]

Sorry for the new posting but i was not sure whether there would be views on stackoverflow. Actually i Posted here after seeing your ur active here in this site too.

Actually id no and fall are same the language is different my db is in another language.

all the tables have the id no(fall) .

No the query doesnt seem to work in query design window, I am checking it now.

 

[tksy]

in thw query design window also i am getting same error
syntax error

on a.fall = table1.fall

 

[Remou]

Please join the two tables in query design view and see if that works. It would also be good to post the full sql string.

 

[tksy]

THis is the query I am running in query design window

SELECT * from table1 a inner join ON table1 a.fall = table1.fall where a.status = 5;

i am getting syntax error in from clause

 

[Remou]

That should be:

SELECT * from table1 a inner join table1 ON a.fall = table1.fall where a.status = 5

 

[tksy]

ok but now i am getting

syntax error missing operatosr in query expression
'a.fall = table1.fall'

 

[Remou]

Is status a text field?

Try:
SELECT * from table1 a inner join table1 ON a.fall = table1.fall

If that works, try:
SELECT * from table1 a inner join table1 ON a.fall = table1.fall where a.status = '5'

 

[tksy]

nope still the same error

syntax error missing operatosr in query expression
'a.fall = table1.fall'


does 'a' has to be declared or something like that

 

[Remou]

No it does not. It is an alias.

Here is the most simple:

SELECT * from table1 where status = '5'

Does it work?

 

[tksy]

yes it works

 

[tksy]

it works without the quotes in 5

 

[Remou]

Try:

SELECT * from table1 tblT inner join table1 on tblT.[fall] = table1.[fall] where tblT.[status] = 5

This is in case 'a' means something else in the edition you are using.

It would also be good to build a query with a join using the wizards and to post the sql. This is in case your edition uses different syntax.

Your table is really called table1 yesno?

 

[tksy]

Well I am getting the same error

syntax error missing operator in query expression
'tblT.fall = table1.fall'

Also no table1 is not the name
as i said the database is in german and the table names are too. here it is 01umwelt.

i used table1 as it was simpler.but while running the query i am using 01umwelt.

btw i am using access 2002

 

[Remou]

Again, it would also be good to build a query with a join using the wizards and to post the sql. This is in case your edition uses different syntax. You can choose Find Duplicates query wizard from the new query drop-down.

 

[tksy]

but using a find duplicates wizard how can i buils a join query

 

[Remou]

It is a wizard, it will prompt you for the names of two tables and the names of the fields that match and will then build the query itself. I suggest you choose 01umwelt and any other table with the field fall.

 

[tksy]

the duplicates wizard asked for only one table

i tried unmatched query wizard and it aske dfor two tables

here is a sample query

SELECT [01UMWELT].FALL, [01UMWELT].OLDCASE
FROM 01UMWELT LEFT JOIN 03_PERSDAT ON [01UMWELT].FALL = [03_PERSDAT].FALL
WHERE ((([03_PERSDAT].FALL) Is Null));

 

[Remou]

Ok. Try:

SELECT * FROM 03_PERSDAT t
INNER JOIN 01UMWELT
ON t.FALL = [01UMWELT].FALL
WHERE [01UMWELT].Status=5