Re: Null values blow up my SQL statement - how can I deal with them?
- From: Dale <dale0610@xxxxxxxxxxx>
- Date: Wed, 13 Apr 2005 09:14:39 -0700
Thanks Bob and John for your replies. I tried what Bob mentioned late yesterday and it seemed to work. But I'm looking at the raw data at the moment and some things got missed. But to quickly answer your question, it is a Date/Time field. This leads to my looking at the raw data now through Access. I guess multiple programs access this table and I see a mix of values in that date field. There are some values with only a date and some with a date and time. Looks like the ones with the time as well gets missed by the Select. Any ideas on how I can get around that? I'm going to continue looking at that right now. Thanks guys.
John B wrote:
Dale wrote:
Hi, I'm looking at some code that opens up a recordset with an SQL Select like below:
SELECT *
FROM InventoryAdjustments
WHERE CDATE(FORMAT(EffectiveDate,"MM/DD/YYYY"))>=#4/10/2005# And CDATE(FORMAT(EffectiveDate,"MM/DD/YYYY"))<=#4/12/2005#
this all works fine until there's some null values in the table for the EffectiveDate field. My SQL is pretty limited, how can I make the above just simply ignore those null values? Thanks.
try this
SELECT <ColList> FROM <Table> WHERE EffectiveDate BETWEEN #2005-04-10# AND #2005-04-12#
I presume that EffectiveDate is defined as a date column?
Formatting date in the manner of yyyy-MM-dd[ HH:mm:ss:nn] removes any ambiguity about the format of the date.
.
- Follow-Ups:
- Re: Null values blow up my SQL statement - how can I deal with them?
- From: Bob Butler
- Re: Null values blow up my SQL statement - how can I deal with them?
- References:
- Prev by Date: Re: Datediff question
- Next by Date: Re: Null values blow up my SQL statement - how can I deal with them?
- Previous by thread: Re: Null values blow up my SQL statement - how can I deal with them?
- Next by thread: Re: Null values blow up my SQL statement - how can I deal with them?
- Index(es):
Relevant Pages
|
