Substitute word for number in textbox

[Stang02GT]

I have a text box that displays a projects status, but the status is
displayed as a number and the number means something like " Completed" or "In
Development".

Is there a way that i can have some VB code look and see that if there is an
8, then display "Completed" or if there is a 10 it display "In development"?



Visual Basic is going to be the end of me lol

[Killer42]

I have a text box ... Is there a way that i can have some VB code look and see that if there is an 8, then display "Completed" or if there is a 10 it display "In development"?

Substituting a string for a number is in itself quite simple, of course. But can you give us a bit more detail? For instance, what version of VB are you using? Is the textbox bound to anything? Is the value of the textbox of any significance to the program, or just a display for the user? Is it modifiable by the user, or locked?

[Stang02GT]

Substituting a string for a number is in itself quite simple, of course. But can you give us a bit more detail? For instance, what version of VB are you using? Is the textbox bound to anything? Is the value of the textbox of any significance to the program, or just a display for the user? Is it modifiable by the user, or locked?

Yes not a problem at all

I am running VB 6.3.

The text box is bound to an ID field in a table( which is made by a make table query that pulls all information from a linked DB). The data in the table is either one of two statuses, Ready For Development or In Development. But my problem is that these statuses are identified by a number.

8= Ready for Dev
10= In dev

On my form i have a status box and it is only showing a number id like it to show Ready for Dev. if there is an 8 in the box and In Dev if there is a 10 in the box.

[Taftheman]

Yes not a problem at all

I am running VB 6.3.

The text box is bound to an ID field in a table( which is made by a make table query that pulls all information from a linked DB). The data in the table is either one of two statuses, Ready For Development or In Development. But my problem is that these statuses are identified by a number.

8= Ready for Dev
10= In dev

On my form i have a status box and it is only showing a number id like it to show Ready for Dev. if there is an 8 in the box and In Dev if there is a 10 in the box.

in your query add a case statement, this will work in sql server as SQL so i think it should work for access if you add it in the sql query.

wher eyou have stated the selecting the Id in the query swap it with the following
"Select Case When Id = 8 Then 'Ready for Dev' When id = 10 Then 'In Dev' End"
then add the rest or ur select statement

[Taftheman]

in your query add a case statement, this will work in sql server as SQL so i think it should work for access if you add it in the sql query.

wher eyou have stated the selecting the Id in the query swap it with the following
"Select Case When Id = 8 Then 'Ready for Dev' When id = 10 Then 'In Dev' End"
then add the rest or ur select statement

Or ultimatly
just case your result.

Select Case (yourResult: should be a number in your case)
Case is 10
textbox.text1.text = 'In Dev'
Case is 8
textbox.text = 'Ready for Dev'
End select

in this case you do not want to assign the textbox to anything
have a think about what you are trying to do if you still can't solve it,
because there are many way such making the current textbox.visable = false and use this value in a case statement and populate a textbox that is visable

[Stang02GT]

in your query add a case statement, this will work in sql server as SQL so i think it should work for access if you add it in the sql query.

wher eyou have stated the selecting the Id in the query swap it with the following
"Select Case When Id = 8 Then 'Ready for Dev' When id = 10 Then 'In Dev' End"
then add the rest or ur select statement

Unfortunately this form is running off of a table not a query.

I created a query using Design view and then switched over to the SQL view, here is the code it gives me....

SELECT TRKALLIMSPROJECTS_TRKSCRSL.X5, TRKALLIMSPROJECTS_TRKSCRSL.X14, TRKALLIMSPROJECTS_TRKSCRST.Z1, TRKALLIMSPROJECTS_TRKSCRST.Z2, TRKALLIMSPROJECTS_TRKSCRST.Z3, TRKALLIMSPROJECTS_TRKSCRST.Z6
FROM TRKALLIMSPROJECTS_TRKSCRSL INNER JOIN TRKALLIMSPROJECTS_TRKSCRST ON TRKALLIMSPROJECTS_TRKSCRSL.ID = TRKALLIMSPROJECTS_TRKSCRST.ID;
 

X14 is the field with the status ID, where should i place your code?

I tried it on my own but couldn't get it to work

[Killer42]

I've just used the query builder in Access to put together simple examples of using the IIF() and Choose() functions to decode a field. I just created a simple table with three fields called a (Text), b (Text) and c (Number). Here's the sample query...

SELECT Table1.a, Table1.b, 
  IIf([c]=8,"Ready for Dev","In Development") AS Converted,
  Choose([c],"","","","","","","","Ready for Dev","","In Development") AS Status
  FROM Table1;

Here are the restults when I fed in some numbers...

c    Converted          Status        
1    In Development    
4    In Development    
8    Ready for Dev      Ready for Dev
5    In Development    
9    In Development    
10    In Development     In Development
4    In Development    
8    Ready for Dev      Ready for Dev
2    In Development    

[Stang02GT]

I'm sorry guys but i can't get any of these to work....what about using the expression builder on the text box?

[Killer42]

I'm sorry guys but i can't get any of these to work....what about using the expression builder on the text box?

I still think the Choose() function should do the trick.

[Stang02GT]

I still think the Choose() function should do the trick.

Ok where im having trouble is incorporating that into my statement in post #6

[Stang02GT]

I'm still at a stand still on this....i walked away form the whole project this weekend just to clear my head and get away from it lol....

[Killer42]

I'm still at a stand still on this....i walked away form the whole project this weekend just to clear my head and get away from it lol....

Sorry, I'll have to get back to this thread at lunch time (a few hours from now).

[Killer42]

Ok, for starters I have to say, I'm not too impressed with your field names. :( (The table names a far too similar, as well - very difficult to work with, in my opinion.)

I've chopped up the SQL a bit so I could follow it, but here's what I've come up with...

SELECT TRKALLIMSPROJECTS_TRKSCRSL.X5,
       TRKALLIMSPROJECTS_TRKSCRSL.X14,
       TRKALLIMSPROJECTS_TRKSCRST.Z1,
       TRKALLIMSPROJECTS_TRKSCRST.Z2,
       TRKALLIMSPROJECTS_TRKSCRST.Z3,
       TRKALLIMSPROJECTS_TRKSCRST.Z6,
       Choose([TRKALLIMSPROJECTS_TRKSCRSL.X14], "", "", "", "", "", "", "", "Ready for Dev", "", "In Development") As Status
 
FROM
 
TRKALLIMSPROJECTS_TRKSCRSL
INNER JOIN 
TRKALLIMSPROJECTS_TRKSCRST
 
ON
 
TRKALLIMSPROJECTS_TRKSCRSL.ID = TRKALLIMSPROJECTS_TRKSCRST.ID;

I don't know whether this will work or not - I've just tried to incorporate the Choose() function into your SQL. I'd recommend that for further help along these lines, you go to the Access forum. You will find a lot of SQL experience over there, while I'm kind of teaching myself as I go.

[Stang02GT]

Those are not my field names. This is a linked table from another database, so those field names were not made by me. Trust me i know how hard it is to work with them.

[Stang02GT]

Killer,

The code that you gave me worked great thank you !

[Killer42]

The code that you gave me worked great thank you !

Hooray! A win for the VB forum. :)

I'm actually learning quite a bit about SQL just from attempting to answer these sort of questions.

Anyway, I'm glad we were able to help.

(Keep in mind, that's just one possible solution. It might be a good idea to go paste your current code into the Access forum and ask whether there's a better way.)

[Stang02GT]

Hooray! A win for the VB forum. :)

I'm actually learning quite a bit about SQL just from attempting to answer these sort of questions.

Anyway, I'm glad we were able to help.

(Keep in mind, that's just one possible solution. It might be a good idea to go paste your current code into the Access forum and ask whether there's a better way.)

Well good we are both learning something :) I am slowly but surely learning a great deal from this site thanks to people like you :)

[Killer42]

Well good we are both learning something :) I am slowly but surely learning a great deal from this site thanks to people like you :)

That's the great thing about a forum like this - it's a two-way affair. You also learn while answering questions, not just asking them.