Format Number field into Date field

[realstandman]

I have linked tables to a server that gives me dates for example 20060216 as a number field. How can I change this to a date/time field? Any Sugestions?

 

[PHV]

DateSerial(Left([linked field],4), Mid([linked field],5,2), Right([linked field],2))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[realstandman]

It gives me a #Error in the column when I run the query. PHV I do appreciate all of your answers you have helped me many times.

Stan

 

[PHV]

Any chance you could post the SQL of your query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[realstandman]

SELECT "0" & [cdclno] & "-0" & [cdwkno] AS [Claim number], clmhdr.chgpno, [cdpdpv]+[cdpdcl] AS [Total Paid], DateSerial(Left([chpddt],4),Mid([chpddt],5,2),Right([chpddt],2)) AS [Paid Date], elgemp.eecvst AS [Coverage Status]
FROM (clmhdr INNER JOIN clmdet ON (clmhdr.chwkno = clmdet.cdwkno) AND (clmhdr.chwkno = clmdet.cdwkno) AND (clmhdr.chclno = clmdet.cdclno)) INNER JOIN elgemp ON clmhdr.chemno = elgemp.eenmbr
WHERE (((clmhdr.chgpno)="75000"));

 

[realstandman]

Any Ideas?

 

[PHV]

chpddt is ALWAYS formatted as yyyymmdd and NEVER null ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[realstandman]

yes, and the datatype is "number