If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
mis-matched data types
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. -- AJ |
#2
|
|||
|
|||
mis-matched data types
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 http://I.Am/DougSteele (no private e-mails, please) "AJ" wrote in message ... 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. -- AJ |
#3
|
|||
|
|||
mis-matched data types
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 http://I.Am/DougSteele (no private e-mails, please) "AJ" wrote in message ... 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. -- AJ |
#4
|
|||
|
|||
mis-matched data types
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" wrote: 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 http://I.Am/DougSteele (no private e-mails, please) "AJ" wrote in message ... 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. -- AJ |
#5
|
|||
|
|||
mis-matched data types
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" wrote: 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 http://I.Am/DougSteele (no private e-mails, please) "AJ" wrote in message ... 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. -- AJ |
#6
|
|||
|
|||
mis-matched data types
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" wrote in message ... 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" wrote: 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 http://I.Am/DougSteele (no private e-mails, please) "AJ" wrote in message ... 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. -- AJ |
#7
|
|||
|
|||
mis-matched data types
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 http://www.accessmonster.com/Uwe/For...forms/200907/1 |
#8
|
|||
|
|||
mis-matched data types
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 http://I.Am/DougSteele (no e-mails, please!) "Linq Adams via AccessMonster.com" u28780@uwe wrote in message news:98c39d09eaf22@uwe... 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 http://www.accessmonster.com/Uwe/For...forms/200907/1 |
#9
|
|||
|
|||
mis-matched data types
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" wrote: 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 http://I.Am/DougSteele (no e-mails, please!) "Linq Adams via AccessMonster.com" u28780@uwe wrote in message news:98c39d09eaf22@uwe... 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 http://www.accessmonster.com/Uwe/For...forms/200907/1 |
#10
|
|||
|
|||
mis-matched data types
What error did you get?
-- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "AJ" wrote in message ... 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" wrote: 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 http://I.Am/DougSteele (no e-mails, please!) "Linq Adams via AccessMonster.com" u28780@uwe wrote in message news:98c39d09eaf22@uwe... 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 http://www.accessmonster.com/Uwe/For...forms/200907/1 |
Thread Tools | |
Display Modes | |
|
|