Runtime Error 2427 help

[mjcaestecker]

Greetings all

I have a bit of code that is returning a 2427 runtime error and Nz() will not fix it. Was wondering if anyone could shed some light...

Here's the setup:
I have a subform that displays query results. At the bottom of the subform (in the footer) is a text box with control source =Sum([Amount]) to sum up all the Amount values displayed. I tried to store that value as a variable and thats where I got the error. Here's the code:

Private Sub TabSetBooks_Change()
    Dim Amount As Currency
    Dim CCfeePct As Double

    CCfeePct = DLookup("Number", "BooksSettings", "Item = 'CreditCardFeePct'")
    *** Amount = Me!BooksFullSubCC.Form!CCchargesTotal.Value ***
    Forms!BooksFull!CCFees = Amount * CCfeePct
End Sub

The error occurs on the line with the Amount variable (starred). The error message says "Run-time error 2427: You entered an expression that has no value"

Any thoughts?

Thanks,

-Martin

 

[jdraw]

I think Number is a reserved word.
Here is a more complete list
http://allenbrowne.com/AppIssueBadWord.html

You could step through the code, or use some debug.print statements to see what values
are in your variables. I'd check to see the value of CCfeePct

 

[mjcaestecker]

Thanks for the reply, jdraw. I checked the value, and having a table column named Number seems to be fine, pulled the value that I wanted correctly.

The error comes when I try and define the value for the variable Amount from a text box on a subform. the text box is a sum. I think that Access doesn't like it when there is nothing to sum up. Instead of returning a null value, it doesnt fill the variable with anything. I checked to see if it was IsEmpty() with an If statement, but that didn't do it either...

 

[jdraw]

Look at the NZ() function and see if it applies to your situation.

 

[vbaInet]

It looks like the OP has already tried Nz() as mentioned in the post, but how exactly was it used? Let's see the code with the Nz() function.

And when you are using reserved keywords you must enclose it in square brackets, even in your DLookup function.

 

[mjcaestecker]

Ok fellas. Thanks again for the reply.

Here's the code

Private Sub TabSetBooks_Change()
'--- Calculate Credit Card Fees ---'
    Dim Amount As Currency
    Dim CCfeePct As Double
    Dim Fees As Currency

    CCfeePct = DLookup("[Number]", "BooksSettings", "Item = 'CreditCardFeePct'")
    Amount = Nz(Me!BooksFullSubCC.Form!CCchargesTotal.Value, 0)
    Fees = Amount * CCfeePct
    Forms!BooksFull!CCFees = Fees
End Sub

Tried to debug it, but when I printed Amount, it didnt even return a 0, it was blank like it was null.then I tried to check for IsNull() and it wasnt... I'm stumped.

 

[vbaInet]

It's not returning Null, it's returning a zero-length string. Use this:

Amount = Val(Nz(Me!BooksFullSubCC.Form!CCchargesTotal.Value, 0))

 

[mjcaestecker]

No, that didnt do it - still returns error 2427...

Is there any way to check with IF THEN statement to see if the statement is 0 length so I can skip the calculation if it is 0 length?

 

[vbaInet]

Then I don't think that's the line the error is coming from or it's returning a space or an unknown character. Let's see a stripped down version of your db.

 

[mjcaestecker]

Ok. It took me a while to strip this all down, but here it is.

This form is supposed to calculate the cost of the credit card fees for payments on train trips for a small railroad. I have included 3 records. Records 1 and 3 are fine, because they have credit card charges listed in the database. The problem occurs when there are no credit card charges associated with the trip. Record 2 has no charges listed - this is when it returns the 2427 error.

Let me know if you need any more information to help you figure this out.

 

[vbaInet]

Which form are you talking about?

 

[mjcaestecker]

BooksFull is the main form. BooksFullSubCC and CashCheckCharges are subforms inside BooksFull

 

[mjcaestecker]

On the Trip Expenses tab of BooksFull, I'm trying to calculate the credit card fees from the total amount of credit card charges in the footer of BooksFullSubCC. The error, I think, is in grabbing the total amount from the footer and trying to send it to the code when there are no entries to query to fill BooksFullSubCC

 

[mjcaestecker]

Hey Everyone, I solved the problem by using a filtered Dsum on the query that fills the form to fill the Amount variable and it worked.

Amount = Nz(DSum("Amount", "MyQuery", "TransactionDetails.ID = " & Me!ID & ""), 0)

Thanks for your help. This was a puzzler...

 

[vbaInet]

I just had a look at your db and see what you're talking about. See attached (without DSum).

Good job on your part too.