Please Help! Very Stuck!

[Tezcatlipoca]

Hokay, firstly my sincere apologies for being a complete newbie at this kind of thing, but I'm having real trouble getting certain funtions working on a database I'm trying to write for work.
To give you some background first, I'm running Access 2000 on a Windows 98SE machine.

I have a database which logs various customers, consisting of the following:

2 x Tables (one holding the coredata of all our customers, the other listing the switchboard items)
2 x Forms (one aesthetically pleasing form which displays the coredata, one switchboard - linked to the coredata table)
3 x Macros (three commands used by the switchboard to view, edit, or add records to the coredata file)

Most of the database has been written and, pleasently surprisingly, seems to work fine, but I'm having trouble adding the following desired functions:


1) I'm trying to get a filter up and running, primarily so I can print out certain customer details who are to defined by the date when they purchased our product. I have a date field in both my Table and Form, but need to setup a button on the Switchboard which will allow me to enter a range of dates and result in showing just those records which have dates between the two figures entered. As a sub required to this, I need to be able to click a button (on the form I assume) that will allow me to print out this filtered record. I think my solution lies in doing something with queries, which I've played with, but can't get to work properly.

2) This is probably a stupid question, but is it possible to finalise the database in any way, such that you don't need to load it into Access? What I'm aiming for is a - preferably single - executable file which contains all the data, and can continue to be used (i.e. records added, etc.).

3) I have a record navigation (find keyword in records) button on the layout of my actual Form, but would like to move it to the Switchboard. Unfortuantely, whilst I can write a button for it, everytime I press it when the database is live, I get an error message telling me that it cannot find coredata1 (the name of my Table which contains all the information). Linked to this problem, is it possible to create a button which acts like a search for keyword funtion, regardless of where it is in the database? At the moment, my Find just opens the ordinary 'find' option, which necessitiates the setting of the field in which to search, and whether complete or any part of the chosen field. I'd rather have a single button which brings up a box so you can enter any keyword and the database will recall the record(s) contain that word, regardless of the field in which that keyword appears.

4) Finally, I have had to have added a new field, which Access seems to now be treating as the primary key. How can I change the primary key back to the original field I had before?

I'm sorry for asking what I'm sure are stupidly simple questions, but when you don't have the answer it's understandly frustrating!

Thanks for reading the post.

 

[ColinEssex]

Before we go any further.

You say you have one table for customer details. What happens if a customer purchases more than one product - are they then entered twice?

Can a customer purchase more than one product?

Col

 

[Tezcatlipoca]

No. The customers only ever purchase once, so only one record exists for each. In the unlikely event they pruchase again, a new record (with the new purchase date, time, etc.) is simply added to the table.

The result is that the database doubles as a record of all the old transactions, as well as the current ones.

 

[Tezcatlipoca]

No-one can help?

 

[Tezcatlipoca]

Can somebody - anybody - please help with this, or at least point me to somewhere I can get help. Being able to filter records effectively is very important in this database, and currently we are having to go through all 234 records manually and do single page prints of each one for the date(s) we want.

 

[Tezcatlipoca]

Re: Queries 1 - 3, thanks a lot. You've been a great help and I've managed to resolve nearly all of my queries so far.

Re: Query 4, I'm still experiencing problems. I have created a separate form (calling it datelist), which consists to two unbound text boxes. I then go to my switchboard and make a button to open the form. At the moment I'm sleecting OpenForm in Edit Mode (should I be choosing the OpenForm in Add Mode instead?).
When I click the button, my form comes up, and I can enter data, but do nothing else and no records are called up or filtered.

I have tried pointing the Switchboard button at a Macro, and writing a Macro to open the form, but I'm afraid I do not understand any of the WHERE command business, which I'm beginning to think is absolutely essential to filtering records.

Let me give you my procedure so far:

I have a separate form which is designed for users to enter two dates, between which all the relevent records should be filtered.
I have added a Switchboard button which points at a Macro (mac-dates)
Mac-Dates has the OpenForm command as it's first instruction. How do I set up this WHERE command code that will filter the records? I'm assuming I have to do something like:

[datelist (my form which is used to filter the records)][textbox0 (the FROM date)] = OR > [coredata (the main table of data)][date1 (the field containing the first date)]
[datelist (my form which is used to filter the records)][textbox1 (the TO date)] = OR < [coredata (the main table of data)][date2 (the field containing the second date)]

I'm fairly certain this is completely wrong, and I'm also a little confused because from what I can see, this method is a command to open the filtered records in the search form (the one with only 2 unbound text boxes).

Please help. It is vitally important our business gets this filter function working, but I have no idea how these WHERE commands and strings work.

 

[Tezcatlipoca]

I understand what I need to do (I think), but do not understand how to do it. All I get is errors when I try to do the WHERE argument thing. Is the code I gave in my last post correct? And what is an OpenForm Method of Filtering Data?

Following your advice I can get the Switchboard to open my unbound search form, I can enter data in the two boxes, but can do nothing else (I can't get it to actually perform the filtering option). Hitting Enter does nothing. The form just sits there like an ordinary record with the dates I've just entered in the boxes. Is there a 'Go' or 'Filter' button I have to add to the search form?

 

[Tezcatlipoca]

Hokay, let's see if I can get this right:

I have created a small form (form1), with two unbound boxes (called Text0 and Text2 respectively). I have also created an Open Form button on form1 which has the following code attached:


****CODE STARTS******************************

Private Sub Command6_Click()

End Sub
Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "coredata1"
DoCmd.OpenForm stDocName, , , , " stLinkCriteria"

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click

End Sub

****CODE ENDS******************************

A button on my Switchboard opens form1, the text on which prompts the user to enter the two dates between which the records should be filtered.

At the moment, entering any dates (or indeed any information) into my boxes, and clicking the button opens a copy of my my data form, but with no records in it.

I understand from the last post that I need to define the WHERE argument in the code, but am a little uncertain as to the exact wording. In the "SomeDate Between " section of the above code, do I put the name of the FROM unbound text box (Text0) here, or do I substitute the FromDate part of the line 'Me.FromDate' to the name of the FROM text box (Text0)?

Incidentally, I'm really sorry for the wave of unforgivable newbieness, but this thing is as interesting as it is frustrating, so I'd like to get it right to ensure I know how to do this in the future.

Thank you.

 

[Tezcatlipoca]

Right, I've done as per instuctions and I'm getting an error box popping up which says "Type Mismatch".

To recap, I have FORM1 (my form which pops up asking the user to enter two dates). There are two unbound text boxes on this form, named FROMDATE and TODATE respectively. Both of these are formated to GENERALDATE entry, to ensure the user enters information correctly.

On my general search form (COREDATA1), I have two date boxes (START and END respectively), which show the date when the product was purchased and the date when the product expires. Both these boxes are formated to the GENERALDATE setting. To recap, my search box has to filter records between AND including the two dates entered by the user.

The Search button on FORM1 has the following code:

***CODE STARTS******************************

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "coredata1"

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

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

***CODE ENDS*************************************

With this setup, I can enter dates into my search box, but everytime I click Search, I get the error message "Type Mismatch". In your kind reply to my query, you mentioned a date column singular. I have two date columns (START and END). Does this have a bearing on my code?

Thankyou in advance for your help.

 

[Tezcatlipoca]

I think you mean that my problem lies in the following line:

stLinkCriteria = "[Start]=" & Me.Fromdate & " AND " & Me.Todate

and that it lies, specifically, in the sqare brackets and equals sign that are contained in the first set of quotation marks. I've tried removing them, leaving the code looking like this:

stLinkCriteria = "Start" & Me.Fromdate & " AND " & Me.Todate

but I still get the same "Type Mismatch" error box. I'm sorry, I just don't understand what I'm doing wrong.

 

[Tezcatlipoca]

I'm still getting the "Type Mistmatch" error. I just don't understand what I'm doing wrong. My Open Form button (on FORM1 (the search box)) now looks like this:

***CODE STARTS***********************************

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "coredata1"

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

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

***CODE ENDS********************************


Is it because I've formatted both my two date (TO and FROM) columns on the main form and the two text boxes (TODATE and FROMDATE) on the search form to 'General Date' format?

 

[ColinEssex]

Could it be the StLinkCriteria?

You've Dimmed it as a string - should it be a date?

edit - thinking about it now - maybe not

Col

 

[Mile-O]

Try this line:

stLinkCriteria = "[Start] Between #" & Me.Fromdate & "# AND #" & Me.Todate & "#"

 

[Tezcatlipoca]

My other slight concern is that when (if?) I get this working, it'll only search one date column (the START column), since the code mentions nothing about search the END column. Surely this would mean that the form will only filter between two dates that appear in the START column of my records, where I need the code to filter everything between dates in the first two columns.

Actually, another thought has just struck me. I need users to be able to enter ANY dates into the search boxes, and have the records between those dates filtered. In my admittedly limited understanding of Access, surely the search code will only look at the dates which are present (i.e. have been entered), and ignore those that have not.

For example, I want to filter my records by date. Let's say that my user is going to enter the dates 01/03/03 (FROM box) and 04/05/03 (TO box). Now let's say that there are 3 out of my 100 records that fall between - but none on - the dates which are to be used. Record 1 has 05/03/03 in it's START date column. Record 2 has 21/03/03 in it's END date column. Record 3 has 19/04/03, also in it's END date column.

Now am I right in thinking that entering 01/03/03 in the FROM box, 04/05/03 in the TO box will fail because none of the records in either the START or END columns match these dates? If so, do I need to employ some kind of calendar coding to force it to look between dates???

 

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

 

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