Expression in Report Query

[FoxProProgrammer]

Hi folks,

My report is based on a query from three related tables. A field in the report named [Serial Number].[US Number] contains a number such as 12, 124, etc. I want to concatenate the string "US0612-" with the US Number to produce US0612-12, US0612-124, etc. in the detail section.

I first tried to concatenate the string in the Control Source of the text box in the detail section. That didn't work.

Next I tried to concatenate the string with the field name in the query that is stored in the Report's Record Source. The query returns the correct results when I run it in Design View, but when I run the report it asks me to enter the USNum, which is the name of the expression. Here is the relevant portion of my query.

Select "US0612-" & Trim(Str([Serial Number].[US Number])) As USNum, ... From .... Inner Join....

Thanks in advance for any ideas on what I did wrong.

dz
dzaccess@yahoo.com

 

[CosmoKramer]

If you only want to display this concatenated field, then add an unbound text box to your detail section and set it's control source to something like:

"US0612-" & [Serial Number].[US Number]

 

[FoxProProgrammer]

Thanks for the reply, Cosmo. I tried something similar to your suggestion first. I set the Control Source for the unbound text box in the detail section to:

"US0612-" & Trim(Str([US Number]))

In this case, the query in the Report's Record Source contained a field named [US Number]. When I run it like this, I get #Error in the US Number field.

I tried it like you suggested and get #Name?

Since US Number is a number, I thought that I had to convert it to a string first. That's why I am using Trim(Str([US Number])) Is that not necessary? I tried putting "US0612-" & Trim(Str([Serial Number].[US Number])) in the Control Source of the field in the detail section and also get #Name? Does Access treat all fields in a report as text even if they are numeric?

By the way, this report has several other fields, but they are not expressions.

Thanks again for your help.
dz
dzaccess@yahoo.com

 

[CosmoKramer]

Is [US Number] by itself a field on the report? If not, add it to the detail line and set its Visible property to No. Then set the control source of your concatenated text box to:

="US0612-" & [US Number]

 

[FoxProProgrammer]

US Number is not by itself in the report. It is a field in the Serial Number table, and I want to concatenate it with "US0612-" in the report. Some sample data for this field in the table is 24, 122, 126, etc. I don't understand why one of the following methods doesn't work:

1. Return [Serial Number].[US Number] in the query in the Report's Record Source. Then concatenate "US0612-" with [US Number] in the text box field in the detail section.

OR

2. Return an expression in the query in the Report's Record Source that concatenates "US0612-" with [Serial Number].[US Number], and include that expression in the detail section.

It seems like either method should work, but neither do.

I tried your last suggestion and it doesn't display any data in the US Number field in the detail section. I don't get an error, but the field is blank. I included [Serial Number].[US Number] in the query and left the US Number bound text box in the detail section with its Visible property set to No. I then created another text box, and stored "US0612-" & [US Number] in its Control Source. I put the unbound text box right on top of the hidden bound text box.

Thanks again for any other ideas. dz
dzaccess@yahoo.com