Can a variable hold 2 values simultaneously - a string value and a numeric?

[MLH]

120 MyString = "How many copies of each letter do you need?"
150 MyVariant = InputBox(MyString, "How Many?", "3")
If MyVariant = "2" Then MsgBox "MyVariant equals the string '2'"
If MyVariant = 2 Then MsgBox "MyVariant also equals the value 2"
160 If MyVariant = "" Then HowManyCopies = 1
170 If Not IsNumeric(MyVariant) Then HowManyCopies = 1
MsgBox "OK. HowManyCopies has a value of " & CStr(HowManyCopies)
180 For i = 1 To HowManyCopies
190 DoCmd.OpenReport "rptITSnotices"
200 Next i

OK, what I'm trying to accomplish with the above code is to
have the user input a NUMBER indicating how many copies
of a report he wants. AND, I am trying to determine whether
the user entered gibberish (IE, "q1w2e3r4t5") instead of a
number. AND I'm trying to determine whether the user entered
nothing at all. So, I've dim'd MyString as string and MyVariant as
variant.

So, when I run it and I enter the number 2 at the InputBox prompt,
MyVariant equals BOTH the string "2" and the value 2 when I check
it immediately afterwards. That's very confusing for a single variable
to have 2 values - one numeric and the other a string.

[Thelma Lubkin]

MLH <CR**@NorthState.net> wrote:
: 120 MyString = "How many copies of each letter do you need?"
: 150 MyVariant = InputBox(MyString, "How Many?", "3")
: If MyVariant = "2" Then MsgBox "MyVariant equals the string '2'"
: If MyVariant = 2 Then MsgBox "MyVariant also equals the value 2"
: 160 If MyVariant = "" Then HowManyCopies = 1
: 170 If Not IsNumeric(MyVariant) Then HowManyCopies = 1
: MsgBox "OK. HowManyCopies has a value of " & CStr(HowManyCopies)
: 180 For i = 1 To HowManyCopies
: 190 DoCmd.OpenReport "rptITSnotices"
: 200 Next i

: OK, what I'm trying to accomplish with the above code is to
: have the user input a NUMBER indicating how many copies
: of a report he wants. AND, I am trying to determine whether
: the user entered gibberish (IE, "q1w2e3r4t5") instead of a
: number. AND I'm trying to determine whether the user entered
: nothing at all. So, I've dim'd MyString as string and MyVariant as
: variant.

: So, when I run it and I enter the number 2 at the InputBox prompt,
: MyVariant equals BOTH the string "2" and the value 2 when I check
: it immediately afterwards. That's very confusing for a single variable
: to have 2 values - one numeric and the other a string.

I have *an opinion* as to why this is, but I will let the experts
tell you the actual situation.

But this input-check seems very incomplete to me: it doesn't check
for negative numbers, nor for large numbers -- surely you don't
mean to allow a print run in the billions. And this is a personal
preference: I would force reinput for an entry like q1w2e3r4t5,
and if for some reason that's not an option, I'd set HowManyCopies to
the original default value, 3.

--thelma

[Steve]

I'm no expert myself but it seems to me that your variant is equating
to the string "2" and the integer 2 because it is a variant. Variants
have to explicit data type (and therefore take up a lot of overhead),
and I would think that Access simply stores the value you assign it (in
this case, 2) and tries to accurately compare it to any data type you
test the value with. For example, if this is correct, Access will try
to cast the variant to a Currency value if you try If
myVariant=myCurrencyValue .

What I would do is get rid of the variant altogether, unless there is
another reason it's there, and stick with the strongly-typed String
variable. After the user inputs the number, do your checks and perhaps
even cast it to an integer. I agree with Thelma that you need to do a
lot more input validation before using the value. One thing you could
do to lower the amount of code you need is to check the length of the
input first. For example, if the user enters more than three
characters, you know it's either a mistake or they're just fooling
around. Then you could check legitimate 3-digit values for negatives,
IsNumeric, etc., as Thelma noted.

[Terry Kreft]

As others have said a variant holds data of any type also VBA will try to
cast the data to the type you want.

So in your example the MyVariant variable can be considered to be equal to
both "2" and 2.

If you are trying to force a number from the user you might look at

Dim intInput as Integer

intInput = Val(InputBox(MyString, "How Many?", "3"))

'intInput now contains a number you can then test for special cases e.g.

Select case intInput
case 0
MsgBox "You entered 0 cancelling the operation"
case > 10
MsgBox "You entered more than 10 cancelling the operation"
case else
For i = 1 To HowManyCopies
DoCmd.OpenReport "rptITSnotices"
Next i
end select

--
Terry Kreft

"MLH" <CR**@NorthState.net> wrote in message
news:hr********************************@4ax.com...

120 MyString = "How many copies of each letter do you need?"
150 MyVariant = InputBox(MyString, "How Many?", "3")
If MyVariant = "2" Then MsgBox "MyVariant equals the string '2'"
If MyVariant = 2 Then MsgBox "MyVariant also equals the value 2"
160 If MyVariant = "" Then HowManyCopies = 1
170 If Not IsNumeric(MyVariant) Then HowManyCopies = 1
MsgBox "OK. HowManyCopies has a value of " & CStr(HowManyCopies)
180 For i = 1 To HowManyCopies
190 DoCmd.OpenReport "rptITSnotices"
200 Next i

OK, what I'm trying to accomplish with the above code is to
have the user input a NUMBER indicating how many copies
of a report he wants. AND, I am trying to determine whether
the user entered gibberish (IE, "q1w2e3r4t5") instead of a
number. AND I'm trying to determine whether the user entered
nothing at all. So, I've dim'd MyString as string and MyVariant as
variant.

So, when I run it and I enter the number 2 at the InputBox prompt,
MyVariant equals BOTH the string "2" and the value 2 when I check
it immediately afterwards. That's very confusing for a single variable
to have 2 values - one numeric and the other a string.

[Randy Harris]

InputBox will always return a string. When you execute "If MyVariant = 2"
Access converts the string to a number to do the comparison. As others have
posted, that is not an effective way to determine if the entered data has a
numeric value.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
"MLH" <CR**@NorthState.net> wrote in message
news:hr********************************@4ax.com...

120 MyString = "How many copies of each letter do you need?"
150 MyVariant = InputBox(MyString, "How Many?", "3")
If MyVariant = "2" Then MsgBox "MyVariant equals the string '2'"
If MyVariant = 2 Then MsgBox "MyVariant also equals the value 2"
160 If MyVariant = "" Then HowManyCopies = 1
170 If Not IsNumeric(MyVariant) Then HowManyCopies = 1
MsgBox "OK. HowManyCopies has a value of " & CStr(HowManyCopies)
180 For i = 1 To HowManyCopies
190 DoCmd.OpenReport "rptITSnotices"
200 Next i

OK, what I'm trying to accomplish with the above code is to
have the user input a NUMBER indicating how many copies
of a report he wants. AND, I am trying to determine whether
the user entered gibberish (IE, "q1w2e3r4t5") instead of a
number. AND I'm trying to determine whether the user entered
nothing at all. So, I've dim'd MyString as string and MyVariant as
variant.

So, when I run it and I enter the number 2 at the InputBox prompt,
MyVariant equals BOTH the string "2" and the value 2 when I check
it immediately afterwards. That's very confusing for a single variable
to have 2 values - one numeric and the other a string.

[David W. Fenton]

MLH <CR**@NorthState.net> wrote in
news:hr********************************@4ax.com:

So, when I run it and I enter the number 2 at the InputBox prompt,
MyVariant equals BOTH the string "2" and the value 2 when I check
it immediately afterwards. That's very confusing for a single
variable to have 2 values - one numeric and the other a string.

Well, first off, don't use a variant for your input type. If you use
an integer, you can then create an error handler that will return a
useful message for anything that can't be stored in an integer. The
principle here is to *never* use a data type that can hold values
other than the type that you really need.

And why is 2 = 2 and 2 = "2"? Because VBA does a lot of implicit
type coercion. When you ask:

?2="2"

and get back TRUE, what VBA is telling you is that it *can* be true,
i.e., that the value you are comparing can be coerced to a data type
that makes the statement true.

That's not a terribly useful piece of information when you really
want to know that the value really *is* a number. That's why it's
important to never rely on implicit type coercion.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Jana]

MLH:

I may be way off base, but maybe it would be easier to use a form with
either a text box format to an integer, or maybe even a combo box with
the values 1 through 10 that would GUARANTEE that you get proper
input...

Just a thought,
Jana

[Dean]

I am MOS certified. Personally I would use a form. In fact, you may
want to reference a table so you can store the value the user used.
Then next time he calls the form, the previous form value will be the
default. Create a table that has only one record, then set your form
not to allow additional records. In turn, you can set validation on
the form if you have a print button.

Also on the form a text box called msg but do not reference a field in
the table.

If Me.count>10 then Me.msg = "Cannot print more than 10 copies"
IF Me.count<1 then me.msg = "Not able to print zero copies"
else Me.Msg = "Printing " & Me.Count &" copies"

Something like that.

[MLH]

I have *an opinion* as to why this is, but I will let the experts
tell you the actual situation.

But this input-check seems very incomplete to me: it doesn't check
for negative numbers, nor for large numbers -- surely you don't
mean to allow a print run in the billions. And this is a personal
preference: I would force reinput for an entry like q1w2e3r4t5,
and if for some reason that's not an option, I'd set HowManyCopies to
the original default value, 3.

--thelma

Yeah, you're right thelma. Its not complete. Dim'd number of copies
as byte - limits copies somewhat.

[Lyle Fairfield]

MLH wrote:

Yeah, you're right thelma. Its not complete.

It's not complete?

[David W. Fenton]

"Jana" <Ba********@gmail.com> wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:

MLH:

I may be way off base, but maybe it would be easier to use a form
with either a text box format to an integer, or maybe even a combo
box with the values 1 through 10 that would GUARANTEE that you get
proper input...

Well, since combo boxes always return strings, you'd have exactly
the same type coercion issues.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Jana]

David:

If you do a combo box where you type in the values, and only type
numbers, then change the format property to a number, it stores as a
number, not a string.

:)

Jana

[Lyle Fairfield]

Stores what where?

[Jana]

Lyle:

David was saying that a combo box on a form always stores its value as
a string. My point was that an unbound combo box with only numbers as
"the values" and with the format property set to a number would indeed
treat the user's selection as a number, not text. For that matter, any
combo box that is a lookup to another table with the bound column being
a number would return a number. Case in point, combo box that looks up
EmpID and EmpName, with bound column as 1 (EmpID) would display the
employee's name, but store the EmpID. (or am I losing my mind?)

<g>

Jana

[Lyle Fairfield]

I think David said that a combo box returns a string. I assumed he was
talking about the column(i).Value of a combo box. TTBOMK this is always
string, as he asserted.

You are talking about something different than what I was thinking
about. Is the value as it is read a string? My guess is Yes. Is it
coerced to a number? My guess is Yes. Do we see any of this? My guess
is No. Does this make what you said correct for all extents and
purposes? My guess is Yes.

[David W. Fenton]

"Jana" <Ba********@gmail.com> wrote in
news:11**********************@g47g2000cwa.googlegr oups.com:

If you do a combo box where you type in the values, and only type
numbers, then change the format property to a number, it stores as
a number, not a string.

Only if it's bound to a field of numeric type. It's implicitly
coerced.

And my point is that avoiding implicit coercion is a good thing.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"Jana" <Ba********@gmail.com> wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:

David was saying that a combo box on a form always stores its
value as a string. . . .
I said NO SUCH THING:

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in
news:Xn**********************************@127.0.0. 1:
Well, since combo boxes always return strings, you'd have exactly
the same type coercion issues.
I said they RETURN strings. What gets stored depends on where that
value is going. If the combo box is bound to a numeric field, the
string is implicitly coerced to the appropriate type. And that's the
central problem of this thread, avoid the side effects of implicit
type coercion. You recommended as a solution to the implicit
coercion problem the use of a control that depends on implicit
coercion to work.
. . . My point was that an unbound combo box with only numbers as
"the values" and with the format property set to a number would
indeed treat the user's selection as a number, not text. . . .
No, it would work exactly the same as MLH's original quandary, where
"2" stored in a variant was both a number and a string (because of
implicit type coercion).
. . . For that matter, any
combo box that is a lookup to another table with the bound column
being a number would return a number. . . .
They all return strings. This is why combo boxes that return dates
are a problem.
. . . Case in point, combo box that looks up
EmpID and EmpName, with bound column as 1 (EmpID) would display
the employee's name, but store the EmpID. (or am I losing my
mind?)
Aha, just checking this, I discoverd that I've mis-spoken above. If
you check the value for the bound column, it returns the appropriate
type. If check it by column number, it returns a string, no matter
the type of the source data. It's hard to tell what happens with
dates, though, as there are always strange formatting things going
on with implicit type coercion.

So, my original statement was incorrect as it stood -- so far as I
can tell, it applies only to values returned by specifying the
column.

Now, I was about to say that your solution would avoid the type
coercion problem, but it depends on setting the format to General
Number for it to work, as just typing a list without the format
returns a string.

However, this has problematic side effects.

If you have a two-column list, such as -1;Yes;0;No, and you hide the
first column but it is bound, if you set the format to General
Number, the column right aligns (like a number), which looks wrong
for Yes/No values. You then have to right align it.

I guess it's a workable solution, but it has an awful lot of steps
in it that are required to make it work. And it's not exactly what
you said in your original post:

"Jana" <Ba********@gmail.com> wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:
I may be way off base, but maybe it would be easier to use a form
with either a text box format to an integer, or maybe even a combo
box with the values 1 through 10 that would GUARANTEE that you get
proper input...

I don't know how to format something as an "integer", only as
numeric *display* format. That apparently works, but it still seems
to me as though it's a matter of a display format being recognized
by the type coercion functions, seems to me. There still seems to me
to be a degree of action on your values over which you have no
control.

The original solution used InputBox(), which doesn't require the
creation of a form. That function returns a string (a ZLS if no
answer is given). What MLH needed was better data typing in his
results. He was storing the result in a variant, and there is simply
no justification for that, as InputBox() cannot return anything but
strings.

So, everything that's returned has to be coerced if the desired
result is not a string.

He could do two different things with the InputBox() solution:

1. store in the string, then explicitly coerce it to the correct
data type and handle errors accordingly.

2. store the return value in an integer variable and handle the
error if the value is invalid (and can't be stored in an integer
variable).

My rule about explicit type coercion should lead me to choose #1,
but, in fact, I'd be more likely to use #2, since there, the
implicit coercion works exactly the way I want it to, with no
unpredictable side effects.

Now, the downside of InputBox() is that it allows people to input
invalid data, and the only way to find that out is *after* the
InputBox() has been removed from the screen, and then you have to
pop it up again to get a corrected value (this is generally done
with a WHILE ... TRUE loop). That's not very good UI, in my opinion,
but it's very *easy*.

So, for the best UI, you'd obviously want to code a dialog form,
anyway, but a combo box does not seem to me to be the best control
for the context of choosing the number of copies to print -- I'd do
a spinner control, instead. This guarantees no coercion issues, and
limits the values to incrementable values. Of course, I would never
make a spinner locked -- you always want the user to be able to type
in a number, so you'd have to trap for an accidentally typed letter,
but that's most easily done in the OnChange event and just throw
away anything but digits.

Interesting discussion, actually, as it shows the importance of two
major topics:

1. correct variable typing.

2. correct control choices.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Jana]

David:

Am enjoying the discussion immensely! I'm sure the OP had no idea he'd
(she'd) get such a response...

As a general rule of thumb, I don't like using input boxes for exactly
the reasons you cited. I much prefer to have a dialog form, where I
can have more control over their input, as well as check everything
they've entered before I go on to the next step. (Does that make me a
control freak?)

Pardon on the mention of formatting a text box as an integer, I should
have said that he could format it as a general number, and set the
decimal places to 0. Upon further investigation, though, I discovered
that it still doesn't stop them from putting in fractional numbers. My
suggestion on the combo box (although I didn't really clearly state it)
was intended to be a 1 column combo box, set to a number, thereby
eliminating any of the coersion issues previously mentioned. Users
could select the most likely (I would assume 1-10) number of copies to
print, or they could type in their own number. Setting the format to a
general number will again force a number. Plus, the visual cue of some
typical values (IMHO) helps to ensure that the user knows exactly what
you're looking for from them.

I'd do a spinner control, instead. This guarantees no coercion issues, and
limits the values to incrementable values. Of course, I would never
make a spinner locked -- you always want the user to be able to type
in a number, so you'd have to trap for an accidentally typed letter,
but that's most easily done in the OnChange event and just throw
away anything but digits.

I am unfamiliar with a spinner control, so am unable to spar with you
on that one :) I am still using Access 97...is that an available
control in 97?

This discussion really brings to light how difficult it can be to
ensure that users are giving the types of responses you're looking for!
Trying to anticipate what crazy stuff a user might do and programming
to handle it is one of the major issues faced when creating an app.

Looking forward to future discussions,
Jana

[Dean]

Umm.
Dim x as variant
x=val(Me.boxField)
If x <1 then Me.msg = "Nothing to do."
If x >10 then Me.msg = "To Many !!"
See my previose post. I haven't tested this, but I am sure this (or
like this) would work fine. I can fully test the code if needed.

[Dean]

Yep, I was right, this works great:

Private Sub Button_Click()
Dim x As Variant
x = Val(Nz(Me.InputCopies, 0))

If x < 1 Then Me.msg = "Nothing to do."
If x > 10 Then Me.msg = "I will not try to print all these."
If x > 1 And x < 11 Then Call PrintSomething

End Sub

Sub PrintSomething()
Me.msg = "Printing your copies."
'your print function

End Sub

[David W. Fenton]

"Jana" <Ba********@gmail.com> wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:

Am enjoying the discussion immensely! I'm sure the OP had no idea
he'd (she'd) get such a response...
If all we did was answer the questions that are asked, the newsgroup
would not be all that interesting. Kind of like asking the Vermonter
for directions "Do you know how to get to Montpelier from here?"
Reply: "Yup."
As a general rule of thumb, I don't like using input boxes for
exactly the reasons you cited. I much prefer to have a dialog
form, where I can have more control over their input, as well as
check everything they've entered before I go on to the next step.
(Does that make me a control freak?)
No, that actually makes you a good database programmer.
Pardon on the mention of formatting a text box as an integer, I
should have said that he could format it as a general number, and
set the decimal places to 0. Upon further investigation, though,
I discovered that it still doesn't stop them from putting in
fractional numbers. My suggestion on the combo box (although I
didn't really clearly state it) was intended to be a 1 column
combo box, set to a number, thereby eliminating any of the
coersion issues previously mentioned. Users could select the most
likely (I would assume 1-10) number of copies to print, or they
could type in their own number. Setting the format to a general
number will again force a number. Plus, the visual cue of some
typical values (IMHO) helps to ensure that the user knows exactly
what you're looking for from them.
Once I realized that it was only .Column(n) that returned strings, I
recognized that your suggestion was and OK alternative.

I'd do a spinner control, instead. This guarantees no coercion
issues, and limits the values to incrementable values. Of course,
I would never make a spinner locked -- you always want the user to
be able to type in a number, so you'd have to trap for an
accidentally typed letter, but that's most easily done in the
OnChange event and just throw away anything but digits.

I am unfamiliar with a spinner control, so am unable to spar with
you on that one :) I am still using Access 97...is that an
available control in 97?

You have to fake it in Access, with a textbox and two very small
command buttons that I usually have display a triangle graphic.
This discussion really brings to light how difficult it can be to
ensure that users are giving the types of responses you're looking
for!
Trying to anticipate what crazy stuff a user might do and
programming
to handle it is one of the major issues faced when creating an
app.

Well, the combo box suggestion was a good one, in that you were
thinking in terms of choosing a control type that automatically
allows you to control the type of data the user can enter. I always
think it's better to prevent a user from entering something
erroneous than it is to write code to recover from the error.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/