Change Currency amount in control

[Aweiher]

I searched the old threads and couldn't find an answer so..



How do I use VBA to change the value of a currency control in my form?

I use the this code to change number controls. But for a currency control it would put "100" in the control instead of "$100.00".

Me.Control.Value = "100"

[ranman256]

Change the text box property to fixed, instead of currency.

[Aweiher]

Okay that didn't really solve my problem, but I found a solution. What I have a search form based on a query that has the forms controls as criteria, which include min a max currency controls to filter. But if the user leaves a currency blank then I want the form to use a min of $0.00 and a max of $10,000,000.00 so I have VBA enter in the values if the user left those null. However the VBA will put 0 instead of $0.00. And your recommendation does 0.00 instead, which doesn't really help.

However I found a solution and I thought I would post it for anyone else who might need it. I left the controls as currency, then had vba tell the control to equal "0" (or whatever I want), then in the next line of code would be to add 1, then another line of code to subtract 1. This would then trigger the form to accept those values as currencies. Here is an example of my code.

     If IsNull(Me.[txtInvoiceAmountGreater].Value) Then
     [txtInvoiceAmountGreater] = "0.00"
     End If
     If IsNull(Me.[txtInvoiceAmountLessThan].Value) Then
     [txtInvoiceAmountLessThan] = "10000000.00"
     End If
          
     If IsNull(Me.[txtAmountDueGreater].Value) Then
     [txtAmountDueGreater] = "0.00"
     End If
     If IsNull(Me.[txtAmountDueLessThan].Value) Then
     [txtAmountDueLessThan] = "10000000.00"
     End If
     
    'This makes the form register the values as currancy instead of numbers
    [txtInvoiceAmountGreater] = [txtInvoiceAmountGreater] + 1
    [txtInvoiceAmountLessThan] = [txtInvoiceAmountLessThan] + 1
    [txtAmountDueGreater] = [txtAmountDueGreater] + 1
    [txtAmountDueLessThan] = [txtAmountDueLessThan] + 1
    [txtInvoiceAmountGreater] = [txtInvoiceAmountGreater] - 1
    [txtInvoiceAmountLessThan] = [txtInvoiceAmountLessThan] - 1
    [txtAmountDueGreater] = [txtAmountDueGreater] - 1
    [txtAmountDueLessThan] = [txtAmountDueLessThan] - 1