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

problem with Nz on microsoft access 2013 vba


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
  • 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


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)
   Msgbox "You didn't enter a number"
End If

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 :)
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
I'm simply glad to help and appreciate the thanks.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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