concatenate question

[Richard]

This seems very odd to me, I'm trying to concatenate two fields in a query.

Expr1: [site] & -[school] = #Error

but if I reverse it works fine?

Expr2: [school] & -[site] = Kennedy-27

I want the query to give me this result 27-Kennedy

Thanks for any help.

 

[louisjohnphillips]

This seems very odd to me, I'm trying to concatenate two fields in a query.

Expr1: [site] & -[school] = #Error

but if I reverse it works fine?

Expr2: [school] & -[site] = Kennedy-27

I want the query to give me this result  27-Kennedy

Thanks for any help.

Try to decompose the problem:


Select CStr( [site] ) & '-' & [School] from table1.

The error probably occurred because the minus sign attempted to negate
a string, not a number.

 

[Richard]

Expr1: ([site]) & '-' & [School]

This seems to work, but I have concatenate two sets of numbers before with
no problems ie 1122-7752 and so on.

Thank You.

This seems very odd to me, I'm trying to concatenate two fields in a query.

Expr1: [site] & -[school] = #Error

but if I reverse it works fine?

Expr2: [school] & -[site] = Kennedy-27

I want the query to give me this result 27-Kennedy

Thanks for any help.

Try to decompose the problem:


Select CStr( [site] ) & '-' & [School] from table1.

The error probably occurred because the minus sign attempted to negate
a string, not a number.

 

[John W. Vinson]

Expr1: ([site]) & '-' & [School]

This seems to work, but I have concatenate two sets of numbers before with
no problems ie 1122-7752 and so on.

The expression above, with quotes around the hyphen, is concatenating three
text strings (two in fields, one a text literal '-') into one new text string.

If you don't enclose the hyphen in quotes, AND if the following argument is
numeric, it will be treated as a numeric operator; that is, if the second
field contains +7752, then the expression

[field1] & - [field2]

first applies the negative value operation to field2 giving the number -7752,
and then concatenates THAT number.

If the second field is Text, Access (quite properly!) reports an error, since
the text string "Kennedy" is not a number, and cannot be made into a negative
value.

 

[Richard]

Thanks John for the futher explaination.

Expr1: ([site]) & '-' & [School]

This seems to work, but I have concatenate two sets of numbers before with
no problems ie 1122-7752 and so on.

The expression above, with quotes around the hyphen, is concatenating three
text strings (two in fields, one a text literal '-') into one new text string.

If you don't enclose the hyphen in quotes, AND if the following argument is
numeric, it will be treated as a numeric operator; that is, if the second
field contains +7752, then the expression

[field1] & - [field2]

first applies the negative value operation to field2 giving the number -7752,
and then concatenates THAT number.

If the second field is Text, Access (quite properly!) reports an error, since
the text string "Kennedy" is not a number, and cannot be made into a negative
value.