Getting a #Num! error

homeshark

New member
Local time
Today, 08:28
Joined
Feb 1, 2007
Messages
3
I am getting this #Num! error for a field which displays items like "Holly Kosove" and "Magnolia Complex". For all other items such as "Argos Complex" and "Andros Complex" it displays the name fine.
Here is the code in its entirety:

=IIf(IsNumeric(Left([Mail code or student box number],3)),IIf(CInt(Left([Mail code or student box number],1))>=1 And CInt(Left([Mail code or student box number],5))<=999,"ARGOS COMPLEX ",IIf(CInt(Left([Mail code or student box number],4))>=1000 And CInt(Left([Mail code or student box number],4))<=1999,"ANDROS COMPLEX ",IIf(CInt(Left([Mail code or student box number],5))>=30101 And CInt(Left([Mail code or student box number],5))<=31194,"USF HOLLY DR ",IIf(CInt(Left([Mail code or student box number],4))>=4000 And CInt(Left([Mail code or student box number],4))<=4399,"GREEK VILLAGE ",IIf(CInt(Left([Mail code or student box number],5))>=55000 And CInt(Left([Mail code or student box number],5))<=56399,"HOLLY KOSOVE ",IIf(CInt(Left([Mail code or student box number],4))>=7000 And CInt(Left([Mail code or student box number],4))<=7999,"CYPRESS MAPLE ",IIf(CInt(Left([Mail code or student box number],5))>=80000 And CInt(Left([Mail code or student box number],5))<=84999,"MAGNOLIA COMPLEX ",[Mail code or student box number]))))))))


This is the same code but more friendly to the eyes

=IIf(IsNumeric(Left([Mail code or student box number],3)),

IIf(CInt(Left([Mail code or student box number],1))>=1 And CInt(Left([Mail code or student box number],5))<=999,"ARGOS COMPLEX ",

IIf(CInt(Left([Mail code or student box number],4))>=1000 And CInt(Left([Mail code or student box number],4))<=1999,"ANDROS COMPLEX ",

IIf(CInt(Left([Mail code or student box number],5))>=30101 And CInt(Left([Mail code or student box number],5))<=31194,"USF HOLLY DR ",

IIf(CInt(Left([Mail code or student box number],4))>=4000 And CInt(Left([Mail code or student box number],4))<=4399,"GREEK VILLAGE ",

IIf(CInt(Left([Mail code or student box number],5))>=55000 And CInt(Left([Mail code or student box number],5))<=56399,"HOLLY KOSOVE ",

IIf(CInt(Left([Mail code or student box number],4))>=7000 And CInt(Left([Mail code or student box number],4))<=7999,"CYPRESS MAPLE ",

IIf(CInt(Left([Mail code or student box number],5))>=80000 And CInt(Left([Mail code or student box number],5))<=84999,"MAGNOLIA COMPLEX ",[Mail code or student box number]))))))))

This data is pulled from a query but I am not sure if the query would have anything to do with it. The data for this report, the mailboxes that is, is numeric.

Thank you so much for your time,
Chase
 
One thought is that your numbers are exceeding the limits of an integer type.
 
Well the data itself is categorized per say. Like 5 entries that fit the "Holly Kosove" go under the category "Holly Kosove" but instead of displaying the category name "Holly Kosove" it says #Num!

So it looks like (example)
Name: #Num!

80000 Data Data Data
81254 Data Data Data
84000 Data Data Data
 
What I'm saying is that CInt(55555) will produce an error, since an integer type can't be that large. Try changing all of them to CLng.
 

Users who are viewing this thread

Back
Top Bottom