formatting a field to be currency in VBA

[Lupson2011]

Hi all,



I am writing some code where I am displaying certain information in a list dependent on a choice from a combo box.

It all works fine, but a couple of the fields are currency format and I wondered if there was anything easy I could add to the line of code below which wold ensure that it is displayed in currency and not just number, or do I have to different lines of code for this?

Me.lstProjectDetails.AddItem "Change Awarded;" & rstqryAssignmentDetails.Fields("AmountAwarded")

thanks

marc

[JoeM]

Maybe something like:

Me.lstProjectDetails.AddItem "Change Awarded;" & Format(rstqryAssignmentDetails.Fields("AmountAward ed"),"$0.00")

Edit: Ignore the space in the word "AmountAward ed". I am not sure why it is doing that. I copied your formula, and when I edit it, it shows no space, but it appears there after posting.

[Lupson2011]

Thanks sorted!

Marc

Maybe something like:

Me.lstProjectDetails.AddItem "Change Awarded;" & Format(rstqryAssignmentDetails.Fields("AmountAward ed"),"$0.00")

Edit: Ignore the space in the word "AmountAward ed". I am not sure why it is doing that. I copied your formula, and when I edit it, it shows no space, but it appears there after posting.

[Lupson2011]

JoeM

Sorry, I know its solved but one more thing. I am trying to get 1000 separator in there as well, but without success. Any help?

[JoeM]

Try this structure:
Format(FieldName,"$0,000.00")

[Lupson2011]

Hi, I tried that

Me.lstProjectDetails.AddItem "Change Awarded;" & Format(rstqryAssignmentDetails.Fields("AmountAwarded"), "£0,000.00")

It came up with just a two number format £67, when it should be £67,000.00

Did I code it right?

Thanks

Marc

Hi all,

I am writing some code where I am displaying certain information in a list dependent on a choice from a combo box.

It all works fine, but a couple of the fields are currency format and I wondered if there was anything easy I could add to the line of code below which wold ensure that it is displayed in currency and not just number, or do I have to different lines of code for this?

Me.lstProjectDetails.AddItem "Change Awarded;" & rstqryAssignmentDetails.Fields("AmountAwarded")

thanks

marc

[JoeM]

Is this value that is stored in your AmountsAwarded field 67 or 67000?
If it is 67, and you want to return 67000 (in whatever format you desire), then multiply by 1000 first before applying the formatting.

[Lupson2011]

hi

It is 67000, but for some reason as soon I put in a comma separator, it changes it from 67000.00 to 67..cant work out why?

Is this value that is stored in your AmountsAwarded field 67 or 67000?
If it is 67, and you want to return 67000 (in whatever format you desire), then multiply by 1000 first before applying the formatting.

[JoeM]

It might have something to do with your Regional Settings. See here: http://support.microsoft.com/kb/142009