Merging 2 fields into 1

DKM

Registered User.
Local time
Today, 12:47
Joined
Feb 24, 2007
Messages
24
Hi

I am having a problem with getting this one to work. I am trying to combine the values from 2 fields into 1 (one of these is a textbox, and the other is a combobox).

The problem is with the combobox side. My combobox (called Name) has 2 columns (ID and Title). I can get it to merge the ID with my textbox (called nameLevel) using this code as the control source.

=[Name] & "" & [NameLevel].

Which leaves me with a value made up of ID and Name Level. What i need is to end up with the value showing as Title and Name Level.

I have tried adding in the .column(1) after the field name but this only returns an error message.

Any suggestions as to where im going wrong with this.

Cheers
 
I dont exactly understand what you are looking for... Maybe a copy of the database or sample of the piece you need help with for us to look at specifically??
 
where you generally have trouble doing this is if the two Field items you are trying to join are of different Data Types. For example String (Text) Data Type and Integer (or Long Integer) Data Type. If this is the case then it will be a no go.

If the field items you are trying to join do indeed contain different Data Types then you will need to convert them so that they are the same OR can be handled as the same and be placed as a common Data Type.

In your case, if [Name] is of a Text Data Type (string) and [NameLevel] is of a Long Integer or Integer Data Type then you will need to do a conversion:

=[Name] & CStr([NameLevel])

Since [Name] is already a Text Data Type we definitely want to convert our second Field item ([NameLevel]) to the same Data Type. The CStr() Function does this for us.

Suppose now that the first field item [MyDate] is a Date/Time Data Type and the second Field Item [InvoiceNumber] is a Long Integer Data Type. Well, we can convert the two Data Types to a string Text Data Type like this:

=CStr([MyDate]) & CStr([InvoiceNumber])

You can also use the CVar() Function do carry out the conversions :

=CVar([MyDate]) & CVar([InvoiceNumber])

I noticed that you also added double Quotes to your property string. There is nothing wrong with that and it is allowable but what is the purpose if there is nothing between the Quotes?

Perhaps you meant to place a space or some sort of delimiter between these Quote marks so as to provide clarity to the Field string.....or perhaps not.

When dealing with Combo Boxes, things are a wee bit different. You need to supply the Column, as you had already suspected, but forget about using the Square Brackets ( [ ] ). You can apply them if you want like this:

=[Name].[Column](1) & CStr([NameLevel])

but MS-Access will just remove them and turn it into this:

=Name.Column(1) & CStr([NameLevel])

That is of course if it is indeed Column(1) that is holding the Data you want.

If you have a required conversion (for whatever reason) then you would need to place this into a Conversion Function like this:

==CStr(Name.Column(1)) & CStr([NameLevel])

If you want to add a delimiter of some sort between the two Fields then you would have:

=Name.Column(1) & " - " & CStr([NameLevel])

Here the Two fields are separated by a Space Dash Space and the outcome would be:

MyDataFromField1 - MyDataFromField2

.
 
I should also mention that you should consider the possibility that one of the Fields within the Property String might be NULL. This can also be handled by either using the Nz() Function or IIF() Statements.

.
 
Many thanks for the help with this one. the conversion guide above was very useful.
 
Many thanks for the help with this one. The conversion guide was very useful.
 
When I've had a field called Name in the past it has led to some very strange events, because Name is a reserved word - being a property of lots of things, I've had Access apparently trying to figure out what I'm trying to do with the name of my form - took hours of head-scratching to resolve the first time. Yet I have seen a number of forum questions in which people have used 'Name' or 'Date' - how do they get away with it?
 
They don't! Sooner or later they'll come to grief! Date is particularly dangerous, as it can result is the changing of your PC's system clock! The combobox name here should changed. This kind of problem is easily avoided, by the simple expediency of always using a naming convention, such as cboName.
 

Users who are viewing this thread

Back
Top Bottom