Convert Query to SQL

  • Thread starter Thread starter Richnep
  • Start date Start date
R

Richnep

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
 
Might want to place a space before the WHERE clause, it could be mistaking it
as [WB]WHERE. Not sure if it'll solve the problem, but it'll help avoid
confusion.
Richnep said:
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
Click to expand...
 
Sorry I missed that you might want to change [tblCatalogPartNumbers]![WB]
WHERE. Convert the "!" to a "."
Richnep said:
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
Click to expand...
 
Back
Top