SQL Syntax?

[aziz rasul]

I have the following snippet of code.

TableName = InputBox("Enter name of table", "Volume", "BTNI Private Circuit Summary June 2002")

DoCmd.CopyObject , TableName, acTable, "BTNI Private Circuit Summary Template"

sqlABTotal = "UPDATE [tblAB Total] INNER JOIN [" & TableName
sqlABTotal = sqlABTotal & "] ON [tblAB Total].[Rev Family A] = [" & TableName & "].[Rev Family] SET ["
sqlABTotal = sqlABTotal & TableName & "].[Total (adj PPC) - Rentals] = [tblAB Total]![Count AB]"
sqlABTotal = sqlABTotal & "WHERE ((([" & TableName & "].ID) Between 2 And 8));"

sqlABBTOU = "UPDATE [tblAB BTOU] INNER JOIN [" & TableName
sqlABBTOU = sqlABBTOU & "] ON [tblAB BTOU].[Rev Family A] = [" & TableName & "].[Rev Family] SET ["
sqlABBTOU = sqlABBTOU & TableName & "].[BT Own Use - Rentals] = [tblAB BTOU]![Count AB]"
sqlABBTOU = sqlABBTOU & "WHERE ((([" & TableName & "].ID) Between 2 And 8));"

DoCmd.RunSQL sqlABTotal
DoCmd.RunSQL sqlABBTOU

The SQL statement for sqlABTotal works, but the SQL statement for sqlABBTOU doesn't?

Both SQL statements are nearly identical, hence I can't see why the former works and the latter doesn't, i.e. it doesn't update the table. If I do the same thing using a query, then it's OK!!!!

Can anyone see what I'm doing wrong.

The reason for using SQL instead of a normal query, is that the table req'd in the query changes and hence I need to use RunSQL.

I'm not a SQL person, so go slowly on this one.

 

[simongallop]

Only things that I can think of are data type mismatches ie linking string field to numbre field. Would suggest checking RevFamily A and BT own use - rentals

 

[aziz rasul]

The data types ARE different. However all that I'm doing is to place numeric values from [Count AB] into a text field in [BT Own Use - Rentals]. This shouldn't make a difference. The text field length is more than adequate. Also the query using the QBE works!

I have tried changing the data types so that they are both either text or numeric. No difference.

Here's something funny. If I change

DoCmd.RunSQL sqlABTotal
DoCmd.RunSQL sqlABBTOU

to

DoCmd.RunSQL sqlABBTOU
DoCmd.RunSQL sqlABTotal

it works! Can't understand that?

This may or may not be a clue. After the above two commands, I have: -

Me!Label52.Visible = False
Me.Repaint

When I step thru the code, it ignores the above two lines of code and does not make Label52 invisible?


[This message has been edited by aziz rasul (edited 05-29-2002).]