Can I display text in a number field

[frostman]

I have whats called a content analysis databse I built to record comments recieved from the public. The way the lookup tables are set up is most the is field is an auto numer, other fielda are number, but I want to display text. If I change the fields to text, the relationships won't work. Is there anyway to fool the program into accepting text

 

[LittleSmudge]

First off - language

You STORE data in FIELDS that are in TABLES

You DISPLAY data on CONTROLS that are on FORMS. ( or Reports )

A FIELD will only store data of the TYPE that it is designed to STORE.

If you BIND a Control on a form to a Field then it will display the data in the field.
However, you can get the CONTROL to format that data in some fancy ways.

If the CONTROL on the FORM is UNBOUND - you can get it to display what the heck you like. You can even get it to display text that is 'based on' or 'derived from' numeric data in some other fields somewhere in the database.

All is possible - even having text based relationship links - but, using accurate terminology, WHAT is it that you want to do ?


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[frostman]

Thank you for the language lesson. I have various tables I want to populate from one main form. the form uses a variety of lookup tables to fill in data. each tables primary key is contolled by a number field. All the relationships are built tying these number fields together, but What I want to display in reports is text data from the tables from different fields. Its complicated, want the database????

 

[LittleSmudge]

What you are asking for is 'bulk standard' referential linking. - It's one of those things that seem to be done with 'Smoke and Mirrors' until you see it once, then it becomes 'common sense'.

On Forms is can be done using combo boxes.
The combo box is BOUND to the Foreign Key field and contains a Number.
The ROWSOURCE of the combo has TWO columns. The first being the PrimeKey of the lookup table, the second being the TEXT data field.
Then make the combo box's ColumnWidths = 0; which sets the first column to zero width - thus displaying the TEXT.


Another option in Forms AND REPORTS is to use DLOOKUP.

In a text box control place the following in the ControlSource
=DLookUp("TextDataFieldName","LookUPTableName","LookUpPrimeKey = MainTableForeignKey&quot

Where
TextDataFieldName is the nasme of the text field in the lookup table that holds the data that you want to display.

LookUPTableName is the name of the Lookup table

LookUpPrimeKey is the name of the primary key field in the lookup table

MainTableForeignKey is the name of the control on the form or report that holds the value of the foreign key from the main table. ( This control can be hidden { .Visible = No } if you don't want the users to see it. )



'ope-that-'elps.
G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[frostman]

Thanks LittleSmudge, we are really close on this. The table it is accessing in the combo box is a lookup table with 3 columns. 1 = the primary key, auto number, 2 and 3 are text, which I want to display in the combo box and the table it populates with data. the combo box works perfectly, you see in the combo box a 4 diget number such as RE02, in the text box next to it is column 3, a description. that works fine. but in the table it populates all you see is the RE02. if you click in the table the drop down arrow shows both columns, but they won't display for queries or reports. looking at the properties of the table I want populated it is a number field with a row source pointing towards column 2. maybe i need to have it go to both 2 and 3? thanks again, hopefully you can give me one more tip to get me on track

 

[LittleSmudge]

Ahhhhhrrr. You're using B£$^^£" Lookup fields in your table design aren't you !!!

Also
you see in the combo box a 4 diget number such as RE02
There is NO WAY that "RE02" is a number. It is a TEXT String.

The combo box has the autoNumber column bound to the underlying table. And it is ONLY the autoNumber value that is stored in the table. The information that the combo box displays in it's other two columns comes from the lookup table that is refered to in the combo's RowSource.

When you need the Code and Description in Forms and Reports you then look them up ( using DLookUp for example - as discussed above ). You do not EVER re-store the same data from the lookup table into another table. That would be duplication of data and an unnecessary break of Normalisation Rules.

The reason you see the Code text when you look at the main table is because the table is LYING to you.
Go into table design mode, select the relevant field and click on the "LookUp" tab at the bottom of the screen. Change the DisplayControl property to "TextBox" and the confusing problem will go away.


'ope-that-'elps.

G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D