hope I am defining the title right. Here goes with the problem...
I had created a pass through query a while back (still green, not much better now...) to get information on staff to form a phone list for each office. The SQL statement on the server side grabs the needed fields and creates a header field (LEFT(dbo.StaffListings.LastName, 1) AS Heads) for separating out the sort into groups. As define, it grabs the first letter of the last names. The report has been painstakingly formatted to separate out the names/numbers and such giving it a nice look to it.
Now that the use of this is spreading, there are requests for different sorts done on the information. I was hoping to not have to rebuild the report, if at all possible. Here is a snip of the code (leaving out some company info) in access vba:
'add the ability to add in the passed value to the stored procedure
sqlStr = sqlStr + " @P;"
'create the query if it does not exist, The actual query (c_Name) must exist as a query in the DB
If DCount("*", "MSysObjects", "name = '" & c_Query & "'") = 0 Then
Set qdf = CurrentDb.CreateQueryDef("")
.Connect = c_Connect
.Name = c_Query
.SQL = sqlStr
Set qdf = CurrentDb.QueryDefs(c_Query)
'reconnect just in case
.Connect = c_Connect
.SQL = Replace(sqlStr, "@P", passVal)
Set qdf = Nothing
'run the report in Print preview
DoCmd.OpenReport aReport, acViewPreview
Again, it's been a while since I did this, and haven't done anything similar since, so I am drawing a few blanks (ya, need better documentation in the future).
I have made a few changes to the form that initiates this code to include a combobox that lets the user select predefined field names to sort by. I have made a new stored procedure that omits creating the Heads field, and returns the maximum required data. I am hoping to be able to re-query this data to add in the Heads field, and possibly sort it, as if not, I would have to look up vba call to a report and adjust the sort there.
At this point, if I am looking at this the wrong way, please let me know. Any help would be greatly appreciated.