Solved

query for two fileds a table multiple values

Posted on 2014-12-30
5
103 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 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 167 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 167 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 166 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

740 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