| Author |
Topic |
|
CoachBarker
Posting Yak Master
170 Posts |
 Posted - 2009-03-27 : 12:32:39
|
| I am looking for a way to insert characters into a string of numbers, something along the line of substring or insert in .net. Insert the forward slash after the fourth and sixth indexes. Can it be done?1234/56/78Thanks for the helpCoachBarker |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-03-27 : 13:30:21
|
| Take a look at the STUFF function in Books Online. |
 |
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-03-27 : 13:50:36
|
Nope it has to be something I can use in a report. IIF Fields!start_date.Value = 8, then insert / after the 4th number and insert /after the 6th number.ANd no I tried this and it wasn't quite right Thanks for the helpCoachBarker |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-28 : 05:04:53
|
| IIF (Len(Fields!start_date.Value) = 8,Cstr(LEFT(Fields!start_date.Value,4))+ "/" + CStr(SUBSTRING(Fields!start_date.Value,5,2)) + "/" + Cstr(Fields!start_date.Value,7,2)) |
|
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-03-28 : 12:44:52
|
| I never would have figured that out, I am used to .net where everything is much more compact and easier to write. I appreciate your efforts in helping me out and showing me how to write expressions gives me a better understanding of what to do.Thanks for the helpCoachBarker |
|
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-03-30 : 08:08:35
|
The Format expression for the textbox ‘start_date’ contains an error: [BC30451] Name 'SUBSTRING' is not declared.???=IIF (Len(Fields!pickup_dt.Value) = 8,Cstr(LEFT(Fields!pickup_dt.Value,4))& "/" & CStr(SUBSTRING(Fields!pickup_dt.Value,5,2)) & "/" & Cstr(Fields!pickup_dt.Value,7,2)) Unrecognized Identifier.Thanks for the helpCoachBarker |
|
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-03-31 : 11:52:17
|
| I have been looking for the last day and a half and haven't been able to figure out what is wrong with the word SUBSTRING, or why it is not "declared". What am I missing?Thanks for the helpCoachBarker |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-03-31 : 12:58:41
|
| Substring isn't supported in VBScript, use the Mid function instead. |
|
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-03-31 : 13:44:47
|
Then the book I have is wrong, it says SUBSTRING is an accepted method.Ok, so using the .ToString method (my .net background)I would do it this way, =IIF (Len(Fields!pickup_dt.Value) = 8,(LEFT(Fields!pickup_dt.Value.ToString,4))+ "/" + (Mid(Fields!pickup_dt.Value.ToString,5,2)) + "/" + (Fields!pickup_dt.Value.ToString,7,2)) It gives me The Format expression for the textbox ‘pickup_dt’ contains an error:[BC30198] ')' expected and the comma in front of 7,2 is a syntax error.Or am I missing something, coma Placement????Thanks for the helpCoachBarker |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-03-31 : 14:19:37
|
| ToString() is a method and may require the parentheses to work right.I'm confused about this, is this code in a .Net module, or a Reporting Services report expression? |
|
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-03-31 : 14:28:27
|
| In the format property of a textbox in reporting services. It is just for formatting a date yyyy/MM/dd, adding the slashes.Thanks for the helpCoachBarker |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-03-31 : 14:43:01
|
| What about the built-in Format functions? There's one specfically for dates and another general one. |
|
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-03-31 : 14:53:41
|
| I want to format the string to LOOK like a DATE, but not to be a date. In the db it is stored as a char(8) as yyyymmdd, they use it for other functions than just a date. But if I format as I have now in my reports =format(Fields!DeliverDate.Value,"yyyy/MM/dd") then when it exports to excel it looks like 39817. Because there are merged cells in the report you can not select the column to formart it as a date in excel. So I need to format it as a string that looks like a date format. I know it is not well thought out but that is what I have to do.Thanks for the helpCoachBarker |
|
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-04-01 : 10:47:27
|
So how can I solve what should be a simple formatting problem of a string? Thanks for the helpCoachBarker |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-04-01 : 11:04:57
|
| I got this to work, I didn't use an IIF() though:=left(Fields!pickup_dt.Value,4) + "/" + Mid(Fields!pickup_dt.Value,5,2) + "/" + Right(Fields!pickup_dt.Value,2) |
|
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-04-01 : 11:25:30
|
So I had to many parenthesis in mine. Oh well it is a learing experience, isn't it. Yours works fine and when it is exported to excel everything is as it should be.Nope must have been seeing things, it isn't formatting the slashes. Also interesting, though they are a char(8) in the db, with the formatting it does change them to number types  Thanks for the helpCoachBarker |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-04-01 : 13:15:02
|
| Change all the + signs to &, that's the other concatenation operator. |
|
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-04-01 : 13:56:48
|
| Nope still isn't formatting the string, no errors just no slashes. I tried a couple of changes but still to no avail. I don't need the IIF because the field will always be 8 characters. Is there any reason you can think of that would explain the string of characters is converting to numbers when exported to excel?OK I got it to work, but in order to do it I had to include the formeatting expression in the Value Expression box, will that cause a problem in the future. Thanks for the helpCoachBarker |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-04-01 : 15:09:49
|
| I was using it in the value expression only. It shouldn't cause any future problems. |
|
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-04-01 : 15:23:29
|
The help was much appreciated Thanks for the helpCoachBarker |
|
|
|
|
String formatting