Numeric versus Long Integer

[CLSkcab]

Background
Frontend MS Access 2010 w/ VBA; Backend MS SQL Server 2008
Just learning SQL

My Problem

I have a SQL table with a field named QTY and it is defined as numeric(18,0). It is being read into a field that is defined as DIM gblQTY as Long.

gblQTY = Nz(DLookup("QTY", "dbo_part Shortages", "[key] = '" & gblKey & "'"))

When a table entry that has a 0 value is read I get the following error: runtime error "6" Overflow.

Thanks in advanced.

[twinnyfo]

If your code is accurate, you are not provided the Nz() function to return a value if the result is Null. Try:

gblQTY = Nz(DLookup("QTY", "dbo_part Shortages", _
    "[key] = '" & gblKey & "'"), 0)

Also, a "key" is usually a numeric value. Your code indicates that it is a text field (the single quotes). Are you sure that it is a text value and not a numeric value?