QBE field formatting 2

[zimmer9]

I have 2 fields that I want to concatenate (and remove the high order position of 1 of these fields) and display them as 1 composite field with a different field name on a report.

For example: I have a field named OfficeNumber and a field named CustomerNumber.

Office Number has a value of 0100
Customer Number has a value of 333333

I wrote a SQL statement as follows to handle this condition:
Right(tblCustomers.OfficeNumber, 3) + ' ' + tblCustomers.CustomerNumber AS [Account Number]

The result is a new field title called "Account Number" with a value of 100 333333

However, I want to translate this SQL code formatting I noted above to these 2 fields on the QBE in the row titled Field:

Could you tell me how I would express the formatting into the fields Office Number and CustomerNumber on the QBE to generate 1 composite field titled Account Number ?

 

[RoyVidar]

If you wite the SQL in the SQL view of the QBE and switch to design view, voila;-)

[tt][Account Number]: Right(tblCustomers.OfficeNumber, 3) & " " & tblCustomers.CustomerNumber[/tt]

We often use & for concatenation in Access, and in the QBE quotes in stead of single quotes.

Roy-Vidar

 

[PHV]

In the QBE you don't choose the OfficeNumber field nor the CustomerNumber, but an expression:
AccountNumber: Right(tblCustomers.OfficeNumber, 3) & ' ' & tblCustomers.CustomerNumber

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

Gee - included [brackets] - they aren't necessary, or could even produce an error

Roy-Vidar