mis-matched data types

  • Thread starter Thread starter AJ
  • Start date Start date
A

AJ

On a form I have a combo box that pull out text. The items in this combo box
are 30-XXX or 31-XXX etc. I have a textbox: [text1]=nz([combo1],0).

Then I have a textbox [text2]=iif([text1]=0,0,left([text1],2)*100)

If my combobox is truly empty it works fine and returns 0. If I have the
text in the combo I get #Error in text2.

I think I am trying to mix numbers and text, but have no clue at all how to
correct.

Thanks in advance for any help.
 
If the combo is "truly empty", that means its value is Null. You cannot use
comparison operators such as = with Null values.

Try

[text2]=iif(Nz([text1],0)=0,0,left([text1],2)*100)
 
that works if there is nothing in Text1, but if there is something in text1
i"m still getting the #Error.

Appreciate the help!!!!!
--
AJ


Douglas J. Steele said:
If the combo is "truly empty", that means its value is Null. You cannot use
comparison operators such as = with Null values.

Try

[text2]=iif(Nz([text1],0)=0,0,left([text1],2)*100)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


AJ said:
On a form I have a combo box that pull out text. The items in this combo
box
are 30-XXX or 31-XXX etc. I have a textbox: [text1]=nz([combo1],0).

Then I have a textbox [text2]=iif([text1]=0,0,left([text1],2)*100)

If my combobox is truly empty it works fine and returns 0. If I have the
text in the combo I get #Error in text2.

I think I am trying to mix numbers and text, but have no clue at all how
to
correct.

Thanks in advance for any help.
Click to expand...
Click to expand...
 
Where exactly does the expression appear? What happens if you break it down
into smaller elements such as:
[text2] = Left([text1],2)
or even:
[text2] = [text1]

AJ said:
that works if there is nothing in Text1, but if there is something in
text1
i"m still getting the #Error.

Appreciate the help!!!!!
--
AJ


Douglas J. Steele said:
If the combo is "truly empty", that means its value is Null. You cannot
use
comparison operators such as = with Null values.

Try

[text2]=iif(Nz([text1],0)=0,0,left([text1],2)*100)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


AJ said:
On a form I have a combo box that pull out text. The items in this
combo
box
are 30-XXX or 31-XXX etc. I have a textbox: [text1]=nz([combo1],0).

Then I have a textbox [text2]=iif([text1]=0,0,left([text1],2)*100)

If my combobox is truly empty it works fine and returns 0. If I have
the
text in the combo I get #Error in text2.

I think I am trying to mix numbers and text, but have no clue at all
how
to
correct.

Thanks in advance for any help.
Click to expand...
Click to expand...
Click to expand...
 
Good point, Linq

[text2]=iif(Nz([text1],"0")="0",0,CLng(Left([text1],2))*100)

or, more simply,

[text2]=CLng(Left(Nz([text1], "00"),2))*100
 
Still errors when I did that. Sorry.

I have working, maybe not the best way, but here is what I did.

new text box: [text3] = iif([left([text1],2)="",0,leftt([text1],2))

Then made [text2] = [text3]*[text7] (note I used 100 instead of text7 in my
previous post)

seems to be working so all is good. Note text1 already has the nx function
in it, not sure that matters for anything, but it does.

Thanks again for all the help!
--
AJ


Douglas J. Steele said:
Good point, Linq

[text2]=iif(Nz([text1],"0")="0",0,CLng(Left([text1],2))*100)

or, more simply,

[text2]=CLng(Left(Nz([text1], "00"),2))*100

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Linq Adams via AccessMonster.com said:
SInce the values in the combobox are Text, shouldn't the Nz() syntan be

[text1]=nz([combo1],"")

or

[text1]=nz([combo1],"0")

rather than

[text1]=nz([combo1],0)

which is plugging a Numeric value into a Text fieldif the field is Null?

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
Click to expand...
Click to expand...
 
What error did you get?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


AJ said:
Still errors when I did that. Sorry.

I have working, maybe not the best way, but here is what I did.

new text box: [text3] = iif([left([text1],2)="",0,leftt([text1],2))

Then made [text2] = [text3]*[text7] (note I used 100 instead of text7 in
my
previous post)

seems to be working so all is good. Note text1 already has the nx
function
in it, not sure that matters for anything, but it does.

Thanks again for all the help!
--
AJ


Douglas J. Steele said:
Good point, Linq

[text2]=iif(Nz([text1],"0")="0",0,CLng(Left([text1],2))*100)

or, more simply,

[text2]=CLng(Left(Nz([text1], "00"),2))*100

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Linq Adams via AccessMonster.com said:
SInce the values in the combobox are Text, shouldn't the Nz() syntan be

[text1]=nz([combo1],"")

or

[text1]=nz([combo1],"0")

rather than

[text1]=nz([combo1],0)

which is plugging a Numeric value into a Text fieldif the field is
Null?

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
Click to expand...
Click to expand...
Click to expand...
 
Back
Top