Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   TechHelp   Forums   Help   Contact   Merch   Join   Order   Logon  
 
Back to Access Forum    Comments List
Upload Images   @Reply   Bookmark    Link   Email   Next Unseen 
Parameters multiple times
Jennifer Showalter 
    
5 years ago
One of the frustrating thing is trying to find the little nuggets of info that were shared as an aside to a bigger lesson.

I want to do a monthly report with three different Tables/Querys so right now I have to enter the month three times. You showed how to enter a variable to use the same information several times once it is entered and I cant find what lesson it was contained in.
Richard Rost  @Reply  
          
5 years ago
Make a form field. Then each of your three queries can pull the value from it.
Jennifer Showalter OP  @Reply  
    
5 years ago
I want to enter a Parameter one time not 3 times.  It is not a fixed value.
Scott Axton  @Reply  
        
5 years ago
Please walk us through, in detail. Do you have 1 query with 3 tables or 3 queries with individual tables that have the same parameter?
Richard gave you a link above that should work for either case.  You don't have to have the value on the Main Menu.
You could make a little form asking for your date and then run the query/queries from that form.
Richard Rost  @Reply  
          
5 years ago
Exactly. Make a field on a form. Let's call it Forms!MainMenuF!StartDate. Now in your queries, you can refer to that field for your criteria. Here's another Tip that might help you.
Jennifer Showalter OP  @Reply  
    
5 years ago
Background: I have to add together the cash income and the credit income from each skating session (Form1) Add in the Barter income from the session since it is not shown in cash or credit but is used to calculate sales tax.(Form2) Then subtract any tips that came in on a credit card since thay are shown in the credit income and are not actually income to the rink for sales tax purposes(Form3)

Right now I have 3 queries and 3 forms, but I was going to look into having just one query.
So I add a field in the Query? BeginDate:[Enter the first date of the month - mm/01/yy] and EndDate: (same) Then I can refer to it in the query under SessionDate field: >=lBeginDate]and <[EndDate] for form 1 and so on for the other forms?
Scott Axton  @Reply  
        
5 years ago
So are you telling us you have a Cash table, Barter table, and a Credit table?  If that is the case I would tell you to put them all in the same table.  Then put in a PaymentType (text) field, and an IsTaxable field (Yes/No).
That will simplify your queries substantially.  You would just enter multiple line items in the PaymentsT.

If I didn't understand you properly take some screenshots of you forms tables and queries so we can take a look at what your trying to do.  The link to post them is in the original post - upper right corner.
Jennifer Showalter OP  @Reply  
    
5 years ago

Jennifer Showalter OP  @Reply  
    
5 years ago

Jennifer Showalter OP  @Reply  
    
5 years ago

Jennifer Showalter OP  @Reply  
    
5 years ago

Jennifer Showalter OP  @Reply  
    
5 years ago

Jennifer Showalter OP  @Reply  
    
5 years ago

Jennifer Showalter OP  @Reply  
    
5 years ago

Jennifer Showalter OP  @Reply  
    
5 years ago
I uploaded a lot of photos but i can't figure out how to delete the ones that don't apply. I wanted to give you a comprehensive idea of what i am trying to do. All our income comes in at Sessions. There is Credit Card, Cash, Barter, Etc. Income categories and it comes in at the Admission drawer, Snack Bar drawer, Party drawer, etc.  The bottom line for the session is Total Cash and Total Credit that goes to the bank and that would be what we should pay sales tax on except for a couple anomalies. The Barter category does not go into the bank and must be calculated in income for sales tax. Also, If a Party Client pays a tip on Credit Card it automatically goes through our accounts and must be subtracted from income because we don't have to pay sales tax on those tips. I generated 3 Queries and 4 Forms to show this information (removing all the records that are Zero and adding a dummy record 11111111 that shows up always to make the calculation work) I am using >=[BeginDate]and<[EndDate]
Jennifer Showalter OP  @Reply  
    
5 years ago
This means I can mess it up if i don't know how many days (30 or 31) are in a month. ***PLUS*** I have to enter the BeginDate and EndDate 3 times each whenever I preview the report or print it after previewing it. I tried to make a form that would be a list of months with some totals but I couldn't figure it out. (I made a report which is useless for form navigation) I would like either a form to choose the month or to enter the month mm/yy one time for preview then not again for printing. I am pretty sure it can be done, but I can't figure it out.
Scott Axton  @Reply  
        
5 years ago



Jennifer -
I'll take your last post and work backward because that is the easiest.  
Fortunately the first day in all of the months is well... Wait for it...  the 1st.  duh ;)  ( humor )

So, by knowing what next month is, we can figure out what the last day of this month is.
It is just:     The first day of the next month minus 1 day.  

I never could remember that darn nursery rhyme any way.  That is where DateSerial and DateAdd functions comes in to play.  Go watch this video for more: DateAdd Months

Also, see this article Last Day of the Month

This will work for ANY month - Whether it is 28, 29, 30, or 31 days.
Scott Axton  @Reply  
        
5 years ago
Did you watch the Value from a Form video Richard suggested in the very first reply?  
He showed you how to use a form to gather the info (or a date) for use in a query or queries.  
That way you can run 1 report, or 6, or more and they will all have the same date range.

OK - The photo's you provided us indicate to me that you don't have your tables set up correctly. (I'm assuming here so please forgive if I'm wrong).
Are these work sheets in Word or Excel? I can't imagine they are from Access.
They do give a good indication of the data you need to collect and the reporting needs you have.

I see that you are a Learning Connection student and have started the Expert Classes.  You are on the right path, but your trying to do more advanced things than what your education levels indicate to me.  Go back and re-watch planning your database in the beginner and then get a good handle on the Relationships
Covered in Expert 1
Scott Axton  @Reply  
        
5 years ago
I'm sorry about all the references.  But there is just no way to give you one-on-one tutelage in about 900 characters.
Unfortunately you just have to keep learning a little more each day.  We can't just pop a lid off your brain in pour in 25-30 years of knowledge.
You WILL get there. Just keep moving forward and asking questions if you don't understand the lessons.
Jennifer Showalter OP  @Reply  
    
5 years ago
I finally got it!!!!!!!!!!!!! There is A LOT of stuff I don't know and plan on getting to. I want to know it all, but am having trouble really learning it without applying it to a project I am working on. This helps a lot.
Scott Axton  @Reply  
        
5 years ago
I'm so glad!  
Richard always says he gives us the Lego pieces - It's up to us how to put them together.  
Probably one of the harder things to do is equate what he is teaching and them applying that concept to what you want to accomplish.
The next hardest is knowing what the question is some times.  Learn to use the Search at the top.  Another good resource is the index at the bottom left of the page.
I know you would benefit from becoming a TechHelp member.  There are TONS of useful videos - Especially the extended cuts that will help you out.  TechHelp Memberships

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.
 

Next Unseen

 
New Feature: Comment Live View
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2026 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 4/30/2026 5:20:31 PM. PLT: 0s