How do I "sum" text with numbers?

[Smapty]

SELECT
("insert into Mytable (PERSON_ORG_RELATION_ID,PERSON_ORG_ID_FROM,PERSON_ORG_ID_TO,PERSON_ORG_RELATION_TYPE_ID) values (next value for seq_PERSON_ORG_RELATION_ID,0001,100,"+[b][My Data].Relation[/b]+");") AS String1
FROM [My Data];

That query works fine as long as the bolded part is a text or memo field, but when the field format is a number, it fails... I guess because its trying to add the number instead of just building it as a part of a larger string. How do I set the query to treat number fields as text values?

Also, the query will skip rows if any of the fields in My Data our empty. I need to account for those and place a '' in its place.

 

[jebry]

Hi!

You can try:

Nz(Format([My Data].Relation), "")

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[Golom]

And use the & operator to concatenate strings (instead of "+").

"+" is an overloaded operator that will add if the arguments are numbers and will concatenate if they are strings.