Report showing numbers instead of text...

[Frankie]

of course I want to do the job properly, i have read that snippet "evils of look ups..." multiple times as people posted it in similar situations. I have read as many other posts as I could before even posting here. Remember, I am a novice, not a "VIP", so there are questions I have on your response which I need clarification:
-my form is based off of a query, not a table. And that query actually brings in text, like for Property, it says "321 Main St" its the report based off the query that just shows "1" the ID KEY for list table used in combo box to populate main table query draws from. so when you say create another query that brings in those text fields and use those instead... I'm lost.

[Welshgasman]

Your query must just be based on your table, not the lookup tables.?

So in your query link to all those tables with the relevant fields and bring in those lookup descriptions.
Then use those in your report.

If you understand how table lookups work, you can work around this issue.
In my very first DB I have table lookups (as I did not know any better either at that time ), I still do, as I undersand how they work, and are convenient if I have to go to the table level. That DB is no longer developed, so I have left well alone.

In fact my Links table holds nothing but keys for all the other tables, which are the links to the actual data, plus it's own primary key.

Show the SQL for your query.

[Frankie]

All the list tables (look ups) I created on my own, just click create-->table; and all keys are automatically listed first, and they are numbers, I did not create those, they are automatically put there. They are all listed as "autonumber".
Here is list table view, and the design view...



When I look at the table that populates from look ups... here is the design and table view for that. Interesting, that we see alias data displayed (text), but in the design, each one of the fields that have look ups are "number" format

[Frankie]

yes...i did this. haha. I used to work with access like, 18 yrs ago, and whatever was in query field was in report, regardless if underlying table has drop down list or not... I have recently begun to get into it again for personal use. So this is frustrating. LOL. How do I fix this. I have tried to change the report to combo box, but still same issue. Additionally, the look up tables were just created with "create-table" and key field was automatically added as autonumber. I cannot change that, but table that populates from look ups is not "text" they are "number" and there is no "long integer" option, just "long number" but I cannot change it due to key associated with it.

[Frankie]

here is SQL, apologies for the paragraph, but there are criteria. I use criteria field, not SQL or code, hardly ever. I also attached design view of query.

SELECT [Bills Expenses].Property, [Bills Expenses].Company, [Bills Expenses].Resource, [Bills Expenses].Dates, [Bills Expenses].[Bill Amt], [Bills Expenses].[Payment Type], [Bills Expenses].Comments
FROM
[List Properties] INNER JOIN (
[List Bills Resource] INNER JOIN (
[List Bills Company] INNER JOIN (
[List Bills Payment Type] INNER JOIN [Bills Expenses] ON
[List Bills Payment Type].ID = [Bills Expenses].[Payment Type]) ON
[List Bills Company].ID = [Bills Expenses].Company) ON
[List Bills Resource].ID = [Bills Expenses].Resource) ON
[List Properties].ID = [Bills Expenses].Property
WHERE ((([Bills Expenses].Property) Like "*" & [Forms]![FormBillsQuerySearch]![FormBillsProperty] & "*") AND (([Bills Expenses].Company) Like "*" & [Forms]![FormBillsQuerySearch]![FormBillsCompany] & "*") AND (([Bills Expenses].Resource) Like "*" & [Forms]![FormBillsQuerySearch]![FormBillsResource] & "*") AND (([Bills Expenses].Dates)>[Forms]![FormBillsQuerySearch]![FormBillsStartDate] And ([Bills Expenses].Dates)<[Forms]![FormBillsQuerySearch]![FormBillsEndDate]) AND (([Bills Expenses].[Payment Type]) Like "*" & [Forms]![FormBillsQuerySearch]![FormBillsPaymentType] & "*")) OR ((([Bills Expenses].Property) Like "*" & [Forms]![FormBillsQuerySearch]![FormBillsProperty] & "*") AND (([Bills Expenses].Company) Like "*" & [Forms]![FormBillsQuerySearch]![FormBillsCompany] & "*") AND (([Bills Expenses].Resource) Like "*" & [Forms]![FormBillsQuerySearch]![FormBillsResource] & "*") AND (([Bills Expenses].[Payment Type]) Like "*" & [Forms]![FormBillsQuerySearch]![FormBillsPaymentType] & "*") AND (([Forms]![FormBillsQuerySearch]![FormBillsStartDate]) Is Null) AND (([Forms]![FormBillsQuerySearch]![FormBillsEndDate]) Is Null));

[Welshgasman]

So you take everything from bills which is just the id fields.
Bring in the fields that they are linked to and use those instead.
I really do not know any other way to say it.

[mike60smart]

I modified the Main Table by changing the Lookup Fields to Text Data Types
I removed the 0 Default values for all these fields
I changed your query to include the fields from the Lookup Tables and gave them Aliases to match the Controls in your Report.
It now does what you want.

[Frankie]

I did read post 6. Foreign key? I created tables with "create-->table" not wizard. The keys were automatically set. I did not make them. They are set to "autonumber" and the table that stores the info does show the alias data (text) but the format for those is "number" there is no long integer, only "large number" but I cannot change that from number to large number because it it part of a relationship. Combo box on report also does same thing. There is something I'm missing... I can't figure it out.

[June7]

How well do you understand relational database concepts? Tables are related by primary and foreign key fields. A primary key is often set as an autonumber type. Then a related dependent (child) table has a field to save the primary key value of the parent table record. This is the foreign key field. In the case of an autonumber primary key, the foreign key must be long integer number type field.

Mike, I don't see that the Lookup fields were changed to Text Data Type. They are still Number type but no longer set as lookups - just textbox instead of combobox.
Otherwise, fixes do work.


As for combobox on report approach, works for me. As an exercise with the original db, delete textboxes from report then drag and drop fields from field list. They should come in as comboboxes and display alias text info. This means report can use table as RecordSource, not a query joining tables.

[mike60smart]

Hi June

True my mistake should have said that the Display Control set as Text Box

[Frankie]

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

OK... so I used combo boxes on report... same issue, came up as numbers. Maybe im missing something that is obvious to you, but not to me. If I open my query in design view (see below). are you saying that instead of using all fields from Bills Expense Table, the look up table fields need to be used? So instead of Property from Bills Expense Table, I should use Property Address from List Properties table? I really need specifics as I am not as advance as everyone giving suggestions, but can easily learn if I have some step by step instructions. If I use combo boxes on report... do I just select "combo box" but where is the source? Still Property from query, just make is a combo box instead of a regular field? It would be sweet if I could just use a combo box and all my problems would go away with the report. It is SUPER FRUSTRATING that every table, query, form displays the text, but the report doesn't, it is inconsistent and garbage set up on Microsoft's part.

[Micron]

it is inconsistent and garbage set up on Microsoft's part.

I don't think so. I think it is because of what I stated in post 9

Instead of using your list table autonumbers as foreign keys in your billing table, you've made them lookups from the list tables.

You have a mix and match of approaches.
Did you follow the link I provided in post 9 wrt your problem?

[June7]

I am not saying you "should" I am saying you "can". It works as I described in my previous post. No, you don't just select "combobox". Drag and drop field from field list. This will create combobox with same properties as lookup field in table. On ribbon, click "Add Existing Fields" to open field list. You will see it where the Properties dialog box was. Or manually build combobox and set properties directly yourself.

And it is not an inconsistency on MS part, it is your novice state learning basics.

Otherwise, use the db as modified by Mike. This does rely on query joining tables and will use Property Address from List Properties table.


BTW, strongly advise not to use spaces in naming convention.

[Frankie]

OK everyone... it seems like anyone who is anyone is telling me I should not have used look ups on table. So, the only place for combo boxes would be on the forms? Right? If so, how would I do that? Just create a combo box on the form and type in my own entries, then connect it to the appropriate field for data entry on the query/table? Let me know. As I would like to do this the correct way, and also for future databases I might create. Thanks.

[June7]

Lookup fields in table is not "incorrect", just not preferred by most experienced developers for reasons outlined in http://access.mvps.org/Access/lookupfields.htm. If used, be sure you really understand them.

Yes, you create comboboxes on form yourself. There is even a wizard to help, which I never use.