Calculation in a QUERY

[htadis]

good day !

There is a numeric field which containing weight of some items. need to make a calculation like below.

if the weight is >9 then need to multiply by 10 (eg 270 )
if the weight is =<9 then need to multiply by 10 and also need to add "0" infront of the figure. (eg 050)

is it possible to do this ? can some expert help me in this regard pls.

thanks in advance.

brgds

 

[CJ_London]

pretty much as you have written if

iif(weight>9, weight*10,format(weight,"000"))

you don't need the condition <=9 because that has been resolved with >9 - if it is not >9 then it has to be <=9

note, you need to be clear about what type of datatype outcome you want - weight*10 is numeric, format(weight,"000") is text. You cannot have a numeric datatype with a preceding zero - although you can display it with preceding zeros using the control format property - so your calc would be

iif(weight>9, weight*10,weight)

and your control format would be 000

so if you need to multiply the resultant value later, you won't be able to if it is '009' without additional functionality to convert it back to a number

 

[htadis]

Many thanks for the swift respond CJ London.

but there is an issue. when put that formula, it does not multiply the weight <=9. so i just changed the formula to <=0. then its multiply s but not shows the front ZERO !!

i just need a text output only.

pls assist further.

 

[jdraw]

I think CJ's expression can be adjusted to

iif(weight>9, weight*10,format(weight * 10,"000"))

to give the result you need

 

[htadis]

many thanks Jdraw.

i got the result what i want by that in the query. however, when export same to a text file, the front Zero for the numbers <=9 not appears. instead it shows as
"50." i need to reflect it as "050"

any solution to solve this issue ?

 

[jdraw]

Tell us more about your export process. It seems you are exporting to a numeric field/data type, and that will remove the leading 0.
You must ensure you are exporting TEXT values to a TEXT data type.

 

[htadis]

my export process is as follows;

1. done the necessary calculation by the query as you instructed.however here i did not use IIF, use as follows instead.

expr1: Format([weight]*10,"000")

so result comes like;

050
270
360
070

2. by using same query, generated the Export specification for a fixed with text file. here i have given 03 digit space for weight column where i wanted to reflect the weight by 03 digit. leading zero should be there for the figures <=9.

3. by using the export specification with VBA "docmd", export the data of the above query to a text file.

4. unfortunately here its comes as mentioned previously i.e. without leading Zero for the figures <=9

Kindly help to get solve this.

THANKS !

 

[jdraw]

Please post all the code or database.

I'm not following the example.

If your weight is 5, you want *10 = 50 and formatted to 050 (text)
If the weight is 12, you want *10 =120 and formatted to 0120 or 120????

 

[htadis]

it should be as follows;

If weight is 5, weight *10 = 50 and formatted to 050 (text)
If weight is 12, weight *10 =120 and formatted to 120 (text)

 

[jadehawk]

I am not very experience but I gave it a shot. It creates two types of files using the DoCmd.Outputto & DoCmd.TransferText

Hope this helps you in any way..