Concatenating 2 fields!

[VS24]

Hi guys... newbie in the house!!

Straight to the point!

I have an Implementers table with Implementer types : "RCL", "RCM", "GB", "MP" etc.

I have a form that filters the Implementers table by restrictions "RCL" & "RCM".

Have a field called [RegImpID] (Text), another field called [Implementer ID] (Autonumber), a third field called [RegID] (Text).

Need to combine [Implementer ID] & [RegID] into [RegImpID].

This is the code I'm using :

[RegImpID].Value = Str([Implementer ID].Value) + [RegionID].Value

It's giving me the "Run-time error 2465" plus "Can't find the field '|' referred to in your expression"

What am I doing wrong?

I've done it to another form that doesn't filter on anything, but just goes through the entire table!
The code is identical (well except for the field names )... ) and that works fine... it combines them perfectly!


Thanks in advance!!

 

[Ziggurat]

Hi vs24

If this is the code that you are using, then the problem is that you are calling the wrong field
eg
>Need to combine [Implementer ID] & [RegID] into [RegImpID].
>
>This is the code I'm using :
>
>[RegImpID].Value = Str([Implementer ID].Value) + [RegionID].Value

It looks like you have replaced [RegID] with [RegionID]
The field [RegionID] doesn't exist and so the error.

Hope that is the problem.


#
###
#####
#######
Ziggurat

 

[VS24]

Sorry... it was a typo!
The field is actually [RegID]
My bad!


Everything is 100% as far as I can see, I just do not understand why I'm getting that error!

Anybody...?

 

[PHV]

And this ?
[RegImpID].Value = [highlight]C[/highlight]Str([Implementer ID].Value) [highlight]&[/highlight] [RegID].Value

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[VS24]

Tried "CStr" and "&" but it didn't work. Stil getting the same error!

Any other code I could try... or are there some options to change somewhere etc.?

Thanx!

 

[earthandfire]

It's giving me the "Run-time error 2465" plus "Can't find the field '|' referred to in your expression"

Does the error message say that it is a field called the pipe character (|) that it can't find?

 

[VS24]

Yes... don't understand why the pipe character appears!

Why is it looking for that?

 

[earthandfire]

You copied and pasted or exactly typed PHV's suggestion?

[RegImpID].Value = CStr([Implementer ID].Value) & [RegID].Value

and still get the same error?

Firstly, I think you can simplify the expression to:

[RegImpID] = [Implementer ID] & [RegID]


VBA will generally convert "suitable" data to the datatype of the left hand side of the argument - and in any case the & tells it that a string is required. However, this in itself will not solve the problem.

Can you post the surrounding code? There may be something in that.

 

[VS24]

Well I had it typed out already so I first changed “Str” to “CStr” and change the “+” to “&” and it didn’t work so then I also tried copying the code as is and pasting it, but it still didn’t work.

I cannot simplify the code as you suggested because it gives me a “Type mismatch” error and that is because the field “Implementer ID” is of type Autonumber.
I’ve also tried your simplified code using Str and CStr only and it still gives me the same error!

Here’s the code in the combo box :


Private Sub cmbRegion_AfterUpdate()

[ProvinceID].Value = cmbRegion.Column(0)

[RegImpID].Value = Str([Implementer ID].Value) + [RegID].Value

End Sub



Here’s an example of what I did on another form with slightly different fields :

Private Sub cmbRegion_AfterUpdate()

[ProvinceID].Value = cmbRegion.Column(0)

[RegSiteID].Value = Str([SiteID].Value) + [RegID].Value

End Sub

The second example works fine!!
So why doesn’t the first example work?

 

[PHV]

And this ?
Me![RegImpID] = CStr(Me![Implementer ID]) & Me![RegID]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[earthandfire]

To be honest, I don't have a clue. Nothing that you have got would appear to to justify Access looking fot a field called |.

The only other thing I can think of trying -

(As two separate tests):

[RegImpID].Value = Str([Implementer ID].Value)

[RegImpID].Value = [RegID].Value

and see what happens with each one.

Clutching at straws, I know, but its still worth trying!!

 

[VS24]

Tried the Me! code and it said that it could not find field "RegImpID" instead of the "|"
And "RegImpID" exists & is spelled identical in the table & the code! (Copied & pasted to make certain!)

As for the separate tests... both of those tests gave me the same error about not finding the "|"

What next...? I'm going crazy with this cos the code that I've written & the code from you guys should work, but isn't! I need to combine those fields!

Suggestions... I'm willing to try anything!

 

[earthandfire]

Do you have a controls on the form called:

RegImpID
Implementer ID
RegID

These are what your code is referencing, not the fields in the underlying table.

 

[PHV]

In code you should use Control name, not field name.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[VS24]

No controls on the form with the same name!

The control saves a value, but I'm also doing two extra storages after the update on the same control.
It works fine on one form but not on this one!

 

[earthandfire]

On the other form do you have controls with the same name as the fields?

This type of code refers to controls (which could well be bound to fields and therefore would most likely have the same names as the underlying fields)

 

[VS24]

Nope, the other form is almost identical to this and all my combo boxes are named cmb... and textfields are named txt... etc. so there isn't anything that is named the same in the table(s) and form(s).

I thought it might be the space in the field "Implementer ID" so I changed it in the table and the code but it still gave me the same error!!

 

[RoyVidar]

As stated by PHV and earthandfire, your code refers to form controls - NOT fields, so if you have no controls with those names, the errormessage is understandable - Access can't find the controls... and the pipe (|), is a replacement character in the err.description, where it's substituted with the control name at runtime, but in some cases, when the control name isn't available, it will show the pipe.

You can address/refer to fields in the forms recordsource through the notation:

[tt]Me.RegImpID[/tt]

I'm a bit curious, why do you do this concatenation? I hope you aren't storing it, when it can easily be calculated on the fly whenever you need to display it. For instance in a query, a report control...

So when you have controls on your form bound to the fields you wish to concatenate, and using a similar naming convention, try:

[tt]Me!txtRegImpID.Value = CStr(Me![txtImplementer ID].Value) & Me![txtRegionID].Value[/tt]

Also a bit confused, PHV has pointed out at least twice by example that Cstr is the function to use, if you need string conversion, and ampersand (&) is the preferred concatenation operator, still, in your last sample, you're still using Str and +...

Roy-Vidar

 

[earthandfire]

Sorry, VS24, I've run out of ideas.

I've just reread your last sentence - did you also strip the space out of the ControlSource property of txtImplementorID?

 

[VS24]

You guys are gonna kill me!
What an idiot I be!

Remember I told you guys that the form that works is linked to a table and this form filters on certain types?

Well in order to filter on certain types the Record Source isn't a link to a table but rather a query instead!

After I created the RegImpID in the table I didn't go back to the Record Source and include it in my query!

Just did it now & it works beautifully! Sorry for all the hassles guys... and thanx a bunch!

Roy-Vidar - The "Str" & "+" works fine... so does the "CStr" and "&" so I guess it's comes down to personal preference unless there are some problems that might crop up with the "Str" & "+" that I do not know about!

Thanx again guys!!