Solved

query for two fileds a table multiple values

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

726 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