• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 584
  • 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!
Thanks
0
brandybob
Asked:
brandybob
  • 5
  • 5
  • 2
1 Solution
 
mankowitzCommented:
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
 
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!
0
 
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())
0
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!

 
brandybobAuthor Commented:
I've created the combo box and used this but it still says the same thing!
0
 
mankowitzCommented:
@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
 
Dale FyeCommented:
@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
 
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())));
0
 
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.
0
 
brandybobAuthor Commented:
So do I need to create a form for the query first or from the booking table for the period type?
0
 
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())

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

Good luck with your project!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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