Passing TempVar to SELECT Query (1 Viewer)

[bprisk]

Picture, if you will, a table with multiple fields, each of which contains the date of a certain action in the process tracked by each record. I have a standard report format that will be used to view the progress of the actions. I want to be able to sort the report - on demand by non-technical users - by whichever date field they choose.

I've created a form with a combo box that is linked to a table with the name of each field in the source table. A Macro has been configured to open the form when the report is opened, which will prompt for the selection of the sort field. That value is then captured into a TempVar.

When the user clicks OK control passes to a procedure in a module that executes a SELECT statement (SQL) that configures the Query that is used for report generation. The "ORDER BY" portion of the statement needs to be modified with the name of the sort field desired. If the statement is hard coded as, for example:

...ORDER BY Main.[Initiated Date] DESC;

it works fine. I want to use the value of the TempVar to provide the name of the date field (ex.: [Initiated Date]), but I can't determine the correct syntax to get the statement to accept it. Concatenating doesn't seem to work, and I've searched high and low for hints on the web. Here's one version of what I've tried (and which fails):

"...ORDER BY Main." & [TempVars].[SortParm] & ";"

The error returned by the above version is: "Object doesn't support this property or method"

(SortParm is the name of the TempVar, and it is correctly populating, and keeping, the value I need from the form.)

Any hints?

 

[spikepl]

Read carefully and use http://www.baldyweb.com/ImmediateWindow.htm because right now your method is akin to flying blind

My bet is that it is yet another case of spaces in field names wasting everybody's time.

 

[Galaxiom]

I recon spike is correct.

However your problems actually started when you put all the dates in separate fields. In a relational structure they should all be in the same field with another field to indicate whatever aspect of the data it is that you use to separate the dates into different fields.

 

[bprisk]

Galaxiom,

You hit the nail on the head! :banghead:

I'm "converting" an existing Excel table/Sharepoint List into a more "elegant" Access tool, and I hadn't normalized or planned the data structure to work like a relational database, vs a flat file. Your reminder of how I should be thinking puts me on a completely different - and I feel more correct - path for this project.

Thanks!!