How To Format String For Currency Format?

[CharlesWhiteman]

I have a txt box on my form set to currency format but the DLookup string seems to verride this. Does anyone know how ui can modify the string to be in currency format?

=DLookUp("Nz([Sumofamount received],0)","QryFinanceSumOfReceipts","CompanyCode = forms!frmDatabase!companycode")

 

[pbaldy]

Wrap the DLookup() in a Format() function.

 

[CharlesWhiteman]

Thanks, am not sure how to write it but will investigate

 

[CharlesWhiteman]

Where am I going wrong?

=DLookUp(Format(("Nz([Sumofamount received],0)","QryFinanceSumOfReceipts","CompanyCode = forms!frmDatabase!companycode"),"£#,##0.00"))

 

[DCrake]

=DLookUp(Format(("Nz([Sumofamount received],0)","QryFinanceSumOfReceipts","CompanyCode = '" & forms!frmDatabase!companycode & "'"),"Currency"))

If the CompanyCode is numeric drop the quotation marks from the string

 

[CharlesWhiteman]

This is the syntax I've used but i keep getting an operator without an operand error and yes the companycode is a numeric field:

DLookUp(Format(("Nz([Sumofamount received],0)","QryFinanceSumOfReceipts","CompanyCode = " & forms!frmDatabase!companycode & "),"Currency"))

 

[pbaldy]

I would wrap the DLookup inside:

Format(DLookup(...), "Currency")

 

[CharlesWhiteman]

Yes! That worked great! Here's the syntax in case anyone has a similar question:

=Format(DLookUp("Nz([Sumofamount received],0)","QryFinanceSumOfReceipts","CompanyCode = forms!frmDatabase!companycode"),"Currency")