At the hospital I have a database with 200 records in it for
volunteers and one of the columns has dates set up like this "Oct.
03". The column is in text format. Those dates change as people take
the required test that the
column of the record is for. It can be empty or have older dates in
it and it can also have Empl in it. When I run a query on that
column I use "Is Null, Or like "*00", Or like "*01", Or Like "*02"".
This gives me everyone who has not taken the test in 03, but it also
picks up the ones marked as Empl.
Is there something I can use in there so it won't pick up the records
marked as Empl? Also is there something I can use to get all dates
in 03 older than Oct. 03. I have tried different things, but get all
200 records in the report. I
have tried the help files, but don't understand how to do it as I'm
self taught and have never taken lessons on this.
Thanks for any help.
Rocky

Rocky,

The data in the field is text data, also known as string data.
Access can only sort it alphabetically. So, if you ask for
fields containing dates earlier than x or dates in the second half
of 2002 (for example), it won't give you what you want.
If the table field contained data of a date/time data type, you could
specify such criteria easily and reliably on dates or parts of dates
(by year, by month, whatever)

I am sure that you don't want to rebuild the
table with a date/time datatype, especially with some records
containing "Empl". But I have a solution for you. Depending on your
database skills, you might want to do it yourself or have someone
build this. It's not a very time-consuming project if you know your
way around Access. (I do consulting work if needed!)

Build a table which contains a date-time conversion for each of the
allowed entries in the field that you're testing on.
It has only two fields - one with the text string, and
one containing an actual date. (You set the field's data type
in Table Design when building the table, or after building it)

Like this:
TEXT - Date/Time data
Empl - 1/1/1800 (see note below)
Jan. 00 - 1/1/2000
Feb. 00 - 2/1/2000
Mar. 00 - 3/1/2000
Apr. 00 - 4/1/2000 (etc.)

I showed a date that would otherwise be unused for "Empl",
just so there would be something there.

Make the Text field the key field, and link that table
to your main table on that field. You can thoroughly
leave the original table's text field as-is.
For instance, if it contains
"Apr 02" and "Apr. 02" and "Apr 2002" you just make extra entries
in your new table for each of those, and give them all a date
value of 4/1/2002. When you pull in your date data in the query,
those records will all read as 4/1/2002.

Now you add the new table to your query, and do all your
specifying of date information against the date/time field
in the new linked table.

You can specify dates before or after some date
as greater than or less than (use > or <, or use >= or use <= )
or you can specify "Between" two dates, etc.
Examples:
Between #1/1/2000# And #4/1/2002#
You can put a criteria in a second row, below the one
where you specify a date range, in the original text field,
to allow in records marked
"Empl" in the original primary table's field. This gets you out of
constantly having to specify to accept a date of 1/1/1800,
or whatever date you use for that text value.

When you specify dates in an Access query's criteria, it
wants # symbols before and after, and no leading zeroes
in the day and month positions. That is the format you need.
The query design view will usually fix up your entry for
you if you use the query-building grid.

Examples:
Between #1/1/2000# And #4/1/2002#

Bruce
Bruce Pick Tech Services

"Rocky" <rockfish (AT) home (DOT) ca> wrote

Bruce I understand what your saying, but I don't know how to do it. I
built this database myself about 4 years ago. I only have two records
that have Empl in them and one NA. In 75% of the others is the date
of Oct. 03. It would not be to much work to make those fields
Date/Time format and change the dates to 1/1/1800 for Empl and
1/1/1801 or NA because we don't search for them. I can make all the
other dates 10/1/2003 and what ever the other few are other then Oct.
03 with copy and paste. This is what you mean by rebuilding the
database, correct? When I said column in the first question I meaning
field and the 200 records are listed in a column, but I guess you
understood that.
Rocky

On Wed, 14 Jan 2004 15:20:27 -0500, "Bruce Pick"
<brucepick1 (AT) comcast (DOT) net> wrote:

Rocky,

That's what I get for assuming. It appears that repopulating the table
data would be reasonable to do, and better than building the complex fix
I came up with.

I would make a backup copy of the table (right click and copy, then
paste, and give it an appropriate new name like
"TableName_backup_Jan22") before going ahead.

Now, back to the original table: In the table's design view, make a new
field and give it a date/time data type. Grab that row (in design view)
and move it to be next to the original row. Save the changes (being
careful that you didn't delete any fields in design view or any do other
such bad deeds).

Now open your table and put in good dates for all those records. Use
the Ctrl + V keys for a fast paste, and the down-arrow to move through
the records. It won't take long. Eventually you can learn to do this
type of work even more quickly with Access or SQL queries, but we'll
leave that alone for now.

I'm not sure what the "Empl" and "NA" mean. If their only purpose is to
indicate that an employee doesn't need to take the tests, then a simple
Yes/No (boolean) field will track that much more efficiently. You can
make one of those fields too, just select "Yes/No" as the data type in
design view. If you want to record a reason why the're exempt, you can
keep a text field where you can note a reason or comment. Either Yes or
No can mean "exempt", I'd use Yes=Exempt. Since the field standardly
defaults to 'NO', most records can be left alone, and a few marked Yes
as needed to indicate Exempt.

For that field, when you run a query, just test for the yes or no. Much
simpler than testing for specific text that could get mis-typed later.
In the criteria row of the design grid, use the date field and the
yes-no field as required for your needs.

Criteria on the same row in the query design grid are combined, making a
more restrictive "filter". Criteria on different rows (you can have a
huge # of rows of criteria) set up an "OR" logic. That is, each row
creates a criteria. If the record passes the test for any one row, it
is shown in the result set.

Bruce
(brucepick1 (AT) comzzzcast (DOT) net) - remove the zzz's to use email
(email me to be sure to reach me, if you like)

You can specify dates before or after some date
as greater than or less than
(use > or <, or use >= or use <= )
or you can specify "Between" two dates, etc.
Examples:
Between #1/1/2000# And #4/1/2002#
(just type "null" or "not null" and watch it fix the syntax)

For the boolean field, you can use Yes and No,
or -1 (= yes) and 0 (= no)
in your Criteria row.
+++++++++++++++++++++++++++++++++++++
Rocky wrote: