Derived Field Troubles

  • Thread starter Thread starter mvdrumaboy
  • Start date Start date
M

mvdrumaboy

I am trying to build a query that uses the following to generate the
query:

UniqueID: Left([FlightID],3) & Left([Surname],3) & Right([PassID],3)

Which generates an output like "773wat1"

I would like to know how I can make the 'wat' into caps ('WAT) and how
I can force the '1' to become "001"

Your help is most appreciated
 
Use the UCase function and use the Format function. Or use concatenation.

UniqueID: Left([FlightID],3) & UCase( Left([Surname],3)) &
Format(Right([PassID],3),"000")

UniqueID: Left([FlightID],3) & UCase(Left([Surname],3)) & Right("000" &
[PassID],3)
 
Hi,


UCase(string) returns the string in all cap,

FORMAT(number, "000") returns the representation of the number with leading
(left) zeros to get at least 3 digits (even if 0s).

Format(Left([FlightID],3), "000") & UCase(Left([Surname],3)) &
Format(Right([PassID],3), "000")



should do.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top