?
Solved

query for two fileds a table multiple values

Posted on 2014-12-30
5
Medium Priority
?
107 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

762 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