query to SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
What you posted seems to be missing a space
= [tblCatalogPartNumbers]![WB]WHERE
in the above line.

In addition I would reference tblCatalogPartNumber.WB with the dot
separator instead of the ! separator.

I always find it better to build the SQL string and assign it to a variable
so I can examine the results

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*' ;"

'Add these 2 lines for testing
Debug.Print strSQL
Stop
'You can then copy the SQL string into a new query and see if it executes
without error.
'If it doesn't you can go back to the code and make corrections to the code

DoCmd.RunSQL StrSQL
Also I would use the execute method to run the query instead of DoCmd.Run

Currentdb().Execute strSQL, dbFailOnError

I tend to create a db object, so I can check my results


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
It was becuase the table had not been populated yet and hence was all NULL.
Once I populated the table it worked.

Thanks Allen!

Allen Browne said:
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.

Rich Ellis 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...
Click to expand...
 
Back
Top