VBA Wildcards

[dssrun]

I am trying to create a loop for each value that looks like what I have below. However since these are calculated fields a value may be like 1.11E10. However I do not care about that because it is essentially zero to me since these are currency field. The wildcard I am using is not working and the wild card 0# does not work if a number is 1.11E10.

Do While Not rst.EOF
        Select Case True
            Case Not rst("Charges").Value Like 0[.]#
                MsgBox "Out of balance in Charges in " & rst("[Codes]").Value & " by " & rst("Charges").Value
                rst.Close
                Set rst = Nothing
                Set db = Nothing
                Exit Function
            Case Not rst("Payments").Value Like 0[.]#
                MsgBox "Out of balance in Payments in " & rst("[Codes]").Value & " by " & rst("Payments").Value
                rst.Close
                Set rst = Nothing
                Set db = Nothing
                Exit Function
            Case Not rst("Count").Value Like 0[.]#
                MsgBox "Out of balance in Overall in " & rst("[Codes]").Value & " by " & rst("Overall").Value
                rst.Close
                Set rst = Nothing
                Set db = Nothing
                Exit Function
        End Select
    Loop

[orange]

See this for an example of Select Case

http://www.databasedev.co.uk/select-case-statement.html

I have never seen a sample with the type of structure you have.
I don't see any Openrecordset(), nor recordset.Movenext in your code sample.

Perhaps if you showed us more of the code involved it would help.
Did you get an error message?

[dssrun]

I didn't paste my whole code for simplicity purposes. This is at the top of my code. The select case works, what it is doing is moving from field to field that i specify for each record to test the value

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("MyRecordSetHere", dbOpenDynaset

[dssrun]

My apologies when i pasted my code over it did not take the rst.movenext at the end of the loop

Do While Not rst.EOF
        Select Case True
            Case Not rst("Charges").Value Like 0[.]#
                MsgBox "Out of balance in Charges in " & rst("[Codes]").Value & " by " & rst("Charges").Value
                rst.Close
                Set rst = Nothing
                Set db = Nothing
                Exit Function
            Case Not rst("Payments").Value Like 0[.]#
                MsgBox "Out of balance in Payments in " & rst("[Codes]").Value & " by " & rst("Payments").Value
                rst.Close
                Set rst = Nothing
                Set db = Nothing
                Exit Function
            Case Not rst("Count").Value Like 0[.]#
                MsgBox "Out of balance in Overall in " & rst("[Codes]").Value & " by " & rst("Overall").Value
                rst.Close
                Set rst = Nothing
                Set db = Nothing
                Exit Function
        End Select
     rst.movenext
    Loop

[orange]

Did you get an error message?
Did you get any output?

Can you try something like this on just one value to see if it gives a number like you want?
Debug.Print Format(x, "Currency") where x is one of your
rst("Charges").Value

[dssrun]

I am getting no error messages and the values are correct. For example, the first record Code is A. The value of the [Codes].Value = A and the Charges.Value = 0. the Next select line the value of the [Codes].Value = A and the Payments.Value = -1.45519...E-11. So my select case fails and it tells me it is not balanced. However I am balanced since really the value is $0.00. the msgbox prints the correct Code.Value of A and prints the Payments.Value of 0 and not the -1.45519...E-11.

Does that help? sorry if i have confused you at all.

[orange]

I am confused because I haven't seen the structure you are using. It may not be wrong, but I haven't seen it.

I am familiar with

Select Case Nvar
Case IS < 5
Debug.Print "Less than 5"
Case 6, 7, 8
Debug.Print "Between 6 and 8"
Case 8 TO 12
Debug.Print "Greater than 8"
Case Else
Debug.Print "Not between 1 and 12"
End Select

where there is an expression or variable to be evaluated;
various case conditions including the Else condition.

Did you try the line I asked about

Can you try something like this on just one value to see if it gives a number like you want?
Debug.Print Format(x, "Currency") where x is one of your
rst("Charges").Value

I was trying to get an expression that could be used

eg Select Case Format(x, "Currency")

Case IS <1 etc.

[dssrun]

My select case statement is evaluating whether the recordset field name "Charge", "payment" and "Overall" value is 0 for each record. If it is not then the select case runs the Messagebox and exits the function because all these values in each record should equal zero, so i should have a table with about 10 records with all fields values equal to zero (this is just a module to check my work). I will try your line tomorrow and let you know. I do not put a "Case Else" because I do not want a default action I just want it to loop and only act if there is a number greater than or less than 0.00

I have yet to figure my initial problem with making my code read low numbers such as -1.445..E-10 as 0.00

[orange]

I will be travelling tomorrow for 5 days and will not be online.

1. Do you know if your code is actually running?
2. I don't see how True is beiing used as the expression to be evaluated. It could be perfect -- I just haven't seen that structure.
3. If you are dealing with payments why do you do calculations to E-10? Depending on your field types I'm sure you''ll get representations that vary at the 8th decimal place.

Good luck

[ajetrumpet]

I have yet to figure my initial problem with making my code read low numbers such as -1.445..E-10 as 0.00

dss,

have you tried manipulating your numbers with the FORMAT() function?? I mean instead of trying to use wildcard characters (which I'm pretty sure don't work without a function, especially if they're not in double quotes).

How about trying the INT(), ROUND(), and FIX() functions, especially for those nums that are in the negative?? ABS() is also available. There are many options available here that might get you to the answer.

The other thing I was wondering is your data type for the numbers. I believe, if double-data types get large enough (like 20 digits or more), there's no way to stop scientific method from being used by Access. I remember reading that somewhere.

(2 cents inputted)

[dssrun]

aje,

I did try the round and int function but they were not working. I have never heard of the Fix function and guess what, it seemed to do the trick!

I can always count on you for some help. Thanks.

[ajetrumpet]

marked solved.