Substring in IIF

[CoachBarker]

I have a field coming into a report from SQL Server 2005 that is a date in a string format. I am using an IIF statement to format the string for the report so when it exports to excel it still reads in it's proper format. This is the IIF:

=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))

And this is the error that I get:

The Format expression for the textbox ‘pickup_dt’ contains an error: [BC30451] Name 'SUBSTRING' is not declared.

in the db the format is yyyyMMdd, it will always have 8 characters so I just need to add in the slashes yyyy/MM/dd.

Any Suggestions?

Thanks
CoachBarker

 

[Kiwiman]

Howzit

You can try the dateserial function:

IIF (Len(Fields!pickup_dt.Value) = 8,dateserial(left(yourdatefield,4),mid(yourdatefield,5,2),right(yourdatefield,2)),false condition)

 

[CoachBarker]

In the expression box for the textbox if I use your version as

[SIZE=2]=IIF (Len(Fields!pickup_dt.Value) = 8,dateserial(left(Fields!pickup_dt,4),mid(Fields!pickup_dt,5,2),right(Fields!pickup_dt,2)),[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]false[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] condition)[/SIZE]

I get a syntax error on condition

When I run the report I get:

[SIZE=2][rsCompilerErrorInExpression] The Format expression for the textbox ‘pickup_dt’ contains an error: [BC30311] Value of type 'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Field' cannot be converted to 'String'.[/SIZE]

This report is SSRS in case that is worth mentioning.

 

[boblarson]

This report is SSRS in case that is worth mentioning.

It would be worth mentioning and it makes me wonder why you would post an SSRS question to a Microsoft ACCESS Reports forum when it has nothing to do with Microsoft Access.

 

[Kiwiman]

ahh

Thanks bob, only read a part of the qustion. Sorry coach, ignore my earlier post

 

[CoachBarker]

On the chance that maybe there was someone here that could help, I assumed an expression was an expression. Not a lot of xamples on the web and the books I got are not much better I am the lucky one at work who was chosen to learn how to do these reports on the fly.

 

[boblarson]

On the chance that maybe there was someone here that could help, I assumed an expression was an expression. Not a lot of xamples on the web and the books I got are not much better I am the lucky one at work who was chosen to learn how to do these reports on the fly.

SQL Server and Access have different syntax. Post the question to the SQL Server portion of the forum here.

 

[Kiwiman]

Howzit

I was playing around in SQL today with dates. This may help - not an expert mind...

I used something similar to update a date field in SQL...

substring(myfield,1,4) + substring(myfield(,5,2) + substring(myfield,7,2)

This link may help...

http://aspalliance.com/1756_Understanding_Date_and_Time_Functions_and_Operations_in_SQL_Server.2

 

[CoachBarker]

Are there moderators here who could move the post to the corrrect forum?

 

[boblarson]

Are there moderators here who could move the post to the corrrect forum?

Just click the icon at the top right of your post that looks like an exclamation mark inside a red triangle and ask for it to be moved.

 

[SQL_Hell]

Hi,

Did you get this fixed?

Kiwiman's answer would have worked, what data type is the field pickup_dt?

 

[CoachBarker]

Yes I got it fixed, turns out substring isn't valid in SSRS, so I got by on a left, mid and then the rest of the string.