Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
Access Q&A: Unmatched, OpenReport, Time
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   14 years ago

More recently answered Access questions...

Hi, Richard:

I have two tables in access (let's name them Table A and Table B). records in Table B is subset of Table A. I need to get records in table A, which is not in Table B.

I don't know how to use unmatched query.

Thank you in advance. Anna

Anna, that is kind of what the Find Unmatched Query Wizard does... it looks at the records in one table that are NOT found in another table. You generally use it to say "show me all of the customers who don't have orders" or "all of the cars that are missing drivers" but you could use it when you have two tables and you want to see which items are missing from one table - as long as you have one field that is identical (an ID or something else you can use the join them).

The wizard is pretty easy to understand, so you shouldn't need step-by-step instructions. If you really can't figure it out, I do cover it in detail in my Access 223 tutorial.

while doing reports - my program will open a query. The thing is once the query is closed, the report should open automatically for preview. My question is how do i find out if the query has been closed and command the report to open. Mohan.

Mohan, how are you doing it now? It's really hard to attach EVENTS to queries. They don't have any VBA code or other event handlers attached to them.

Here's what I would do: take your query and build a FORM based on it. Put the form in Datasheet view so it looks like a query. Then, in the OnClose event for the Form, you can open your report with a little DoCmd.OpenReport command... or just stuff it in a command button.

I cover DoCmd.OpenReport in my Access 304 tutorial.

I'm running a query on a table with 6000 records. The query should return about 4500 lines because I'm excluding a certain region. The funny thing now is that access return about 2 billion records running this query. How is this possible? Harvey.

Harvey, it sounds to me like you might have accidentally created a Cartesian Product with your query. Check to make sure you haven't accidentally put two unjoined tables in your query.

This would happen, for example, if you had two tables with Students and Tests, and you wanted to list every student with every test. Essentially it's a 'product' of the two tables.

Delete the second table - or create a JOIN between them - and the product goes away.

I talk about this in my Access 223 class.

In fact, the only way I remembered what it was called was because I searched my own web site for it. Ha ha ha.

Background: I have written db to measure press productivity and efficency. Reports display past 24 hours of data begining with Day shift. For Example, a report reviewed on Monday morning would include Friday A & B shift data and Sunday C Shift data. Tuesday mornings report would include Monday A&B shift and Tuesday C shift. Information needs to be retrieved for the current and previous months.

Question: How do I set criteria to retrieve this data?

I was using Date()-1 or Date()-2, etc. This worked as long as the workweek begins with C shift on Sunday and ends with B shift on Friday. It does not, However, when it begins with Day shift Monday and ends with C Sunday. Including Sunday C with the previous Friday A & B is throwing me. Kelley

If I understand your problem, you need to be able to generate a report based on fractions of a day... not just whole days. Access treats days as whole units, so one hour is 1/24 of a day.

If you need to generate a query showing all of the data from midnight to 12 noon today, you would say

Between Date and Date+(0.5)

So, if you need to generate a report showing last night's work (let's say after 8pm) and all of today's work until 3pm, you would say:

Between (Date-1+(20/24)) and (Date+(15/24))

Remember, Access thinks in 24hr time, so 8pm = 20:00 hrs; 3pm = 15:00 hrs. Yes, you can simplify the fractions, but it makes it easier to read if you don't.

Now, you should be able to take this into consideration to fix your problem. Unless I'm misunderstanding your question, this should help. If not (if I'm off base) please explain how your shifts work in more detail.

I cover how Access deals with time in more detail in my Access 221 tutorial.

I am a basic Access designer and need a basic answer please:
I have a table with tasks in it.
Each task has a due date.
The date could be for example Nov 3 07 8:58:19 AM
In a query, I would like to display all records that have a date that is less than or equal to today.
I have been using the following criteria in the [Due date]field:
<=Now() Or Is Null
I would like to display all records if they are due today, regardless of the time of day.
I find this hard to explain so please allow me to use an example:
1) Say the current time is Nov 3 07 9:00:00 AM
2) Say the due date of a record is Nov 3 07 11:00:00 AM
3) I would like the record displayed becuase it's date is less than or equal to today
4) BUT because the record's time is LATER than the current time, it does not get displayed.
5) In other words, I would like to display a record if the date portion is <= today, regardless of the time portion.

I have tried DatePart function to no success.

Help would be much appreciated. Joseph.

Read what I sent to the previous gentleman about how Access handles dates and times.

I would just make your criteria for your DueDate field:

Between Date and Date+1

This says, "show me everything due from 12:00:01 this morning to 12:00:00 tomorrow morning."

If you want to include everything in the past up to and including everything today, say:

< Date+1

Easy enough?

Upload Images   Link  
Richard Rost 
13 years ago
Absolutely. Microsoft Access is what I do best. Start with my ACCESS 101 course - which is the perfect place to begin - and if you have any questions, post them on the discussion board for that class.
Add a Reply
Upload Images   Link  
Shina Thomas 
13 years ago
I am just starting Microsoft Access and I have just few weeks to know it. i can work on Excel quite well but I need Access. Could you, please, help me out to start right. I'll be using it for database in a market survey. thanks.
Add a Reply
Upload Images   Link  
Richard Rost 
14 years ago
If you want to get the values OTHER THAN the bound value - which is usually the first column, or column(0) then you can use the Column property:

MyListBox.Column(0) is the first column (if you've built your box right, it's the hidden ID field) Read More...
Add a Reply

Show Older Comments...
View in Table Format

Start a NEW Conversation
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.

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

11/30/2021Import Multiline Cells
11/30/2021Adam's Access Games
11/29/2021Prevent Close
11/22/2021Currency Symbols
11/13/2021Access Developer 36
11/13/2021Access Developer 36 Lessons
11/13/2021Access Developer 36
11/13/2021Control Tip Text
11/11/2021Link to Excel

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Account Login
Online Theater
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: access q&a Unmatched OpenReport Cartesian Time  PermaLink