Solved

Microsoft Access User input Query

Posted on 2013-12-26
14
564 Views
Last Modified: 2013-12-26
I need to create a query in access to display an invoice for future events
I need the user to input how far ahead they would like to display the results, so far I have that the user inputs the amount of days ahead they would like using:

Between Date() And [End Date]

This works fine at the moment but what I actually need is to ask the user if they want days or months and then enter how many days/ months they want to see what parties they have up until that day/month

I have been trying to work this out for a few days but no success - if anyone could help would be greatly appreciated!
Thanks
0
Comment
Question by:brandybob
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
14 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 39739924
The best way to do it is to have two input boxes, one that specifies the number of time periods and the other one specifies the length of the time period.

For example

number: 3
time_periods: year, day, week, month.

Your query would end up being

SELECT xxx WHERE xxx BETWEEN Date() AND DateAdd ( time_periods, number, Date())
0
 

Author Comment

by:brandybob
ID: 39739938
I've tried putting this in the criteria box but says "Syntax of the subquery in this expression is correct"
What does this mean?
Sorry - haven't used Access before so I am very unfamiliar with it!
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 39739941
I would add that I would use a combo (cbo_PeriodType) with a value list ("d", "Days", "m", "Months") to ensure consistency, and then a textbox for the # of periods.

then the query would look like:

SELECT * FROM yourTable
WHERE [dateField] BETWEEN Date()
AND DateAdd([Forms]![yourFormName]![cbo_PeriodType], [Forms]![yourFormName]![txtNumPeriods], Date())
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:brandybob
ID: 39739959
I've created the combo box and used this but it still says the same thing!
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 39739960
@fyed - I agree about the combo box and the text box. That is the right way to do it.

@JDettman - As far as the link to the syntax of date add, is this better?

http://office.microsoft.com/en-us/access-help/dateadd-function-HA001228810.aspx?CTT=1
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 39739971
@brandybob,

Once you create the combo box, and enter the data I provided as the:

RowSource:"d", "Days", "m", "Months"
RowSourceType: ValueList

Then you will also need to set these other properties:
BoundColumn: 1                      'this is on the data tab of the properties dialo
ColumnCount: 2                       'this is on the format tab
ColumnWidths: 0, .75              'also on the format tab

When you view the combo in form view, with these settings, you should see "Days" or "Months" displayed.  But when you reference the combo box in your query, the DateAdd( ) function will see "d" or "m", which are valid parameters for that field.

It would help us if we could use the correct names of your form, controls, and fields.  This will prevent you from having to translate the examples we give you into the proper syntax for your form or table.  Can you post the SQL you currently have?  Change the view from query design view to the SQL view and copy the text.
0
 

Author Comment

by:brandybob
ID: 39739978
SELECT tblmember.Title, tblmember.Initial, tblmember.TelephoneNumber, tblbooking.DateOfParty, tblbooking.TimeOfParty, tblPartyType.PartyTitle, tblPartyType.MaxNumber, tblbooking.NoOfChildrenAttending, tblbooking.[Non-VegetarianMeals], tblbooking.VegetarianMeals
FROM tblmember INNER JOIN (tblPartyType INNER JOIN tblbooking ON tblPartyType.[PartyCode] = tblbooking.[PartyCode]) ON tblmember.[MembershipNumber] = tblbooking.[MembershipNumber]
WHERE (((tblbooking.DateOfParty) Between Date() And AddDate("time_periods","number ",Date())));
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 39739990
OK, so try:

SELECT tblmember.Title
           , tblmember.Initial
           , tblmember.TelephoneNumber
           , tblbooking.DateOfParty
           , tblbooking.TimeOfParty
           , tblPartyType.PartyTitle
           , tblPartyType.MaxNumber
           , tblbooking.NoOfChildrenAttending
           , tblbooking.[Non-VegetarianMeals]
           , tblbooking.VegetarianMeals
FROM tblmember
INNER JOIN (tblPartyType
                      INNER JOIN tblbooking
                      ON tblPartyType.[PartyCode] = tblbooking.[PartyCode])
ON tblmember.[MembershipNumber] = tblbooking.[MembershipNumber]
WHERE tblbooking.DateOfParty Between Date()
And DateAdd([Forms]![yourFormName]![cbo_PeriodType],
                        [Forms]![yourFormName]![txtNumPeriods], Date())

Note that in the DateAdd function you will need to replace "yourFormName" with the name of the form you are using to enter the period type and # of periods.  You will also need to replace "cbo_PeriodType"  and "txtNumPeriods" with the names of the controls on your form.

Watch the word wrap in the DateAdd function.
0
 

Author Comment

by:brandybob
ID: 39740008
So do I need to create a form for the query first or from the booking table for the period type?
0
 
LVL 48

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39740029
Yes,  to make sure your users don't enter incorrect data, the best way to do this is to create a form with the combo box and textbox to enter these values.  Then add a command button that will run the query based on the values in those controls.  But for testing purposes, you could start out by replacing:

And DateAdd([Forms]![yourFormName]![cbo_PeriodType],
                        [Forms]![yourFormName]![txtNumPeriods], Date())

With:

And DateAdd([Period Type], [Number of Periods], Date())

When you run that query, it should popup two questions:

Period Type?

To that question, enter either the letter d or m for (day or month).  Then it will ask:

Number of Periods?

Enter a number of days/months.
0
 

Author Closing Comment

by:brandybob
ID: 39740038
The expert was great to help me solve my problem! As I am unfamiliar with the software they were very helpful in ensuring I understood and took me through the steps
I am very grateful and would recommend to others
Excellent - thank you!!
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 39740055
Glad to help, brandybob.

Good luck with your project!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

690 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