Lookup Columns

[Grieve1]

Hi I have a column on a form which is a lookup from a table and I need to be able to create a left function to lift 1st 2 characters but won't come over as text just applies a number e.g. Field Name County inputs "Tyrone" on form would expect Ty but left Function is inputting 23. Any help much Appreciated

 

[Remou]

If you are using look-up fields in your table, get rid of them, they are an anti-feature. If this is a combobox with two columns, use the column property to get the name of the county.

Left(Me.cboCountry.Column(1),2)

As an aside, there is no reason to key a county table on a number. Counties are not going to change any time soon, so a table:

AM Antrim
KK Kilkenny
KY Kerry

Works quite well.

 

[jrbarnett]

Concur with Remou on lookup fields in tables, I'm working on a system with this in use and they are a pain to work with.

JOhn

 

[PHV]

Have a look here:

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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Grieve1]

Thanks to each of you for your response Can any of you suggest what would be the best alternative a to a lookup as company wants to easily select data on different forms. Thanks again for all your time and effort

 

[Remou]

You can use a combo on a form, it is look-ups in tables that are deprecated.

 

[MajP]

A combo box on your form is fine. Just do not put a look up in the table itself. The user should never see the table anyway.

The reason that it returns a number is that most likely the value in your table is a persons ID; however, the evil lookup is showing you the linked name.

So assuming the field is "personID" and stores a number, the combo box on the form should have as its rowsource a query that returns two fields.
personID
personName

The combo box is bound to the first field
bound column: 1
column count: 2
column widths: 0";1"

The combo is bound to "personID", but you only see the name field becuase the first column has width of 0.

 

[Grieve1]

Thanks for that