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
Hi,
I have a table with a date field. In my form code I have a query with a WHERE clause that looks at this date. I want to check that date to see if it is less than some fixed date (like 1/1/2005) or if it is null/blank If the date field has never been initialized to any value, then it does not return that row. So, for example, dateTable has 3 rows. 2 rows have dates less than 1/1/2005. The third row has never had a date entered. If I run this query: SELECT mydate FROM dateTable WHERE mydate #1/1/2005#, I only get 2 rows returned. I can set the default for the date field to 1/1/1000 and everything works fine except the user doesn't like to see this date being displayed in list boxes where I can't intervene and change 1/1/1000 to blank (unless there is some clever SQL that would do this). So, how can I select those rows that have this date field that has never been initialized? Or how can I change a default value like1/1/1000 to a blank inside of an SQL statement? Thanks |
#2
|
|||
|
|||
On Thu, 24 Mar 2005 15:16:55 -0600, "dave h" wrote:
So, how can I select those rows that have this date field that has never been initialized? Or how can I change a default value like1/1/1000 to a blank inside of an SQL statement? I'd just use [Enter date:] OR IS NULL on the criteria line. The OR IS NULL will find those records where the date field is empty. I'd recommend against storing a phony date for the purpose. John W. Vinson[MVP] |
#3
|
|||
|
|||
Thanks John, that does it - much appreciated!
"John Vinson" wrote in message ... On Thu, 24 Mar 2005 15:16:55 -0600, "dave h" wrote: So, how can I select those rows that have this date field that has never been initialized? Or how can I change a default value like1/1/1000 to a blank inside of an SQL statement? I'd just use [Enter date:] OR IS NULL on the criteria line. The OR IS NULL will find those records where the date field is empty. I'd recommend against storing a phony date for the purpose. John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Importing and storing a different date format | Jim T | General Discussion | 1 | November 2nd, 2004 01:45 PM |
i cant change the format of the date when referencing another cell | Brian | Worksheet Functions | 3 | September 29th, 2004 05:02 AM |
Date format incorrect mail merge | Emily | Mailmerge | 1 | September 9th, 2004 10:22 AM |
Date Format | Date Formating Problem | Database Design | 3 | August 16th, 2004 06:41 PM |
If statement | Doug | Worksheet Functions | 9 | June 28th, 2004 06:13 AM |