Date of birth question

[Lillian]

I receive a table from the main office and it has a Date of Birth field that
is formated text and as yyyy/mm/dd. How do I make a query field as a real
date and time format as mm/dd/yyyy and filter for all ages less than 25
years old?

I have this for a criteria but I don't have a real date format for the DOB
field.

<=DateAdd("yyyy",-25,Date())

 

[Anthos]

I receive a table from the main office and it has a Date of Birth field that
is formated text and as yyyy/mm/dd. How do I make a query field as a real
date and time format as mm/dd/yyyy and filter for all ages less than 25
years old?

I have this for a criteria but I don't have a real date format for the DOB
field.

<=DateAdd("yyyy",-25,Date())

Try the CDate(Format([field], "yyyy/mm/dd")) then do your criteria
based on that.

Let me know how that goes, and if it helps.

Kind Regards
Anthony Moore

 

[John W. Vinson]

I receive a table from the main office and it has a Date of Birth field that
is formated text and as yyyy/mm/dd. How do I make a query field as a real
date and time format as mm/dd/yyyy and filter for all ages less than 25
years old?

I have this for a criteria but I don't have a real date format for the DOB
field.

<=DateAdd("yyyy",-25,Date())

Either convert the criterion to a text string matching your text date (which
is fortunately of a form that is chronological):

<= Format(DateAdd("yyyy", -25, Date()), "yyyy/mm/dd")

or convert the text date to a real Date/Time field:

CDate([Date of Birth])

and omit the Format expression from your criterion.

The first option will have better performance if the table is large and the
field is indexed.

Your best bet would be to add a genuine date/time field to the table and run
an update query to populate it using CDate().

John W. Vinson [MVP]

 

[Lillian]

I receive a table from the main office and it has a Date of Birth field
that
is formated text and as yyyy/mm/dd. How do I make a query field as a real
date and time format as mm/dd/yyyy and filter for all ages less than 25
years old?

I have this for a criteria but I don't have a real date format for the DOB
field.

<=DateAdd("yyyy",-25,Date())

Either convert the criterion to a text string matching your text date
(which is fortunately of a form that is chronological):

<= Format(DateAdd("yyyy", -25, Date()), "yyyy/mm/dd")

or convert the text date to a real Date/Time field:

CDate([Date of Birth])

and omit the Format expression from your criterion.

The first option will have better performance if the table is large and
the field is indexed.

Your best bet would be to add a genuine date/time field to the table and
run an update query to populate it using CDate().

John W. Vinson [MVP]

I'm sorry it is a number format not a text format.

I have to import the date.txt file and use an import specification. I tried
to import as a date field but it fails and the DOB field is blank. Is
there away of changing the Number format to a date format in the import
specification? I can see how at this time. I all tried to make another
table and append to it. The number DOB field won't append to the Date
format DOB Field in the other table.

 

[John W. Vinson]

I have to import the date.txt file and use an import specification. I tried
to import as a date field but it fails and the DOB field is blank. Is
there away of changing the Number format to a date format in the import
specification? I can see how at this time. I all tried to make another
table and append to it. The number DOB field won't append to the Date
format DOB Field in the other table.

I'd suggest using a Link to the text file (rather than an import); base an
Append query on the linked table, and use a calculated expression to parse the
number into a date. I presume that what's actually stored in the text file is
a number like 20071106? If so, it will not be recognized as a date; you'll
need an expression like

CDate(Format([datefield], "0000/00/00")

or

DateSerial([datefield] \ 10000, [datefield] MOD 10000 \ 100, [datefield] MOD
100)

as a calculated field in the append query.

John W. Vinson [MVP]

 

[Lillian]

I have to import the date.txt file and use an import specification. I
tried
to import as a date field but it fails and the DOB field is blank. Is
there away of changing the Number format to a date format in the import
specification? I can see how at this time. I all tried to make another
table and append to it. The number DOB field won't append to the Date
format DOB Field in the other table.

I'd suggest using a Link to the text file (rather than an import); base an
Append query on the linked table, and use a calculated expression to parse
the number into a date. I presume that what's actually stored in the text
file is a number like 20071106? If so, it will not be recognized as a
date; you'll need an expression like

CDate(Format([datefield], "0000/00/00")

or

DateSerial([datefield] \ 10000, [datefield] MOD 10000 \ 100, [datefield]
MOD 100)

as a calculated field in the append query.

John W. Vinson [MVP]

Ok. It is working. I linked the table then append to another one like it
with the DOB field formated Date. Then the criteria finally did work.
Plus it is very accurate total of records with all under 25 years old.
Very amazing to see this.

I'll try the other CDate functions above and see if they work too.