• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 590
  • Last Modified:

Microsoft Access User input Query

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!
  • 5
  • 5
  • 2
1 Solution
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())
brandybobAuthor Commented:
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!
Dale FyeCommented:
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())
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

brandybobAuthor Commented:
I've created the combo box and used this but it still says the same thing!
@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?

Dale FyeCommented:

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.
brandybobAuthor Commented:
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())));
Dale FyeCommented:
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.
brandybobAuthor Commented:
So do I need to create a form for the query first or from the booking table for the period type?
Dale FyeCommented:
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())


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.
brandybobAuthor Commented:
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!!
Dale FyeCommented:
Glad to help, brandybob.

Good luck with your project!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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