If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date Format in a Query
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? |
#2
|
|||
|
|||
Date Format in a Query
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 -- "K. Hulse" wrote in message ... 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? |
#3
|
|||
|
|||
Date Format in a Query
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 "Duane Hookom" wrote: 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 -- "K. Hulse" wrote in message ... 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? |
#4
|
|||
|
|||
Date Format in a Query
"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 -- "K. Hulse" wrote in message ... 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 "Duane Hookom" wrote: 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 -- "K. Hulse" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Running Average | cutthroatjess | Running & Setting Up Queries | 17 | December 19th, 2005 07:52 PM |
Date format in crosstab query | Matt | Running & Setting Up Queries | 3 | September 12th, 2005 07:32 AM |
DATE FORMAT in crosstab query | Mary | Running & Setting Up Queries | 2 | February 18th, 2005 03:31 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Aggregating Date Data into Weeks and Quarters | Roger | Running & Setting Up Queries | 3 | July 11th, 2004 05:56 PM |