Query is too complex? 3

[GPM4663]

Hi Everyone,
I have a query based on a subquery which filters the data between a date range which is entered on a form. It was running OK but when I tried to run it between 01/10/2004 and 31/05/2005 I got an error saying the the expression entered was incorrect or the query was too complex. I thought there was a problem with the date format because I'm using dd/mm/yyyy and I know Access likes to use mm/dd/yyyy but I have fixed that.

Then I thought that the amount of data was too much but when I run the same query from 01/09/2004 to 31/05/2005 (i.e.more records) it runs fine. I've been trying different dates and a very strange thing happens e.g. it will run if the start date is 29/09/04 but not if it is 30/09/04???? I am using the following expression in the criteria section:

>=Format([forms]![frmSalesCriteria].[StartDate],"dd-mmm-yyyy") And <=Format([forms]![frmSalesCriteria].[EndDate],"dd-mmm-yyyy")

Any suggestions?

Many thanks,

GPM

 

[PHV]

What about this (in the SQL view):
PARAMETERS [Forms]![frmSalesCriteria]![StartDate] DateTime, [Forms]![frmSalesCriteria]![EndDate] DateTime;
SELECT ...
FROM ...
WHERE ...
AND ([yourDateField] Between [Forms]![frmSalesCriteria]![StartDate] And [Forms]![frmSalesCriteria]![EndDate])
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JerryKlmns]

Hi GPM,

Between Format([forms]![frmSalesCriteria].[StartDate],"yyyy-mm-dd") And Format([forms]![frmSalesCriteria].[EndDate], "yyyy-mm-dd")

should be better for your criteria. Both dates are included in the result set concerning Jet Engine.

 

[RoyVidar]

Now, where on earth did I see something about declaring the parameters, and getting rid of the formatting of the control references within a stored query, let me see, oh yeah - here thread702-1080735, wasn't it...

Should you still have problems, I'd consider dropping the input masks, and perhaps do take a look at the section I recommended in the link...

Btw - JerryKlmns, did you see I updated the faq?

Roy-Vidar

 

[GPM4663]

Hi Everyone,
Thanks for all the input, I really appreciate it. The problem turned out to be an expression I was using in the query that for some reason had an error in it that worked sometimes and not others - I don't know why. I did try each of your suggestions and they do work well in the sense of ensuring the correct date format but that didn't seem to be the problem. As Roy-vidar pointed out I did have an issue with that earlier today but had managed to correct it.

Roy-vidar,
I don't want you to think I was asking the same question twice. The issue with the date formats from the form you had helped me with perfectly but the continuing problem with the query seemed to be something different - especially since a date like the 29/09/04 would work and the 30/09/04 didn't. That's why I posted a slightly different question in the query forum. Would hate for you to think that I had been ignoring your good advice.

Thanks to everyone on this.

GPM