Concatenation gives #Error 2

[swaybright]

Hey All,

I've got a mystery here:
I am trying to concatenate a field value with text in the control source property, I have =[myfield] & "text" I know that the syntax is right b/c I used the same syntax in another db form and get the desired results, but in this case I get #Error.

Does anyone know why? Is there some control property that has to be set a certain way, etc.

Thanks,

Shane

 

[ETID]

[myfield] maybe formated as something other than a string...

try:


=str$([myfield]) & "text"

 

[swaybright]

ETID,

Good thought, but didn't work. The data type is number with a field size of single if that helps. I don't have any formating (that I know of).
It blows my mind that sometimes I get the error and other times I don't.

SHane

 

[Hmadyson]

Are you getting the error when the field is null, in that case your formula should be =IIf(IsNull([myfield]),"text",cstr([myfield])&"text&quot

 

[swaybright]

Thats a good point, but no. What I've found is that for this particular field, the syntax gives #Error, but for other fields on the same form it concatenates correctly.
The difference is that this field is filled via an update query that selects a value from another table (i.e the value from a record that has a highest value in another field from the many side of a relationship for each record on the one side).
I've simplified the problem down to just the field value, so if I try to put =[myfield] in the control source I get #Error

if I use myfield, I get the desired value

if I use cstr([myfield]) I get Error 0

if I use str$([myfield]) I get #Error

 

[jvanleuvan]

Hey, I have a suggestion. This is a text box control on a form, right, in "control source" you have
=[myfield] & "TEXT"

make sure that "Name" (of the text box control) is not myfield

or chage the "control source" to
=[myquery].[myfield] & "TEXT"


If you use a wizard to make a form, Access automaticaly gives the text boxes the same name as the fields that the are connected to. If you try to reference [myfield] then access assumes you mean the text box, which is a circular refenece!

 

[swaybright]

Right on!!

Changing the name of the control did the job!!

Thanks for the explanation, it makes sense when you put it that way.

Thanks!!

Shane