Complex Format of textbox / query

[DubaiDave]

Merry Christmas & hope you are anjoying your cold turkey sandwiches (I am).

I have a query with one field that concatonates five other fields in the query, thus:

RfiRef: "RFI" & "-" & [ProjectNo] & "-" & [TaskNo] & "-" & [RfiNo] & "-" & [RfiIssue].

RfiNo and RfiIssue are fomatted in thier tables as three digt numbers eg 003.

When I display RfiRef in a textbox, RfiNo and RfiIssue display as:

RFI-80001-41r-3-4.

I want the textbox to show:

RFI-80001-41r-003-004.

How do I do that? Do I format the textbox or the query or something else?

Cheers

David

 

[twoplustwo]

How are you treating numbers > 9?

Will they still require the 00?

If so, just add that to the query like & "00" & [RfiNo] &......

If not, maybe IIf([RfiNo] <= 9, "00" & [RfiNo], [RfiNo]) in the statement.

Maybe stick a CNum in front of that to make sure it's converted to a number.

 

[DubaiDave]

Hi,

So I tried to to place the following in the expression builder, but it does not accept it.

RfiRef: "RFI" & "-" & [ProjectNo] & "-" & [TaskNo] & "-" & [RfiNo] & "-" & Right([RfiIssue] +100;3)

Right([original_number] + 100; 3) will add two leading digits to a numeric string

Is there a way to incoporate a formula into a query?

David

 

[DubaiDave]

Dear Uncle Gizmo and twoplus two,

Thanks for your help. This expression did the business in the end (I had a ; instead of an , ).

RfiRef: "RFI" & "-" & [ProjectNo] & "-" & [TaskNo] & "-" & Right([RfiNo]+1000,3) & "-" & Right([RfiIssue]+1000,3)

Ref http://members.fortunecity.com/digispy/C2/P18/C2P18A2059.htm

Dave