MS Query Date Select Problems

[shelby55]

Hello

I am working on a project that Skip has been great at helping with but I've hit a snag.

I am using MSQuery to pull two columns from one worksheet into another worksheet. I want when the initial column, 'referral', is not null but also not equal to March 31, 2009. The date in the query is showing in the format of 2009-04-16 00:00:00 because it is just date and no time.

My current SQL formula is:
select "09_10$".'Referral', "09_10$".'Asess' From "09_10$" "09_10$" Where ((('Referral') is not null and ('Referral') <> #3/31/2009#));

It works for blanks but not for where the field = #3/31/2009#. Please help - thanks!

Shelby

 

[SkipVought]

Because DateTime is really a double precision value, you may not have EXACTLY an integer value. Oh, yes, date/time is REALLY a number.

Select the date that you THINK is 3/31/2009.

Change the Cell Format - Number to GENERAL.

I'd bet that the value is not exactly 39903.

So....

for dates in a query...

select 
  'Referral'
, 'Asess' 

From [09_10$] 

Where 'Referral' is not null
  and INT('Referral') <> #3/31/2009#;

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Thanks but when I do that I get the error message "data type mismatch in criteria expression".

Any other ideas? Thanks.

 

[SkipVought]

Hmmmmm. I dummied up some data and ran the query just fine.

Please post some of your data, from the FIRST 20 ROWS.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

I got it to work with my previous info by just adding the worksheet reference:

((("09_10$".'Referral') is not null and
("09_10$".'Referral')<>#2009-03-31 00:00:00#;

Thanks!