SQL Issue ... Runtime error 3061

[QTNM]

Not sure how to work in the '* ROLL *' into this SQL statement. The query statement works fine ... I have tried different quotation methods ( Not Like " & " '
* ROLL * & ' " & " ) AND .... )

sql = "SELECT DISTINCTROW Sum(CDbl([Scrap Factor])) AS SumOFScrap FROM [RT: Signpro1: Costs] LEFT JOIN [DT: InventoryExtend] ON [RT: Signpro1: Costs].[Part Number] = [DT: InventoryExtend].[Part#] GROUP BY [DT: InventoryExtend].CategoryID, [DT: InventoryExtend].Description, [forms]![signpro sign estima parameters]![combo14] HAVING ((([DT: InventoryExtend].CategoryID)=30) AND (([DT: InventoryExtend].Description) Not Like '* ROLL *') AND (([forms]![signpro sign estima parameters]![combo14])=1));"


ERROR: Runtime error 3061 - Too few parameters. Expected 1.

ANY HELP WOULD BE APPRECIATED.

Cheers,
QTNM

[ScaryBinary]

Looks like you're using Access or SQL Server....? In their infinite stupidity, Microsoft made the percent sign (%) the wildcard for SQL server, but the asterisk (*) the wildcard in Access. So depending on where you're sending your query, you may need a different wildcard. I'd guess you're using Access since you're referencing forms, but you might want to try the percent sign anyway:.NOT LIKE "% ROLL %".

Help on LIKE from MSDN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_3qpf.asp)

That error message looks familiar....I think I've seen it when I mis-spell a field name, but if you're sure the rest of the query works, then try the percent sign for the wildcard.

What's with the "DT" and "RT" in your field names? I've never seen that before.

[ScaryBinary]

Doh...nevermind my last message. In all reality, even if you did use the wrong wildcard character, you'd probably just get an empty recordset as your result......not an error message. I'll take a closer look at your SQL....

:rolleyes: :)

[QTNM]

Just wondering if this post is in the right section .... not getting much feedback ..... it's now Monday and my client is on the prowl for this update ...... ARRRGGG!! :mad:

The "DT" and "RT" are NOT part of the field names ..... these are part of the table names. I did not design this DB, I'm just customizing it for my client. Like most DB's you walk into ..... it's not Kosher programming or even logical for that matter ( in ANY sense ) .... kid straight out of school, but the DB works, and that's all the client cares about.

ANY SUGGESTIONS ARE HIGHLY VALUABLE AT THIS POINT !!! HELP !! : (

It's something to do with the quoting around " * ROLL * " - I've tried many different combinations .... ALL TABLES AND FIELDS ARE SPELT CORRECTLY, this works when I import it into a query design.

THIS DB WAS BUILT IN MS ACCESS 97 .... if that helps any !!

QT

[ScaryBinary]

The only other quote trick I know is to use Chr(34) for the double quote. Thus, your SQL statement would be something like....(([DT: InventoryExtend].Description) Not Like " & Chr(34) & "* ROLL *" & Chr(34) & ") AND....

Also, you have a reference to a form control in your query in several places...If I remember correctly, the form has to be open for this to work. Make sure that's the case when you're querying the database from code.

[QTNM]

I tried the chr(34) trick ..... no luck.

Yes, the form is open .... like I mentioned .... when I import this sql statement into a query it works just peachy. But there is something wrong with the quotations!! ............ STILL CANNOT FIGURE IT OUT !!

ANY OTHER SUGGESTIONS ARE APPRECIATED !!!

QT :mad:

[ScaryBinary]

It's not the quotes.

Apparently you can't refer to the forms in the way you are (for example, "[Forms]![SomeForm]![SomeControl]"), in code. This is why it works in the query window but not in the code. I did a little test at home and verified this.

In your code, before you construct your SQL, add something like thisstrCombo14 = Forms("signpro sign estima parameters").Controls("combo14") for each control you need to use in the query. Basically you want to store the value for the control in a variable. You will probably need to adjust your variable type depending on what kind of value (text, integer, etc.) the control returns when you reference it. "strCombo14" might actually be an integer, for example.

Then change your SQL to something like....Not Like '* ROLL *') AND ((" & strCombo14 & ")=1));, in otherwords, replace the [Forms]![SomeForm]![SomeControl] stuff with your appropriate variable.

This theory matches up with the error message you were getting. The error message indicates that Access was looking for a missing parameter - a parameter it couldn't find because it didn't recognize the form syntax (that's why I asked if you spelled all the field names properly; you would have gotten the same message if you had misspelled a field name).

[QTNM]

Thanks Scary Binary .... however, this DB was created in Access 97 ... I have refered to fields in this manner many a times .... within all the modules in the DB, fields are refered to like this .... and it works just fine.

My best guess, is it's the quotations .... Yes, I could be wrong.

Are you testing this in Access 2000 or higher. Just curious. I knot the VBA is a little different in versions 2000 +. DAO to ADO, etc.

Any suggestions ...

QTNM :r

[ScaryBinary]

Ya, I was using Access 2000. When I referred to forms like you did, I got the same error you are getting. When I changed the form field references, it worked.

I just can't find anything wrong with the quotes. :confused:

Have you tried just taking out the part of the SQL statement with those quotes and seeing if it works? In other words, change your code query to:

sql = "SELECT DISTINCTROW Sum(CDbl([Scrap Factor])) AS SumOFScrap FROM [RT: Signpro1: Costs] LEFT JOIN [DT: InventoryExtend] ON [RT: Signpro1: Costs].[Part Number] = [DT: InventoryExtend].[Part#] GROUP BY [DT: InventoryExtend].CategoryID, [DT: InventoryExtend].Description, [forms]![signpro sign estima parameters]![combo14] HAVING ((([DT: InventoryExtend].CategoryID)=30) AND (([forms]![signpro sign estima parameters]![combo14])=1));"

If the query runs (and even if it doesn't return any results) then it's probably the quotes. If you still get the same error message, then we'll have to start looking at other things.

[QTNM]

Good Call ..... I'm going to try that right now .....

QT :(