mis-matched data types

[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.

 

[Douglas J. Steele]

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)

 

[AJ]

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

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)

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.

 

[BruceM]

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]

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

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)

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.

 

[Douglas J. Steele]

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

 

[AJ]

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

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!)

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

 

[Douglas J. Steele]

What error did you get?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

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!)

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