Access 2007-2013
Access 2000-2003
Seminars 		Tips & Tricks
Access Forum
Course Index Insider Circle 		Collapse Menubar
 
Tutorials   News   Tips   Templates   Forums   Help   Logon   Order  
 

News      User Comments     History     Notify Me

7/23/2012 2:50:26 PM
Access 2010 Beginner 5
 
 This Microsoft Access video tutorial picks up where Level 4 left off. This class covers query criteria in a lot more depth, parameter queries, wildcards, and lots more. Topics include:

- Query Criteria
- Multiple AND, OR Conditions
- Inequalities
- Records Between Two Dates
- Dealing with Null Values
- Wildcards and LIKE
- Parameter Queries
- Top X Values
- Formatting Query Field Output

Click here for more information on Access 2010 Beginner Level 5, including a course outline, sample videos, and more.

Permanent Link
Course Link: Access 2010 Beginner 5
Keywords: access 2010 beginner 5 query criteria and or inequality inequalities between is null is not null wi
Page Tag: whatsnew
Post Reply

Comment from Glynis Carey @ 3/2/2013
Sorry, the time index for the video I was referring to is 14:04
Show Just This Thread        Post Reply
Pound Symbol Comment from Jennifer @ 2/27/2013
Thank you - explains why I was confused by Canadian friends!   The ref article interesting. Am familiar with the sharp sign from music. In medicine # is also sometimes used as an abbreviation for a 'fracture'!!!!  
Show Just This Thread        Post Reply
Pound Symbol Comment from Jennifer Hull @ 2/26/2013
Why do you call # "Pound" symbol. Is this common usage in the US? In SA and UK ,  pound is a crossed L, and # is Hash or used for number abreviation.

Reply from Richard Rost:

Yep. That's what we called it here in the US... pound sign, hash mark, etc. See this.
Show Just This Thread        Post Reply
Comment from Jennifer Hull @ 2/23/2013
Thank you for making this so clear ( run vs view) !!!!
Show Just This Thread        Post Reply
Comment from Jennifer Hull @ 2/23/2013
query criteria lesson 3, 08.13  If you started you criteria with 5/*/2012, why does it come up with 7/21/2012. I  thought the * only allowed any value in the 2nd area of the date, i.e. 17 and 21, and the other criteria should have been 5/  / 2012
Show Just This Thread        Post Reply
Comment from Alex Hedley @ 1/31/2013
I think you're missing the space
IsNull => Is Null

IsNull([FIELDNAME]) is a function.
Show Just This Thread        Post Reply
Comment from Nick WILLIAMS @ 1/29/2013
Richard, I have my own table that I am working on these days.  In this 'people table,' I have 6 fields: ID number, status, prefix, first name, middle initial and last name. 11 records so far. I'd like to set up a query for null values.
I can see that 7 out of 11 records are missing the middle initial. I set up the query with the 6 fields, based on a table. All the Show boxes are marked. I run it and all 11 records appear. I save the query.  
Then I type in "IsNull" for Criteria in the middle initial field (Design View). When I run the query again, no records appear.  I expected 7 records to appear - those without a middle initial.  What's wrong with this picture?
Show Just This Thread        Post Reply
Default Value Comment from Patrick Breuer @ 1/23/2013
Richard,

when you run the query, there is always a default in the last record.
1.Is that default always there?
2.Is ithe defalut (say state) NY because that's the first state in the record?

Thanks,

Patrick

Reply from Richard Rost:

1. Yep. If you've set a default value in the table it will always show there.
2. No. That will ONLY show up there if you've set it as the DEFAULT VALUE property for the field in the table design. The value isn't ACTUALLY there until you start entering other data in the record.
Show Just This Thread        Post Reply
Paid or Unpaid Comment from Raul Pendas @ 12/30/2012
I have a question whether you can use a query to find who has paid for an order and who has not paid.
I use the nil or not nil to get this?
Thank you for your attetion to this matter.
Raul

Reply from Richard Rost:

I would make an IsPaid field which is a Yes/No value. Then you can just make your query criteria for this field "Yes" or "No" depending on which you want to see.
Show Just This Thread        Post Reply
cant get customer in query Comment from Gary Wilson @ 12/8/2012
Hello Richard.. question , I have this customer below  in my table ,in my Query I have first name,last name,phone and state checked ,ran query about 10 times , saved and shut down access and I still can't get customer in my query run ...any suggestions ? I do get the other 3 NY customers just not this one.
Suzan Jones 123 Packing 101 Main St Buffalo NY suzan@amicron.com (562) 436-5146 25 10.00% 1/2/1998 1500 False

Reply from Richard Rost:

I really can't tell from just looking at this. Do you have any criteria in your query? Perhaps excluding NULL values? This data alone doesn't show me any reason why she wouldn't appear in the query results. If you want me to look at your database, feel free to submit it via the TechHelp page and I'll look at it when I get a chance.
Show Just This Thread        Post Reply
capital works number Comment from Connie @ 9/29/2012
I will be setting up 6 tables.  Each of these tables will have a common field (capital works number).  Will this cause a problem when I want to generate reports pulling from several different tables?

Reply from Richard Rost:

I'd need to see your database (or at least your design schema) to tell you for sure... but generally it's OK to have the same number in several tables if it's a HUMAN-GENERATED number. For example, you might have a PART NUMBER that represents a specific product, and is assigned by the manufacturer, and you have it entered into several different tables (order, product, inventory, etc.)
Show Just This Thread        Post Reply
Multiple items Comment from Connie @ 9/28/2012
My question is about setting up the table.  I have a large table to track capital work projects individually identified with a capital works number (in addition to the Access id).  One column is for estimates and there are often estimates for multiple vendors.  Should I have different fields, e.g. estimate 1, estimate 2, etc. or should I just make separate entries for each estimate.  This would mean one capital work number might have 5 different access id numbers!  Help!

Reply from Richard Rost:

Whenever you say that something can have multiple entries, you should be looking at another table. Even something like phone numbers. If you only want to stick to 1 or possibly 2 phone numbers, then multiple fields in your table are fine. However, if you want the ability to track 3, 4, 5 phone numbers (work, home, cell, fax, etc.) then you really should be looking at a SECOND table.

Now, I don't cover how to do this in my BEGINNER lessons. The first Expert lesson will talk about setting up relationships between multiple tables. If you want to learn it NOW, pick up a copy of Access 201 or my Relationship Seminar.
Show Just This Thread        Post Reply
multiple dates Comment from kalyan090 @ 9/23/2012
Hi Richard
I have two columns of date fields(LeaveFrom and LeaveUpto) and I want a report based on date provided in query. Example: All leaves taken between i.e from 21 January 2012 upto 20 February 2012.



Reply from Richard Rost:

Your query criteria would be:

LeaveFrom: BETWEEN #1/21/12# AND #2/20/12#

OR (down a row)

LeaveUpTo: BETWEEN #1/21/12# AND #2/20/12#

This should catch anyone where their leave falls anywhere in that date range.
Show Just This Thread        Post Reply
Parameter for 3 Fields Comment from richard g @ 9/7/2012
I have a customer table with 3 different phone number fields.  I need to do a parameter query and enter a phone number once and have access search all 3 phone number fields for a match.  Possible?

Reply from Richard Rost:

Very good question. Yes, it's possible, but it's a little beyond what I showed in today's class. You can either use a query-level parameter or an unbound form with a search field on it. I will cover both of these in the Expert classes, but this tutorial will show you how to set up the search form. You would just use the same text box as your criteria for each of the 3 different forms with an OR condition.
Show Just This Thread        Post Reply
Parameter Form Comment from Maurice C @ 7/30/2012
Richard

Really enjoyed the Access 2010 Beginner 5 tutorial. With regards to parameter queries, would you be able to walk us through how to set up a form that works off a parameter query in a future lesson? For example, if some one wants to check their sales between 2 dates, a sales log query is created. Then feeding off that we create a form which has the fields for the start and end dates with a preview/print button so a report showing the sales between those dates can be generated.

Kind regards

Maurice

Reply from Richard Rost:

I cover this concept in Access 201 and 202, plus this Tip Video.
Show Just This Thread        Post Reply
Love the forum Comment from Albert T @ 7/26/2012
Richard I love this forum - small notes and remarks are very informative - like read some problems your students encounter and your solutions - keep them coming, Bert.

Reply from Richard Rost:

Thanks, Bert. I love the fact that we have a small community of users who help each other and I'm not the only one who posts all the answers. :)
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
 
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
 
  Your Name:  Shown
  Your Email:  NOT Shown
  Subject:
  Comments:

 

Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 3+5:
  
  Notify me when the News is updated.
  Remember Me for my next comments
  
 
 
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be. As always, I promise to never give away your personal information to anyone else, ever.

 

 

You may want to read these articles from the 599CD News:

5/14/2013Access Tip: Many-to-Many Relationships
5/14/2013Access Expert 5 Handbook Ready
5/10/2013Access Tip: Email Using Outlook
5/10/2013Microsoft Access Expert 5
5/2/2013Access Amort Handbook
4/29/2013Microsoft Access Expert 4
4/28/2013Access Tip: Toggle Button Colors
4/27/2013Access Expert 4
4/23/2013New Access Tip: Conditional Format Expression
4/15/2013New Access Tip: Grid Lines v. Dots
   
Learn
 
Microsoft Accessindex
Microsoft Excelindex
Microsoft Wordindex
Microsoft Windowsindex
Microsoft PowerPointindex
Adobe Photoshopindex
Visual Basicindex
Active Server Pagesindex
Online Seminars
More...
Customers
 
 Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
 Info
 
 Latest News
New Releases
User Forums
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
 Help
 
 Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
 About Us
 
 Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
 Order
 
 Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
 Contact
 
 Live Chat
General Contact Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey