Date Format in a Query

[Guest]

I am linked to READ ONLY tables that have the dates in the following formats,
(a mixture of lower case and upper case):
Jan-05 and JAN-06

If I run the query on date = Jan-06 ; no records are returned.
If I run the query on date =JAN-06 ;I get records for JAN-06.

I would like users to be abel to query on a SINGLE CASE from a Form and be
able to generate a range of records despite the date format in the tables I
am querying.

Can someone please help with this?

 

[Duane Hookom]

You can query on UCase([Your Field]) = UCase([ some expression ])
Are we supposed to understand all the different types of values that might
be in the field? I can't even tell if JAN-06 is 1/6/2006 or 1/*/2006. Is the
format always the same other than the case?

 

[Guest]

This query is reading from our Oracle database.
Oracle has an input mask that requires mm-yy, (non-case sensitive)
I was surprised to see the date field in the table displaying the data
different ways.
Though I noticed that prior to JAN-06, the date format was proper i.e. Jan-05.

I will have to check with the Oracle DB on the actual format.

But I will try what you suggested and provide some feed back.

Thanks Duane

You can query on UCase([Your Field]) = UCase([ some expression ])
Are we supposed to understand all the different types of values that might
be in the field? I can't even tell if JAN-06 is 1/6/2006 or 1/*/2006. Is the
format always the same other than the case?

--
Duane Hookom
MS Access MVP
--

I am linked to READ ONLY tables that have the dates in the following
formats,
(a mixture of lower case and upper case):
Jan-05 and JAN-06

If I run the query on date = Jan-06 ; no records are returned.
If I run the query on date =JAN-06 ;I get records for JAN-06.

I would like users to be abel to query on a SINGLE CASE from a Form and be
able to generate a range of records despite the date format in the tables
I
am querying.

Can someone please help with this?

 

[Duane Hookom]

"input mask that requires mm-yy," doesn't fit JAN-06. I assume this is a
typo. If you want to query that allows use of a range of dates, you will
have to use a calculation that converts the text to an actual date.

--
Duane Hookom
MS Access MVP
--

This query is reading from our Oracle database.
Oracle has an input mask that requires mm-yy, (non-case sensitive)
I was surprised to see the date field in the table displaying the data
different ways.
Though I noticed that prior to JAN-06, the date format was proper i.e.
Jan-05.

I will have to check with the Oracle DB on the actual format.

But I will try what you suggested and provide some feed back.

Thanks Duane

You can query on UCase([Your Field]) = UCase([ some expression ])
Are we supposed to understand all the different types of values that
might
be in the field? I can't even tell if JAN-06 is 1/6/2006 or 1/*/2006. Is
the
format always the same other than the case?

--
Duane Hookom
MS Access MVP
--

I am linked to READ ONLY tables that have the dates in the following
formats,
(a mixture of lower case and upper case):
Jan-05 and JAN-06

If I run the query on date = Jan-06 ; no records are returned.
If I run the query on date =JAN-06 ;I get records for JAN-06.

I would like users to be abel to query on a SINGLE CASE from a Form and
be
able to generate a range of records despite the date format in the
tables
I
am querying.

Can someone please help with this?