• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 953
  • Last Modified:

problem with Nz on microsoft access 2013 vba

hi

i have a code like this

Dim QtyL As integer
QtyL = Nz(InputBox("please enter a number"), 0)

Open in new window


but when i don't add  any number on the inputbox i get an error "type mismatch" do the NZ function dont has to handle it and replace the NULL with a ZERO? so why i get an error message
0
bill201
Asked:
bill201
  • 3
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Type mis-match means your using a string for a numeric, or a numeric for a string.

The input box returns a string and your trying to place it in a variable that's an integer.

What you want is:

Dim QtyL As integer
QtyL = CInt(Nz(InputBox("please enter a number"), 0))

It's also a good idea to prefix variables:

int = Integer
lng = Long
str = string
var = variant

etc.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
BTW, the other thing is that a NULL and a zero-length string are not one in the same thing, so your going to want something like this:

Dim varRet as Variant
Dim intQtyL as Integer

varRet = InputBox("please enter a number")

If NZ(varRet,0)<>"" then
    intQtyL = CInt(varRet)
Else
   Msgbox "You didn't enter a number"
End If

Jim.
0
 
bill201Author Commented:
Many thanks for the reply, I am very grateful to you, I wish there was an option to give more than five hundred points :)
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
I'm simply glad to help and appreciate the thanks.

Jim.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now