Microsoft Query from excel issue

[lespaul]

I am trying to do a CASE statement from a query into Excel. This operation works fine if I am trying to do a comparison on a text field but fails when trying to perform on a date field.

For instance, if I do:

SELECT Case When CasPre <> 'TR' then 'Not Traffic' else 'Traffic' end From TableName

then I get a list of Traffic/Not Traffic

If however I try to run:

SELECT Case When VoidDate > '1900-01-00' then VoidDate else '' end From TableName

then i get an error "Didn't expect VoidDate after the SELECT column List".

I have tried all variations of date formats I can think of:
mm/dd/yyyy
yyyy-mm-dd
yyyy/m/d
m/d/yyyy

have used ' and "

nothing I have done has made the case statement valid.

Any suggestions?

Thanks,
Leslie



Leslie

 

[xlbo]

SELECT Case VoidDate When > '1900-01-00' then VoidDate else '' end


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

If your VoidDate is a REAL DATE then

SELECT Case VoidDate When > #1900-01-00# then VoidDate else '' end

to CONVERT the string to a date.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]