Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 21st, 2006, 02:41 PM
Authorized User
 
Join Date: Aug 2006
Location: Berlin, , Germany.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Roe2000
Default Access help

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

 
Old August 22nd, 2006, 06:51 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old August 24th, 2006, 03:41 AM
Authorized User
 
Join Date: Aug 2006
Location: Berlin, , Germany.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Roe2000
Default

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

 
Old August 24th, 2006, 03:48 AM
Authorized User
 
Join Date: Aug 2006
Location: Berlin, , Germany.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Roe2000
Default

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

 
Old August 24th, 2006, 05:37 AM
Authorized User
 
Join Date: Aug 2006
Location: Berlin, , Germany.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Roe2000
Default

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

 
Old August 24th, 2006, 08:54 AM
Authorized User
 
Join Date: Oct 2005
Location: South Ockendon, Essex, United Kingdom.
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Figgis
Default

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
 
Old August 24th, 2006, 10:21 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old August 24th, 2006, 11:34 AM
Authorized User
 
Join Date: Aug 2006
Location: Berlin, , Germany.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Roe2000
Default

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


 
Old August 24th, 2006, 11:39 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old August 25th, 2006, 04:59 AM
Authorized User
 
Join Date: Aug 2006
Location: Berlin, , Germany.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Roe2000
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
401.3 Access denied due to Access Control List cforsyth .NET Framework 2.0 8 May 28th, 2009 01:56 PM
SQL Access/ASP.NET data access issue saeta57 SQL Server ASP 1 July 4th, 2004 04:29 PM
SQL Access/ASP.NET data access issue saeta57 Classic ASP Databases 1 July 4th, 2004 03:32 PM
ADE file in Access 2000 <---> Access XP ginoitalo Access 3 April 14th, 2004 09:06 PM
Access XP VBA compatibility issues w/ Access 2000 bourgeois02 Access VBA 1 August 19th, 2003 04:14 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.