Query criteria "Between " dates

[kev747]

hi all,
I have a form that I use to search for records by various fields in my main table.
One of these search options is for records between certain dates.
On the Frmdatabasesearch form I have two text boxes - Txtstartdate and Txtfinishdate, and a Search Now command button.
The user enters the start date and finish dates for the required period of time, then clicks on the Search now button. This opens the Frmsearchbydaterange form, which is populated by a query.
Here's the problem - running the query by itself works fine, but if I try to run it through the frmdatabasesearch form, I get the following error

"Run-Time error '2501'
The OpenForm action was cancelled.
You used a method of the DoCmd object to carry out an action in visual basic, but then clicked Cancel in a dialog box".

The critera in my query is -

Between [forms]![frmdatabasesearch]![txtstartdate] And [forms]![frmdatabasesearch]![txtfinishdate]

Any one got any ideas whats wrong with this?

I can open the frmsearchbydaterange form by itself, and enter the dates in the pop-up input boxes, and it works fine, so I'm guessing that the problem might be in the opening of one form from the other. Maybe this thread should be in a different forum.

If I don't enter any dates, the form opens, but with no data (obviously).

any help is greatly appreciated.

Thanks in advance,

Kev.

 

[scriverb]

Cordury: You posting states that you are being prompted once for each of the prompts. That is what we wanted to do. So that code is working. Now the recordset being returned has to do with the join and your table design.

Give us some examples of the records that are being returned. Are the dates within the range? If they are what exactly is the problem with the records that are returned.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

Sorry,

WHERE (((TblLostTimeInjuries.[Report Initiation Date]) Between " #" & [forms]![frmdatabasesearch]![txtstartdate] & "# " & And & " #" & [forms]![frmdatabasesearch]![txtfinishdate] & "# &quot);

Let me know.



Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[kev747]

Sorry Bob, still getting the same syntax error with the new SQL.

Kev.

 

[dhookom]

If this is the sql view of a query, I would suggest you get rid of the "#"s.
WHERE [Report Initiation Date] Between
[forms]![frmdatabasesearch]![txtstartdate] And
[forms]![frmdatabasesearch]![txtfinishdate];

How did we ever get to another query? Apparently there are a couple issues both going here.



Duane
MS Access MVP

 

[kev747]

Many thanks to Bob, John and Duane, but I'm starting to think that the problem may lie elsewhere. Something to do with the opening of the form.

Time for a coffee and a rethink I suspect.

And your right Duane, there are two issues at work here.
Who hijacked my thread???

Thanks again,

Kev.

 

[lespaul]

I think that kev747's query is missing a " before the AND and after the &

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[SpankYou]

Hey,

Just thought that I'd add to this don't know how relevant it is but it's to do with dates and queries. bAsically if you have a query for example...

Between [forms]![FrmEnterDate]![TxtDate] And Date()

This should search between the date you enter and todays date, but it doesn't, to get this working you have to switch the parameters round...

Between Date() And [forms]![FrmEnterDate]![TxtDate]

Now this will search from todays date to a past date you enter (in my case in a text box). Now I know this seems very backwards, but it works If this helps anyone then good. If not possibly best to keep in mind.

Thanks

Sam

 

[scriverb]

Kev747: Let's backup here. Your WHERE clause statement is just fine in your query.
Between [forms]![frmdatabasesearch]![txtstartdate] And [forms]![frmdatabasesearch]![txtfinishdate]
The error that you originally posted has nothing to do with the queries SQL. It has to do with the code behind your button or in the form that you are opening. Please post your code behind the button and then look through your form being opened and look for code in the OnOpen or OnLoad event procedures that could be causing the problem.

When you the error does the focus go back to a VBA code and highlight a line? If so indicate the line that shows up as Yellow Highlight. If not, try filling out the form with the dates and then minimize the form. From the database windows then select the form and try to open it. See if the error occurs during this opening.



Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Cordury]

Bob,

Thanks again for the response. Sorry it took me so long to reply-- CRAZY MORNING.

Anyway, The prompts are returning the records within the required dates. I was just hoping that the end user could enter one Start Date and one End Date and the query would search for all records that fall between those two dates in either field.

One more thing I noticed is if I have the Sigma "Totals" button depressed in the design view of the query- It seems to eliminate the duplicate store #'s but will not return the comments field (text field).

 

[scriverb]

First, you still should only get prompted once for each date( start and end ) if the prompting info is exactly the same. I don't see anyway for it to prompt twice. If it is there is still a problem. If you can't find it you can send me a sample database tables, forms, queries that relate to this problem. See my profile for email addr.

Second, the Totals button in the query design should only be used when you want to group the records by certain fields thus creating less rows. But, this has to be done correctly so that you do get the data that you want.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[kev747]

Ok,
As there are two issues in this thread, I'm going to end mine to stop the confusion (at least, I'm geting confused).

Bob, you are right. After a bit of investigative work, the problem exsisted on the form, not with the query. Although I couldn't find the exacy cause, I fixed it simply creating a new form (luckily it was only a very simple form).
Many thanks to yourself and the others who've posted replies. I did learn something which is the main goal, right?

Kev.

 

[scriverb]

That's right. Glad that I could first confuse you and then help you unravel your confusion.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???