Currency Problem In Report

[richardplr]

Hi,

I have a purchase Contract and on the body of contract I have fixed wording say "currency : Singapore Dollars" and if I have a different currency on the amount then I have to change the description on the body of the contract manual to read as what ever the currency is on the amount.

May I know what is the best way to do it,

I have another problem that is the symbol on the currency is limited, is there a way to include other currency. eg Yen, US, RM..

I have attached my program for your references. Please advise.

Thank you

Regards
Richard

 

[GumbyD]

I took a look at your database. When I have had to deal with multiple currencies, I will create a new code table called Currency and put in the fields CurrencyID, CurrencyDesc, and CurrencySymbol. The data for this might be BP, British Pound, and the unicode symbol for the British Pound. Then on your "Sub-form" table add a CurrencyID field and change the three fields that currently have a "Currency" datatype to Double and format them to two decimal places. Then on your report put the currency symbol and the amount fields together in an expression ([CurrencySymbol]&[CurrencyFieldname]. Try it out and see if it gives you want you want!

GumbyD

 

[richardplr]

I took a look at your database. When I have had to deal with multiple currencies, I will create a new code table called Currency and put in the fields CurrencyID, CurrencyDesc, and CurrencySymbol. The data for this might be BP, British Pound, and the unicode symbol for the British Pound. Then on your "Sub-form" table add a CurrencyID field and change the three fields that currently have a "Currency" datatype to Double and format them to two decimal places. Then on your report put the currency symbol and the amount fields together in an expression ([CurrencySymbol]&[CurrencyFieldname]. Try it out and see if it gives you want you want!

GumbyD

Hi, Thank you for your advise. However, I am very new in Ms Access, with your explanation, I cannot figure out how to make the program work. R u able to create a sample for me.

Hope to hear from you.

Regards,

 

[GumbyD]

Ok - here is a bit more information. I will attach a jpg with some images that might help and below is the how I changed your SQL for the query running the report. You wil still have to make a few changes to the report and may want to have your "Unit Price" and "Amount" fields in the detail section but not visable to make your totals work.

GumbyD

SELECT [Supplier Master List].SupplierName, [Supplier Master List].StreetName, [Supplier Master List].[Postal Code], [Main Data].PCNo, [Main Data].Date, [Main Data].Salesman, [Main Data].[Required Date], [Main Data].[Quotation No], [Main Data].[Quotation Date], [salesperson contact no].[Tel No], [salesperson contact no].[Fax No], [Main Data].[Partial Shpt], [Main Data].[Term of Payment], [Main Data].Remarks, [Requisitor Name].Initial, [Main Data].Section, [Sub-form].Item, [Sub-form].[Product Code], [Sub-form].[Product Description], [Sub-form].Qty, [Sub-form].[Unit Price], [amount]*0.04 AS GST, [unit price]*[qty] AS Amount, [amount]+[GST] AS Total, Currency.CurrencySymbol, Currency.CurrencyDesc
FROM (([Sub-form] INNER JOIN ([Requisitor Name] INNER JOIN [Main Data] ON [Requisitor Name].Name = [Main Data].Requisitor) ON [Sub-form].PCNO = [Main Data].PCNo) INNER JOIN ([Supplier Master List] INNER JOIN [salesperson contact no] ON [Supplier Master List].[Supplier Code] = [salesperson contact no].[Supplier Code]) ON [Main Data].Salesman = [salesperson contact no].Name) INNER JOIN [Currency] ON [Sub-form].CurrencyCode = Currency.CurrencyCode
WHERE ((([Main Data].PCNo)=[Please Enter Contract Number]));

 

[richardplr]

Thanks a million

I will try on my system, I do not know whether I can manage or not. I will let you know

Thanks again.

Regards,
Richard Tan

 

[richardplr]

Any one can help on this

Ok - here is a bit more information. I will attach a jpg with some images that might help and below is the how I changed your SQL for the query running the report. You wil still have to make a few changes to the report and may want to have your "Unit Price" and "Amount" fields in the detail section but not visable to make your totals work.

GumbyD

SELECT [Supplier Master List].SupplierName, [Supplier Master List].StreetName, [Supplier Master List].[Postal Code], [Main Data].PCNo, [Main Data].Date, [Main Data].Salesman, [Main Data].[Required Date], [Main Data].[Quotation No], [Main Data].[Quotation Date], [salesperson contact no].[Tel No], [salesperson contact no].[Fax No], [Main Data].[Partial Shpt], [Main Data].[Term of Payment], [Main Data].Remarks, [Requisitor Name].Initial, [Main Data].Section, [Sub-form].Item, [Sub-form].[Product Code], [Sub-form].[Product Description], [Sub-form].Qty, [Sub-form].[Unit Price], [amount]*0.04 AS GST, [unit price]*[qty] AS Amount, [amount]+[GST] AS Total, Currency.CurrencySymbol, Currency.CurrencyDesc
FROM (([Sub-form] INNER JOIN ([Requisitor Name] INNER JOIN [Main Data] ON [Requisitor Name].Name = [Main Data].Requisitor) ON [Sub-form].PCNO = [Main Data].PCNo) INNER JOIN ([Supplier Master List] INNER JOIN [salesperson contact no] ON [Supplier Master List].[Supplier Code] = [salesperson contact no].[Supplier Code]) ON [Main Data].Salesman = [salesperson contact no].Name) INNER JOIN [Currency] ON [Sub-form].CurrencyCode = Currency.CurrencyCode
WHERE ((([Main Data].PCNo)=[Please Enter Contract Number]));

Hi

Thank you for your help. I have made all the arrangement. But I have a little problem.

The GST cannot work and all the digit change to numberic instead of numeric plus decimal. I try to change the property decimal place also cannot. if I use #,###.00 or #,###,## the digit may give me the number but if I have any cents example S$0.50, it will not appear S$0.50, it will either plus to S$1.00 or reduce to
S$0.00

Can one can help.

Thanks

 

[Rich]

Change the field to single or double, instead of integer

 

[richardplr]

Change the field to single or double, instead of integer

I tried to change all the field to double but still cannot work.
I can now get the 2 digit decimal but on my purchase contract, i cannot get, why?? can anyone help.

2nd thing, my calcuation for GST 4% alway give me error, can anyone help me to solve.

Thank you

 

[richardplr]

I tried to change all the field to double but still cannot work.
I can now get the 2 digit decimal but on my purchase contract, i cannot get, why?? can anyone help.

2nd thing, my calcuation for GST 4% alway give me error, can anyone help me to solve.

Thank you

Can someone please help....

Please....

Thanks

 

[Rich]

Post your calculation and GST's data type

 

[GumbyD]

Try this for the GST field

=[currencysymbol] & format((Sum([Amount])*0.04),"0.00")

and this for the total field (TEXT127 field)

=[currencysymbol] & format((Sum([Amount])*1.04),"0.00")

GumbyD

 

[Rich]

Does =format((Sum([Amount])*0.04),"0.00") on its own work?