Report showing numbers instead of text...

[Frankie]

I know this has been posted before, and I have read them, but am still trying to figure this out.



So, I have a database. I enter in cost information for a home I purchased to rent. I have created some list tables that I can use for combo box data entry to keep data uniform. I have a separate list tables for each: Property, Company, Resource, and Payment Type. When I enter these into table (from form combo box), it works perfectly. Table is populated with text precisely as I wish it to.

I then have a form to search my records which is based off of a query. I can enter a Property, Company, Resource, Payment Type (which are all unbound combo boxes on form, they reference the list tables) or I can enter a date range, which is a standard unbound date format box. The query uses the unbounded fields from the form in its criteria to retrieve data and populate the query itself. This works wonderfully! I can open the query and everything works like a charm.

Now that you know what works... here is the hick-up. I now decide to create and run a report (for tax purposes) off of the query mentioned above that runs so beautifully on its own. The report populates any field that originated from combo box (list table) as a number, and not the text that appears in the query. For example, the Property might be listed on the form combo box as "59 Main St" and it appears this way on the query as well. However, on the report, in the Property box, it shows a "1" instead of "59 Main St". Perhaps because it is the first item listed in the combo box/list table? I'm guessing here.

I read that I need to add/join any list tables to the query besides the main table it draws from, and use those fields, but when I did that, I got a "type mismatch"? I have no idea where that even came from, nor how to fix it. The query and report wouldn't even run. So I removed the list tables off of the query again, as it was previously, and the queries run beautifully, but still no dice on reports. Maybe I've done something wrong? (obviously).

Any help would be appreciated.
Frankie

[CJ_London]

difficult to provide help without any real substance. My guess would be you are using lookups in your table design which can produce this sort of error. If that is the case, remove the lookups, then you can see what you actually have.

[orange]

Are you using lookup fields in your table(s)?
You may find useful info from this material from Isladogs (Colin).
There are many articles in my signature link Database Planning and Design also.

[Frankie]

What is a lookup? I have basic knowledge: tables, queries, forms, macros... I have form fields that refer to tables for combo boxes (they look up data in those tables), is that what you mean by lookup? If I need to send copy of database or send/post screen shots i def will. this is bothering me and I'm the type of individual who gets little rest until I figure things out, lol.

[Micron]

is that what you mean by lookup?

No. Lookup tables are fine - they just produce records/lists for listboxes and combo boxes mostly. The autonumber pk of the lookup table would be what you store in the table with the records you're building. The query uses the lookup table autonumber pk which is joined to the main table on the field that holds that number. The lookup table is where you get the descriptive text from that relates to that autonumber field.

Lookup fields in tables is what is "bad" according to many db developers. If you click in a table field and a combo appears, you have a lookup field. Not that you should be editing directly in tables anyway.

To post a db, copy it, compact/repair and zip it. See "How to attach files" at the top of the forum page.

[June7]

Your "list tables" are lookup tables. You have set up lookup fields in tables and apparently set up to save autonumber ID but display text - aka alias data display. Since you already have them, might as well leave, just need to understand how they work. If you use a combobox on report then you would see the alias data displayed and combobox will look and act like a textbox in PrintPreview or direct to printer. Otherwise, need to build a query that joins tables to retrieve related info and use that as report's RecordSource.

If you get datatype mismatch when trying to join tables, sounds like the primary and foreign key fields are not compatible data types. If primary key field is autonumber then the related foreign key field must be Long Integer, not Text. Possibly just need to change the foreign key field data type in table design.

How did you create the lookup field? Did you use the lookup wizard? Changing a lookup field that was created with the lookup field wizard is not simple. However, the wizard should have handled the data types and then should not have mismatch issue.

Why are you using UNBOUND form and controls for data entry?

[Frankie]

Here it is. You will see the relationships I have between 4 list tables and main table. I do have the data entry form connected to table for data entry. Maybe that needs to change as you say?? Suggestions welcome.

Query is based on FormBillsQuerySearch, then report is based on query. And the report is the only thing I am having issues with.

thanks, I appreciate it.

[Micron]

If you click in a table field and a combo appears, you have a lookup field

Did you do this? Your table fields are lookups. That's why your report shows the numbers. Queries and tables handle the hidden values (the text you seek) - reports don't. So, what June7 said.

[Micron]

If you can make sense of this you might be able to fix your issue.

Here's an example of its application - just to get the Companies related to table properties autonumber 1:

SELECT [Bills Expenses].ID, [Bills Expenses].Property, [Bills Expenses].Company, 
[List Properties].ID
FROM 
[List Properties] INNER JOIN [Bills Expenses] ON 
[List Properties].ID = [Bills Expenses].Property
WHERE (((
[List Properties].[Property Address])="321 Main Street"));

321 Main Street would come from your form.

Instead of using your list table autonumbers as foreign keys in your billing table, you've made them lookups from the list tables. Might as well not bother with the autonumber fields. Not what you want anyone to say I imagine, but in the long run you'd be better off starting over and doing it right. If you do, adopt a better and more consistent naming convention.

[June7]

Frankie, did you read post 6? Fixing report is simple. As I described, either use comboboxes on report or since you have query as report RecordSource, include fields from the lookup tables in query grid and bind textboxes to those field. As is, you have the tables in query but don't pull in the fields.

Might want to explore technique of cascading comboboxes for the search form.

I don't use parameterized queries. I prefer to apply filter to form or report. Review http://allenbrowne.com/ser-62.html

[mike60smart]

Hi Frankie
When you run your Report does the Report Flicker continuously?

[Frankie]

unbound field aren't for data entry, they are on form and used as criteria for query.

[Frankie]

no... just shows the ID (key) instead of text for my Property, Company, Resource, and Payment Type (which are all entered into original tables by combo box)

[Welshgasman]

no... just shows the ID (key) instead of text for my Property, Company, Resource, and Payment Type (which are all entered into original tables by combo box)

But hey are table lookups , which hide their true value, which is the ID NOT the lookup value.

If you do not wan to do the job properly, just create a query that brings in those text fields and use those instead in your report.

http://access.mvps.org/access/lookupfields.htm

[Frankie]

The unbound form is criteria for query search, not for table entry.