Memo in a combo-box

[Dave Hoder]

I have a form with a combo-box for Stock Number that gets it's value from a
linked table. In the combo-box I want to show both Stock Number & Description
(Stock Number is the bound column) but the description field is a memo data
type in the linked table. I need to convert this to text for performance
reasons.

My work-around has been to create another table that gets rebuilt each time
the database is opened but it seems there's got to be a more practical
approach. What's the easiest way to convert memo to text in this case? (using
Access 2003)

Thanks,
Dave Hoder

 

[Jeff Boyce]

Dave

I can't tell if you are trying to do a one-time conversion of a memo field
to a text field (if any of your memo fields hold more than 255 characters,
they'll be lost), or looking for a way to only see the first 255 characters
of your memo field.

If the former, add a new field in the table and do an update query. You
might need to use:
Left([YourMemoField],255)

If the latter, use the above in a query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ron2006]

As Jeff mentions, you should be able to see the first 255 characters
of your memo field in the combo.

If you really wnt to keep those characters as separate, instead of
creating / recreating a table all the time simply add another field to
your original table that is 255 characters. Do a one time query to
load the field with the left(xxx,255) function and then in any form
where you can change the memo field have it do the same thing in the
afterupdate event of the memo field.

Ron

 

[Dave Hoder]

Thanks guys
I don't even get the option to use the memo field if I use the wizard to add
the combo-box. I can add it with code but the performance is really bad. The
linked table is the Part Master table from our ERP system and is read-only
and contains about 50,000 records. Is there any way to convert the memo field
to text on the fly? If I could specify a data type in a query field that
would solve my problem. Right now I don't actually create a new table,
instead my intermediary table with the part description as text gets
refreshed with a delete and then an append query when the form containing the
combo box is loaded. It works but there's a lag while the query runs & I'd
like it to be instant.

 

[Jeff Boyce]

Don't specify a data type field in your query -- and you can't, as far as I
know.

Instead, use the suggestions you've received here about
Left([YourField],255) to grab the left-most 255 characters of that memo
field.

Or if you only need 10 characters, use Left([...],10).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dave Hoder]

Got it. Thanks Jeff. The visible length of the text isn't really an issue,
just the lag. Re-writing my query to filter only the range of stock numbers
required seems to produce the best results. The linked table can't be
modified & maintaining another table created a bigger lag while the query ran
to refresh it.