Please Help! Very Stuck!

[Rich]

It's much easier to base your form on a query and set the criteria there to reference the form fields.
To use the str criteria on date fields is far more complex, I don't use the between operator but here's an example which uses two conditions, one the CustID and the other a date field.
If you live outside of the US then you also have to to return the dates in the US format, something like
stLinkCriteria = "[Customer]= " & "'" & Me.List23.Column(2) & "'" & "And [Issued]= #" & Format(Me!List23.Column(1), "mm dd yyyy") & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Tezcatlipoca]

Sorry, Mike-o-Phile, I was typing my last post as you posted. Unfortuantely, your code still returns a "Type Mismatch" error.

 

[Mile-O]

Any chance of a sample db being posted?

 

[Tezcatlipoca]

Sure. I'll have to take out the actual records, as the Data Protection Act in my industry prevents me givening them out, but I'll post a copy of the database with some false records in here in a few days.
Actually I'm convinced that you guys will be able to see where I'm going wrong in a matter of minutes once you get the database as I have it set-up.

 

[Pat Hartman]

If you are getting a type mismatch error one of the three fields is NOT a date. Is Start defined as a date?

Your other problem will be because you are not using standard US date formats. There have been a gazillion posts here regarding this problem.

Pound signs (#) are required ONLY to surround literal dates. They are not necessary when the date is contained in a variable.

 

[Tezcatlipoca]

Hokay, here is a copy of the database I've built. I've removed the actual records and identifying title information on the switchboard, but the actual layout is exactly the same as the one I'm using, as are all the forms and commands.

As you can see, this database is designed to hold course details about various students, the two important columns in question being Course Start (START column I mentioned earlier) and Course End (END column).

To recap, FORM1 is the popup search box (or rather, it should be when this thing starts working). The two boxes should allow the user to enter two dates between which the records will be filtered. Bear in mind this is a UK database, so the dates are setup to the "dd/mm/yyyy" format, and should be kept that way.

Please don't play with any of the other coding when tinkering with this database as everything controlled by the switchboard (with the obvious exception of the 'Filter Records by Date') are working fine on my systems and I'd really like to keep them the way they are. I don't know if it makes any difference, but I'm running Access 2000 on a Windows 98SE OS.

Thanks in advance for your advice/help!

 

[Tezcatlipoca]

Ahem...it..er..would of course helped if I'd attached the file. Here it is.

 

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

 

[Pat Hartman]

The name of the table field is NOT [Start] it is [Course Start].

 

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

 

[Pat Hartman]

You have too many comas in the OpenForm statement. I stand corrected on the pound sign (#) statement I made earlier. You do need them in this context. The following works on my PC. However, my date setting is US so you still may have trouble. If you do, search the archives.

stLinkCriteria = "[Course Start] Between #" & Me.Fromdate & "# AND #" & Me.Todate & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

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