Select custom number format from table for report

[flect]

Hi there

I have a stock control / stocktake report system that deals with a few different countries.

Pretty much all of these countries have their own unique currency format.

Initially I had several copies of my database, one for each country with the appropriate number formats already programmed in on my reports.

Now i'm trying to consolidate all of those individual db's into one big one.

So far it's been successful but I'm still having to go in and manually change the number formats on the reports before printing.


How can I program my text boxes to retrieve the appropriate currency format from the appropriate table??


i've started with

Private Sub report_open(cancel as Integer)
StockValA.Format =

and what i'm logically thinking is

Private Sub report_open(cancel as Integer)
StockValA.Format = [tblagency].[currencyformat] where [agentID]=forms!frmnavigation!masterfilter

But obviously that syntax is incorrect and my VB experience is limited.

 

[RuralGuy]

How about using DLookup()?

 

[flect]

i'm not sure what DLookup will do for me,


- i've thought about it and started again with VBA

Private sub report_open(cancel as integer)
Dim cformat as String
cformat = "SELECT currencyformat from tblAgency where agencyID = forms!frmnavigation!masterfilter"

StockValA.Format = cformat
StockValB.Format = cformat

now, as a query -

"SELECT currencyformat from tblAgency where agencyID = forms!frmnavigation!masterfilter"

returns the string

"SG$"#,##0.00

which is the format I need.

Unfortunatly the result I get from StockValA is
"

48ELE27/11/1914 7:40:48 AMT 27/11/1914 7:40:48 AMurre4027/11/1914 7:40:48 AM331for11at fro11 tblAge4027/11/1914 7:40:48 AM331 57ere age4027/11/1914 7:40:48 AM331I27 =

and the result SHOULD be

SG$5,445.32

 

[RuralGuy]

DLookup() will retrieve one value from a table and you can provide a WhereCondition. Here's a sample usage link: http://www.mvps.org/access/general/gen0018.htm

 

[flect]

ok, well DLookup still returns the string

"SG$"#,##0.00

but my VBA is still using the query as the format, not the result.

Any ideas?

i'm thinking I need to dim the result as another parameter, if so, how do i refer to that result?

 

[RuralGuy]

I'm thinking you need to enclose the string with Chr(34) when you set the .Format property.

 

[flect]

Ok, i'm still having problems.

It's still setting the format to the string "SELECT currencyformat FROM tblagency WHERE agencyID = 6" ; NOT the result of it, which should be " "SG$"#,##0.00"

Private Sub report_open(cancel As integer)
Dim strsql As String
strsql = "SELECT currencyformat FROM tblagency WHERE agencyID = 6;"

StockValA.Format = strsql
End Sub

So, i think i'm missing a crucial step - I need to RUN that qry string, store/save the result and make StockValA.Format refer to that result.

Where do I start?

 

[RuralGuy]

What happened to using a DLookup()?

 

[flect]

Ummm.... what happened is that I was rather presumptuous -that since DLookup() would return the same string as the SQL that it would not work....

I was very very very very wrong.

A thousand pardons sir, It's now working perfectly - and I have learned a great deal in the process.

 

[RuralGuy]

Outstanding! Thanks for posting back with your success.

 

[flect]

Cheers again!

for the record, the final working code was:

Private Sub report_open(cancel as integer)
Dim Cformat As String
Cformat = DLookup("currencyformat", "tblagency", "agencyID = [forms]![frmnavigation]![masterfilter]")

StockValA.Format = Cformat
StockValB.Format = Cformat
VALTOT.Format = Cformat
txtcommission.format = Cformat

End Sub

 

[RuralGuy]

Thanks for the update.