VB SQL Update Question

[dupuisjl]

I'm trying to update a field (status_code) in one table (p_temp_merged_file) by accessing a column (status_code)in another table (p_pha_plan). The update statement is not working and I am not all that familiar with SQL. I wasn't sure if a select statement was needed or if I needed to join the tables in order to do an update. Any help would be appreciated. My code is as follows:

Conn.Execute "UPDATE p_temp_merged_file "_
&"SET p_temp_merged_file.status_code=PLAN_ALREADY_APPROVED "_
&"WHERE p_pha_plan.status_code=APPROVED "_
&"AND p_temp_merged_file.state_code=p_pha_plan.state_code "_
&"AND p_temp_merged_file.pha_code=p_pha_plan.pha_code "_
&"AND p_temp_merged_file.fiscal_year=p_pha_plan.fiscal_year"


This is an easier one, but it won't work either...

UPDATE p_temp_merged_file " _
SET status_code=READY_TO_SUBMIT_FOR_APPROVAL " _
WHERE status_code=PHA_PLAN_PDF_FILE_CREATED "

Any help would be greatly appreciated!

 

[dishakibria]

It may be that you need '" &...&"' around your string variables.

e.g. "update tblxyz Set abc = '" & .....&"'
If you are updating with a string.

 

[listener22]

PLAN_ALREADY_APPROVED needs single quotes around it in
order to have it evaluated as text. Jim

oracle, vb

 

[chiph]

Is PLAN_ALREADY_APPROVED a variable or a literal?

If it's a variable, it needs to be outside the double-quotes (concatenated together with the ampersand operator). If it's a literal, then it needs to be surrounded by single-quotes.

Oh, and if it is a variable -- make sure it doesn't have any double or single-quotes inside it. That would throw off the count and VB would get confused. If there are any in there, for each double-quote character, add one more just after it. For each single-quote character, add one more single-quote character right after it. That way VB's string processor is happy.

Example:
If the variable contains:
[tab]She said "Bob's car is blue"

You would replace characters so that it ended up containing:
[tab]She said ""Bob''s car is blue""

Chip H.

 

[MelissaEvans]

I find it very helpful to use a debug.print statment so I can see what I've concatinated together. If the SQL statement looks OK there, and it still doesn't work, copy and paste it into a query analyzer and run it in your SQL environment - that way you can get more helpful error messages. =)