Solved

Access97 won't pass field names as a query parameter

Posted on 1998-07-10
5
293 Views
Last Modified: 2012-05-04
Here's a question that's really bugging me... it's quite simple in nature but Access just won't work for me.

Here's the situation:
I have data tables set up like this:

AdID SalespersonA SalespersonB SalespersonC ...
-----------------------------------------------
1                     1
2         1
3         1
4                                   1
.
.

Whereas whenever a salesperson makes a sale, a 1 is inserted in the appropriate AdID row.  Possibly not the best set up table, but thats what I have to work with.

I want to create a query where the user is prompted to enter a salesperson name, then the system returns the AdIDs corresponding to that salesperson.  Using Access's query design feature I come up with this:

Type: Select      
Field: [Enter Salesperson name]
Table: Sales
Criteria: 1

Simple, huh?  Well, when I switch to the design view Access prompts me for the salesperson name.  After entering the text, Access replies "Expression typed incorrectly, or is too complex to be evaluated".  

The same query works just fine when I 'hard code' a field into the search.  Seems like this should work... try it on your machine if you can't believe it.

Can you figure out why Access won't let me pass a field name in a query as a paramter?  Its causing me a lot of headache, please help!!
0
Comment
Question by:Beans0063
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 4

Accepted Solution

by:
tomook earned 50 total points
ID: 1976499
Sorry, Access does not let you do this they way you want. Here is another method which may help: dynamically altering the SQL in a QueryDef. Make a query called Query1. Set up a form with a combo box (Combo1) which contains the salesperson names. Set up a button (Button1), and in the Click event handler:

Dim qd As QueryDef
Dim strSQL As String

Set qd = CurrentDB.QueryDefs("Query1")
strSQL = "SELECT AdID, [" & Combo1 & "] FROM Sales WHERE [" & Combo1 & "] = 1;"
qd.SQL = strSQL
Set qd = Nothing
DoCmd.OpenQuery "Query1"


btw, you could set up a different table structure and use cross tab queries to make a look-alike table to what you are using.
0
 

Author Comment

by:Beans0063
ID: 1976500
Thanks, it really makes me feel a lot better just to know this was a limitation of Access and not just my stupditiy.  I tried to set up the first solution, dynamically altering SQL, but ran into a couple of problems outright.  Firstly, the list of salespeople names exceeds the size limit of a ComboBox.  But, more importantly, the table field names (salespeople) will be changing on a monthly basis and cant (i think) just be hardcoded into a form.

Perhaps you could go into a little more detail about the cross tab technique.  I'm new to Access and am really banging my head here.  This seemingly simple query is holding the whole project up.

Once again, thanks for the tips they are tremendously useful!
0
 
LVL 4

Expert Comment

by:tomook
ID: 1976501
Crosstabs have their own limits, for example they do not allow editing, but to use them, set up a two column table like this:
AdID :Number(Long)
Salesperson :Text(whatever length you use)

You might want another field to hold the date, or something. This is OK. The easiest way to build a crosstab query from here is the Crosstab Wizard. Doing it by hand is a pain. The SQL looks something like:
TRANSFORM Count(Table1.SalesPerson) AS [The Value]
SELECT Table1.SalesPerson, Count(Table1.SalesPerson) AS [Total Of SalesPerson]
FROM Table1
GROUP BY Table1.SalesPerson PIVOT Table1.AdID;

0
 

Author Comment

by:Beans0063
ID: 1976502
I've made a bit of progress here but am still coming up short.  In order to get the salespeople names to appear in a combobox, I created a new table which holds salespeople names in a single row.  Next I set up the VBA as you indicated, using my Combo0 to get the Field name for the SQL statement.  Here's what the code looks like:

Private Sub FindSales_Click()

Dim qd As QueryDef
Dim strSQL As String

Set qd = CurrentDb.QueryDefs("Query1")
strSQL = "SELECT Sales.[" & Combo0 & "] FROM Sales WHERE (((Sales.[" & Combo0 & "] )=1));"

qd.SQL = strSQL
Set qd = Nothing
DoCmd.OpenQuery "Query1"

Seems like it should work, but when I execute the search Access prompts me for the value of Sales.19 instead of just going ahead and doing the query.

Where am I going wrong here?  Should I be using $ instead of &?  I would really appreciate a little more help.  Thanks!
0
 
LVL 4

Expert Comment

by:tomook
ID: 1976503
I suspect your combo box is set up with two or more columns with one or more hidden, and the column holding the value has the value "19" in your example. Look at the properties of your combo box, and if the number of columns is more than one, figure out which column has the info you want. When building the SQL, use

Combo0.Columns(<Column#>)

Note that the Columns collection is 0 based, so the first column of the combo box is Columns(0), the second column is Columns(1) and so on.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question