Return the name of a field in a table 1

[MBorofsky]

I decided to merge a large number of queries into 6 because each set did the same basic thing to a different field in the table.

The way I did this was to create a global variable and a function that would return that variable. I had the front-end set that variable (say to "PCP" or "BF"). I then created a query that would say IIF(Global="PCP", [Primary Care Provider, IIF(Global="BF", [Breast Feeding])).

The problem is that these statements keep getting longer and longer as I add more fields to the reports. They are complex to look at and hard to understand (for the testers). I also believe that they might be making the whole process slower. Is there a way to return or set the name of a field? I can't generate the SQL on my own because the data is complex enough that I need 6 seperate queries (4 of which would need to use the 'return field name' function) to accomplish my task.

 

[cmmrfrds]

There are a number of ways to get at the Fields Collection depending on what you need to do. QueryDef, Recordset, and ADOX Catalog all have field collections. What are you familiar coding with? Are you trying to build dynamic SQL?

 

[MBorofsky]

I am trying to avoid using SQL and stick with the query builders access provides, though I am slightly familier with ADOX.

I guess the major issue I see with generating custom queries is:
1) Supportability: I am afraid other programmers here would not understand them. Nor can I say I'm here for the long-hall so it should be easy to support. I doubt many people here could look at the SQL code for as many queries as I need to do this for and understand what they do.
2) Complexity: Can I run 6 queries, all custom made where query 3 relies on query 1 and 2, ect? This is a relatively complex set of queries I need to do.

The SQL Statements are rather long and arduous. I really was hoping for a nice simple function that would allow me to return a string or something that would be converted to a field name. For example, Data: Eval("[Primary Care Provider]") would have been perfect if it actually worked. It just lists everything as literally "[Primary Care Provider]" instead of what values [Primary Care Provider] contains.

 

[cmmrfrds]

I am still not clear on what you are trying to do, but lets say you want to know all the field names of a query bound to a Form and put them in an array.

Dim glbFieldNameList() As String
Upper = ME.RECORDSET.Fields.Count - 1
ReDim glbFieldNameList(Upper)
Debug.Print "field Count = "; ME.RECORDSET.Fields.Count

For indx = 0 To (ME.RECORDSET.Fields.Count - 1)
Debug.Print "field Number = "; indx
glbFieldNameList(indx) = ME.RECORDSET.Fields(indx).Name
Debug.Print "field Names = "; ME.RECORDSET.Fields(indx).Name
Debug.Print "field Type = "; ME.RECORDSET.Fields(indx).type
Debug.Print "field Type = "; ME.RECORDSET.Fields(indx).Value
Next '-- end for

 

[MBorofsky]

Lets say I have a query that pulls 2 things from the following table:

[Family ID] (Long Integer)
[A] (Integer)
[...] (Integer)
[Z] (Integer)

The query will always pull [Family ID]. The other thing it pulls will change. On the form, I have a dropdown box that lets you select [A]-[Z]. So the user selects a data element. The old method before I joined the company was to have 26 queries, one for each variable. I combined all 26 queries into 1 query the had a long nested IIF statement to pick out A, B, C.... based on what the drop down box said. The IIF statement is complex, hard to maintain, and slow; but still easier than maintaining 26 seperate queries. I am looking for a way to set a single variable to be [A] (or , [C], ect). This way when the query runs all it needs to do is look at that one variable and translate it into the name of the field it should be looking for.

One thing I tried that didn't work was to set a string FIELD1 = "[A]". The query then looked like this:

[Family ID] Variable: Eval(FIELD1)

It was my hope that eval would change the string into the name of the field I was looking for and look the value of the name up in the table. Instead it filled every record with "[A]".

Does this better explain the problem? I am drastically simplifying the queries I am actually writing, but I think this demonstrates the problem.

My internet connection is spotty b/c I'm visiting relatives who don't have it. Right now I'm sitting outside someone's house using their wireless so I may not respond very quickly to you. Thanks for attempting to help.

 

[cmmrfrds]

You could use the fields collection.

Make public variables in the Form
Public pubFieldNameList As Variant
Public pubSQLString as Variant

In the OnOpen Event
Dim indx as integer
For indx = 0 To (ME.RECORDSET.Fields.Count - 1)
'build the list box data
pubFieldNameList = pubFieldNameList +
ME.RECORDSET.Fields(indx).Name + ";"
Next

Requery the list box with the field names as the source.

Let the user select the field names they want in the query.
Build an SQL String with the fields.
For example. pubSQLString =
Select
pick the field names from the list box
yourtable

Using QueryDef replace the SQL in the QueryDef with the pubSQLString, now you can run the query with the modified SQL.

The field values can be picked out of the recordset fields collection the same way as the field names. Is this addressing what you are doing???

 

[MBorofsky]

That would definately work, but the complexity would be huge. There are 6 different basic types of reports, each of which has its own line of 5-6 queries. The way I have it now, each of these queries will pull 1 out of 10 different types of data, depending on the drop-downs. I would need to store pages and pages of SQL script in the VB-code, and use a complex set of nested if-thens to create the proper reports. Additionally, this would involve basicly rewriting everything which I don't have the time to do. I am really trying to avoid rewriting the SQL at runtime for these reasons.

All I am really looking for is a function that will return a field to a query. The query would then look up the value of that field for each of its records.

 

[hkaing79]

I've done something similar, but I use number, so I just loop through it using the loop variable as the field name.

But if you're using the alphabet, perhaps it is possible to loop through those variables using ASCII?

For i = 65 to 90
  fldName = char(i)
  CODE
Next i

However, if your field names are more complex than simple numbers and letters, then you can reference the column by its position. Unfortunately, I forgot how to do that.

 

[MBorofsky]

As far as I am aware you can only do those things in the visual basic macro. I am trying to do this in query design view. A column lookup would be perfect (if it were possible in design view and the column # I was looking up could be a variable). I have looked through the help files and don't see anything about it.

 

[HenryAnthony]

Not sure if I understand your problem but it sounds like you want to set the criteria of your query from a form. If so, you could include comboboxes in your form that list your variables and then refer to the combobox in the criteria of the underlying query. Could it be as simple as this?

Henry

 

[MBorofsky]

That sounds close to what I'm doing. What I need is those variables to refer to a field in a table. That way, when the query evaluates the variables, it looks up the value of the returned field. Let me try to explain it like this:

Query1:

Table1
|-------|
| ID |
| Field1|
| Field2|
---------

----------------------------------------------
ID | Data: ReturnField() |
Table1 | |
----------------------------------------------

1) Query1 would pull out the value of 'ID' for each record in Table1.
2) ReturnField() returns something that causes Query1 to pull out the value of either Field1 or Field2 (depending on the dropdown box)

So if the dropdown box were "Field1", query1 would be the ID and Field1 for each of its records.

If the dropdown box were "Field2", query1 would be the ID and Field2 for each of its records.

The way I am doing this now:


Query1:

Table1
|-------|
| ID |
| Field1|
| Field2|
---------

----------------------------------------------
ID | Data: See "code" below|
Table1 | |
----------------------------------------------

code:
IIF(forms!form!dropdown="Field1", [Field1], IIF(forms!form!dropdown="Field2", [Field2]))

The problem is that I am looking at many more than two fields so this statement is quite cumbersome.

Does this make any sense?

 

[HenryAnthony]

OK, sorry, but I am having a difficult time understanding. If you could accurately describe your tables and the results you need from your query, I am sure a solution could be found. In your initial post, you mention PCP and Breast Feeding. Somehow these fields don't seem like they belong in the same table (I am in the health care sector).

 

[MBorofsky]

It isn't really important what data I am dealing with, I offered those as examples. Breast Feeding and PCP are together because they are gathered together by the case worker on a home visit along with a plethora of other information. It really would take weeks to explain the entire thing so I tried to simplify the problem down to exactly what I needed. Apparently without much success.

The important thing is that a table has a Family ID and a bunch of fields pertaining to information about the family. Some of the fields are [Primary Care Provider] and [Tobacco Exposure] (whether or not the mother exposes her child to tobacco).

I will always need the Family ID because it is the primary key. Sometimes the user will want to see Tobacco Exposure for all of their families, othertimes Primary Care Provider.


There used to be 2 seperate queries, one to look at PCP and the other tobacco exposure. In reality their are many more queries involved because we are performing calculations, but they don't matter for the problem at hand. I combined the 2 queries into 1 query. It would look at PCP when a drop down box said "PCP" and would look at Tobacco Exposure when a drop down box said "Tobacco Exposure". I did this with an IIF statement. In design mode of a query, one of the fields would be defined as 'Data: IIF(DropDown="PCP", [Primary Care Provider], IIF(DropDown="Tobacco Exposure", [Tobacco Exposure]))'. That way if the drop down box said "PCP" the field 'Data' in the query would be from [Primary Care Provider]. The IIF statement is very long and cumbersome so I want to eliminate it but still have the query be able to determine what data it pulls at runtime.

 

[HenryAnthony]

Could you not have a dropdown that cooresponds to the criteria for each field in your query?

 

[MBorofsky]

That might actually work. How do I do that? I have looked into criteria before without any luck.

 

[HenryAnthony]

I would probably build an unbound form where I would place all the combo boxes. Then a subform that returned the results of the query. In the criteria row of your query just refer to the name of the combo box like:

[forms]![nameofyourunboundform]![nameofyourcombobox]

Here is a helpful faq that tells how to return all values when using this technique:

http://www.tek-tips.com/faqs.cfm?fid=3177

There is a mention of a faq by trbroadbent that would also help but the search function of this site seems to be down.

HTH

 

[MBorofsky]

After looking at the faq I'm not so sure. I think that criteria place the 'actual value' of the criteria into the query. That would work if I wanted one of the values to be constant, like 2 or "PCP". It would even work if I want to say something like IIF([PCP]=CRITERIA, 1, 0). I think my IIF statement is using criteria because it uses the value of the drop down to pick the right field.

So the criteria would evaluate to be the same value for each record in the query.

I would need the criteria to place the name of a field into the query and have the query look the value of that field up for each record.

 

[HenryAnthony]

Is not your PCP field a list of doctor names? Is not your tobacco usage field Yes or No? I am assuming you would select for example "Dr. Smith" and "Tobacco Exposure = Yes" to return a list of Dr. Smith's patients that are exposed to tobacco. Is this not the result you seek?

 

[MBorofsky]

No, thats not what I'm looking for.

I need to be able to change the name of the field being looked up.

There are 2 fields in the query. Field1 is the Family ID for each record. Field2 will either be [PCP] or [Tobacco Exposure], depending on the dropdown box. I need every family's doctor or every family's tobacco exposure, but not both. Whichever one it chooses should be renamed "Data" so they can use the same report. I accomplish this as follows:

Field: Data: IIF(CRITERIA="PCP", [PCP], IIF(CRITERIA="Tobacco Exposure", [Tobacco Exposure]))
Table: tblFamily
Show: <checked>
Criteria: <blank>

The problem is the IIF statement is too long so I was looking for another method.

 

[hkaing79]

I think what you're looking for is DLookUp.

So something like:
Data: DLookup(forms!form!dropdown, "table1","ID=" & ID)

So if you have a table1:
ID | Tobacco | PCP
1 | Yes | No
2 | No | Yes

And you select PCP from the dropdown box, your result will be:
ID | Data
1 | No
2 | Yes