Complex query help needed!

[maGemme]

Hopefully this won't be as complex as I imagine it!

I tried searching but am not sure what keywords to use properly so if this was addressed before, I appologise.

Here's my problem:

I have a table with multiple fields in it. Amongst the fields I have Agent_Name, Call_Date, talk_time, wrap_time, preview_time (there are more but i'm keeping my example simple).

Basically this will record all the calls we receive, the table records the name of the agent who took the call, the date and 3 different time references we use later.

Here's where I'm stuck. I want to build a form that would allow me to search that table. I need to be able for instance to find only the wrap_time for a single agent in the month of may.

The way I imagine this is with a drop down list that shows all available fields in the table. Then another drop down list that shows the agent's names and finaly a list box with 12 months that would allow the user to chose either a single month or multiple months (I know that's a problem in itself).

Right now my main concern is the first combo box, chosing which field to search from that list.

How should I approach this?

N.b. unfortunately I can't alter the orignal table's format as it comes from another system.

thank you in advance!

 

[jdraw]

Here's a video tutorial on a simple search form. I think it should help.
http://www.datapigtechnologies.com/flashfiles/searchform.html

 

[maGemme]

Well that's a great tutorial but it's not at all what I'm looking for.

First, I can't use indexes. Like I mentionned in my post, I cannot modify the table in any way (it's a linked excel spreadsheet).

I'll try to re-explain what I need.

The user selects an agent and the stat he wants from the drop down list. This drop down list contains the name of every field in the table.

The results that I want to show must only be the name and the chosen stat. I don't want all the other fields to show up (there's about 20 in total).

 

[boblarson]

You would have to create your own query on the fly in the combo's After Update event.

Something like this:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb

Set qdf = db.QueryDefs("YourBaseQueryNameHere")

strSQL = "SELECT " & Me.YourComboNameHere & " FROM TABLENameHere"

qdf.SQL = strSQL
qdf.Close

DoCmd.OpenQuery "YourBaseQueryNameHere"

 

[maGemme]

ok, is the "YourBaseQueryNameHere" query a simple query of the whole table?

 

[boblarson]

ok, is the "YourBaseQueryNameHere" query a simple query of the whole table?

Well, it could be but it really doesn't need to be anything more than a single field in it. By changing the SQL using the qdf, it becomes what you make it at that time and it retains that until the next time you do change it.