Another too few parameters for ya 2

[Trudye]

I have two SQL statement that are almost identical, the first one works but the second one blows up. Err Msg: Too few parameters.

I have ck'd the spelling of the fieldnames (they are the same in both) and the spelling of the string value (I even pasted it in. Does anyone have any idea what I could be missing.

strSQL = "SELECT Max(DealDetail_Hdr.COST) AS MaxCards, DealDetail_Hdr.Item " _
& "FROM (Clients " _
& "inner Join Deals on Clients.ID = Deals.ClientId) " _
& "inner Join DealDetail_Hdr " _
& "on Deals.DealID = DealDetail_Hdr.DEALID " _
& "WHERE clientid = " & sID & " And Item = '" & strCards & "'" _
& " GROUP BY DealDetail_Hdr.Item, Clients.ID"
Set rst = db1.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
sItem = rst!MaxCards
GoSub Write_Recs
End If

strSQL = "SELECT Max(DealDetail_Hdr.COST) AS MaxAppear, DealDetail_Hdr.Item " _
& "FROM (Clients " _
& "INNER JOIN Deals on Clients.ID = Deals.ClientId) " _
& "INNER JOIN DealDetail_Hdr " _
& "ON Deals.DealID = DealDetail_Hdr.DEALID " _
& "WHERE clientid = " & sID & " AND Item = " & "Appearance " _
& " GROUP BY DealDetail_Hdr.Item, Clients.ID"
Set rst = db1.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
sItem = rst!MaxAppear
GoSub Write_Recs
End If

Thanks much
Trudye

 

[RoyVidar]

What is Appearance ?

If Appearance is a numeric variable:

[tt]& "WHERE clientid = " & sID & " AND Item = " & Appearance _[/tt]

If it's a string variable (as indicated by the first SQL statement)

[tt]& "WHERE clientid = " & sID & " AND Item = '" & Appearance _
& "' GROUP BY DealDetail_Hdr.Item, Clients.ID"[/tt]

If it's a string litteral:

[tt]& "WHERE clientid = " & sID & " AND Item = 'Appearance'" _[/tt]

Roy-Vidar

 

[r937]

the 2nd fails because you have a line in there that says

... WHERE clientid = 937 AND Item = Appearance

most likely you don't have an Appearance column, right?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Trudye]

Thanks Guys for getting back to me so quicky. Roy as usual you were 100% correct.Posting on Tek Tips is a humbling experience. I know that a string has to have single quotes. Just goes to show you can stare directly at an error and not see it. I'm ashamed to tell you how long I looked it.

For example this next one, the err msg says there is a problem with the JOIN. I re-created the SQL in a query and except for the HAVING statement our code is identical.

What am I missing this time?

Thanks again Guys
Trudye

strSQL = "SELECT max(Cost) AS MaxPhoto16x20, [Deal Detail].Item " _
& "FROM (Clients " _
& "JOIN Deals on Clients.ID = Deals.ClientId) " _
& "JOIN [Deal Detail] on Deals.Dealid = [Deal Detail].Dealid " _
& "WHERE Clientid = " & sID & " AND Item = " & "16x20 Photo " _
& "GROUP BY [Deal Detail].Item, Clients.Id "
Set rst = db1.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
sItem = rst!MaxPhoto16x20
GoSub Write_Recs
End If

 

[Trudye]

Sorry, sorry, I know, I know single quotes again.

TRudye

 

[Trudye]

I was wrong (Suprise, surprise), I'm still getting the err msg.

strSQL = "SELECT max(Cost) AS MaxPhoto16x20, [Deal Detail].Item " _
& "FROM (Clients " _
& "JOIN Deals on Clients.ID = Deals.ClientId) " _
& "JOIN [Deal Detail] on Deals.Dealid = [Deal Detail].Dealid " _
& "WHERE Clientid = " & sID & " AND Item = '16x20 Photo'" _
& " GROUP BY [Deal Detail].Item, Clients.Id "
Set rst = db1.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
sItem = rst!MaxPhoto16x20
GoSub Write_Recs
End If

Trudye

 

[Golom]

Is it just a typo or have you lost the "INNER" on your "JOIN"s?

 

[Trudye]

GET OUT!! There is no way I would have believed the problem was a missing INNER. I thought INNER was implied/the default?

Thank you so much Godom I see why you are an MVP, its always the little things isn't it?

BE well,
Trudye