Mid Function

[medusa3604]

Hi,

I have imported data from another database where the dates are in the wrong format.

It´s yyyymmdd and I want yyyy-mm-dd.

My plan was to use Left +"-" + Mid +"-" + Right

Left and right works fine but Mid does not. Can someone help me with this, Im going crazy.

This is how I write the Mid function Mid( [GILTIGFRAN] , 5, 2)

And I get this back



What am I doing wrong?

/ Anders
Sweden

[pbaldy]

For starters, try & instead of + to concatenate. Also make sure the field doesn't have any Nulls.

[medusa3604]

Thanks for your answer.

Right now Im just trying to get this function to work so I not using + or & but thanks for the tip.

Can it be some setting somewhere because now I try another formula on another field.

IIf( [KVARVARANDE DJUR] =0, "G", "IG") and I get the same message.

I should say that I put these formulas in calculated fields, can that be some reason?

/ Anders
Sweden

[June7]

I tested the Mid and IIf functions in table Calculated type field and they work. Post the complete exact expression.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Missinglinq]

...Post the complete exact expression...

...follow instructions at bottom of my post...

Both excellent suggestions! Having problems with two different standard VBA Functions means you're either

• Trying to assign the results incorrectly
• Are missing a Reference Library that the Functions reside in

Seeing exactly what you're doing will help us figure out which.

Linq ;0)>

[medusa3604]

Hi,

In this picture you see the left function working properly:


Then I create a new calculated field that is not working, see picture.


Thanks for your help.

/ Anders
Sweden

[pbaldy]

Looks like the wrong pic, but try taking the spaces out. Shouldn't matter, but worth trying.

[June7]

Maybe something changed with 2013 because it works for me in 2010. Access drops the unnecessary spaces.

Instead of Calculated type field in table, try the expression in a query.

[medusa3604]

Hi,

Thanks, the pictures are correct.

Ok, I will try it in a query.

/ Anders
Sweden

[pbaldy]

Hi,

Thanks, the pictures are correct.

both pics of query results show the successful Left() function.