Complex Format of textbox / query

DubaiDave

Registered User.
Local time
Today, 16:28
Joined
Nov 25, 2008
Messages
69
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
 
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.
 
Assuming that the fields RfiNo and RfiIssue are storing number based information.

when you extract the information, the leading zeros are dropped off.

One solution would be to convert the fields RfiNo and RfiIssue to text and have them store text versions of the numbers you want: "003"

Another solution would be to keep them as number fields and add the leading zeros with a function.
 
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
 

Users who are viewing this thread

Back
Top Bottom