Not So Simple Query....

[zyus]

I have this data sample in my NatID field

NatID
701011234567
700709112567

The first 6 character represent DOB in yymmdd format.
How i can make a new column in query to grab the first 6 character and to
make it a valid DOB with new format ddmmyy..

So it will look like this

DOB
11101970
09071970

Thanks

 

[Tom van Stiphout]

On Mon, 29 Sep 2008 17:29:00 -0700, zyus

Use a combination of Left$ and Mid$ - see help file.

Deciding which dates are 19xx or 20xx is not necessary; Access will do
it for you once you specify a normally-formatted date.

-Tom.
Microsoft Access MVP

 

[Ken Sheridan]

The following expression will do it:

CDate(Format(Left([NatID],6),"00-00-00"))

Date/time data is stored in Access as a 64 bit floating point number, not as
a formatted value. The format you see can be anything you wish; just set the
Format property of the column or of a control in a form or report to the
desired format. By default date/time columns in a table are formatted in the
short date format set in Windows Control Panel.

Ken Sheridan
Stafford, England

 

[Douglas J. Steele]

Actually, given that zyus wants the date in ddmmyyyy format, you'd need

Format(CDate(Format(Left([NatID],6),"00-00-00")), "ddmmyyyy")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The following expression will do it:

CDate(Format(Left([NatID],6),"00-00-00"))

Date/time data is stored in Access as a 64 bit floating point number, not
as
a formatted value. The format you see can be anything you wish; just set
the
Format property of the column or of a control in a form or report to the
desired format. By default date/time columns in a table are formatted in
the
short date format set in Windows Control Panel.

Ken Sheridan
Stafford, England

I have this data sample in my NatID field

NatID
701011234567
700709112567

The first 6 character represent DOB in yymmdd format.
How i can make a new column in query to grab the first 6 character and to
make it a valid DOB with new format ddmmyy..

So it will look like this

DOB
11101970
09071970

Thanks

 

[Ken Sheridan]

If that's all that's wanted the date conversion can be skipped:

Format(Format(Left([NatID],6),"00-00-00"),"ddmmyyyy")

I'd interpreted 'valid DOB' as meaning a valid date/time value, which could
then be formatted as desired, but maybe not. Any date computations on the
return value of the above expression would give some strange results: I'd not
be born until 26 May 4944!

Ken Sheridan
Stafford, England

Actually, given that zyus wants the date in ddmmyyyy format, you'd need

Format(CDate(Format(Left([NatID],6),"00-00-00")), "ddmmyyyy")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The following expression will do it:

CDate(Format(Left([NatID],6),"00-00-00"))

Date/time data is stored in Access as a 64 bit floating point number, not
as
a formatted value. The format you see can be anything you wish; just set
the
Format property of the column or of a control in a form or report to the
desired format. By default date/time columns in a table are formatted in
the
short date format set in Windows Control Panel.

Ken Sheridan
Stafford, England

I have this data sample in my NatID field

NatID
701011234567
700709112567

The first 6 character represent DOB in yymmdd format.
How i can make a new column in query to grab the first 6 character and to
make it a valid DOB with new format ddmmyy..

So it will look like this

DOB
11101970
09071970

Thanks