Please Help! Very Stuck! (1 Viewer)

[Rich]

I don't have that version, I can't convert it

 

[Tezcatlipoca]

Here is the database, converted to Access 97.

 

[Rich]

Yes I have to admit I rec'd the same error, I don't know why in this case, I've cheated and used the easy method, just based the form on a query.

 

[Tezcatlipoca]

Thanks for your help, but I'm getting to point of thinking this problem is impossible to achieve. I downloaded your code, and exported everything exactly as you'd done it (with the exception of the original table of records, obviously) into my databse. I can open the search box (form1), can enter dates, but clicking the button now just opens up by ordinary form without filtering any records. :-(


Edited to say: Ah, hang on. My stupid mistake. I've now *correctly* exported your forms and they work brilliantly! Thankyou very, very, very, etc., much!
Which jubilation brings me to one tiny point I did want to get working and wonder if you could help with. As stated in my earlier posts, I need the search facility to filter records between and including the two dates entered, but these dates must be from two different columns (the Course Start and Course End fields). Your code works fantastically, but only filters records from the Course Start column. This is good, but I need it to also filter from the Course End column. I don't need to setup a separate search box for this, just have the Start and End records all filtered in one go. Is this possible?

 

[Rich]

Did you import coredata1 form and delete the original?

 

[Tezcatlipoca]

Yes, I've corrected it. Thanks. I'm still querying the possibility to it filtering from two columns, as per the edit in my last post. Can I do this?

 

[Tezcatlipoca]

Ummm...anyone? To recap, I need the search facility to filter records between and including the two dates entered, but these dates must be from two different columns (the Course Start and Course End fields). Rich's code works fantastically, but only filters records from the Course Start column. This is good, but I need it to also filter from the Course End column. I don't need to setup a separate search box for this, just have the Start and End records all filtered in one go. Is this possible?

 

[neileg]

It's not clear what you want to do.

Do you want to find a course that:
1) is current at any time between the two dates
2) starts and ends between the two dates
3) starts or ends between the two dates?
4) something else I haven't thought of

 

[Tezcatlipoca]

Option 3. To give the context, the database has to be able to show which students (who are in the database long before they actually start their studies) are arriving and which are leaving between any two dates given (the From and To on form1). This is normally a weekend, given as a Friday to Monday, so I need to be able to give any Friday in the From box, and the following Monday in the To box, and have the query automatically filter records which have either those two dates, or the dates between (the saturday and sunday), appearing in their Course Start or Course Ends fields.

At the moment, the database query which Rich kindly did works brilliantly, but only filters the Course Start box, meaning I can create a list of arrivals on any given Fri-Mon, but not the leavers. It's that latter bit I need to add.

Another small point is that Rich's form filters correctly, but opens the filtered form in Add/Edit mode. It's purely a cosmetic concern, but how do I alter it to open in Read Only mode?

 

[neileg]

At the moment, the database query which Rich kindly did works brilliantly, but only filters the Course Start box, meaning I can create a list of arrivals on any given Fri-Mon, but not the leavers. It's that latter bit I need to add.
In the QBE grid enter exactly the same expression in the end date column as you have in the start date column but ensure it is on the next line down so it becomes an OR. Thus your query will pull records where the start or the end date falls between your parameters.

Another small point is that Rich's form filters correctly, but opens the filtered form in Add/Edit mode. It's purely a cosmetic concern, but how do I alter it to open in Read Only mode?
Change the form properties

 

[simongallop]

Open the form Coredata1 in design view.

On the toolbar click View / Properties

On the recordsource section at the far right is a small button with 3 dots (...). Click it

A query builder view will show

In the 5th column (Course Start) you will see some code in the criteria section. Highlight the whole string and copy

goto the 6th column (Course End) and paste it IN THE NEXT ROW DOWN (on the far left the row is titled 'Or:')

HTH

 

[simongallop]

Oops posted at the same time!

 

[Tezcatlipoca]

It works! After four pages, two uploads and repeated confusion on my part, the monster I have created lives!!!!
thanks so much to everyone who pitched in with advice. I now have a database that works exactly as it's supposed to. Virtual hugs all round