formating date from text to normal text

[megatronixs]

Hi all,

I have 2 fields inside a form that are used to run a query.
the only thing is that the first field will be filled in, and the second field needs to format the text into a date.
Field 1 is called from_date_text and field 2 is called limit_date_text
in the first field I have the date like this: 20121210 and need to convert it to 10/12/2012.
I tried to use it like this in the second field: =[from_date_text].DateValue("10/12/2012") but no luck.

any tips in getting this working?

Greetings.

 

[CJ_London]

you need to use the string functions. try

=DateValue(left([from_date_text],2) & "/" & mid([from_date_text],3,2) & "/" & right([from_date_text],4))

 

[megatronixs]

Hi,
I don't get it to work, just get the error #Name?
but then I actually saw it is stored in number format :-(
my mistake.

Greetings.

 

[missinglinq]

CJ's suffering from a little bit of dyslexia, this morning...testing shows it should be

=DateValue(Right([from_date_text],2) & "/" & mid([from_date_text],3,2) & "/" & Left([from_date_text],4))

if the example given is correct, i.e going from

20121210

to

10/12/2012

As for it being a Number Datatype, is there any reason you can't simply go into Table Design View and change it from Number to Text? If it's all digits, as indicated, it will convert without a problem.

Linq ;0)>

 

[CJ_London]

sorry - my mistake - arse and elbow comes to mind

 

[megatronixs]

hi all,
It worked out BIG THANKS to both.
I was wondering if it can be used also in vba. I will give it a try

Greetings.

 

[missinglinq]

...arse and elbow comes to mind...

You Brits use the English language so much better than we do...but, of course, you've had so much more experience than we have!

Linq ;0)>