Format Function

[Martin]

Hello,

I have a query which takes the right side of an alpha numeric unique
reference number. Here is the function I am trying to perform:

Right: IIf([Len]=7,Format(Right([tbl Details]![Ref
No],1),"Standard"),IIf([Len]=8,Format(Right([tbl Details]![Ref
No],2),"Standard"),IIf([Len]=9,Format(Right([tbl Details]![Ref
No],3),"Standard"),IIf([Len]=10,Format(Right([tbl Details]![Ref
No],4),"Standard"),""))))

It wll works fine except the format is ignored and it is still in text only
looking in a standard format. This is important because I have another query
finding the max of this query and as it is in a non numeric format the max
function does not work. Can anyone help?

Thank you.

Martin

 

[Gary Walter]

I have a query which takes the right side of an alpha numeric unique
reference number. Here is the function I am trying to perform:

Right: IIf([Len]=7,Format(Right([tbl Details]![Ref
No],1),"Standard"),IIf([Len]=8,Format(Right([tbl Details]![Ref
No],2),"Standard"),IIf([Len]=9,Format(Right([tbl Details]![Ref
No],3),"Standard"),IIf([Len]=10,Format(Right([tbl Details]![Ref
No],4),"Standard"),""))))

It wll works fine except the format is ignored and it is still in text
only
looking in a standard format. This is important because I have another
query
finding the max of this query and as it is in a non numeric format the max
function does not work. Can anyone help?

Hi Martin,

I believe you are saying you want to strip
off the numbers from the refno, and then
have those "numbers" be "numbers" so
your Max will work numerically.

Any time you use Format(), the result
will be a string (plus final "" will cast result
to string anyway).

What will the range of these numbers be?

My guess is that you can get rid of all the Format's
in your expression, then wrap the IIF() in CLng?

{"Right" is reserved word so I don't think
you really want to use it as an alias}

try something like?

RefNumber: CLng(
IIf([Len]=7, Right([tbl Details].[Ref No],1),
IIf([Len]=8, Right([tbl Details].[Ref No],2),
IIf([Len]=9, Right([tbl Details].[Ref No],3),
IIf([Len]=10, Right([tbl Details].[Ref No],4),"0"))))
)

maybe you have a better idea than "0" for a
final IIF-fail RefNumber (say "9999999")....

good luck,

gary

 

[Gary Walter]

Hi Martin,

Also...if your [Ref No] is so constructed
that all number chars are together, you
might just try

RefNumber: CLng(Val([Ref No]))

or (better)

RefNumber: CLng(Val(Nz([Ref No],0)))

good luck,

gary

I have a query which takes the right side of an alpha numeric unique
reference number. Here is the function I am trying to perform:

Right: IIf([Len]=7,Format(Right([tbl Details]![Ref
No],1),"Standard"),IIf([Len]=8,Format(Right([tbl Details]![Ref
No],2),"Standard"),IIf([Len]=9,Format(Right([tbl Details]![Ref
No],3),"Standard"),IIf([Len]=10,Format(Right([tbl Details]![Ref
No],4),"Standard"),""))))

It wll works fine except the format is ignored and it is still in text
only
looking in a standard format. This is important because I have another
query
finding the max of this query and as it is in a non numeric format the
max
function does not work. Can anyone help?

Hi Martin,

I believe you are saying you want to strip
off the numbers from the refno, and then
have those "numbers" be "numbers" so
your Max will work numerically.

Any time you use Format(), the result
will be a string (plus final "" will cast result
to string anyway).

What will the range of these numbers be?

My guess is that you can get rid of all the Format's
in your expression, then wrap the IIF() in CLng?

{"Right" is reserved word so I don't think
you really want to use it as an alias}

try something like?

RefNumber: CLng(
IIf([Len]=7, Right([tbl Details].[Ref No],1),
IIf([Len]=8, Right([tbl Details].[Ref No],2),
IIf([Len]=9, Right([tbl Details].[Ref No],3),
IIf([Len]=10, Right([tbl Details].[Ref No],4),"0"))))
)

maybe you have a better idea than "0" for a
final IIF-fail RefNumber (say "9999999")....

good luck,

gary

 

[Martin]

Gary,

This first solution worked perfectly. Thank you for your help, it is much
appreciated.

Martin

Hi Martin,

Also...if your [Ref No] is so constructed
that all number chars are together, you
might just try

RefNumber: CLng(Val([Ref No]))

or (better)

RefNumber: CLng(Val(Nz([Ref No],0)))

good luck,

gary

I have a query which takes the right side of an alpha numeric unique
reference number. Here is the function I am trying to perform:

Right: IIf([Len]=7,Format(Right([tbl Details]![Ref
No],1),"Standard"),IIf([Len]=8,Format(Right([tbl Details]![Ref
No],2),"Standard"),IIf([Len]=9,Format(Right([tbl Details]![Ref
No],3),"Standard"),IIf([Len]=10,Format(Right([tbl Details]![Ref
No],4),"Standard"),""))))

It wll works fine except the format is ignored and it is still in text
only
looking in a standard format. This is important because I have another
query
finding the max of this query and as it is in a non numeric format the
max
function does not work. Can anyone help?

Hi Martin,

I believe you are saying you want to strip
off the numbers from the refno, and then
have those "numbers" be "numbers" so
your Max will work numerically.

Any time you use Format(), the result
will be a string (plus final "" will cast result
to string anyway).

What will the range of these numbers be?

My guess is that you can get rid of all the Format's
in your expression, then wrap the IIF() in CLng?

{"Right" is reserved word so I don't think
you really want to use it as an alias}

try something like?

RefNumber: CLng(
IIf([Len]=7, Right([tbl Details].[Ref No],1),
IIf([Len]=8, Right([tbl Details].[Ref No],2),
IIf([Len]=9, Right([tbl Details].[Ref No],3),
IIf([Len]=10, Right([tbl Details].[Ref No],4),"0"))))
)

maybe you have a better idea than "0" for a
final IIF-fail RefNumber (say "9999999")....

good luck,

gary

 

[John Spencer]

I think you should be able to use the following expression.

TheNumber: IIF([Len] between 7 and 10,Val(Right([tbl Details].[Ref no],
[Len] -6)),Null)

This will error if RefNo is null.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..