Isdate?

[Golfinray]

I was trying to use isdate in a query. I have a field that contains dates,
numbers, text, symbols. I'm trying to extract just the records that are
dates. I couldn't get isdate to work in an IIF statement. Is there a better
way? I also tried Between but it just wouldn't pull ALL of the dates. Thanks
for your help.

 

[KARL DEWEY]

Try this --
Expr1: IsDate(DateValue([YourField]))

 

[Golfinray]

Karl. that gave me -1's, not the actual dates. Thanks!

Try this --
Expr1: IsDate(DateValue([YourField]))
--
KARL DEWEY
Build a little - Test a little

I was trying to use isdate in a query. I have a field that contains dates,
numbers, text, symbols. I'm trying to extract just the records that are
dates. I couldn't get isdate to work in an IIF statement. Is there a better
way? I also tried Between but it just wouldn't pull ALL of the dates. Thanks
for your help.

 

[KARL DEWEY]

IsDate returns a -1 if it test True. You need another field for the actual
date.
--
KARL DEWEY
Build a little - Test a little

Karl. that gave me -1's, not the actual dates. Thanks!

Try this --
Expr1: IsDate(DateValue([YourField]))
--
KARL DEWEY
Build a little - Test a little

I was trying to use isdate in a query. I have a field that contains dates,
numbers, text, symbols. I'm trying to extract just the records that are
dates. I couldn't get isdate to work in an IIF statement. Is there a better
way? I also tried Between but it just wouldn't pull ALL of the dates. Thanks
for your help.

 

[John W. Vinson]

I was trying to use isdate in a query. I have a field that contains dates,
numbers, text, symbols. I'm trying to extract just the records that are
dates. I couldn't get isdate to work in an IIF statement. Is there a better
way? I also tried Between but it just wouldn't pull ALL of the dates. Thanks
for your help.

Put a calculated field in your query by typing

ItsADate: IsDate([fieldname])

in a vacant Field cell in your query. Put a criterion of True on this field.
This will limit retrieval to those records where the field contains a text
string which can be interpreted as a date.

Put a second calculated field in the query:

CDate([fieldname])

to get a Date/Time value for those records; this field can then be used for
sorting, criteria, etc.

Naturally records where the field does not contain a date will not be
searchable. You're paying the penalty for letting garbage data into your
database... <g>

 

[John Spencer]

Or you can use

Field: ShowDates: IIF(IsDate(YourField),CDate(YourField),Null)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Golfinray]

Thanks so much, guys. This is not my garbage, it's someones' garbage from a
spreadsheet I imported to try to get some of their data. Spreadsheets -
BluuuuuHHHH!

I was trying to use isdate in a query. I have a field that contains dates,
numbers, text, symbols. I'm trying to extract just the records that are
dates. I couldn't get isdate to work in an IIF statement. Is there a better
way? I also tried Between but it just wouldn't pull ALL of the dates. Thanks
for your help.

Put a calculated field in your query by typing

ItsADate: IsDate([fieldname])

in a vacant Field cell in your query. Put a criterion of True on this field.
This will limit retrieval to those records where the field contains a text
string which can be interpreted as a date.

Put a second calculated field in the query:

CDate([fieldname])

to get a Date/Time value for those records; this field can then be used for
sorting, criteria, etc.

Naturally records where the field does not contain a date will not be
searchable. You're paying the penalty for letting garbage data into your
database... <g>