[RESOLVED] Method not valid without a suitable object error

[bx51dtu]

Hi all,

This is a first time post and am using VBA to access a worksheet built in Excel 2013, extract information and group according to three fields.

The current version of the SQL string I am trying to use is as follows but am getting the "Method not valid without a suitable object". I understand that this means I am trying to do something with a field but it is not in the correct format. I have tried numerous versions but without success.

The work sheet is simply multiple rows of data in a non specific sequence. All fields are formatted as you would expect i.e General, Number, Time etc.

I want to summarise a group of likeminded rows at a particular level. For instance, each row has an arrival time. I want to find the first and last arrival time in likeminded rows before they are grouped so I can save the two pieces of information. I also want to count the number of rows and to derive a total of a couple of numeric fields.

Row 1 has the Column headings.

The SQL string is:
sqlstring4 = "SELECT [xxxx_X], [xxxx_Y], [xxxx_Canx], Sum (xxxx_Total_1] As [WorkSheetData$].[result_Total_1]), Max ([xxxx_Time_1] As [WorkSheetData$].[result_Time_1]), Count ([xxxx_Records_1] As [WorkSheetData$].[result_Records_1]) FROM [WorkSheetData$A1:Y625] Where [WorkSheetData$].[xxxx_Canx] = " & "'" & [sqlCanx] & "'" & " Group By [xxxx_X], [xxxx_Y], [xxxx_Canx];"

Hope you can help.

Many thanks.

[techgnome]

It's your aliasing:

 Max ([xxxx_Time_1] As [WorkSheetData$].[result_Time_1]), Count ([xxxx_Records_1] As [WorkSheetData$].[result_Records_1])

Specifically this part: [WorkSheetData$]. you don't need it. You don't need (or can't it seems) put a table name on a field alias. There's really no need to. It should simply be like this:

 Max ([xxxx_Time_1] As [result_Time_1]), Count ([xxxx_Records_1] As [result_Records_1])

If anything, if you do need to include a table name reference - and since you're only pulling from one table, you don't - then it would be inside the aggregate function.

-tg

EDIT... wait... that's all still screwed up. The alias should be OUTSIDE the aggregate, not inside...

 Max ([xxxx_Time_1]) As [result_Time_1], Count ([xxxx_Records_1]) As [result_Records_1]

[bx51dtu]

Hi Techgnome,

Many thanks for your very prompt reply. I shall try this tomorrow morning. Thanks again

[bx51dtu]

Hi Techgnome,

I made the changes as you suggested but it is still coming up with the same error.

The code is now:
sqlstring4 = "SELECT [xxxx_X], [xxxx_Y], [xxxx_Canx], Sum ([xxxx_Total_1]) As [result_Total_1], Max ([xxxx_Time_1]) As [result_Time_1], Count ([xxxx_Records_1]) AS [result_Records_1] FROM [WorkSheetData$A1:Y625] Where [xxxx_Canx] = " & "'" & [sqlCanx] & "'" & " Group By [xxxx_X], [xxxx_Y], [xxxx_Canx];"

I have double checked the fields on the source data worksheet and they are formatted correctly.

I am sure I have messed it up somewhere. The original file is not sorted in any order.

Any ideas?

Many thanks

[techgnome]

The only other thing I can think of is this: [WorkSheetData$A1:Y625] you've got a range on there... try removing it and having just the sheet name: [WorkSheetData$]


-tg

[westconn1]

the range should not cause a problem

i would test with a simple sql, if that works keep building it up till you find the part that causes the error,
if not check your connection object is correct

[bx51dtu]

Hi Techgnome, westconn1,

I did exactly what you said. I removed the range and stripped the SQL statement all the way back to "Select *" and it worked thereby proving the connection was ok.

I then added each field to the Select statement and tested they worked. Then went onto the Where clause. That failed without punctuation but worked with punctuation. And that is where I found part of the problem. It seems that my Excel, once it finds an error, doesn't let go of it even though it had been fixed. Once I came out of Excel and then went back into it, the error disappeared.

Once I realised that the error remains, even when the VBA code was amended, I was able to put each field back, one by one, testing as it went (and exiting / reloading Excel whenever a typo error was encountered). Now the Group, Min, Max and Count statements all work.

I have tested the code and it is all working exactly as intended. I am really very grateful to both of you for your help and steering me along the right path.

Many thanks.

[westconn1]

pls mark thread resolved