Update Query Syntax Error

S

Shytown_Turk

When I try to run this query I am getting a syntax error Too Few Parameters,
expected 20. The unique ID used in the WHERE clause is a string and I am
having problems with the proper syntax.

-------------------------
"UPDATE tblSecurity SET tblSecurity.SecurityNm = [tblHistory].[SecurityNm],
" & _
"tblSecurity.AssetClassID = [tblHistory].[AssetClassID],
tblSecurity.ExpDate = [tblHistory].[ExpDate], tblSecurity.CreditID =
[tblHistory].[CreditID], " & _
"tblSecurity.Risk = [tblHistory].[Risk], tblSecurity.Taxable =
[tblHistory].[Taxable], tblSecurity.SecurityType =
[tblHistory].[SecurityType], " & _
"tblSecurity.SchwabType = [tblHistory].[SchwabType],
tblSecurity.ExpYr = [tblHistory].[ExpYr], tblSecurity.ExpMt =
[tblHistory].[ExpMt], tblSecurity.SectorTypeID = [tblHistory].[SectorTypeID],
" & _
"tblSecurity.IndustryTypeID = [tblHistory].[IndustryTypeID],
tblSecurity.MStarRate = [tblHistory].[MStarRate], tblSecurity.FairValue =
[tblHistory].[FairValue], " & _
"tblSecurity.[P/E] = [tblHistory].[P/E], tblSecurity.[P/B] =
[tblHistory].[P/B], tblSecurity.[P/CF] = [tblHistory].[P/CF],
tblSecurity.Moat = [tblHistory].[Moat], " & _
"tblSecurity.RatingDt = [tblHistory].[RatingDt],
tblSecurity.MStarRisk = [tblHistory].[MStarRisk], tblSecurity.LastUpdDt =
Date() " & _
"WHERE (((tblSecurity.CUSIP)=""CUSIP""))"
 
S

Shytown_Turk

Not sure why the sql statement turned out the way it did.

Shytown_Turk said:
When I try to run this query I am getting a syntax error Too Few Parameters,
expected 20. The unique ID used in the WHERE clause is a string and I am
having problems with the proper syntax.

-------------------------
Click to expand...
"UPDATE tblSecurity SET tblSecurity.SecurityNm = [tblHistory].[SecurityNm],"
& _
"tblSecurity.AssetClassID = [tblHistory].[AssetClassID],tblSecurity.ExpDate
= [tblHistory].[ExpDate], tblSecurity.CreditID = [tblHistory].[CreditID], " &
_
"tblSecurity.Risk = [tblHistory].[Risk], tblSecurity.Taxable =
[tblHistory].[Taxable], tblSecurity.SecurityType =
[tblHistory].[SecurityType], " & _
"tblSecurity.SchwabType = [tblHistory].[SchwabType], tblSecurity.ExpYr =
[tblHistory].[ExpYr], tblSecurity.ExpMt = [tblHistory].[ExpMt],
tblSecurity.SectorTypeID = [tblHistory].[SectorTypeID], " & _
"tblSecurity.IndustryTypeID = [tblHistory].[IndustryTypeID],
tblSecurity.MStarRate = [tblHistory].[MStarRate], tblSecurity.FairValue =
[tblHistory].[FairValue], " & _
"tblSecurity.[P/E] = [tblHistory].[P/E], tblSecurity.[P/B] =
[tblHistory].[P/B], tblSecurity.[P/CF] = [tblHistory].[P/CF],
tblSecurity.Moat = [tblHistory].[Moat], " & _
"tblSecurity.RatingDt = [tblHistory].[RatingDt], tblSecurity.MStarRisk =
[tblHistory].[MStarRisk], tblSecurity.LastUpdDt = Date() " & _
"WHERE (((tblSecurity.CUSIP)=""CUSIP""))"
 
J

John Spencer

You reference tblHistory fields but you have no reference to the table
itself in your query.

Are you trying to add new records (append query) or change the contents
of existing records (update query)?

Add new records is like:

INSERT INTO tblSecurity
(SecurityNm, AssetClassID, ExpDate,CreditID)
SELECT SecurityNm, AssetClassID, ExpDate,CreditID
FROM tblHistory
WHERE ???

UPDATING Existing records is like:

UPDate tblSecurity INNER JOIN tblHistory
ON TblSecurity.SomeField = tblHistory.SomeField
SET tblSecurity.SecurityNM = [tblHistory].[SecurityNm]
WHERE tblSecurity.CUSIP = "CUSIP"

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Shytown_Turk said:
Not sure why the sql statement turned out the way it did.

Shytown_Turk said:
When I try to run this query I am getting a syntax error Too Few Parameters,
expected 20. The unique ID used in the WHERE clause is a string and I am
having problems with the proper syntax.

-------------------------
Click to expand...
"UPDATE tblSecurity SET tblSecurity.SecurityNm = [tblHistory].[SecurityNm],"
& _
"tblSecurity.AssetClassID = [tblHistory].[AssetClassID],tblSecurity.ExpDate
= [tblHistory].[ExpDate], tblSecurity.CreditID = [tblHistory].[CreditID], " &
_
"tblSecurity.Risk = [tblHistory].[Risk], tblSecurity.Taxable =
[tblHistory].[Taxable], tblSecurity.SecurityType =
[tblHistory].[SecurityType], " & _
"tblSecurity.SchwabType = [tblHistory].[SchwabType], tblSecurity.ExpYr =
[tblHistory].[ExpYr], tblSecurity.ExpMt = [tblHistory].[ExpMt],
tblSecurity.SectorTypeID = [tblHistory].[SectorTypeID], " & _
"tblSecurity.IndustryTypeID = [tblHistory].[IndustryTypeID],
tblSecurity.MStarRate = [tblHistory].[MStarRate], tblSecurity.FairValue =
[tblHistory].[FairValue], " & _
"tblSecurity.[P/E] = [tblHistory].[P/E], tblSecurity.[P/B] =
[tblHistory].[P/B], tblSecurity.[P/CF] = [tblHistory].[P/CF],
tblSecurity.Moat = [tblHistory].[Moat], " & _
"tblSecurity.RatingDt = [tblHistory].[RatingDt], tblSecurity.MStarRisk =
[tblHistory].[MStarRisk], tblSecurity.LastUpdDt = Date() " & _
"WHERE (((tblSecurity.CUSIP)=""CUSIP""))"
Click to expand...
 
S

Shytown_Turk

John,

Thank you for the post. I tried to paste the SQL statement after making
some corrections but I pasted the wrong one. I had already added the
reference to the tables as you mentioned. My main concern with the post was
to learn how to break the statement across several lines within my VBA code.
I have since figured that out.

The query works fine when I run it outside of my code (query design), but
when I run it within my module it doesn't make the update. It doesn't error
out, but it doesn't update the values either.

Not even sure what details I should include to begin to describe this
problem, but here goes.

I have a form that lists the details of a selected record and allows the
end-user to make changes (updates) to this record. When the user is
finished, they select the 'save' button and I run a vba module that updates
the record's changes in another table (eventually two tables when I get the
first one working). This is where the problem begins. As mentioned earlier,
the update query works when it is run outside of the module but it doesn't
work with my code when called via db.execute. If I walk through the code and
capture the SQL string as it is about to be executed and paste it into the
query designer it works fine.

John Spencer said:
You reference tblHistory fields but you have no reference to the table
itself in your query.

Are you trying to add new records (append query) or change the contents
of existing records (update query)?

Add new records is like:

INSERT INTO tblSecurity
(SecurityNm, AssetClassID, ExpDate,CreditID)
SELECT SecurityNm, AssetClassID, ExpDate,CreditID
FROM tblHistory
WHERE ???

UPDATING Existing records is like:

UPDate tblSecurity INNER JOIN tblHistory
ON TblSecurity.SomeField = tblHistory.SomeField
SET tblSecurity.SecurityNM = [tblHistory].[SecurityNm]
WHERE tblSecurity.CUSIP = "CUSIP"

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Shytown_Turk said:
Not sure why the sql statement turned out the way it did.

Shytown_Turk said:
When I try to run this query I am getting a syntax error Too Few Parameters,
expected 20. The unique ID used in the WHERE clause is a string and I am
having problems with the proper syntax.

-------------------------
Click to expand...
"UPDATE tblSecurity SET tblSecurity.SecurityNm = [tblHistory].[SecurityNm],"
& _
"tblSecurity.AssetClassID = [tblHistory].[AssetClassID],tblSecurity.ExpDate
= [tblHistory].[ExpDate], tblSecurity.CreditID = [tblHistory].[CreditID], " &
_
"tblSecurity.Risk = [tblHistory].[Risk], tblSecurity.Taxable =
[tblHistory].[Taxable], tblSecurity.SecurityType =
[tblHistory].[SecurityType], " & _
"tblSecurity.SchwabType = [tblHistory].[SchwabType], tblSecurity.ExpYr =
[tblHistory].[ExpYr], tblSecurity.ExpMt = [tblHistory].[ExpMt],
tblSecurity.SectorTypeID = [tblHistory].[SectorTypeID], " & _
"tblSecurity.IndustryTypeID = [tblHistory].[IndustryTypeID],
tblSecurity.MStarRate = [tblHistory].[MStarRate], tblSecurity.FairValue =
[tblHistory].[FairValue], " & _
"tblSecurity.[P/E] = [tblHistory].[P/E], tblSecurity.[P/B] =
[tblHistory].[P/B], tblSecurity.[P/CF] = [tblHistory].[P/CF],
tblSecurity.Moat = [tblHistory].[Moat], " & _
"tblSecurity.RatingDt = [tblHistory].[RatingDt], tblSecurity.MStarRisk =
[tblHistory].[MStarRisk], tblSecurity.LastUpdDt = Date() " & _
"WHERE (((tblSecurity.CUSIP)=""CUSIP""))"
Click to expand...
Click to expand...
 
S

Shytown_Turk

I have more information on what is not working. In my sql statement I have a
variable that is passed to the function and is used in the WHERE clause.
This variable is a string and as such I think the problem has to do with the
proper usage of quotes. Any help on the proper syntax on how to use a string
based variable in the WHERE clause would probably solve this issue. Thanks
in advance.

Shaun

Shytown_Turk said:
John,

Thank you for the post. I tried to paste the SQL statement after making
some corrections but I pasted the wrong one. I had already added the
reference to the tables as you mentioned. My main concern with the post was
to learn how to break the statement across several lines within my VBA code.
I have since figured that out.

The query works fine when I run it outside of my code (query design), but
when I run it within my module it doesn't make the update. It doesn't error
out, but it doesn't update the values either.

Not even sure what details I should include to begin to describe this
problem, but here goes.

I have a form that lists the details of a selected record and allows the
end-user to make changes (updates) to this record. When the user is
finished, they select the 'save' button and I run a vba module that updates
the record's changes in another table (eventually two tables when I get the
first one working). This is where the problem begins. As mentioned earlier,
the update query works when it is run outside of the module but it doesn't
work with my code when called via db.execute. If I walk through the code and
capture the SQL string as it is about to be executed and paste it into the
query designer it works fine.

John Spencer said:
You reference tblHistory fields but you have no reference to the table
itself in your query.

Are you trying to add new records (append query) or change the contents
of existing records (update query)?

Add new records is like:

INSERT INTO tblSecurity
(SecurityNm, AssetClassID, ExpDate,CreditID)
SELECT SecurityNm, AssetClassID, ExpDate,CreditID
FROM tblHistory
WHERE ???

UPDATING Existing records is like:

UPDate tblSecurity INNER JOIN tblHistory
ON TblSecurity.SomeField = tblHistory.SomeField
SET tblSecurity.SecurityNM = [tblHistory].[SecurityNm]
WHERE tblSecurity.CUSIP = "CUSIP"

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Shytown_Turk said:
Not sure why the sql statement turned out the way it did.

:

When I try to run this query I am getting a syntax error Too Few Parameters,
expected 20. The unique ID used in the WHERE clause is a string and I am
having problems with the proper syntax.

-------------------------
"UPDATE tblSecurity SET tblSecurity.SecurityNm = [tblHistory].[SecurityNm],"
& _
"tblSecurity.AssetClassID = [tblHistory].[AssetClassID],tblSecurity.ExpDate
= [tblHistory].[ExpDate], tblSecurity.CreditID = [tblHistory].[CreditID], " &
_
"tblSecurity.Risk = [tblHistory].[Risk], tblSecurity.Taxable =
[tblHistory].[Taxable], tblSecurity.SecurityType =
[tblHistory].[SecurityType], " & _
"tblSecurity.SchwabType = [tblHistory].[SchwabType], tblSecurity.ExpYr =
[tblHistory].[ExpYr], tblSecurity.ExpMt = [tblHistory].[ExpMt],
tblSecurity.SectorTypeID = [tblHistory].[SectorTypeID], " & _
"tblSecurity.IndustryTypeID = [tblHistory].[IndustryTypeID],
tblSecurity.MStarRate = [tblHistory].[MStarRate], tblSecurity.FairValue =
[tblHistory].[FairValue], " & _
"tblSecurity.[P/E] = [tblHistory].[P/E], tblSecurity.[P/B] =
[tblHistory].[P/B], tblSecurity.[P/CF] = [tblHistory].[P/CF],
tblSecurity.Moat = [tblHistory].[Moat], " & _
"tblSecurity.RatingDt = [tblHistory].[RatingDt], tblSecurity.MStarRisk =
[tblHistory].[MStarRisk], tblSecurity.LastUpdDt = Date() " & _
"WHERE (((tblSecurity.CUSIP)=""CUSIP""))"
Click to expand...
Click to expand...
Click to expand...
 
Top