Solved

Microsoft Access User input Query

Posted on 2013-12-26
14
549 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
  • 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 47

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
 

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 47

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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 47

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 47

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 47

Expert Comment

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

Good luck with your project!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server views 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 Access…
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…

708 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

19 Experts available now in Live!

Get 1:1 Help Now