Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

Where condition : Inputbox for a between 2 date

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
mafle
Asked:
mafle
1 Solution
 
stevbeCommented:
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
 
dds110Commented:
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
 
sonsofperezCommented:
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
sonsofperezCommented:
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
 
mafleAuthor Commented:
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
 
sonsofperezCommented:
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
 
mafleAuthor Commented:
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
 
sonsofperezCommented:
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
 
sonsofperezCommented:
Do the results end with the correct week entered?
0
 
sonsofperezCommented:
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
 
mafleAuthor Commented:
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
 
mafleAuthor Commented:
.. 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now