Hi Trevor, thanks for the post, life is treating me generally well :)
I'll have a play around with that and see where it gets me. For now I've gone the other way - created queries in Access with variables for criteria and am cycling through a recordset as I open and close all the spreadsheets...
G'day all, long time... Hope everyone's well!
I've got this spreadsheet with four tabs on it. Three of them have querytables looking at an Access DB and all is well. But...
I want to change some of the criteria in a couple of the queries to reflect what's in a particular cell on the...
Morning campers! YES! a 2D array might just work....
I read somewhere on t'interweb that 29 is the limit for parameter arrays. As it happens I've got 29 countries as arguments, so that was lucky :) I tried putting 30 test arguments in, and it comes back with something about it being too complex...
Grr though.... maximum arguments = 29, I already have 29 countries to put in there - git!! Do you know if that's just a limit on paramarrays, or arguments in general? I'll have aplay tomorrow, home time now :)
Hi all, how's it going? Got an annoying one.... (aren't they all)
I've got a routine that looks like this:
Public Function Max_Salesorg(ParamArray flds() As Variant) As Double
Dim i As Byte, MaxOrg As String, MaxVal As Double
MaxOrg = flds(1).Name
MaxVal = flds(1)
For i = LBound(flds) + 1 To...
Forgotten I'd started this thread....
Not a design issue at all - I needed average sales, where there were sales, across a number of countries, represented by a number of different fields in the table.
VBA's solution worked perfectly.
That's the automation part. I'm not 100% sure on the syntax to be honest, but I'm thinking something along the lines of the following in VBA:
Create a table
Populate each cell with the data from the recordset
Insert a page break
Move to next record
Repeat the above until EOF.
There'll be a...
There's a few things that could cause problems, data types being one of them, primary keys being another. Have a look in the MS knowledge base and see if that helps:
http://support.microsoft.com/kb/304466
I would do a debug.print on all those variables and see what's in them. First thing I saw that would error out is the len(m2)... expressions - if m2's length is zero, and you subtract 2, the left function will produce an error.