Solved

Where condition : Inputbox for a between 2 date

Posted on 2004-08-06
12
316 Views
Last Modified: 2006-11-17
Hello experts,

I have a report that i want to filter using a where condition.

i want an input box that will ask the user to filter the date (format ww/aaaa) using a between.

between 2 weeks number. :
21/2004 to 25/2004.

How can i do that?

thks mafle
0
Comment
Question by:mafle
12 Comments
 
LVL 39

Expert Comment

by:stevbe
ID: 11735509
I would build the filter into the query itself or use a form to capture the dates.

query example ...

SELECT tblPeople.*
FROM tblPeople
WHERE (((tblPeople.TS) Between [Start date] And [End date]));


tblPeople.TS is the field you want to filter against.

Steve
0
 
LVL 8

Expert Comment

by:dds110
ID: 11735516
If you are openeing this report from a form, then you can supply two fields on the form to put in your dates and then pass the dates to the query.

Or you can pass the dates directly into the OpenForm method like so:

DoCmd.OpenReport "reportname", acViewPreview,,"datefield between " & InputBox("EnterDate 1") " And " & (InputBox("EnterDate 2")

(I wrote this freehand, double check it before you use it)
0
 
LVL 1

Expert Comment

by:sonsofperez
ID: 11735680
You need to put the criteria in the query that feeds the report.  I am assuming your table has actual dates not just (21/2004) stored in it.  If you want the user to input the criteria like you have above (21/2004) you will need to format your date field in the query.  Enter this code in the field line in your query:

Format([YourDateField],"ww/yyyy")

Set the Criteria line for this field to:

>=Format([Enter the Starting Week],"Short Date") and <=Format([Enter the Ending Week],"Short Date")

When the report is ran a dialogue box will pop up that says "Enter the Starting Week" after they enter it another will pop up that says "Enter the Ending Week"

Wayne
0
 
LVL 1

Accepted Solution

by:
sonsofperez earned 100 total points
ID: 11735780
My above code will work for you, but I would change the criteria line to this:

>=Format([Enter the Starting Week],"ww/yyyy") and <=Format([Enter the Ending Week],"ww/yyyy")

as it just makes a little more look a little more consistent.  :-)

Wayne
0
 

Author Comment

by:mafle
ID: 11736063
Wayne,

this is working :
>=Format([Enter the Starting Week],"ww/yyyy") and <=Format([Enter the Ending Week],"ww/yyyy")

but if i enter for starting date :
27/2004

the results start at 28/2004. problem with >=?
0
 
LVL 1

Expert Comment

by:sonsofperez
ID: 11736112
How is the data in your table stored?  Is it an actual date?

Also are you certain you have dates that fall in the 27th week in your table?
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:mafle
ID: 11736167
the data is a date but i want it as as week that's  why i use format([date];"ww/aaaa").
and yes i'm certain that i have record in the weeks of the 27th.

i f i type as stating date 26/2004.
the result starts as 27/2004
0
 
LVL 1

Expert Comment

by:sonsofperez
ID: 11736241
Double check that you have the equal sign in your criteria.  I build a sample table and it is working on my end.  You can try changing back to:

>=Format([Enter the Starting Week],"Short Date") and <=Format([Enter the Ending Week],"Short Date")

and see if it works for you.
0
 
LVL 1

Expert Comment

by:sonsofperez
ID: 11736322
Do the results end with the correct week entered?
0
 
LVL 1

Expert Comment

by:sonsofperez
ID: 11736359
One other idea is take the query by itself and make the column you have the criteria in so it is visible in the result set.  Run the query with the same criteria and see if 27/2004 comes back as a value in that field.  Perhaps you are calculating weeks different then access?
0
 

Author Comment

by:mafle
ID: 11736367
when i use short date it's working..
stil the same when i use weeks number.

still the same with this.
>=[Enter the Starting Week]
0
 

Author Comment

by:mafle
ID: 11736407
.. forget the whole thing it's working great and your earn the point!
sorry about the error i was getting my mistake
mafle
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now