Lookup Fields and Tables

[mmjr103]

OK, as a novice it appears that I have sinned. I created tables that have lookup fields referencing another field. Actually I have several tables that all have relationships and object dependencies in my database. Now that I am trying to create some different reports, when I run the report I get the ID rather than the contents of the field.

Also, I have a 'Report Dashboard' so to speak that I can run different reports from. On the form I have Combo boxes that reference one of the tables. I can generate the report showing the information, however I tried using a text box (criteria)(=[Forms]![Reports Form]![Combo47]+" County") in the header of the report to reference the selection made in the form combo box. When I run the report I generate the ID in the header.

I guess, first how do I get rid of the lookup fields in the tables, or is there another work around?

I am completely a novice and I have been learning as I go.... Any help would be appreciated. Thank you

 

[Solo712]

OK, as a novice it appears that I have sinned. I created tables that have lookup fields referencing another field. Actually I have several tables that all have relationships and object dependencies in my database. Now that I am trying to create some different reports, when I run the report I get the ID rather than the contents of the field.

Also, I have a 'Report Dashboard' so to speak that I can run different reports from. On the form I have Combo boxes that reference one of the tables. I can generate the report showing the information, however I tried using a text box (criteria)(=[Forms]![Reports Form]![Combo47]+" County") in the header of the report to reference the selection made in the form combo box. When I run the report I generate the ID in the header.

I guess, first how do I get rid of the lookup fields in the tables, or is there another work around?

I am completely a novice and I have been learning as I go.... Any help would be appreciated. Thank you

If you run your reports off queries rather than tables then there is a quick workaround. Create a computed field for the report using the Builder and do a DLookup in it for the sought after name using the ID shown in the field as criteria. In the report you then refer to the computed field rather than criteria.

Best,
Jiri

 

[mmjr103]

OK, I'm not sure exactly what you mean with your response. Where am I putting this DLookup? Is this the Textbox on the top of the report? I apologize, but I am still learning Access.

 

[Solo712]

OK, I'm not sure exactly what you mean with your response. Where am I putting this DLookup? Is this the Textbox on the top of the report? I apologize, but I am still learning Access.

Do you know how to work with the Query Builder ?

Jiri

 

[mmjr103]

Yes, to a certain degree. That would be the record source of my report. I know how to add tables, show/hide field, certain criteria. I can navigate my way there, I guess it would depend on what I need to do. On criteria I always have trouble with the (, [, ] + " and when to use them. I haven't successfully created an expression builder yet... I always get an error.

 

[Solo712]

Yes, to a certain degree. That would be the record source of my report. I know how to add tables, show/hide field, certain criteria. I can navigate my way there, I guess it would depend on what I need to do. On criteria I always have trouble with the (, [, ] + " and when to use them. I haven't successfully created an expression builder yet... I always get an error.

There is an Expression Builder in the Ribbon: Insert a new field and then, while the cursor is blinking click on the Builder icon (it has a magic wand):

Type into the text field that opens substituting your own names :

MyNewField: Nz(DLookup("MyName", "MyTable", "ID=" & [MyComboField))

When you go to view the query you should see the the "MyName" values in a column called "MyNewField". The report would then use this field to display the names

Good Luck !

Best,
Jiri