Isdate?

G

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.
 
G

Golfinray

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

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


Golfinray said:
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.
Click to expand...
Click to expand...
 
K

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


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

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


Golfinray said:
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.
Click to expand...
Click to expand...
Click to expand...
 
J

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.
Click to expand...

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>
 
J

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
 
G

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!

John W. Vinson said:
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.
Click to expand...

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>
Click to expand...
 
Top