Report Recordsource 1

[FoxProProgrammer]

Hi folks,

I posted a question similar to this about two weeks ago. It wasn't resolved and I have been off working on other things. This really seems like a simple issue but I just can't seem to figure out why it isn't working.

Problem: My table has a field named [US Number] that contains integers such as 3, 19 and 126. I want to concatenate that field with the string "US0612-" to produce US0612-3, US0612-19, and US0612-126 in the report. I have tried several methods and none of them work.

The Recordsource for the report is based on the following query:

SELECT "US0612-" & [Serial Number].[US Number] AS USNum, TRU.[Part Number], TRU.Description, [Serial Number].[Serial Number], Calibration.[Last Cal], Calibration.[Cal Due], Calibration.[Last Cal Report]
FROM TRU INNER JOIN ([Serial Number] INNER JOIN Calibration ON [Serial Number].[Serial Number ID] = Calibration.[Serial Number ID]) ON TRU.[TRU ID] = [Serial Number].[TRU ID];

The results of this query in the Query Builder window are correct. The output column named USNum contains "US0612-" concatenated with each [US Number] as required.

The detail section of the report has a field named USNum and its Control Source is set to USNum. When I run the report, no data shows up in that field.

Does anyone know why this is happening? I don't understand why the correct data shows up in the Query output in the design window, but not in the detail section when I run the report.

Thanks for any ideas.
dz
dzaccess@yahoo.com

 

[LittleSmudge]

Yes, I think I know why.

It is because your SQL string effectivly generates field NAMES with the "US0612" prefix ( Which are all then renamed to USNum, rather than what you want which is field names which contain DATA with the "US0612" prefix.


Solution:-
Get rid of the "US0612" text in the SQL string
Then in the USNum control change the NAME of the control to ConCatUSNum and change the ControlSource to
="US0612" & [USNum]


This is one good example where you must NOT have the control name and the bound source field of the same name. If you do it makes a circular reference and Access has no way of either doing it - or warning about it.



'ope-that-'elps.

G LS

 

[FoxProProgrammer]

Hi G LS,

I tried something similar to what you suggested last week when I was working on this problem, and it didn't work. Here is a link to the thread where it was discussed before.

thread703-327102

In my last post, I wrote that I tried the following, which seems like what you have suggested:

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.

I just tried it again, and get no data in that field in the report. This problem has me baffled. I don't understand why something that should be so easy is causing me so many problems!

Thanks again for your help. Do you know what I might be doing wrong?
dz
dzaccess@yahoo.com

 

[CosmoKramer]

dz,

I've gotten this to work both ways; creating an expression in a query, or appending the string in a text box in the report. Can you send me a copy of your db? Maybe I can see something.....

sw3540@yahoo.com

 

[FoxProProgrammer]

Thank you, Cosmo. I'll zip it up and send it to you. I must have overlooked something. I sincerely appreciate your offering to look at it. dz
dzaccess@yahoo.com

 

[CosmoKramer]

Dan,

I found the problem and sent you a response.....

 

[FoxProProgrammer]

G LS,

Thank you so much! I am embarrased to say what the problem was, and don't really know how it happened. Perhaps I changed the state of that property while debugging and forgot to set it back. I am puzzled why the report didn't work in the first place because I implemented it the way that it is working now. Oh well, voodoo at work again. lol

Thanks again.

dz
dzaccess@yahoo.com