query to SQL

[Rich Ellis]

Hi All,

I wrote this query (in access03) and then went to sql view and tried top
copy and paste it into a button's on click event

DoCmd.RunSQL "UPDATE tblAnimalBleedsStatus INNER JOIN tblCatalogPartNumbers
ON tblAnimalBleedsStatus.CatalogNumber = tblCatalogPartNumbers.CatalogNumber
SET tblAnimalBleedsStatus.FinalLotsWB = [tblCatalogPartNumbers]![WB]WHERE
(((tblAnimalBleedsStatus.FinalLotsWB) Not Like '11*' And
(tblAnimalBleedsStatus.FinalLotsWB) Not Like 'R&D' And
(tblAnimalBleedsStatus.FinalLotsWB) Not Like '44*'));"


I had to modify it by changing the NOT LIKE values into single quotation
marks and obviously I added the DoCMD.RUNSQL command. When I run it I see a
qucik blip on the staus bar like it ran the query but in
tblAnimalBleedsStatus the fields are all still NULL.

Any ideas what I am doing wrong?

THanks in advance

 

[Rich Ellis]

It was becuase the table had not been populated yet and hence was all NULL.
Once I populated the table it worked.

Thanks Allen!

Several things here.

Firstly, you do have to handle the embedded quotes. The single-quote is fine
for what you have here, or you can double-up the quotes. For an explanation,
see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

More importantly, Access will make no changes to the records where
FinalLotsWB is null. For an explantion of why, see Error #1 in:
Common Errors with Null
at:
http://allenbrowne.com/casu-12.html

Thirdly, if RunSQL fails and you turned off SetWarnings as people often do
to get rid of the confirmation dialog, you get no warning when something
goes wrong. For a safer approach, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html
The code would look something like this:
Dim strSql As String
strSql = "UPDATE tblAnimalBleedsStatus INNER JOIN tblCatalogPartNumbers ON "
& _
"tblAnimalBleedsStatus.CatalogNumber = tblCatalogPartNumbers.CatalogNumber "
& _
"SET tblAnimalBleedsStatus.FinalLotsWB = [tblCatalogPartNumbers]![WB] " & _
"WHERE (((tblAnimalBleedsStatus.FinalLotsWB) Not Like '11*' " & _
"And (tblAnimalBleedsStatus.FinalLotsWB) Not Like 'R&D' " & _
"And (tblAnimalBleedsStatus.FinalLotsWB) Not Like '44*'));"
dbEngine(0)(0).Execute strSql, dbFailOnError

Finally, if you want some help to copy the SQL statement from a query into
your code, here's a form you can copy into your database to do that for you:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi All,

I wrote this query (in access03) and then went to sql view and tried top
copy and paste it into a button's on click event

DoCmd.RunSQL "UPDATE tblAnimalBleedsStatus INNER JOIN
tblCatalogPartNumbers
ON tblAnimalBleedsStatus.CatalogNumber =
tblCatalogPartNumbers.CatalogNumber
SET tblAnimalBleedsStatus.FinalLotsWB = [tblCatalogPartNumbers]![WB]WHERE
(((tblAnimalBleedsStatus.FinalLotsWB) Not Like '11*' And
(tblAnimalBleedsStatus.FinalLotsWB) Not Like 'R&D' And
(tblAnimalBleedsStatus.FinalLotsWB) Not Like '44*'));"


I had to modify it by changing the NOT LIKE values into single quotation
marks and obviously I added the DoCMD.RUNSQL command. When I run it I see
a
qucik blip on the staus bar like it ran the query but in
tblAnimalBleedsStatus the fields are all still NULL.

Any ideas what I am doing wrong?

THanks in advance