Field Formats

[Guest]

Hi,

I have a numeric field, which needs to be right justified eg. the values are
4000 and 33.10, the field size is 15 and the format is 9(9)v9(6). The output
basically needs to be “000004000000000†and “000000033100000†respectively.

My question is if I need to put this in the query, in the relevant field, I
know that if you go into the properties of the field, there is a format
option, but I am not sure what that format is to achieve this. I have tried
various ways, but am not getting to
the right solution.

Thank You.

 

[John Spencer]

I don't know of any format that will do this

I would try a calculated string field in a query that looks like
Field: RightJusified: FORMAT( Int([TheNumber],"000000000") &
Format([TheNumber]-Int([TheNumber],"000000")

Another way to do this would be to multiply the number and then apply the
format
Field:RightJustified: Format(TheNumber * 1000000,"000000000000000")

 

[John Spencer]

That first line should be
I don't know of any format you can use in the format property to do this.

I don't know of any format that will do this

I would try a calculated string field in a query that looks like
Field: RightJusified: FORMAT( Int([TheNumber],"000000000") &
Format([TheNumber]-Int([TheNumber],"000000")

Another way to do this would be to multiply the number and then apply the
format
Field:RightJustified: Format(TheNumber * 1000000,"000000000000000")

Hi,

I have a numeric field, which needs to be right justified eg. the values
are
4000 and 33.10, the field size is 15 and the format is 9(9)v9(6). The
output
basically needs to be "000004000000000" and "000000033100000"
respectively.

My question is if I need to put this in the query, in the relevant field,
I
know that if you go into the properties of the field, there is a format
option, but I am not sure what that format is to achieve this. I have
tried
various ways, but am not getting to
the right solution.

Thank You.

 

[John Nurick]

Hi Lucy,

I'd probably do
Format(33.10 * 10E6, "000000000000000")

 

[Guest]

Thanks so much for the quick reply John. I will try this and let you know.

That first line should be
I don't know of any format you can use in the format property to do this.

I don't know of any format that will do this

I would try a calculated string field in a query that looks like
Field: RightJusified: FORMAT( Int([TheNumber],"000000000") &
Format([TheNumber]-Int([TheNumber],"000000")

Another way to do this would be to multiply the number and then apply the
format
Field:RightJustified: Format(TheNumber * 1000000,"000000000000000")

Hi,

I have a numeric field, which needs to be right justified eg. the values
are
4000 and 33.10, the field size is 15 and the format is 9(9)v9(6). The
output
basically needs to be "000004000000000" and "000000033100000"
respectively.

My question is if I need to put this in the query, in the relevant field,
I
know that if you go into the properties of the field, there is a format
option, but I am not sure what that format is to achieve this. I have
tried
various ways, but am not getting to
the right solution.

Thank You.

 

[Guest]

Thanks so much John. Really appreciate your quick response.This is the format
that I needed and it resolved my issue.
I have another question related to this. How do I combine this format to
other calculations that I am doing on a particular field in a query. eg. In
my query, I have a calculation:

Expr1: (IIf([Grant Type Name]="2005 PS-8","0",IIf([Grant Type Name]="2005
RSU-5","0",IIf([Grant Type Name]="2005 SO-1","[Quantity Granted]",IIf([Grant
Type Name]="2006 SAR","[Quantity Granted]",IIf([Grant Type Name]="2006
LTCSH","0",IIf([Grant Type Name]="2006 PS-8","0","0")))))))
and I want to apply the special formatting below to the result achieved in
the calculation above:
RightJustified: Format([Quantity Granted]*1000000,"000000000000000") to it,
how am I able to combine both of these calculations? I have tried various
ways and I am still getting errors.

Any help would be greatly appreciated.

Many Thanks,

 

[Guest]

Thanks so much John. Really appreciate your quick response.This is the format
that I needed and it resolved my issue.
I have another question related to this. How do I combine this format to
other calculations that I am doing on a particular field in a query. eg. In
my query, I have a calculation:

Expr1: (IIf([Grant Type Name]="2005 PS-8","0",IIf([Grant Type Name]="2005
RSU-5","0",IIf([Grant Type Name]="2005 SO-1","[Quantity Granted]",IIf([Grant
Type Name]="2006 SAR","[Quantity Granted]",IIf([Grant Type Name]="2006
LTCSH","0",IIf([Grant Type Name]="2006 PS-8","0","0")))))))
and I want to apply the special formatting below to the result achieved in
the calculation above:
RightJustified: Format([Quantity Granted]*1000000,"000000000000000") to it,
how am I able to combine both of these calculations? I have tried various
ways and I am still getting errors.

Any help would be greatly appreciated.

Many Thanks,

I don't know of any format that will do this

I would try a calculated string field in a query that looks like
Field: RightJusified: FORMAT( Int([TheNumber],"000000000") &
Format([TheNumber]-Int([TheNumber],"000000")

Another way to do this would be to multiply the number and then apply the
format
Field:RightJustified: Format(TheNumber * 1000000,"000000000000000")

Hi,

I have a numeric field, which needs to be right justified eg. the values
are
4000 and 33.10, the field size is 15 and the format is 9(9)v9(6). The
output
basically needs to be "000004000000000" and "000000033100000"
respectively.

My question is if I need to put this in the query, in the relevant field,
I
know that if you go into the properties of the field, there is a format
option, but I am not sure what that format is to achieve this. I have
tried
various ways, but am not getting to
the right solution.

Thank You.

 

[John Spencer]

Well, first of all what do you mean by combine? The Expression you posted seems
to return a string of that consists of the character zero or the characters
[Quantity Granted].

I think what you might want is more like the following where the value in
quantity granted is returned when it is one of the two values or zero is returned


RightJustified:

Format(IIf([Grant Type Name]="2005 SO-1" OR [Grant Type Name]="2006
SAR",[Quantity Granted],0) * 10e6,"000000000000000")

Thanks so much John. Really appreciate your quick response.This is the format
that I needed and it resolved my issue.
I have another question related to this. How do I combine this format to
other calculations that I am doing on a particular field in a query. eg. In
my query, I have a calculation:

Expr1: (IIf([Grant Type Name]="2005 PS-8","0",IIf([Grant Type Name]="2005
RSU-5","0",IIf([Grant Type Name]="2005 SO-1","[Quantity Granted]",IIf([Grant
Type Name]="2006 SAR","[Quantity Granted]",IIf([Grant Type Name]="2006
LTCSH","0",IIf([Grant Type Name]="2006 PS-8","0","0")))))))
and I want to apply the special formatting below to the result achieved in
the calculation above:
RightJustified: Format([Quantity Granted]*1000000,"000000000000000") to it,
how am I able to combine both of these calculations? I have tried various
ways and I am still getting errors.

Any help would be greatly appreciated.

Many Thanks,

I don't know of any format that will do this

I would try a calculated string field in a query that looks like
Field: RightJusified: FORMAT( Int([TheNumber],"000000000") &
Format([TheNumber]-Int([TheNumber],"000000")

Another way to do this would be to multiply the number and then apply the
format
Field:RightJustified: Format(TheNumber * 1000000,"000000000000000")

Hi,

I have a numeric field, which needs to be right justified eg. the values
are
4000 and 33.10, the field size is 15 and the format is 9(9)v9(6). The
output
basically needs to be "000004000000000" and "000000033100000"
respectively.

My question is if I need to put this in the query, in the relevant field,
I
know that if you go into the properties of the field, there is a format
option, but I am not sure what that format is to achieve this. I have
tried
various ways, but am not getting to
the right solution.

Thank You.