SQL ERROR - Runtime error 3061 -

[QTNM]

SQL Issue ...

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

------------------------------------------------------------------------

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));"

ANY HELP WOULD BE APPRECIATED ...

Cheers,
QTNM

 

[RV]

You can't simply create a dynamic query and copy and paste the statement into VBA.
VBA comes with specific syntaxis.
See for instance this thread, should help you to get things started:

http://www.access-programmers.co.uk/forums/showthread.php?t=89422

RV

 

[QTNM]

Yes, I understand ...

I know that you cannot just take the sql code and paste it into VBA ...

But it doesn't really answer my question ..... I tried quoting the single quotes as in the link you posted .... same error.

Any suggestions are highly welcome ... need to finish up this customization and it's really got my hands tied right now ....

 

[RV]

I know that you cannot just take the sql code and paste it into VBA ...

Doesn't show from your code though so you're contradicting yourself .....
Now I did refer you to a good link, which apparently wasn't really appreciated.
Not much more I can do here I guess, good luck.

RV

 

[RuralGuy]

I would suggest putting your SQL into the Query Builder and let it work out the problems.

 

[QTNM]

No room to argue ....

RV .... "I tried quoting the single quotes as in the link you posted .... same error ... " - I did look into the link you posted, and I do appreciate it highly!

In many cases, when I am having difficulty with a query I will throw it into a query builder and try to troubleshoot it from there .... (HENCE, what RuralGuy suggested ) .... however, I have been unable to successfully get it to work, telling me all the table and field names are correct and the forms are open and recognized.

Now I KNOW the issue is arising due to the "* ROLL*" part of the statement .... I just don't know how to fix it ..... BUT I DID TRY THE SINGLE QUOTING OPTION IN THE LINK YOU POSTED, it did not work .... SO don't be so quick to judge .... read the posts and you'll get it.

Let's just get back to the issue at hand .... I do APPRECIATE any suggestions!

THANKS!
QT

 

[RuralGuy]

Do I understand this correctly?
(([DT: InventoryExtend].Description) Not Like '* ROLL *')

You do *not* want the stand alone word "ROLL" to be anywhere in the Description field?

 

[WayneRyan]

I would imagine that this might be a case where there are single-quotes in
the data. Try using double-quotes as a delimiter.

Not Like """* Roll *"""

Wayne

 

[QTNM]

yes, I tried the single quotations, does not work.

 

[QTNM]

I would imagine that this might be a case where there are single-quotes in
the data. Try using double-quotes as a delimiter.

Not Like """* Roll *"""

Wayne

Are those 3 sets of double quotes on each side of the string ??

I will try this ..... thanks for your suggestion

QT

 

[WayneRyan]

QT,

Are there single quotes IN the data?

Wayne

 

[QTNM]

There are no single quotes in the data .....

 

[WayneRyan]

QT,

Can you post a sample? This shouldn't be that difficult.

Wayne

 

[QTNM]

Sample data ...

Partially, below is the data from the [DT: InventoryExend] table included in the SQL statement you requested:

Part# CategoryID Description
RP0046 30 ACRYLIC ROLL 75" W x 3/16" WHITE, LFT
RP0043 30 ACRYLIC ROLL 63" W x 3/16" CLEAR
RP0003 30 4' x 8' x 1/8" WHITE PLEX. SQ. FT.
RP0002 30 4' x 8' x 1/8" CLEAR PLEXIE SQ. FT.
RP0053 30 COOLEY T/C VINYL 62" WHITE
RP0044 30 ACRYLIC ROLL 63" W x 3/16" WHITE
RP0054 30 COOLEY T/C VINYL 78" WHITE

My bad I understood your post as, "is there single quotes in the statement" ------ YES, there are single quotes in the data .... but why would that affect the statement ??

I think you are onto something ... but I am not quite following.

QT

 

[QTNM]

Not Resolved .....

Still not resolved ... this is not makin' any sense ....

NOT HAPPY ..

AAAahhhhrrrggg ... spend so many useless hours on one little glitch .... moved on ... but I still need to figure out this mess.

If any one else has some suggestion .... highly, HIGHLY APPRECIATED!

QT

 

[RV]

The problem is definitely NOT with your LIKE clause....
Copy this code exactly as stated below and give it a go

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#] " _
& "WHERE [DT: InventoryExtend].[CategoryID] = 30 " _
& "AND [DT: InventoryExtend].[Description] Not Like '*ROLL*' " _
& "AND [forms]![signpro sign estima parameters]![combo14]=1;"

RV

 

[WayneRyan]

QT,

When VBA parses your "sql" statement it gets:

[Description] Not Like 'RP0003 30 4' x 8' x 1/8" WHITE PLEX. SQ. FT.

Put yourself in the poor parsers situation

[Description] Not Like 'RP0003 30 4' x 8' x 1/8" WHITE PLEX. SQ. FT.
                       ^           ^
                       |           |
     Start of string --+           +--- End Of string

If you do the following:

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#] " & _
      "WHERE  [DT: InventoryExtend].[CategoryID] = 30 And " & _
      "       [DT: InventoryExtend].[Description] Not Like """ *ROLL* """ And " & _
              [forms]![signpro sign estima parameters]![combo14] & " = 1;"

It handles those single quotes quite nicely:

BUT what does it think about the 1/8" ????

You have both single and double-quotes in the Data!

I think if you use a query with the Criteria for the Description field:

Like "*" & Forms![SomeForm]![SomeField] & "*"

that you'll be OK.

I probably won't sleep until I hear that you have it resolved.

Wayne

 

[WayneRyan]

QT,

Got this idea from Liv Mantro in another forum here.

Just remove all ambiguous characters (single/double quotes):

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#] " & _
      "WHERE  [DT: InventoryExtend].[CategoryID] = 30 And " & _
      "       Replace(Replace([DT: InventoryExtend].[Description], "'", ""), """", "") Not Like """ *ROLL* """ And " & _
              [forms]![signpro sign estima parameters]![combo14] & " = 1;"

Wayne