|
Subject:
|
Access help
|
|
Posted By:
|
Roe2000
|
Post Date:
|
8/21/2006 2:41:26 PM
|
Im trying to create a Filter or should i say search button so that i can search between certain dates within my database.... the thing is im very new to access 2003 and need just to know what im doing wrong.
I have a database with 20 fields were i need to input a date, what im trying to do is get it so that i can search for all the dates that come between, lets say 01/01/00 and 01/01/05 now i know that for sure that i have dates between them dates,
also i need to have it so that the customers name and customer come up on the reports sheet..... any ideas would be great, or even more questions about this post.
Cheers
Lee
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
8/22/2006 6:51:12 AM
|
How are you generating the reports?
Usually you can put criteria right in the query behind the report that checks the dates on your form itself.
"BETWEEN [Forms]![frmMyForm].[StartDate] AND [Forms]![frmMyForm].[EndDate]"
This will work fine for the report, but the form must be open for it to run, and dates must be selected. You can do data validation on the report button like this:
If IsNull(Me.StartDate) Or Me.StartDate = "" Then
MsgBox("Please enter a start date")
Exit Sub
End If
Check for the end date the same way and then run the report.
You can also pass the start and end dates yourself to the report like this:
'----------
Dim dtStart As Date
Dim dtEnd As Date
Dim sLink As String
Dim sDocName As String
If IsNull(Me.StartDate) Or Me.StartDate = "" Then
MsgBox("Please enter a start date")
Exit Sub
dtStart = Me.StartDate
End If
If IsNull(Me.EndDate) Or Me.EndDate = "" Then
MsgBox("Please enter an end date")
Exit Sub
dtStart = Me.EndDate
End If
sLink = "[Date] BETWEEN " & "#" & dtStart & "# AND #" & dtEnd & "#"
DoCmd.OpenReport sDocName, , , sLink
'---------------------
Or something similar. I would have to mess around with the syntax a minute. Make sure the sLink is in the WHERE clause section of the OpenReport or OpenForm DoCmd.
Does this help?
mmcdonal
|
|
Reply By:
|
Roe2000
|
Reply Date:
|
8/24/2006 3:41:15 AM
|
I will give this script a try and let u know how if it works, many thanks, keep in touch as i am a newby with access, finding it difficult but im sure i'll get there....
Thanks again
|
|
Reply By:
|
Roe2000
|
Reply Date:
|
8/24/2006 3:48:43 AM
|
Also if u didnt mind i could send u the database for u to look at, or at least an image to show u where the date fields are, there are 20 fields where i input a date for each customer and i have a thousand or more customer but each of the 20 dates would be different....
i will at least give the script first a try....
|
|
Reply By:
|
Roe2000
|
Reply Date:
|
8/24/2006 5:37:03 AM
|
Just had a try of the script u sent for me.... it works for a single fields, but if i put the "Between [Forms]![frmMyForm].[StartDate] And [Forms]![frmMyForm].[EndDate]" in criteria for all the fields that have dates in. it does not find any or doesnt work at all. it seems to be confused maybe, not sure.
I need to be able to get the dates from all records and able to view them through the query filter, normally i think u should be able to enter the same string into the criteria for all fields that have dates in them... or do i have relate the remainder fields with the first date field...
many thanks for helping, its very much appreciated
lee
|
|
Reply By:
|
Figgis
|
Reply Date:
|
8/24/2006 8:54:14 AM
|
Lee,
If you are searching 20 different fields for particular dates I dont think that a simple query would work. For most queries on a date you are only working with one or two dates. If you enter the criteria in every field then it would only return records that match ever field. Can you explain what you are trying to achieve with it?
Fig
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
8/24/2006 10:21:46 AM
|
That statement must be correctly parsed. That is
"Between [Forms]![frmMyForm].[StartDate] And [Forms]![frmMyForm].[EndDate]"
must be
"Between #" & [Forms]![frmMyForm].[StartDate] "# And #" & [Forms]![frmMyForm].[EndDate] & "#"
And if your query source is the same as the form, then you can even state
"Between #" & [StartDate] "# And #" & [EndDate] & "#"
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
Reply By:
|
Roe2000
|
Reply Date:
|
8/24/2006 11:34:22 AM
|
Basicly i have a database with customers, each customer has Maschines of particuler kind... So 20 fields for the maschines 20 fields for the modle type (bosch, B&B) etc and each maschine needs servicing so i created 20 fields for the dates to, every two years the maschines all need servicing could be 2006 and onwards so my aim is to sort through these dates and create a report for lets say all maschines that need servicing in 2008 so id need to see which firm, which maschine, and the service date in 2008.... now i have got it so far to sort these dates out but not into a report, yes i still see the other dates there included but thats not so bad. the main thing is its sorted the customers out from the 1000 there is down to how ever many with the dates 2008, i just need to have that in a report form.
many thanks for all replys
Lee
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
8/24/2006 11:39:52 AM
|
Do you actually mean 20 FIELDS or do you mean that you have ONE field for machine, ONE field for type, ONE field for date, and you have 20 customers? In that case you have 20 RECORDS, not 20 FIELDS. It's important to know the difference and the jargon so we can help you better.
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
Reply By:
|
Roe2000
|
Reply Date:
|
8/25/2006 4:59:02 AM
|
what i said b4 was correct 1000 customers each having 20 fields for machine, modle and date
yesterday i had it working to a point were my computer then crashed and for some reason would not work again
|
|
Reply By:
|
Roe2000
|
Reply Date:
|
8/25/2006 6:03:48 AM
|
>=[forms]![frmReports]![txtDateFrom] And <=[forms]![frmReports]![txtDateTo]
this is the string i use for the query, so it works if i put it in on of the date fields to query, now if i put it in all the date fields it querys then nothing and show nothing now i had this working yesterday and all of sudden its not working now... i cant understand that as it worked and at least showed me the custmers within the date fields i queried...... man this is proofing to be a pain in the ass
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
8/25/2006 7:02:04 AM
|
Hmmmm... judging from your last two posts, it suspect that your table is not normalized and you're not understanding placement of criteria.
If you have fields such as this Machine01, Machine02, ... , Machine20; and Type01, Type02, ... , Type20; Date01, Date02, ... , Date20, etc. then you're not normalized. You just need one field each called, say strMachine and strType and dtmDate, and another field called bytMachineNo (this one can be set to a number from 1 to 20).
Criteria need only be placed once in a query, not for "all the dates" as you suggested. Not knowing the structure of your table, query, and form will make it hard to solve cleanly... given my suspicions.
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
Reply By:
|
Roe2000
|
Reply Date:
|
8/25/2006 1:19:20 PM
|
The thing is each company owning these machines could have upto 20 plus i have to use a combo box thingy with the list of machines, if u dont mind i could send u the file over and u could then get a better idea of what im looking at....
be great if so
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
8/25/2006 1:26:30 PM
|
What Greg is saying is that your table structure should look like this:
tblCompany
CompanyID - PK
CompanyName - text
Address1 - text
Address2 - text
etc
tblMachine
MachineID
MachineName - text
etc
tblType
TypeID - PK
TypeName - text
etc
tblJunction
JunctionID - PK
CompanyID - FK (combo box)
MachineID - FK (combo box)
TypeID - FK (combo box) (could also be in Machine table)
iDate - Date
This would allow you do have a company with a million (n number) of machines without having to redesign the table. What if a customer gets their 21st machine? In your design, you redesign the table. In this normalized design, you add another record. Always design for n records.
mmcdonal
|
|
Reply By:
|
Roe2000
|
Reply Date:
|
8/26/2006 1:37:25 AM
|
Whats PK, FK mean, sorry im very new to access and need maybe complete tuition....
but for all your help its great gives me ideas and then i can try and put something better together
many thanks
|
|
Reply By:
|
echovue
|
Reply Date:
|
8/26/2006 9:41:43 AM
|
PK is used to designate a Primary Key, and FK is used to designate a Foreign Key.
In a relational database, you might have a table of employees, and another table with multiple records that would be linked to that employee. In the employee table you would set up a PK field that contains a number (typically an autonumber) and then in the other table, instead of entering the employee name, you just enter the PK associated with them in the FK field. Designing your database like this dramatically decreases the size, and increases the performance of your database.
Hope that helps,
Mike
Mike
EchoVue.com
|
|
Reply By:
|
Roe2000
|
Reply Date:
|
8/26/2006 11:29:22 AM
|
Helps greatly, thanks
|
