Please Help! Very Stuck!

[neileg]

There's a few oddities here!

I don't understand why you are using a macro to open your forms when you can simply use a form open instruction on your switchboard! Never mind, that's not what you asked about.

What a strange way to set up your forms. Why haven't you based your forms on queries? Then passing your dates from the input form to the underlying queries would be a doddle.

I don't understand how your code is supposed to work. I don't know where to start helping you, so I'll just watch this thread and see what others say.

 

[Tezcatlipoca]

"What a strange way to set up your forms. Why haven't you based your forms on queries? Then passing your dates from the input form to the underlying queries would be a doddle."

I've never really understood how the whole queries thing works, and was advised that an easier route was to set up a search box as a form. Guess not.

 

[neileg]

Mmm....

Really, queries are the whole reasoning behind a relational database. They allow you to hold the data in convenient tables and bring it together to display to your users. If you haven't really understood how the whole queries thing works then you need to before you go any further with databases!

Your advice about using forms is OK as far as it goes. I would be using a form too, but using the dates you capture and passing them as parameters to a parameter query.

As I say, I'll wait to see what others have to say.

 

[Tezcatlipoca]

The label on the form is, sure. But the actual name of the box used to contained the Course Start data should be START. It is on my copy.

Edited to say: Ah, I see what you mean. The table field is Course Start. I also tried [Course Start] in my original code that you gave me, but still got a type mismatch error.

 

[Tezcatlipoca]

Right, now I'm thoroughly confused. Taking out a comma works in as much as it will allow me to enter dates and, clicking on Search form, *seems* to filter records. I say seems, because the dates on the filtered records bear absolutely no relation as far as I can see to the dates entered on the form. for example, I tried running from the 21st March to the 13th July. 24 records out of several hundred were filtered, but many of them included dates outside this range.

My biggest concern is not that it's not working, so much as I don't see which variables it's using to filter these records, since it's clearly not the date range.

Help!!!!



Edited to say: I've also noticed that if I just type in a quick date (let's say I type "3 march"), then enter the second date in a similar fashion, Access automatically turns it into the proper UK date format for me (i.e. dd/mm/yy), but clicking on Search brings up an empty form with no filtered records. However, entering the dates completely (i.e. typing "03/03/03" for the first box), and then clicking Search produces the filtered records with the same problems as outlined above. I don't know if this has a bearing on the problem.

 

[Rich]

I gave you the answer, or at least part of it, you have to use the Format function to change the date to the US format

 

[Tezcatlipoca]

There is no option of my Format menu to change to a mm/dd/yy setting. All three of my date options (General/Long/Short) are set to the UK standard format for giving dates.

Surely if the whole database is formatted to UK dates it should work, given that it's only making column and data comparisons?

 

[Rich]

stLinkCriteria = "[Course Start] Between #" & Format(Me.Fromdate,mm/dd/yyyy) & "# AND #" & Format(Me.Todate,mm/dd/yyyy) & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Tezcatlipoca]

Inserted your code. I now get an "Overflow" error box.

 

[Rich]

Post a copy of your db here in 97ver, remember to compile and save all Modules and compact the db before zipping it. One of us will take a look at it for you

 

[Tezcatlipoca]

I've posted a copy of my database on the last page (seond to last reply). It's in 2000 format as that's what I'm using. Can it not be converted after you download it?

 

[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?