Solved

problem with Nz on microsoft access 2013 vba

Posted on 2014-02-12
4
898 Views
Last Modified: 2014-02-12
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
Comment
Question by:bill201
  • 3
4 Comments
 
LVL 57
ID: 39854208
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39854231
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
 

Author Closing Comment

by:bill201
ID: 39854326
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
 
LVL 57
ID: 39854345
I'm simply glad to help and appreciate the thanks.

Jim.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question