Query ignores date parameters - works when hardcoded but not as cell reference.

[9tanstaafl9]

Please help, or I'm going to have a bald spot (bad for girls)! The following query works fine if I substitute actual dates for the parameters, but if I try to enter dates into fields on the spreadsheet and reference those fields in the query instead, the query completely ignores the dates and returns only the rows that happened to have had null dates.

Using Microsoft Query Editor to query Visual Foxpro tables.

So this returns the null dates:

SELECT 'COR' as Type, prmchg.recnum, prmchg.jobnum, prmchg.chgdte as Creation
FROM prmchg prmchg
WHERE prmchg.status = $1 AND prmchg.chgdte >={?CO1} AND prmchg.chgdte<={?CO2}UNION ALL
 
SELECT 'JOB', actrec.recnum, actrec.recnum, actrec.biddte
FROM actrec actrec
WHERE actrec.biddte>={?JOB1} AND actrec.biddte<={?JOB2}

=============

But this works fine and gives me real values:

SELECT 'COR' as Type, prmchg.recnum, prmchg.jobnum, prmchg.chgdte as Creation
FROM prmchg prmchg
WHERE prmchg.status = $1 AND prmchg.chgdte >={12/1/11} AND prmchg.chgdte<={12/31/12}
 
UNION ALL
 
SELECT 'JOB', actrec.recnum, actrec.recnum, actrec.biddte
FROM actrec actrec
WHERE actrec.biddte>={12/1/11} AND actrec.biddte<={12/31/12}

==================

The only possible hint I have is that I had to use French brackets to make it accept my code at all, I didn't have to use them last time, but last time I used query editor, which doesn't work in 2010, so i'm not sure if that has something to do with it - this is a very different query and I'm not very good at these.

 

[AlphaFrog]

I found this http://www.mrexcel.com/forum/showthread.php?t=21264 that suggest you convert the serial dates in the fields to text strings.

 

[9tanstaafl9]

Thanks, but no cigar. I am seriously going insane. We did figure out from doing this that somehow the automatic calculation had been turned off, but again, that didn't solve the problem either.

 

[9tanstaafl9]

If you wanted to design Hell for nerds, this would be the way to do it. Just make things that look like they should work, but make them not work.