Solved

query for two fileds a table multiple values

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help writing a query 6 73
Alter an update query which rounds 7 33
VBA pass value between two fields different tables 10 39
Set focus on next field when character count = 5 9 12
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

896 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

16 Experts available now in Live!

Get 1:1 Help Now