Problems executing code

[Dachande11]

Hello to anybody who can help,

I am trying to update a table by using the code below, i know that it works without the criteria but as soon as i use the 'Where' things go wrong.

I currently get a type mismatch failure, if i change the = to like it runs without error but does not update the table.


Private Sub Update_SAC_Click()
Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection

cn.Execute "UPDATE SAC_Periods INNER JOIN SAC_SOrg_Temp ON SAC_Periods.Channel = SAC_SOrg_Temp.Channel SET SAC_Periods.P01 = [niv] WHERE (((SAC_SOrg_Temp.Period)=" & "p01" & "));"

End Sub

Thanks to anybody who can help.

 

[AncientOne]

First, try dumping all the brackets, they shouldn't be necessary. Then, what type of field is SAC_SOrg_Temp.Period? Is it a string, a number or a date?

 

[Dachande11]

Thanks for your reply,

I have dumped the kbrackets and still no joy. The SAC_SOrg_Temp.Period field is a text field.

Thanks

Mark

 

[AncientOne]

WHERE SAC_SOrg_Temp.Period='" & "p01' "

there is a single quote followed by a double quote after= and after 1

 

[Dgar007]

Is p01 a variable, if so don't put it in double quotes change it to have a single quote after the = and a single quote before the ):

[niv] WHERE (((SAC_SOrg_Temp.Period)= '" & p01 & "'));"

 

[AncientOne]

if PO1 is a variable, which I don't think it is, it doesn't need any quotes at all...

 

[Dachande11]

Thanks everybody,

AncientOne, your advice worked a treat.

Thanks again

Mark

 

[Dgar007]

I thought if the field is a text field it must be incased within single quotes. WHERE SAC_SOrg_Temp.Period= 'textfield';

I could be wrong.

 

[AncientOne]

I thought if the field is a text field it must be incased within single quotes. WHERE SAC_SOrg_Temp.Period= 'textfield';

I could be wrong.

Not if it's a variable which is of datatype text.