Query not working with variable date from main form

[jodo]

I have a query that pulls data from a table that has 5.7 million rows. The query has a date field (DTM) among several other fields that I pull. The DTM field has a criteria string that looks like this:

Between [forms]![frmMain]![StartDate] And CDate([forms]![frmMain]![FeederEndDate])

It is used to look at what the user entered for the date on the Main Form for use as it's criteria in the query. However, I have jobs that run on the server at 30 minutes passed every hour that takes about 20 minutes. During that 20 minute time frame, the query doesn't work. If I substitute the user data with hard data like, (Between #5/21/2008# And #5/22/2008#) it will work every time.

I get error code 3146 when it times out with the user data. I've researched this error and there's nothing really set in stone as to what it is and why it is happening.

Why won't my query work using the criteria expression Between [forms]![frmMain]![StartDate] And CDate([forms]![frmMain]![FeederEndDate]) when my jobs are running on the server?

Please Help!!

Thanks.







jodom
LUCK - WHERE PREPARATION MEETS OPPORTUNITY!!

 

[Golom]

I'm not at all sure but you could be running into problems with dynamic conversion of the text field from the form on each of the 5.7 million records. Try forcing the conversion

Between [red]CDate([/red][forms]![frmMain]![StartDate][red])[/red] And CDate([forms]![frmMain]![FeederEndDate])

 

[jodo]

I missed that inital CDate on the original post, but it was in there. It still doesn't work. I've been running this application since 2003 and haven't had any issues until a couple of months ago when the users were pulling data when the jobs on the server were running.

Any other suggestions.

Thanks.

jodom
LUCK - WHERE PREPARATION MEETS OPPORTUNITY!!