Solved

Microsoft Access User input Query

Posted on 2013-12-26
14
550 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ms/access ftp / SFTP 3 32
Auto Filter in Combo Box 7 30
Importing and Dropping Table in Access 11 22
Set focus on next field when character count = 5 9 12
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

929 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

12 Experts available now in Live!

Get 1:1 Help Now