Incorrect syntax?

**supportservice** · 08-25-2015, 12:29 PM

Hello,
There's this Module in Excel file (.xlsm)

For some reason it is erroring.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I cannot figure out what it is. When I click on Debug it takes me to the Module and to this line

Please Login or Register  to view this content.

In the Properties under Connection, the SQL works. Verified it on SQL Server Management Studio 2014.

Help would be appreciated.

Here's the SQL that works as is in the Connection Properties for the Excel Macro:

Please Login or Register  to view this content.

**judgeh59** · 08-25-2015, 12:40 PM

just a little confused....the only thing I see "weird" is, in the SQL on the line where "pm", you have a few +'s and I'm wondering if they should be &'s....

but you say the SQL is working so I'm not sure exactly what's going on..

I could be totally off base...

**supportservice** · 08-25-2015, 01:11 PM

Since I'm not that familiar with working with either, what I did try is the SQL and it produces the results.

But do not see what could be wrong on the Excel side.

All I know is that it's erroring on 'pm' somewhere.

hope someone can see what and where the 'pm' is a problem

**supportservice** · 08-25-2015, 01:38 PM

This is the SQL:

Please Login or Register  to view this content.

How about this ...
How do I change this for user input for the date range portion?

Please Login or Register  to view this content.

**judgeh59** · 08-25-2015, 02:37 PM

a few questions...

1) are you still getting the error?
2) are you running this SQL (post #4) from an XL Macro?

I use the code below for Oracle SQL when running from a PL/SQL window (NOT XL)

Please Login or Register  to view this content.

this technique prompts the user for input

Please Login or Register  to view this content.

in XL I would create a variable and use INPUTBOX to ask the user for the date

then change your code to the following:

Please Login or Register  to view this content.

**supportservice** · 08-25-2015, 03:28 PM

I will try that ...

Anyone know why I am getting the error though?

Please Login or Register  to view this content.

If that can be found and fixed, trying to change and do the input parameters will be moot.
I was going for the input parameters if can't find the resolution to the original error.

However if using the same code and modifying for the date prompts, the error will still exist.

**judgeh59** · 08-25-2015, 03:37 PM

have you tried changing the +'s to &'s

Please Login or Register  to view this content.

**supportservice** · 08-25-2015, 04:03 PM

Originally Posted by judgeh59

have you tried changing the +'s to &'s

Split3 = " PersonMembership.CreatedDate > DATEADD( day, -120, GETDATE() ) AND " & _
" ( ( SUBSTRING( invWork.BACHNUMB, 1, 8 ) >= '" + StartingBatchDate + "' AND SUBSTRING( invWork.BACHNUMB, 1, 8 ) <= '" + EndingBatchDate + "' ) " & _
"OR ( SUBSTRING( invHist.BACHNUMB, 1, 8 ) >= '" + StartingBatchDate + "' AND SUBSTRING( invHist.BACHNUMB, 1, 8 ) <= '" + EndingBatchDate + "' ) ) ) pm "

Replaced with that and still the same error

**judgeh59** · 08-25-2015, 04:05 PM

the best I can come up right now is to have you upload the file so I can see what is going on....right now I'm guessing on how this is all interconnected....

sorry about that...

**supportservice** · 08-25-2015, 04:14 PM

Originally Posted by judgeh59

the best I can come up right now is to have you upload the file so I can see what is going on....right now I'm guessing on how this is all interconnected....

sorry about that...

I'm attaching. the codes shared at the beginning is a copy & paste from the .xlsm file, so not sure having the Excel file with the codes in it makes much of a difference?

Membership Card Export.zip

**judgeh59** · 08-25-2015, 04:36 PM

I'm sending this to the Guru's.....sorry I couldn't help....

**Kyle123** · 08-25-2015, 05:08 PM

You're missing a comma and a space after REGION in the second line. I don't think it's causing your error but it's not helping

**GeneralDisarray** · 08-26-2015, 11:12 AM

Looks like you have an extra closing parenthesis on your where clause (in the excel macro) right before the alias name pm.

EXCEL:

Please Login or Register  to view this content.

SQL FROM OTHER POST:

Please Login or Register  to view this content.

I'm just comparing those 2 lines.

If that doesn't work - I would interrupt the code and somehow get excel to kick the full SQL statement to a workbook - then I would copy out that text to the mgt studio and see what happens when I run it.

It should be easier to see any bad chracters (missing spaces, extra parenthesis, unclosed single quotes, etc.) from within the management studio.

**supportservice** · 08-26-2015, 01:25 PM

I removed the "extra" ")" and still the same error.
NOTE: It appears "AS" is allowed or rather used in the Module. It errors when I added that there as well.

In Split 2 is where the Alias is assigned from what i can tell. However when the 'pm' is removed at the end of that substring line the error is on 'JOIN' at the end of Split 3.

The references used in these to sections only has PersonMembership from attribute.PersonMembership or the Alias 'pm'

Yet in the first line in Split 4, it uses the Alias 'pm'.

I've tried replacing the PersonMembership to pm as well as attribute.PersonMembership in Split 2 and Split 3, no go. same error.

And attempting to Step Into and Run, it errors at

ActiveWorkbook.Connections("RegularMemberships").Refresh

**supportservice** · 08-26-2015, 01:54 PM

I also copied the SQL that works in SSMS and used in the Properties Definition for the Connection and split it in the Excel Module:

Please Login or Register  to view this content.

The error is now on 'attribute'

**supportservice** · 08-27-2015, 09:55 AM

Any Excel experts out there see why still would be happening and what needs to be modified?

**Kyle123** · 08-27-2015, 10:28 AM

Why aren't you outputting the result and running that? If you do your current issue becomes clear:

Please Login or Register  to view this content.

You've also still not added the required space after REGION

**Kyle123** · 08-27-2015, 10:35 AM

You're also missing a comma after 'CityStateZip' (as well as REGION)

**supportservice** · 08-27-2015, 11:30 AM

Kyle123,
those did not work.

same error

Incorrect syntax near 'pm'

**supportservice** · 08-27-2015, 11:46 AM

Finally figured it out.

Changed from this

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

Oddly the other placements didn't error that wasn't using 'pm'

I also removed the CreatedDate line. They said it's incorrect anyway to use that field.

**GeneralDisarray** · 08-27-2015, 01:33 PM

EDIT: I realize this is solved for you - but this general idea below (work it out in SMSS, go to notepad to add the S = S & " , then copy to VBA and alter as needed with variables in place of hard coded values...) is good to know in general. You would have found your error immediately in the management studio (it's hard to see it once you are letting excel run it under the hood).

Well - I'm having trouble reading through all of that - here's my suggestion.

IN GENERAL - when you have a query that is working well in the SMSS just copy it over directly to notepad and add S = S & " To ever line. THERE IS A SPACE THERE!

THEN just copy over your notepad code to the code module and viola - usable code is born!

NOTE: I got this from your earlier post - you indicated that the query did in fact work.

When you paste your Notepad text into your VBA module, excel adds the final " on each line. The nice thing about this is you can be certain you have all the spaces needed...

All you need to do is place this in the code, then pass it to your command text :D you'll just be passing "S" instead of Split1 + Split2 + ..... etc.

Please Login or Register  to view this content.

**supportservice** · 08-27-2015, 03:00 PM

Good to know! i'll try that.
Though when i copied the SSMS working SQL, it still error'd

Incorrect syntax?

LinkBack

Thread Tools

Rate This Thread

Display

Incorrect syntax?

Re: Incorrect syntax?

Re: Incorrect syntax?

Re: Incorrect syntax?

Re: Incorrect syntax?

Re: Incorrect syntax?

Re: Incorrect syntax?

Re: Incorrect syntax?

Re: Incorrect syntax?

Re: Incorrect syntax?

Re: Incorrect syntax?

Re: Incorrect syntax?

Re: Incorrect syntax?

Re: Incorrect syntax?

Re: Incorrect syntax?

Re: Incorrect syntax?

Re: Incorrect syntax?

Re: Incorrect syntax?

Re: Incorrect syntax?

Re: Incorrect syntax?

Re: Incorrect syntax?

Re: Incorrect syntax?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Nested IF AND not working as I expect - Returns 'False' so probably is incorrect syntax

SQL code in VBA - incorrect syntax near

Incorrect Vlookup syntax

[SOLVED] Incorrect Syntax

IF(COUNTIF statement seems to have incorrect syntax

Incorrect syntax near '#' problem when returning to MS Query fromExcel

[SOLVED] Incorrect syntax near '#' problem when returning to MS Query fromExcel

Bookmarks

Bookmarks

Posting Permissions