By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,587 Members | 1,076 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

SQL Query - Returning One Specific Column

P: 27
Hi all,

I need some help with a sql query I am trying to do.
I had a sheet which I copied from excel.

Currently the columns which I have are Name, Phone, W1, W2, W3, W4, W5, W6, W7.

These W columns contain dates in each of them (as Strings).

The query I want to do is to Select a Name and W cell for each person that has a 09 (ie. September) in any of the W columns. I am able to do this using the following code:

SELECT Adults.NAME, W1, W2, W3, W4, W5, W6, W7
FROM Adults
WHERE (((Adults.W1) Like '*09/*')) OR (((Adults.W2) Like '*09/*')) OR (((Adults.W3) Like '*09/*')) OR (((Adults.W4) Like '*09/*')) OR (((Adults.W5) Like '*09/*')) OR (((Adults.W6) Like '*09/*')) OR (((Adults.W7) Like '*09/*'));

However, what I want my output to show is ONLY the September date. Right now it shows all of the other dates associated with the person. I know I get all of this extra output because my SELECT line states W1 ... W7 - but I am not sure how to change my code to result only the September stuff.

I know it sounds a bit confusing, but any help would be MUCH appreciated.

thanks
Oct 20 '06 #1
Share this Question
Share on Google+
60 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
SELECT NAME, W1, W2, W3, W4, W5, W6, W7
FROM Adults
WHERE ((Month(CDate(W1)) = 9) OR (Month(CDate(W2)) = 9)
OR (Month(CDate(W3)) = 9) OR (Month(CDate(W4)) = 9)
OR (Month(CDate(W5)) = 9) OR (Month(CDate(W6)) = 9)
OR (Month(CDate(W7)) = 9));
Oct 20 '06 #2

P: 27
hi, thanks for that...however, i don't specifically use date.

let me show you an example of what i mean - please let me know if i can email you with a sample file so that it is easier for you to understand what i am asking :-)

the columns of W contain text such as:
D09/17 or G03/23 - the letter preceding is some sort code that I need for my coumns. Hence, that is why I have used the term Like in my query so that I can select other columns which have the 09.
Oct 21 '06 #3

Expert 5K+
P: 8,434
hi, thanks for that...however, i don't specifically use date.

let me show you an example of what i mean - please let me know if i can email you with a sample file so that it is easier for you to understand what i am asking :-)

the columns of W contain text such as:
D09/17 or G03/23 - the letter preceding is some sort code that I need for my coumns. Hence, that is why I have used the term Like in my query so that I can select other columns which have the 09.
Hi.

Can I just try to clarify the question here? I believe what you are asking is not how to select the rows, but how to display only the column(s) containing the valid date(s). Correct?

I don't know whether I can help much, but I wonder whether an IIF() for each field might be useful to produce either the valid date or nothing.
Oct 21 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
SELECT NAME, IIf(Mid(W1,2,2)='09',W1,Null) As W1, IIf(Mid(W2,2,2)='09',W2,Null) As W2, IIf(Mid(W3,2,2)='09',W3,Null) As W3, IIf(Mid(W4,2,2)='09',W4,Null) As W4, IIf(Mid(W5,2,2)='09',W5,Null) As W5, IIf(Mid(W6,2,2)='09',W6,Null) As W6, IIf(Mid(W7,2,2)='09',W7,Null) As W7 FROM Adults WHERE W1 Is Not Null Or W2 Is Not Null Or W3 Is Not Null Or W4 Is Not Null Or W5 Is Not Null Or W6 Is Not Null Or W7 Is Not Null GROUP BY Name;

If this doesn't work, remove the group by name and try again.

If you only want one column it can be done, but only if no more than one of the columns will hold the september date at any one time. If you want to go for that let me know.
Oct 21 '06 #5

P: 27
when i try to run this query I get an error stating that W1 is circular :S
Oct 22 '06 #6

P: 27
when i try to run this query I get an error stating that W1 is circular :S

sorry i forgot to include that yes, many columns can hold the september date in them ... which makes it even more confusing.
Oct 22 '06 #7

P: 27
new question:
this one should be simple i hope.

i want to try to do a not like '09' but the result I get when it is executed are all the names.

this is the query:
SELECT Elementary.NAME, W1, W2, W3, W4, W5, W6, W7
FROM Elementary
WHERE (((Elementary.W1) not like '*09/*')) OR (((Elementary.W2) not like '*09/*')) OR (((Elementary.W3) not like '*09/*')) OR (((Elementary.W4) not like '*09/*')) OR (((Elementary.W5) not like '*09/*')) OR (((Elementary.W6) not like '*09/*')) OR (((Elementary.W7) not like '*09/*'));

i want it NOT to return any of the cells which have 09 in them, however they are all returned.
Oct 22 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Change the OR's to AND's



new question:
this one should be simple i hope.

i want to try to do a not like '09' but the result I get when it is executed are all the names.

this is the query:
SELECT Elementary.NAME, W1, W2, W3, W4, W5, W6, W7
FROM Elementary
WHERE (((Elementary.W1) not like '*09/*')) OR (((Elementary.W2) not like '*09/*')) OR (((Elementary.W3) not like '*09/*')) OR (((Elementary.W4) not like '*09/*')) OR (((Elementary.W5) not like '*09/*')) OR (((Elementary.W6) not like '*09/*')) OR (((Elementary.W7) not like '*09/*'));

i want it NOT to return any of the cells which have 09 in them, however they are all returned.
Oct 22 '06 #9

Expert 5K+
P: 8,434
Change the OR's to AND's
I think the basic problem here is that whether you use AND, OR or whatever, these criteria are selecting records (of course). While tdotsmiley, if I'm reading correctly, wants to pick and choose particular fields. That was why I brought up the issue of IIF().

If I'm mistaken, just ignore me - everyone else does. :)
Oct 22 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
I think you could be right killer and I could never ignore you.



This should solve the circular reference problem:



SELECT NAME, IIf(Mid(W1,2,2)='09',W1,Null) As Col1,

IIf(Mid(W2,2,2)='09',W2,Null) As Col2,

IIf(Mid(W3,2,2)='09',W3,Null) As Col3,

IIf(Mid(W4,2,2)='09',W4,Null) As Col4,

IIf(Mid(W5,2,2)='09',W5,Null) As Col5,

IIf(Mid(W6,2,2)='09',W6,Null) As Col6,

IIf(Mid(W7,2,2)='09',W7,Null) As Col7

FROM Adults

WHERE Col1 Is Not Null Or Col2 Is Not Null Or Col3 Is Not Null

Or Col4 Is Not Null Or Col5 Is Not Null Or Col6 Is Not Null

Or Col7 Is Not Null GROUP BY Name;


For the negative query try:



SELECT NAME, IIf(Mid(W1,2,2)<>'09',W1,Null) As Col1,

IIf(Mid(W2,2,2)<>'09',W2,Null) As Col2,

IIf(Mid(W3,2,2)<>'09',W3,Null) As Col3,

IIf(Mid(W4,2,2)<>'09',W4,Null) As Col4,

IIf(Mid(W5,2,2)<>'09',W5,Null) As Col5,

IIf(Mid(W6,2,2)<>'09',W6,Null) As Col6,

IIf(Mid(W7,2,2)<>'09',W7,Null) As Col7

FROM Adults

WHERE Col1 Is Not Null Or Col2 Is Not Null Or Col3 Is Not Null

Or Col4 Is Not Null Or Col5 Is Not Null Or Col6 Is Not Null Or Col7 Is Not Null GROUP BY Name;


Oct 22 '06 #11

P: 27
I think the basic problem here is that whether you use AND, OR or whatever, these criteria are selecting records (of course). While tdotsmiley, if I'm reading correctly, wants to pick and choose particular fields. That was why I brought up the issue of IIF().

If I'm mistaken, just ignore me - everyone else does. :)

yes i do want to select the fields ... but i still am getting an error when i run the statement that mccarthy has supplied me with :-S the IIF thing makes sense, but the error i get is:
You tried to execute a query that does not include the specified expression 'IIf(Mid(W1,2,2)='09',W1,Null)' as part of an aggregate function.
Oct 23 '06 #12

P: 27
SELECT Adults.NAME
FROM Adults
WHERE (((Adults.W1) Not Like '*09/*')) AND (((Adults.W2) Not Like '*09/*')) AND (((Adults.W3) Not Like '*09/*')) AND (((Adults.W4) Not Like '*09/*')) AND (((Adults.W5) Not Like '*09/*')) AND (((Adults.W6) Not Like '*09/*')) AND (((Adults.W7) Not Like '*09/*'));

this returns just the column header of NAME
Oct 23 '06 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
Remove the GROUP BY Name bit from the end of both queries.



yes i do want to select the fields ... but i still am getting an error when i run the statement that mccarthy has supplied me with :-S the IIF thing makes sense, but the error i get is:
You tried to execute a query that does not include the specified expression 'IIf(Mid(W1,2,2)='09',W1,Null)' as part of an aggregate function.
Oct 23 '06 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
Change the negative query to:

SELECT NAME, IIf(Mid(W1,2,2)<>'09',W1,Null) As Col1,
IIf(Mid(W2,2,2)<>'09',W2,Null) As Col2,
IIf(Mid(W3,2,2)<>'09',W3,Null) As Col3,
IIf(Mid(W4,2,2)<>'09',W4,Null) As Col4,
IIf(Mid(W5,2,2)<>'09',W5,Null) As Col5,
IIf(Mid(W6,2,2)<>'09',W6,Null) As Col6,
IIf(Mid(W7,2,2)<>'09',W7,Null) As Col7
FROM Adults
WHERE Col1 Is Not Null And Col2 Is Not Null And Col3 Is Not Null
And Col4 Is Not Null And Col5 Is Not Null And Col6 Is Not Null And Col7 Is Not Null;
Oct 23 '06 #15

P: 27
still doesnt give me the output i need. it removes the first name which does not have 09 in it, but then the rest are still there.
what this query is doing is removing the 09 date from the column and displaying all the names.

what i need it to do is to remove the name (with the record) of people who have 09 anywhere in their record

thanks a lot
Oct 23 '06 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
SELECT NAME, IIf(Mid(W1,2,2)<>'09',W1,Null) As Col1,
IIf(Mid(W2,2,2)<>'09',W2,Null) As Col2,
IIf(Mid(W3,2,2)<>'09',W3,Null) As Col3,
IIf(Mid(W4,2,2)<>'09',W4,Null) As Col4,
IIf(Mid(W5,2,2)<>'09',W5,Null) As Col5,
IIf(Mid(W6,2,2)<>'09',W6,Null) As Col6,
IIf(Mid(W7,2,2)<>'09',W7,Null) As Col7
FROM Adults
WHERE Col1 Is Not Null OR Col2 Is Not Null OR Col3 Is Not Null
OR Col4 Is Not Null OR Col5 Is Not Null OR Col6 Is Not Null OR Col7 Is Not Null;
Oct 23 '06 #17

P: 27
SELECT NAME, IIf(Mid(W1,2,2)<>'09',W1,Null) As Col1,
IIf(Mid(W2,2,2)<>'09',W2,Null) As Col2,
IIf(Mid(W3,2,2)<>'09',W3,Null) As Col3,
IIf(Mid(W4,2,2)<>'09',W4,Null) As Col4,
IIf(Mid(W5,2,2)<>'09',W5,Null) As Col5,
IIf(Mid(W6,2,2)<>'09',W6,Null) As Col6,
IIf(Mid(W7,2,2)<>'09',W7,Null) As Col7
FROM Adults
WHERE Col1 Is Not Null OR Col2 Is Not Null OR Col3 Is Not Null
OR Col4 Is Not Null OR Col5 Is Not Null OR Col6 Is Not Null OR Col7 Is Not Null;
that still does not perform the correct query :S
Oct 24 '06 #18

Expert 5K+
P: 8,434
that still does not perform the correct query :S
Could you try spelling out in as much detail as possible, exactly what you want the query to do?
Oct 24 '06 #19

MMcCarthy
Expert Mod 10K+
P: 14,534
Does this make any difference?
SELECT Name, IIf(Mid(W1,2,2)<>'09',W1,Null) As Col1,
IIf(Mid(W2,2,2)<>'09',W2,Null) As Col2,
IIf(Mid(W3,2,2)<>'09',W3,Null) As Col3,
IIf(Mid(W4,2,2)<>'09',W4,Null) As Col4,
IIf(Mid(W5,2,2)<>'09',W5,Null) As Col5,
IIf(Mid(W6,2,2)<>'09',W6,Null) As Col6,
IIf(Mid(W7,2,2)<>'09',W7,Null) As Col7
FROM Adults
WHERE Col1 Is Not Null OR Col2 Is Not Null OR Col3 Is Not Null
OR Col4 Is Not Null OR Col5 Is Not Null OR Col6 Is Not Null OR Col7 Is Not Null GROUP BY Name;
Oct 25 '06 #20

P: 27
Does this make any difference?
SELECT Name, IIf(Mid(W1,2,2)<>'09',W1,Null) As Col1,
IIf(Mid(W2,2,2)<>'09',W2,Null) As Col2,
IIf(Mid(W3,2,2)<>'09',W3,Null) As Col3,
IIf(Mid(W4,2,2)<>'09',W4,Null) As Col4,
IIf(Mid(W5,2,2)<>'09',W5,Null) As Col5,
IIf(Mid(W6,2,2)<>'09',W6,Null) As Col6,
IIf(Mid(W7,2,2)<>'09',W7,Null) As Col7
FROM Adults
WHERE Col1 Is Not Null OR Col2 Is Not Null OR Col3 Is Not Null
OR Col4 Is Not Null OR Col5 Is Not Null OR Col6 Is Not Null OR Col7 Is Not Null GROUP BY Name;
when i execute this query i get the error that the specified expression IIf ... is not part of an aggregate function.

with this query i want to be able to select NAMES of people who do not have 09 in any of the W1 - W7 columns. basically, now i want to result the opposite of what was first done - selecting those that have 09.

i tried doing the simple opposite of using NOT LIKE, but that does not work.

i hope i am a bit more clearer.
Oct 25 '06 #21

Expert 5K+
P: 8,434
when i execute this query i get the error that the specified expression IIf ... is not part of an aggregate function. with this query i want to be able to select NAMES of people who do not have 09 in any of the W1 - W7 columns. basically, now i want to result the opposite of what was first done - selecting those that have 09.
i tried doing the simple opposite of using NOT LIKE, but that does not work.
i hope i am a bit more clearer.
Ok, given this description it sounds extremely simple, and has in fact already been covered by mmccarthy. I think this will do it
Expand|Select|Wrap|Line Numbers
  1. SELECT * From Adults
  2. WHERE ((W1 Not Like "*/09*")    AND (W2 Not Like "*/09*")
  3.   AND (W3 Not Like "*/09*")    AND (W4 Not Like "*/09*")
  4.   AND (W5 Not Like "*/09*")    AND (W6 Not Like "*/09*")
  5.   AND (W7 Not Like "*/09*"));
So, given that this has already been tried and rejected, what is the complication? Is it that you want each name only once? That would be where the aggregate (GROUP BY) comes in.
Oct 25 '06 #22

P: 27
Ok, given this description it sounds extremely simple, and has in fact already been covered by mmccarthy. I think this will do it
Expand|Select|Wrap|Line Numbers
  1. SELECT * From Adults
  2. WHERE ((W1 Not Like "*/09*")    AND (W2 Not Like "*/09*")
  3.   AND (W3 Not Like "*/09*")    AND (W4 Not Like "*/09*")
  4.   AND (W5 Not Like "*/09*")    AND (W6 Not Like "*/09*")
  5.   AND (W7 Not Like "*/09*"));
So, given that this has already been tried and rejected, what is the complication? Is it that you want each name only once? That would be where the aggregate (GROUP BY) comes in.
yes, i did try the above code - basically what comes up are just the column names - no data is shown in the cells.
Oct 25 '06 #23

Expert 5K+
P: 8,434
yes, i did try the above code - basically what comes up are just the column names - no data is shown in the cells.
Well... I hate to ask the obvious, but are you sure there are any records that match the criteria? Maybe all of your records do have a "09" entry somewhere.
Oct 25 '06 #24

P: 27
Well... I hate to ask the obvious, but are you sure there are any records that match the criteria? Maybe all of your records do have a "09" entry somewhere.
yup, i am 100% sure that there are multiple records which do not have 09 anywhere.

i don't know what else to try, you all have been of great help, but nothing seems to be able to execute the result i need :S
Oct 25 '06 #25

Expert 5K+
P: 8,434
yup, i am 100% sure that there are multiple records which do not have 09 anywhere.

i don't know what else to try, you all have been of great help, but nothing seems to be able to execute the result i need :S
This question is directed at someone like mmccarthy, who knows what they're talking about. Can you use functions like Instr() in a query? Perhaps something like this would work...
Expand|Select|Wrap|Line Numbers
  1. SELECT * From Adults
  2. WHERE ((Instr(W1,"/09") = 0)    AND (Instr(W2,"/09") = 0)
  3.   AND (Instr(W3,"/09") = 0)    AND (Instr(W4,"/09") = 0)
  4.   AND (Instr(W5,"/09") = 0)    AND (Instr(W6,"/09") = 0)
  5.   AND (Instr(W7,"/09") = 0));
Oct 25 '06 #26

P: 27
This question is directed at someone like mmccarthy, who knows what they're talking about. Can you use functions like Instr() in a query? Perhaps something like this would work...
Expand|Select|Wrap|Line Numbers
  1. SELECT * From Adults
  2. WHERE ((Instr(W1,"/09") = 0)    AND (Instr(W2,"/09") = 0)
  3.   AND (Instr(W3,"/09") = 0)    AND (Instr(W4,"/09") = 0)
  4.   AND (Instr(W5,"/09") = 0)    AND (Instr(W6,"/09") = 0)
  5.   AND (Instr(W7,"/09") = 0));
ya, i'm not that great at access - just know the basics. hopefully mccarthy can provide some insight!
Oct 25 '06 #27

Expert 5K+
P: 8,434
ya, i'm not that great at access - just know the basics. hopefully mccarthy can provide some insight!
Of course, the best way to learn is probably to try it and see what happens. :)
Oct 25 '06 #28

MMcCarthy
Expert Mod 10K+
P: 14,534
Good suggestion Killer. OK Lets try:

To get all names that do have "09":

SELECT Name, IIf(InStr([W1],"09"),[W1],Null) AS Col1
IIf(InStr([W2],"09"),[W2],Null) As Col2,
IIf(InStr([W3],"09"),[W3],Null) As Col3,
IIf(InStr([W4],"09"),[W4],Null) As Col4,
IIf(InStr([W5],"09"),[W5],Null) As Col5,
IIf(InStr([W6],"09"),[W6],Null) As Col6,
IIf(InStr([W7],"09"),[W7],Null) As Col7
FROM Adults
WHERE (IIf(InStr([W1],"09"),[W1],Null) Is Not Null
AND IIf(InStr([W2],"09"),[W2],Null) Is Not Null
AND IIf(InStr([W3],"09"),[W3],Null) Is Not Null
AND IIf(InStr([W4],"09"),[W4],Null) Is Not Null
AND IIf(InStr([W5],"09"),[W5],Null) Is Not Null
AND IIf(InStr([W6],"09"),[W6],Null) Is Not Null
AND IIf(InStr([W7],"09"),[W7],Null) Is Not Null);

To get all names that don't have "09":

SELECT Name, IIf(InStr([W1],"09"),Null,[W1]) AS Col1
IIf(InStr([W2],"09"),Null,[W2]) As Col2,
IIf(InStr([W3],"09"),Null,[W3]) As Col3,
IIf(InStr([W4],"09"),Null,[W4]) As Col4,
IIf(InStr([W5],"09"),Null,[W5]) As Col5,
IIf(InStr([W6],"09"),Null,[W6]) As Col6,
IIf(InStr([W7],"09"),Null,[W7]) As Col7
FROM Adults
WHERE (IIf(InStr([W1],"09"),[W1],Null) Is Not Null
AND IIf(InStr([W2],"09"),Null,[W2]) Is Not Null
AND IIf(InStr([W3],"09"),Null,[W3]) Is Not Null
AND IIf(InStr([W4],"09"),Null,[W4]) Is Not Null
AND IIf(InStr([W5],"09"),Null,[W5]) Is Not Null
AND IIf(InStr([W6],"09"),Null,[W6]) Is Not Null
AND IIf(InStr([W7],"09"),Null,[W7]) Is Not Null);
Oct 25 '06 #29

P: 27
Good suggestion Killer. OK Lets try:

To get all names that do have "09":

SELECT Name, IIf(InStr([W1],"09"),[W1],Null) AS Col1
IIf(InStr([W2],"09"),[W2],Null) As Col2,
IIf(InStr([W3],"09"),[W3],Null) As Col3,
IIf(InStr([W4],"09"),[W4],Null) As Col4,
IIf(InStr([W5],"09"),[W5],Null) As Col5,
IIf(InStr([W6],"09"),[W6],Null) As Col6,
IIf(InStr([W7],"09"),[W7],Null) As Col7
FROM Adults
WHERE (IIf(InStr([W1],"09"),[W1],Null) Is Not Null
AND IIf(InStr([W2],"09"),[W2],Null) Is Not Null
AND IIf(InStr([W3],"09"),[W3],Null) Is Not Null
AND IIf(InStr([W4],"09"),[W4],Null) Is Not Null
AND IIf(InStr([W5],"09"),[W5],Null) Is Not Null
AND IIf(InStr([W6],"09"),[W6],Null) Is Not Null
AND IIf(InStr([W7],"09"),[W7],Null) Is Not Null);

To get all names that don't have "09":

SELECT Name, IIf(InStr([W1],"09"),Null,[W1]) AS Col1
IIf(InStr([W2],"09"),Null,[W2]) As Col2,
IIf(InStr([W3],"09"),Null,[W3]) As Col3,
IIf(InStr([W4],"09"),Null,[W4]) As Col4,
IIf(InStr([W5],"09"),Null,[W5]) As Col5,
IIf(InStr([W6],"09"),Null,[W6]) As Col6,
IIf(InStr([W7],"09"),Null,[W7]) As Col7
FROM Adults
WHERE (IIf(InStr([W1],"09"),[W1],Null) Is Not Null
AND IIf(InStr([W2],"09"),Null,[W2]) Is Not Null
AND IIf(InStr([W3],"09"),Null,[W3]) Is Not Null
AND IIf(InStr([W4],"09"),Null,[W4]) Is Not Null
AND IIf(InStr([W5],"09"),Null,[W5]) Is Not Null
AND IIf(InStr([W6],"09"),Null,[W6]) Is Not Null
AND IIf(InStr([W7],"09"),Null,[W7]) Is Not Null);
ok, this is giving me an error that the SELECT statement contains a word that is mis-spelled or punctutation is incorrect.
Oct 26 '06 #30

P: 27
oh ya, and i got an easier way (or so it looks simpler) to select those with '09':

SELECT Adults.NAME, W1
FROM Adults
WHERE (((Adults.W1) Like '*09/*'))

UNION
SELECT Adults.NAME, W2
FROM Adults
WHERE (((Adults.W2) Like '*09/*'))

UNION
SELECT Adults.NAME, W3
FROM Adults
WHERE (((Adults.W3) Like '*09/*'))

UNION
SELECT Adults.NAME, W4
FROM Adults
WHERE (((Adults.W4) Like '*09/*'))

UNION
SELECT Adults.NAME, W5
FROM Adults
WHERE (((Adults.W5) Like '*09/*'))

UNION
SELECT Adults.NAME, W6
FROM Adults
WHERE (((Adults.W6) Like '*09/*'))

UNION SELECT Adults.NAME, W7
FROM Adults
WHERE (((Adults.W7) Like '*09/*'));
Oct 26 '06 #31

Expert 5K+
P: 8,434
I don't like to go up against the master :) but I do have to question the "wordiness" of the negative query. The whole IIF() thing seems pointless, since by definition it is always false. In other words, where you say
SELECT Name, IIf(InStr([W1],"09"),Null,[W1]) AS Col1
IIf(InStr([W2],"09"),Null,[W2]) As Col2,
IIf(InStr([W3],"09"),Null,[W3]) As Col3,
IIf(InStr([W4],"09"),Null,[W4]) As Col4,
IIf(InStr([W5],"09"),Null,[W5]) As Col5,
IIf(InStr([W6],"09"),Null,[W6]) As Col6,
IIf(InStr([W7],"09"),Null,[W7]) As Col7
FROM Adults
WHERE (IIf(InStr([W1],"09"),[W1],Null) Is Not Null
AND IIf(InStr([W2],"09"),Null,[W2]) Is Not Null
AND IIf(InStr([W3],"09"),Null,[W3]) Is Not Null
AND IIf(InStr([W4],"09"),Null,[W4]) Is Not Null
AND IIf(InStr([W5],"09"),Null,[W5]) Is Not Null
AND IIf(InStr([W6],"09"),Null,[W6]) Is Not Null
AND IIf(InStr([W7],"09"),Null,[W7]) Is Not Null);
Wouldn't you get much the same result with
Expand|Select|Wrap|Line Numbers
  1. SELECT Name, W1, W2, W3, W4, W5, W6, W7
  2. FROM Adults 
  3. WHERE (IIf(InStr([W1],"09/"),[W1],Null) Is Not Null
  4. AND IIf(InStr([W2],"09/"),Null,[W2]) Is Not Null
  5. AND IIf(InStr([W3],"09/"),Null,[W3]) Is Not Null
  6. AND IIf(InStr([W4],"09/"),Null,[W4]) Is Not Null
  7. AND IIf(InStr([W5],"09/"),Null,[W5]) Is Not Null
  8. AND IIf(InStr([W6],"09/"),Null,[W6]) Is Not Null
  9. AND IIf(InStr([W7],"09/"),Null,[W7]) Is Not Null);
Haha... I was just about to say you had the Is Not Null backward here, too, when I realised you had reversed the order of the True/False results. Tricky! :)
Anyway, maybe it would still work if we boiled it down to a simplified version like
Expand|Select|Wrap|Line Numbers
  1. SELECT Name, W1, W2, W3, W4, W5, W6, W7
  2. FROM Adults 
  3. WHERE (Mid([W1],2,2)<>"09"
  4. AND Mid([W2],2,2)<>"09"
  5. AND Mid([W3],2,2)<>"09"
  6. AND Mid([W4],2,2)<>"09"
  7. AND Mid([W5],2,2)<>"09"
  8. AND Mid([W6],2,2)<>"09"
  9. AND Mid([W7],2,2)<>"09");
This seems as though it would be slightly more efficient, if it works.
Oct 26 '06 #32

Expert 5K+
P: 8,434
ok, this is giving me an error that the SELECT statement contains a word that is mis-spelled or punctutation is incorrect.
I believe there's a comma missing after Col1.
Oct 26 '06 #33

MMcCarthy
Expert Mod 10K+
P: 14,534
Just a missing comma, try again:

To get all names that do have "09":

SELECT Name, IIf(InStr([W1],"09"),[W1],Null) AS Col1,
IIf(InStr([W2],"09"),[W2],Null) As Col2,
IIf(InStr([W3],"09"),[W3],Null) As Col3,
IIf(InStr([W4],"09"),[W4],Null) As Col4,
IIf(InStr([W5],"09"),[W5],Null) As Col5,
IIf(InStr([W6],"09"),[W6],Null) As Col6,
IIf(InStr([W7],"09"),[W7],Null) As Col7
FROM Adults
WHERE (IIf(InStr([W1],"09"),[W1],Null) Is Not Null
AND IIf(InStr([W2],"09"),[W2],Null) Is Not Null
AND IIf(InStr([W3],"09"),[W3],Null) Is Not Null
AND IIf(InStr([W4],"09"),[W4],Null) Is Not Null
AND IIf(InStr([W5],"09"),[W5],Null) Is Not Null
AND IIf(InStr([W6],"09"),[W6],Null) Is Not Null
AND IIf(InStr([W7],"09"),[W7],Null) Is Not Null);

To get all names that don't have "09":

SELECT Name, IIf(InStr([W1],"09"),Null,[W1]) AS Col1,
IIf(InStr([W2],"09"),Null,[W2]) As Col2,
IIf(InStr([W3],"09"),Null,[W3]) As Col3,
IIf(InStr([W4],"09"),Null,[W4]) As Col4,
IIf(InStr([W5],"09"),Null,[W5]) As Col5,
IIf(InStr([W6],"09"),Null,[W6]) As Col6,
IIf(InStr([W7],"09"),Null,[W7]) As Col7
FROM Adults
WHERE (IIf(InStr([W1],"09"),[W1],Null) Is Not Null
AND IIf(InStr([W2],"09"),Null,[W2]) Is Not Null
AND IIf(InStr([W3],"09"),Null,[W3]) Is Not Null
AND IIf(InStr([W4],"09"),Null,[W4]) Is Not Null
AND IIf(InStr([W5],"09"),Null,[W5]) Is Not Null
AND IIf(InStr([W6],"09"),Null,[W6]) Is Not Null
AND IIf(InStr([W7],"09"),Null,[W7]) Is Not Null);
Oct 26 '06 #34

NeoPa
Expert Mod 15k+
P: 31,485
That's a beautiful and elegant result.
Bear in mind, though, that this may produce multiple records for the same NAME.
Also, probably not a problem, the resulting set shows all the W fields as W1 regardless of which field they came from.
Oct 26 '06 #35

Expert 5K+
P: 8,434
SELECT Adults.NAME, W1
FROM Adults
WHERE (((Adults.W1) Like '*09/*'))
UNION
SELECT Adults.NAME, W2
FROM Adults
WHERE (((Adults.W2) Like '*09/*'))
Could be good, but I think this will potentially return the same record multiple times. Is this a problem?
Oct 26 '06 #36

Expert 5K+
P: 8,434
These overlapping replies get a bit confusing at times, huh.
Oct 26 '06 #37

NeoPa
Expert Mod 15k+
P: 31,485
I just replied to T.:-)'s post (#31) and when I refresh, you guys have fitted in 3 (THREE) extra posts.
I'm off to bed.
Oct 26 '06 #38

MMcCarthy
Expert Mod 10K+
P: 14,534
I don't like to go up against the master :) but I do have to question the "wordiness" of the negative query. The whole IIF() thing seems pointless, since by definition it is always false. In other words, where you say
You can go against me anytime you like. I'm far from a master. Like most people in our game I picked up most of my information the hard way. That can leave some interesting gaps in knowledge.

You could very well be right about the query. I've done it so many times now I got locked into one approach. My biggest problem with queries is I get so bogged down in the logic I sometimes forget the preformance.
Oct 26 '06 #39

NeoPa
Expert Mod 15k+
P: 31,485
These overlapping replies get a bit confusing at times, huh.
roflmao
I was just gonna post that but I needed at least 10 chars - lol
Oct 26 '06 #40

P: 27
Could be good, but I think this will potentially return the same record multiple times. Is this a problem?
nope, this one actually does what i want it too ... let me try the other stuff you all have posted, i shall be bak with a reply to the others
Oct 26 '06 #41

P: 27
I don't like to go up against the master :) but I do have to question the "wordiness" of the negative query. The whole IIF() thing seems pointless, since by definition it is always false. In other words, where you say
Wouldn't you get much the same result with
Expand|Select|Wrap|Line Numbers
  1. SELECT Name, W1, W2, W3, W4, W5, W6, W7
  2. FROM Adults 
  3. WHERE (IIf(InStr([W1],"09/"),[W1],Null) Is Not Null
  4. AND IIf(InStr([W2],"09/"),Null,[W2]) Is Not Null
  5. AND IIf(InStr([W3],"09/"),Null,[W3]) Is Not Null
  6. AND IIf(InStr([W4],"09/"),Null,[W4]) Is Not Null
  7. AND IIf(InStr([W5],"09/"),Null,[W5]) Is Not Null
  8. AND IIf(InStr([W6],"09/"),Null,[W6]) Is Not Null
  9. AND IIf(InStr([W7],"09/"),Null,[W7]) Is Not Null);
Haha... I was just about to say you had the Is Not Null backward here, too, when I realised you had reversed the order of the True/False results. Tricky! :)
Anyway, maybe it would still work if we boiled it down to a simplified version like
Expand|Select|Wrap|Line Numbers
  1. SELECT Name, W1, W2, W3, W4, W5, W6, W7
  2. FROM Adults 
  3. WHERE (Mid([W1],2,2)<>"09"
  4. AND Mid([W2],2,2)<>"09"
  5. AND Mid([W3],2,2)<>"09"
  6. AND Mid([W4],2,2)<>"09"
  7. AND Mid([W5],2,2)<>"09"
  8. AND Mid([W6],2,2)<>"09"
  9. AND Mid([W7],2,2)<>"09");
This seems as though it would be slightly more efficient, if it works.
This code atleast generates a result. But another problem - I need to have the LIKE. The string in the field ie. T09/15 mean 09 - september and 15 - date .... if a name has D07/09 it also excludes this one - when in fact I just want to select ones that do not have September (09) ... therefore, I would have a whole lot more results.
Oct 26 '06 #42

Expert 5K+
P: 8,434
This code atleast generates a result. But another problem - I need to have the LIKE. The string in the field ie. T09/15 mean 09 - september and 15 - date .... if a name has D07/09 it also excludes this one - when in fact I just want to select ones that do not have September (09) ... therefore, I would have a whole lot more results.
No, that's not right. The Mid() function in that code specifies the 2nd and 3rd characters only, so the day would not be picked up. The parameters for Mid() are (String, startpos, length). If you used the Instr() function or the Like operator to scan for the month then you would need to specify "09/" to avoid the day.
Oct 26 '06 #43

P: 27
No, that's not right. The Mid() function in that code specifies the 2nd and 3rd characters only, so the day would not be picked up. The parameters for Mid() are (String, startpos, length). If you used the Instr() function or the Like operator to scan for the month then you would need to specify "09/" to avoid the day.

killer42 - i meant to say that YOUR code worked for me.
mmccarthy's is still not working.

i have used the 09/ with LIKE in my very first query about showing just those in September, thats why that one works like a charm. but this one is still giving problems ...
Oct 26 '06 #44

MMcCarthy
Expert Mod 10K+
P: 14,534
killer42 - i meant to say that YOUR code worked for me.
mmccarthy's is still not working.

i have used the 09/ with LIKE in my very first query about showing just those in September, thats why that one works like a charm. but this one is still giving problems ...
What error are you getting on mine?
Oct 26 '06 #45

P: 27
What error are you getting on mine?

it is not returning any records, when i know that in fact there are dozens which should be returned.

all that comes up are the column names, and one record of empty cells.
Oct 26 '06 #46

P: 27
i tried to do this, so that i can make use of the like which i need - but it too is not working to remove all names which have month as 09

Expand|Select|Wrap|Line Numbers
  1. SELECT Name, W1, W2, W3, W4, W5, W6, W7
  2. FROM Adults 
  3. WHERE (Mid([W1],2,2)<>"*09/*"
  4. AND Mid([W2],2,2)<>"*09/*"
  5. AND Mid([W3],2,2)<>"*09/*"
  6. AND Mid([W4],2,2)<>"*09/*"
  7. AND Mid([W5],2,2)<>"*09/*"
  8. AND Mid([W6],2,2)<>"*09/*"
  9. AND Mid([W7],2,2)<>"*09/*");
  10.  
any more ideas, please
Oct 26 '06 #47

NeoPa
Expert Mod 15k+
P: 31,485
i tried to do this, so that i can make use of the like which i need - but it too is not working to remove all names which have month as 09

Expand|Select|Wrap|Line Numbers
  1. SELECT Name, W1, W2, W3, W4, W5, W6, W7
  2. FROM Adults 
  3. WHERE (Mid([W1],2,2)<>"*09/*"
  4. AND Mid([W2],2,2)<>"*09/*"
  5. AND Mid([W3],2,2)<>"*09/*"
  6. AND Mid([W4],2,2)<>"*09/*"
  7. AND Mid([W5],2,2)<>"*09/*"
  8. AND Mid([W6],2,2)<>"*09/*"
  9. AND Mid([W7],2,2)<>"*09/*");
  10.  
any more ideas, please
I thought you had what you wanted earlier...
But that's by the by.

In the code above you are comparing two character strings (Mid([W6],2,2)) against 5 character strings ("*09/*").
These will ALWAYS be <>.

The "*"s should only be used in a Like construct.

Try
Expand|Select|Wrap|Line Numbers
  1. WHERE [W1] & [W2] & ... & [W7] Not Like "*09/*"
Oct 26 '06 #48

P: 27
I thought you had what you wanted earlier...
But that's by the by.

In the code above you are comparing two character strings (Mid([W6],2,2)) against 5 character strings ("*09/*").
These will ALWAYS be <>.

The "*"s should only be used in a Like construct.

Try
Expand|Select|Wrap|Line Numbers
  1. WHERE [W1] & [W2] & ... & [W7] Not Like "*09/*"
I get syntax error when I try to do the [].
Just another thought, when i do the following:

Expand|Select|Wrap|Line Numbers
  1. SELECT Name, W1, W2, W3, W4, W5, W6, W7
  2. FROM Adults
  3. WHERE W1 Not Like "*09/*" OR W2 Not Like "*09/*" OR W3 Not Like "*09/*" OR W4 Not Like "*09/*" OR W5 Not Like "*09/*" OR W6 Not Like "*09/*" OR W7 Not Like "*09/*";
  4.  
all the names for W1's that have 09 as their month are eliminated, which is exactly what i want. but anything after that it is not picking up (ie. if a name has 09 in W2 or W6 - it will not eliminate this persons name). it seems that it would work, but it is just not displaying the rest of it.
Oct 26 '06 #49

NeoPa
Expert Mod 15k+
P: 31,485
I get syntax error when I try to do the [].
I normally put () parentheses around the WHERE parameters. I don't know why I left it off on this occasion but maybe that's where your syntax error is. Also, the [] are often unnecessary but they indicate a name of some sort when there could be any ambiguity. for instance, you often see posted on here field names as Date. Now Date is a word that's already defined as a function returning the current date so [Date] indicates that you want the field & not to confuse it with the function. It's also useful for where objects have embedded spaces so,
Expand|Select|Wrap|Line Numbers
  1. SELECT My Field FROM BlahBlah
would not work but
Expand|Select|Wrap|Line Numbers
  1. SELECT [My Field] FROM BlahBlah
would.

Question?
In all of this you're looking for :-
Records in which ANY one or more of the Wn fields coontain "09/" and
Records in which NONE of the Wn fields coontain "09/".

In that case you want :-
Expand|Select|Wrap|Line Numbers
  1. WHERE ([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] Like "*09/*")
and
Expand|Select|Wrap|Line Numbers
  1. WHERE ([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] Not Like "*09/*")
respectively. I think parentheses () will work in place of the brackets [] if you prefer.
Oct 26 '06 #50

60 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.