Can't concatenate field with text with another field with numbers

[lbcarvalho]

Hi, every1,



I have a table with a field of numbers (PmtReceived) and another field with the currency (CUR). The latter can be USD for US dollars or EUR for euros.

I would like to join these two fields into a single one (in a report) with CUR + PmtReceived and I have tried millions of things to no avail.

Example:
CUR VALUE = JOINED NEW FIELD
USD 2,000.00 = USD 2,000.00

Thanks in advance for any suggestion!

lbcarvalho

[pbaldy]

Have you tried & as the concatenation operator?

[lbcarvalho]

Yes, I have used the operator & millions of times.

[lbcarvalho]

Yes, I have used the operator & millions of times.

I have tried this, for example: =[CUR] & [PmtReceived] but it didn't work.

[Gicu]

Have you tried COmbined:[Cur] & " " & Format([PmtReceived, "Currency")?

Cheers,
Vlad

[CJ_London]

you can use the format property for the control e.g. "USD "#,##0.00

or you can use the format function e.g. format([pmtRecieved],"""USD ""#,##0.00")

the function converts your value to text so use the property method if you want to refer to the original value - to total for example. In your case, because the CUR value changes you will need to use the function - something like

[CUR] & " " & format([pmtRecieved],"#,##0.00")

or

Format([pmtRecieved],"""" & [CUR] & """ #,##0.00")

for the future, comments such as 'I have tried millions of things to no avail.' without providing examples of what you have tried give the impression you haven't tried anything. It helps to know what you have tried and what failed - it may be the code you tried was wrong or right but in the wrong place.

[lbcarvalho]

I just tried you suggestion: =[CUR] & " " & Format([PmtReceived],"Currency") but when I click on the Display Mode button the fields show #Type!

[pbaldy]

Make sure no control has the same name as those 2 fields. Can you attach the db here?

[lbcarvalho]

This is what I am getting so far. Not sure if I made it enough clear, but it is a report I'm trying to build.

[lbcarvalho]

I've also tried this: =[CUR] & " " & Format([pmtReceived];"#,000\.00") in the Data > Control Source

[lbcarvalho]

BTW, [MOEDA] is Portuguese for [CUR]. Sorry for the confusion, but I am in Brazil and also trying to make things easier for you guys.

[pbaldy]

Did you notice this?

Make sure no control has the same name as those 2 fields.

The picture shows the textbox has the same name as the field.

[Gicu]

Or even betterdo the concatenation in the report's record source (query) and use that field as the control's source.

Cheers,
Vlad

[lbcarvalho]

Actually no, I had not noticed that. Thanks for pointing it out! And it really seems to have been the cause of all the trouble.

I deleted the field and inserted it again using the Access right pane (Tools > Add Existing Fields) and inserted this: =[CUR] & " " & Format([PmtReceived];"#.###,00") and Access itself took care of the same name issue. This time it worked out fine!

Thank you all very much!

[pbaldy]

Happy to help!