Formatting Foreign Keys

[tuna]

Hi, I'm having trouble using a lookup field with foreign keys containing formatting that violates the lookup field's data type - I was under the impression that formatting did not affect the underlying data stored.

For example, if I have a numerical foreign key with formatting 0000-0000-0000 applied, I cannot use it in a numerical lookup field because of the dashes (-) in the formatting which Access seems to believe is also part of the underlying data.

However, I have noticed when opening the lookup query the Query Designer, the Property Sheet for the fields contains a "format" option. I had hoped this would let you specify the format of the foreign key, but any changes to this option seems to be lost as soon as I exit the Query Designer, even after saving.

Thanks for you help

 

[vbaInet]

Have a look at this:

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

The way to format in code or in a query is by using the Format() function. Have a look at the different uses of this function. You may also find the other functions listed useful :

http://www.techonthenet.com/access/functions/

Generally, formatting at table level or query level is not advised. If you format in your table or query then the value that will be returned will be the formatted value. If you think about it, you format for display purposes, hence, if you want to return the real value then don't format the source. A form/report on the other hand is used for "displaying" records and hence, any formats you want to apply should be done on form level - on the control that is. You either use VBA to set the format on the control in your form/report or you do it via the Property Sheet for that control.

Welcome to AWF by the way