Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

query for two fileds a table multiple values

Posted on 2014-12-30
5
Medium Priority
?
110 Views
Last Modified: 2015-01-26
I am trying to write a query which will return records in a table that
The “expire” field has a value of all dates of 2014
The “ Compcode” field has values of  "220,12345,king,348,346"

I only know how to use the design grid, and listed in the grids as follows:

The first column shows the table, and a ck mark  so that all fields of the table will be in the result.
In the next column of the design grid, it shows the expire filed of this table, on the criteria line    >#12/31/2013# And <#1/1/2015#    this works just fine by itself.

Things start going whack o when I try to further filter the query by another field.
What should be in the next column of the design grid, which specifies the “compcode” field , should I be using the “critera “ line or the “ or” line ?
Should my values separated by commas, the words “or”, the words ”and”  


I am lost,  Any help appreciated
0
Comment
Question by:intelogent
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 668 total points
ID: 40523599
The SQL for something like that would be:

SELECT * FROM YourTAble WHERE DatePart("yyyy", [expire]) = 2014 AND CompCode IN ('220','12345','king','348','346')

To test this out, create a new Query but do NOT add any tables. Next, switch over to SQL View, and paste that SELECT statement above into the editor pane. Make sure you change "YourTAble" to the name of your table.

Now, switch back over to Design view, and Access should show you the way it would write that statement in the viewer.
0
 
LVL 19

Assisted Solution

by:Eric Sherman
Eric Sherman earned 668 total points
ID: 40523732
The “ Compcode” field has values of  "220,12345,king,348,346"

In this case, are you trying to find an exact match to "220,12345,king,348,346"

or are you looking for "220" or "12345" or "king" or "348" or "346" ???

Please explain.

ET
0
 

Author Comment

by:intelogent
ID: 40524885
et,
i would like to return all records which have a compcode expiring in 2014.

the records are insurance policies. and the compcode are certain insurance companies.

so what i am returning is for all policies written last year with each of these companies.   i am guessing that is   " OR"
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 40525586
Ok, the suggestion Scott McDaniel posted should do the trick for you.  On the same criteria line but under the compcode field add the following:

IN ('220','12345','king','348','346')

ET
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 664 total points
ID: 40533505
<Absolutely no points wanted here>

if you waned this to always look at the "Last Year"
You could change the syntax to something like this:
DatePart("yyyy",[expire])=DatePart("yyyy",Date())-1

...obviously, if you wanted the current year only, you could use something like this , ...and so on...
DatePart("yyyy",[expire])=DatePart("yyyy",Date())

Again, ...no points wanted here, as your question was answered, ...as posted, ...by Scott.
;-)

JeffCoachman
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

636 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