Wrong number of arguments with function in query expression error message

Question

Receiving the above error message, I am modifying code that was written for me.

I am trying to produce a table that sorts data based upon two parameters, the first is a text name (city), the second is a numeric field (area).

The original code;

Public Function bytZoneOrder2(strArea, strCity As String) As Byte

"Select case strArea"

I thought if I added another select case, that would enable me to use both fields.

How do you add another select case?

Thx

Answer 1

You say you want to 'sort data based upon two parameters', but parameters are normally used to restrict the result table of a query, not sort (order) it.  If we take data from Northwind as an example, the following would be a sort:

SELECT [First Name], [Last Name] , [State/Province], City

FROM Customers

ORDER BY [State/Province], City;

and would return a result table like this:

First Name      Last Name    State/Province    City

Thomas          Axen             CA                      Los Angelas

Alexander       Eggerer         CA                      Los Angelas

Daniel            Goldschmidt  CA                      San Francisco

Carlos            Grilo              CO                      Denver

Soo Jung        Lee                CO                     Denver

Peter              Krschne         FL                       Miami

Run                Liu                 FL                       Miami

and so on

The following would be restriction on State/Province and City:

SELECT [First Name], [Last Name] , [State/Province], City

FROM Customers

WHERE [State/Province] = "CA"

AND City ="Los Angelas";

and would return the following two row result table:

First Name      Last Name    State/Province    City

Thomas          Axen             CA                      Los Angelas

Alexander       Eggerer         CA                      Los Angelas

A sort and restriction can of course be combined in a single query.

The parameters in the second example above are literal strings within the query, but in reality they'd be variable values passed into the query at runtime.  In either case the result table of the query could be exported to Excel.

So which is it you want, a sort or a restriction?  Whichever is the case it is difficult to see why anyone would do this by means of a Select Case construct in a VBA function.  Perhaps if you posted the full code of the function we'd be able to see what the original writer of it intended.

Answer 2

It sounds like you will need two Queries - one grouping by city name, and the other grouping by MLS area (whatever that is, it's not a term that I know). Once you create the queries you can use External Data... Export on the ribbon, or VBA code using the TransferSpreadsheet method to export them. 

The one line of code - actually just a quoted text literal - that you posted does nothing at all; is that all the code that you have? If not could you post the actual code?

Answer 3

I am trying to create an output table that groups real estate data by two different geographic categories, one group is based upon the city name and the other is based upon the MLS area. The current code was written to export the grouped data to an excel spreadsheet. The current code groups by MLS areas, not by city. I am trying to figure out how to create the excel spreadsheet than contains groupings by MLS area and city location.

Thanks for response.

Answer 4

Sorry, but you're  not just barking up the wrong tree - you're in the wrong woodlot! :-{)

If you want to take the data stored in a Table and display it sorted in a specific order, you don't need or want any VBA code at all. It's the wrong tool! Instead you want a Query. Create a new Query based on your table; include the city and area fields, along with any other fields you want displayed. On the query grid there will be a "sort" row under the fields; put Ascending under these fields. The sort will be applied left to right. I have no idea what you intend: do you want to see the cities alphabetically showing the area, or do you want them sorted by area, with them sorted alphabetically within blocks of identical area? What is your code intended to accomplish? Or are you using the term "sort" in some other sense than its Access meaning: "to put a set of records in a sequential order"?