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.

 

[jrbarnett]

Try this instead:

Between "#" & [forms]![frmdatabasesearch]![txtstartdate] & "# And #" & [forms]![frmdatabasesearch]![txtfinishdate] & "#"

John

 

[scriverb]

kev747: jrbarnett's answer is correct and what you should do when using date parameters from a form. I have seen this same problem many times here in TT. I just wanted to explain why this works and the direct reference doesn't. When you enter the date parameters in your form you are entering them in a date format but the characters are just a string representation of the date value that access needs. So, by bracketing the string date representation in # signs you are converting them to date values that the between clause can use. You could also use CDate function to do the same thing.

I hope this helps you and others understand how access handles these parameters.

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

 

[Cordury]

I was wondering if I could add to this question?

I have a simple query that uses two tables and is linked by StoreNumber. tblOne has two sets of dates; "Possession" and "Actual". The user needs to pull all records within a certain date range for both date fields (the same date range). Is it possible for the user to enter the date range just once or do I need to add a Between function in each criteria?

 

[scriverb]

Yes, just use the square bracket user prompt method(i.e. [Enter Begin Date: ] [Enter End Date:] ) and make sure you use the exact same phrase in your query for both tables. It will only prompt once for each date and will pass that value on to the other prompts.


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

 

[Cordury]

Thanks for the quick resposne, Bob. I entered: Between [Enter Begin Date: ] And [Enter End Date:]in the criteria for each date field and now it is returning zero records?

 

[scriverb]

I am leaving now for a couple of hours but if you send the SQL for your query I will take a look at it when I get back. In the meantime maybe MichaelRed or someone else could help you. My inclination is that with the join between the two tables there just doesn't happen to be dates that match the criteria. Thus the empty record set. But, i would have to see the query before making a final determination.

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

 

[dhookom]

Post your SQL so we have a clue as to what you are doing.

Duane
MS Access MVP

 

[Cordury]

Thanks for the help! The query is now returning records but some are duplicates and it is still asking for the dates twice?


SQL Statement:

SELECT [PTrak X].StoreNum, [PTrak X].City, [PTrak X].State, [PTrak X].Subdivision, [PTrak X].ProjectType, [PTrak X].ActualPossession, [PTrak X].ConStartActual, Construction071503.Comments, Construction071503.[Poss Date], Construction071503.[Const Date]
FROM Construction071503 INNER JOIN [PTrak X] ON Construction071503.StoreNum = [PTrak X].StoreNum
WHERE ((([PTrak X].ActualPossession) Between [Start Date] And [End Date]) AND (([PTrak X].ConStartActual) Between [Start Date] And [End Date]))
ORDER BY [PTrak X].ConStartActual DESC;

Thanks,
Cord

 

[dhookom]

Duplicates are usually caused be a JOIN that is a many to many where a one to many is expected. Does one of your FROM objects (Construction071503 or PTrak X) have a criteria using [Start Date] and [End Date]?

Duane
MS Access MVP

 

[Cordury]

Duane,

The Between '[Start Date] And [End Date]'criteria is on both the ActualPossession and the ConStartActual field from the PTrak table as you have probably already noticed from the SQL.

There is no criteria for any field from the Construction071503 table.

I am not sure if I answered your question?

 

[dhookom]

If the parameters are spelled exactly the same, you should not be asked twice. Are either Construction071503 or [PTrak X] queries? Are you being asked twice in the query or once in the query and once in a report? Are the prompts spelled exactly the same?

Duane
MS Access MVP

 

[Cordury]

I copied and pasted the prompts so there should be no issue there with typos.


Both Construction and Ptrak are tables.


The prompts are being asked as soon as the query is run. I have no created a report yet, I wanted to wait until the query was running properly.

 

[scriverb]

Cordury: I am back now. Are saying that the query is asking for both dates twice? Or just two prompts one for StartDate and one for EndDate? I see no reason from your SQL that the query should prompt you twice for the same value. They seem to be spelled correctly.

I just used your WHERE clause code with a table of mine with two dates and just changed the table and date field names. Ran the query and was only prompted once for each date prompt.

Something else is at play here for you to be getting 4 prompts.

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

 

[kev747]

Thanks John and Bob for the help, and the explanation of how it works. Very useful.

I've struck a problem though.
When I enter the criteria exactly as John has written it, I get an error message when trying to save the query.
The message is:
You did not enter the keyword And in the Between....And operator.

Does it have something to do with the quotation marks?
Don't really know much about this.

Thanks again,

Kev.

 

[dhookom]

Your SQL as posted didn't have any quotation marks. If you have changed your SQL and are having problems with it, you should post your current SQL for all to see.

Duane
MS Access MVP

 

[Cordury]

Hi Bob- welcome back

I am being prompted once for the Possession date and once for the Actual Construction Date. I would like for the end user to enter one Start Date and one End Date and for the query to pull all records that fall within those two dates for both fields.

Sorry about the confusion.

 

[kev747]

Duane,
Here is the SQL for my query.
Can you see any problems with this?

SELECT TblLostTimeInjuries.[Record Number], TblLostTimeInjuries.[Work Area], TblLostTimeInjuries.[LTI reported by], TblLostTimeInjuries.[Report Initiation Date], TblLostTimeInjuries.[User Staff Number]
FROM TblLostTimeInjuries
WHERE (((TblLostTimeInjuries.[Report Initiation Date]) Between "#" & [forms]![frmdatabasesearch]![txtstartdate] & "#" And "#" & [forms]![frmdatabasesearch]![txtfinishdate] & "#&quot);

Kev.

 

[scriverb]

Hey this is getting confusing. Two problems walking on top of each other.

Kev747: You need a space before the first # sign and after the second # sign for each instance. See Below.

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

I think this should fix it for you.


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

 

[kev747]

Fast reply, thanks Bob.
Still getting a syntax error though (missing operator).
I've checked all of the names and all seem correct.
When I click ok on the error pop up box, the And in the criteria is highlighted.
Could the problem be there?

Kev.