Separate Multiple Selections with Semicolon

[ToddNYC]

Is there a way to have multiple selections from a dropdown combobox writtin to the table with a semicolon separating them rather than a comma?

So when I select multiple items it saves as "first item;second item;third item" instead of the default format of "first item, second item, third item".

The reason is that when the data gets imported into my site, data within fields must be presented this way.

Thanks for your suggestions!

 

[John Big Booty]

You could use the Replace() function to locate and replace the commas with semi colons prior to exporting/importing.

 

[ToddNYC]

Thank you! I'll play around with that to try and get it to work. I appreciate the help!

Todd

 

[ToddNYC]

I haven't been able to get replace to work for me, can you set me straight? I am using Replace() in a query. One field is pulling in multiple values from a table (ie. "Pontiac, Ford") and I'm trying to use a separate expression to replace the comma with a semicolon but can't get it to work as I get an error that I can't use replace on a multi-value field. Should I be executing this elsewhere?

Thank you!

 

[John Big Booty]

The expression you are looking for is;

=Replace([Record],",",";")

Have a look at the attached samples for two examples of how you might use it;

1. In an unbound text box
2. A query

 

[ToddNYC]

Thanks John for creating the example for me! Makes sense, but I still get error "The multi-valued field '[primary]' is not valid in the expression 'Replace([primary],",",";")'.

The problem is probably that the field "primary" is created via a combobox.

So sorry if I'm missing something obvious here.

 

[John Big Booty]

Can you post a copy of your DB?

 

[ToddNYC]

I've made some progress... if I use:

Expr1: Replace(DLookUp("primary","tblEvents","guid = " & [guid]),",",";")

I do get the values I want...almost.... I get the ID number of "primary" values. Would you know how I get the field value?

 

[John Big Booty]

I'd really need to see the DB.

 

[ToddNYC]

Here it is... hope it's not too shockingly messy!

I am trying to accomplish the following:
In Query "Upload_Events", the last column (Expr1) is my attempt to take the data from field "primary_event_type"and replace the comma with a semicolon. It is now giving me the ID fields, but I am looking for the values. (I need it to print this way for a data upload.)

(For some reason, the query just started to duplicate itself as I was trying to figure this out and now each key ID repeats itself over and over.....)

Thanks again,

 

[John Big Booty]

You are using Table Level Lookups, see this link for why this is a bad idea.

Your problem is covered by point 2

Fix that and life will be a whole lot easier.

You might also want to read up on the subject of Normalisation and possibly work through a tutorial on the subject.

Also consider implementing a naming convention to make the identification of various DB objects and controls easier.

 

[ToddNYC]

Thank you for your advice. I'll take a look through and see if this gets it working.