workaround for dates in non date fields <?>

[Mike K.]

Hello all

Due to complex design requirements, I was forced to use a
table field that contains dates but had to be a non date
data type...

just to quickly explain, the field actually contains
values that look like: "054 - 07/09/2003" and when I want
to Isolate the date part, I use a query parameter that
looks like:
Admission Date: Right([AdmissionID],10)
so now I have a column in the query that look and could
behave just like a date data type, but access seems to
only view it as integers naturally.
So, obviously when I run a query criteria parameter
like "Between 6/1/2002 And 6/29/2003" it returns
incorrect results...

is there any "work around" for this?? any way to force
access to treat the date "look-a-likes" as true dates??
(Expressions, process, or code??)

Thanks in advance for the help,

Mike K.

PS. the only "work around that I have found so far is to
import that look-a-like dates column into a new table
where that field IS a date data type, then design a query
based on that new table, but this is pretty cumbersome
and time consuming since it would have to be done every
time...

 

[Rick Brandt]

Hello all

Due to complex design requirements, I was forced to use a
table field that contains dates but had to be a non date
data type...

just to quickly explain, the field actually contains
values that look like: "054 - 07/09/2003" and when I want
to Isolate the date part, I use a query parameter that
looks like:
Admission Date: Right([AdmissionID],10)
so now I have a column in the query that look and could
behave just like a date data type, but access seems to
only view it as integers naturally.
So, obviously when I run a query criteria parameter
like "Between 6/1/2002 And 6/29/2003" it returns
incorrect results...

is there any "work around" for this?? any way to force
access to treat the date "look-a-likes" as true dates??
(Expressions, process, or code??)

This expression will convert to an actual date using your systems regional settings.

Admission Date: CDate(Right([AdmissionID],10))

 

[Tom Ellison]

Hello all

Due to complex design requirements, I was forced to use a
table field that contains dates but had to be a non date
data type...

just to quickly explain, the field actually contains
values that look like: "054 - 07/09/2003" and when I want
to Isolate the date part, I use a query parameter that
looks like:
Admission Date: Right([AdmissionID],10)
so now I have a column in the query that look and could
behave just like a date data type, but access seems to
only view it as integers naturally.
So, obviously when I run a query criteria parameter
like "Between 6/1/2002 And 6/29/2003" it returns
incorrect results...

is there any "work around" for this?? any way to force
access to treat the date "look-a-likes" as true dates??
(Expressions, process, or code??)

Thanks in advance for the help,

Mike K.

PS. the only "work around that I have found so far is to
import that look-a-like dates column into a new table
where that field IS a date data type, then design a query
based on that new table, but this is pretty cumbersome
and time consuming since it would have to be done every
time...

Dear Mike:

In order to try to help you at all, I feel I must begin by criticizing
(with constructive intent) your statements above.

To put multiple values into one field, such as your 3 digit number
plus a date, is a violation of one of the most basic principles of
database design. In many years of my experience, there has never been
a situation that would force such a requirement.

The implications of having done this are that your coding will be more
complex and that you cannot build any indexing on the dates. Any
criteria that works on this, after you've done the extra coding to
extract the date, will require complete table scans. If there is much
data, this will be unnecessarily slow.

As a stopgap measure, you might want to try:

Admission Date: CDate(Right(AdmissionID, 10))

Be advised that, if any of these are not dates, you're going to get
errors from this. You should probably test existing values for this
problem. Whether you can keep non-dates out of this portion in the
string in the future is also a good question.

Hope some of this may help.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[John Vinson]

Admission Date: Right([AdmissionID],10)
so now I have a column in the query that look and could
behave just like a date data type, but access seems to
only view it as integers naturally.

If you use

AdmissionDate: CDate(Right([AdmissionID], 10))

you'll find that Access gives you a Date/Time value.