take Left of another column..

[QuietRiot]

Not sure if this is possible? I have a column called Issues and another column I want to call ShortIssues and I basically want ShortIssues to be Left(Issues,50) so that its just 50 characters long of column Issues.

any ideas.

Thanks,

 

[namliam]

Left(Issues,50)

You give your own solution

I hope I am not feeding a trol

 

[QuietRiot]

Left(Issues,50)

You give your own solution

I hope I am not feeding a trol

No troll, sorry..

how do I make it so when items get added to a table its automatic? So that when the Issues cell is filled it automatically populates the ShortIssue column with Left(Issues,50). I tried putting that in the default value section but no luck.

Thanks,

 

[DJkarl]

No troll, sorry..

how do I make it so when items get added to a table its automatic? So that when the Issues cell is filled it automatically populates the ShortIssue column with Left(Issues,50). I tried putting that in the default value section but no luck.

Thanks,

This shouldn't be done in a table, you are storing duplicate data in two different columns. Create a query based on your table and create a new field in the query with Left(Issues,50) as the value. Then every time you need this data you just run the query and it will always be up to date.

 

[namliam]

This shouldn't be done in a table, you are storing duplicate data in two different columns. Create a query based on your table and create a new field in the query with Left(Issues,50) as the value. Then every time you need this data you just run the query and it will always be up to date.

QFT (=> Quoted For Truth)

This is one of the rules of normalisation, do not store calculated values... Just re-calculate them when needed.
(except in very rare, exception to the rule, ocassions)

 

[gemma-the-husky]

out of interest a field length of 50 seems excessive anyway - what are you using the field for?

 

[Mike375]

What about a calculated field in your query that is an IFF for Left and based on Len fucntion for the Issues field.

 

[QuietRiot]

Yah, maybe I should explain the original issue.

I have a listbox that im trying to populate via query and I'm doing it in VBA because i don't know how to write a query using form textboxes. My Issue field is a Memo so i'm assuming (cause I tried) that It doesn't work and will not populate in the listbox. So I figured if i were to make a new column called ShortIssue and have it just Text and say 50 charcters so that in the listbox you get a preview of the issue.

Private Sub FindButton_Click()
      Dim strRowSource As String
          strRowSource = "SELECT DateReported, ProjectTeamMember, Issue, TimeHRs FROM ProductionSupport " & _
                         "WHERE MonthName(month(DateReported)) ='" & Me.MonthBox.Value & "' AND Year(DateReported)='" & Me.QYearBox.Value & "';"
          Me.List362.RowSourceType = "Table/Query"
          Me.List362.RowSource = strRowSource
          Me.List362.Requery
    
End Sub

I don't know i think im really lost now. I basically have a dropdown and a textbox on my form that I want to use this data to filter the listbox. The Issue (memo field) doesn't show up as an option and im assuming its because its a memo.

 

[gemma-the-husky]

just put left(issue,50) in your query

that should work, i think

 

[gemma-the-husky]

you would have to put


strRowSource = "SELECT DateReported, ProjectTeamMember, " & left(issue,50) &", TimeHRs FROM ProductionSupport " & _
"WHERE MonthName(month(DateReported)) ='" & Me.MonthBox.Value & "' AND Year(DateReported)='" & Me.QYearBox.Value & "';"

 

[DCrake]

Simple Software Solutions

The code:

strRowSource = "SELECT DateReported, ProjectTeamMember, " & left(issue,50) &", TimeHRs FROM ProductionSupport " & _
"WHERE MonthName(month(DateReported)) ='" & Me.MonthBox.Value & "' AND Year(DateReported)='" & Me.QYearBox.Value & "';"

Should read:

strRowSource = "SELECT DateReported, ProjectTeamMember, " & left(issue,50) &" As ShortIssue, TimeHRs FROM ProductionSupport " & _
"WHERE MonthName(month(DateReported)) ='" & Me.MonthBox.Value & "' AND Year(DateReported)='" & Me.QYearBox.Value & "';"

 

[namliam]

Accoording to the query given by the OP Issue seems to be a column in the table not a parameter of some sort...
Which would make it...

strRowSource = "SELECT DateReported " & _
",     ProjectTeamMember " & _
",     left(issue,50) As ShortIssue " & _
",     TimeHRs " &_
"FROM ProductionSupport " & _
"WHERE MonthName(month(DateReported)) ='" & Me.MonthBox.Value & _
"' AND Year(DateReported)='" & Me.QYearBox.Value & "';"

-- Yes needness counts, particularly in code --

Why are you using a self created function MonthName, when we have format???

Format(DateReported,"MMMM") should return the full month name in "regional settings language" which 99 out of 100 times is what you are looking for.

BTW Year() returns an Integer not a String value... tho the query will probably work....