Solved

How do I reference another table's field value in MS Access query?

Posted on 2016-08-24
5
33 Views
Last Modified: 2016-08-25
Dear Experts!

Please see attached file. What is the proper syntax for referencing a field from a different table in my query? The query works great when I type a date in manually but when I reference a field it does not.

Thanks!
screenshots.pdf
0
Comment
Question by:shogun5
  • 3
  • 2
5 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
You can only reference tables that are included in the query.  You can drag this table onto the QBE and draw a join line between the two dates.  Remove the criteria because the join will serve that purpose.  Then put the query into SQL view and change this join's relational operator from "=" to ">=".  Once you change to a non-equi join, you won't be able to return to QBE view since the editor can only display equi-joins graphically.

Another way to enter criteria is to use a form to run the query/report/form.  The query would reference the form.

Where SomeField = Forms!someform!somefield
0
 

Author Closing Comment

by:shogun5
Comment Utility
Pat,

Worked like a charm! Thanks!
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Great.  Which method did you use?
0
 

Author Comment

by:shogun5
Comment Utility
I just changed the sign in SQL it was easy enough.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Thanks.  It is always good to clarify which solution worked for you when more than one is offered.  That helps people who find the thread later.
0

Featured Post

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)

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
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…

743 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