Changing information in a datagrid to show text not numbers. 1

[BSG75]

I am running a select statement in a visual basic program. I pull in the information in an adodc into a data grid. I have 6 columns and in one of the columns the infromation that is being pulled in is in a number format (0,1,2,3,4). They represent a level of urgency 0 = Low and 1 = Medium and so on. I need to changed the information from the number to the text. My statement is this Adodc1.RecordSource = "SELECT HPD_HelpDesk.Submitted_By, DateAdd(s,HPD_HelpDesk.Assign_Time,'12/31/1969 18:00:00 PM') as Ticket_Assigned_Time, HPD_HelpDesk.Case_ID_, HPD_HelpDesk.SubItem, HPD_HelpDesk.Assigned_To_Individual_, HPD_HelpDesk.Priority FROM REMEDY_SUPPORT.dbo.HPD_HelpDesk " & _
"WHERE (HPD_HelpDesk.Assigned_To_Individual_='Leticia Najera' OR HPD_HelpDesk.Assigned_To_Individual_='Loretta G. Villarreal' OR HPD_HelpDesk.Assigned_To_Individual_='Virginia S. Nevarez' OR HPD_HelpDesk.Assigned_To_Individual_='Robert G. Villa' OR HPD_HelpDesk.Assigned_To_Individual_='Calvin D. Drummond') " & _
"AND (HPD_HelpDesk.SubItem <> 'New Printer ID Assignments' AND HPD_HelpDesk.SubItem <> 'EFS Install' AND HPD_HelpDesk.SubItem <> 'Changes to Location Table' AND HPD_HelpDesk.SubItem <> 'Needs more Instant Data Reports Added to site' AND HPD_HelpDesk.SubItem <> 'Setup') " & _
"AND (DATEADD(s, Assign_Time, '12/31/1969 18:00:00 PM') > '" & Date & "')" & "ORDER BY DATEADD(s, Assign_Time, ' 12/31/1969 18:00:00 PM ') DESC"
Adodc1.Refresh

The table that has the information in it it the Priority table. Any help would be appreciated, i am new to VB.

 

[cowski]

Sorry I can't help with your problem directly, but you may also want to check out eng-tips.com sister site tek-tips.com. They have a very active and helpful VB forum.

 

[CajunCenturion]

You should be able to do this be adding a LEFT JOIN on the Priority Table based on the Priority Value. I am assuming that the Priorty table is called "PRIORITY" and has two fields, "Priority" which is the value, and "PriorityDescription" which is the text.

SELECT HPD_HelpDesk.Submitted_By, _
       DateAdd(s,HPD_HelpDesk.Assign_Time,'12/31/1969 18:00:00 PM') as Ticket_Assigned_Time, _
       HPD_HelpDesk.Case_ID_, _
       HPD_HelpDesk.SubItem, _
       HPD_HelpDesk.Assigned_To_Individual_, _
       HPD_HelpDesk.Priority, _
       [blue]Priority.PriorityDescription[/blue]
FROM   REMEDY_SUPPORT.dbo.HPD_HelpDesk [blue]LEFT JOIN REMEDY_SUPPORT.dbo.Priority ON HPD_HelpDesk.Priority = REMEDY_SUPPORT.dbo.Priority[/blue] _

and the WHERE and ORDER BY clauses remain the same.


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[BSG75]

Mr. CajunCenturion thank you for your post of a possible solution, it sounds great and i will look into it. My question is if i do not have the value description and just have values and i have to place in descriptions for the values when the data gets pulled into the grid, how might i be able to do this. The informamtion in the Priority table just has values. I tried something like a Case statement but it is not working. I can't put in a IF Priority =1 then 'Low' since i can't use a If statement in a select statement. This is my delema. If you have any other suggestions i would appreciate it.

 

[CajunCenturion]

That's what the 'LEFT JOIN' does. It takes the value from the HPD_HelpDesk table and matches it up with the corresponding record in the 'Priority' table which contains both the value and the text.
[tt]
FROM REMEDY_SUPPORT.dbo.HPD_HelpDesk LEFT JOIN REMEDY_SUPPORT.dbo.Priority ON HPD_HelpDesk.Priority = REMEDY_SUPPORT.dbo.Priority[blue].Priority[/blue]
[/tt]
(Please note that the column name (Priority) was left out in the original post)

The text column is then appended to the results set, by including that column (Priority.PriorityDescription) in the SELECT clause of the SQL statement.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein