SQL Query - Returning One Specific Column

[tdotsmiley]

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.

[tdotsmiley]

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

SELECT Name, W1, W2, W3, W4, W5, W6, W7
FROM Adults 
WHERE (Mid([W1],2,2)<>"*09/*"
AND Mid([W2],2,2)<>"*09/*"
AND Mid([W3],2,2)<>"*09/*"
AND Mid([W4],2,2)<>"*09/*"
AND Mid([W5],2,2)<>"*09/*"
AND Mid([W6],2,2)<>"*09/*"
AND Mid([W7],2,2)<>"*09/*");

any more ideas, please

[NeoPa]

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

SELECT Name, W1, W2, W3, W4, W5, W6, W7
FROM Adults 
WHERE (Mid([W1],2,2)<>"*09/*"
AND Mid([W2],2,2)<>"*09/*"
AND Mid([W3],2,2)<>"*09/*"
AND Mid([W4],2,2)<>"*09/*"
AND Mid([W5],2,2)<>"*09/*"
AND Mid([W6],2,2)<>"*09/*"
AND Mid([W7],2,2)<>"*09/*");

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

WHERE [W1] & [W2] & ... & [W7] Not Like "*09/*"

[tdotsmiley]

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

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:

SELECT Name, W1, W2, W3, W4, W5, W6, W7
FROM Adults
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/*";

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.

[NeoPa]

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,

SELECT My Field FROM BlahBlah

would not work but

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 :-

WHERE ([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] Like "*09/*")

and

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.

[tdotsmiley]

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,

SELECT My Field FROM BlahBlah

would not work but

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 :-

WHERE ([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] Like "*09/*")

and

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.

NeoPa - YOU ROCK!!!
Thanks so muchhh for the explanation! The code works beautifully! I'm amazed, and it is a whole lot shorter which makes it easier to follow!

Thank you again for all your help! :-)
Much Appreciated!

[MMcCarthy]

NeoPa - YOU ROCK!!!
Thanks so muchhh for the explanation! The code works beautifully! I'm amazed, and it is a whole lot shorter which makes it easier to follow!

Thank you again for all your help! :-)
Much Appreciated!

I couldn't agree more. ** Nice solution **

Congratulations to both of you.

I think this thread needs to be consigned to the annals of miscommunicatio ns.

[tdotsmiley]

I couldn't agree more. ** Nice solution **

Congratulations to both of you.

I think this thread needs to be consigned to the annals of miscommunicatio ns.

one more catch .. lol
(just when you thought i would leave you all alone ;-))

how can i use null in line ([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9] = Null) - this does not work correctly - I want it to also display names which have no contents in them.

I have been able to succesfully get names to show for:

SELECT Name
FROM YoungAdults
WHERE ([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9] Not Like "*08/*") And ([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9] Not Like "*09/*") And ([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9] Not Like "*10/*");

just that now i would like to add that those that have no data in them, that they also be shown.

[NeoPa]

You're really getting your money's worth T. aren't you. Up to NINE W fields now I notice :-(.

Anyway, to the point, you can either put the whole construct [W1] & [W2] & ... & [W9] inside an IsNull() function or prepend an empty string - that way all nulls will be converted to empty strings and you can compare it to an empty string to determine if all are nulls (IE

IsNull([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9])

Or

"" & [W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9] = ""

).

[tdotsmiley]

You're really getting your money's worth T. aren't you. Up to NINE W fields now I notice :-(.

Anyway, to the point, you can either put the whole construct [W1] & [W2] & ... & [W9] inside an IsNull() function or prepend an empty string - that way all nulls will be converted to empty strings and you can compare it to an empty string to determine if all are nulls (IE

IsNull([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9])

Or

"" & [W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9] = ""

).

i tried:

SELECT Name
FROM YoungAdults
WHERE ([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9] Not Like "*08/*") And ([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9] Not Like "*09/*") And ([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9] Not Like "*10/*") And IsNull([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9]);

but I get just an empty field of Name - no results are displayed.

[NeoPa]

You just tried that one version?
Did you try looking at it to see where it might have failed?
Did you try the other way of checking for nulls that I suggested earlier?
Did you try adding () around your complex WHERE clause?

I hope you did all these things before posting here for someone else to try it for you.

[Killer42]

Hi again.

I gather you have your answer, so I won't go into that. Just wanted to point out something you should watch out for. You wrote

SELECT Name, W1, W2, W3, W4, W5, W6, W7
FROM Adults
WHERE W1 Not Like "*09/*" 
  OR W2 Not Like "*09/*" 
  OR W3 Not Like "*09/*"
  OR W4 Not Like "*09/*"
   ...

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.

This sort of combination of "OR" with negative logic has to be watched carefully, or it will bite you. The logic you have here would, in fact, choose every record which has any W field that doesn't have "09/" in it. At a guess is, that's probably every record.

[tdotsmiley]

You just tried that one version?
Did you try looking at it to see where it might have failed?
Did you try the other way of checking for nulls that I suggested earlier?
Did you try adding () around your complex WHERE clause?

I hope you did all these things before posting here for someone else to try it for you.

- i tried both versions - with the empty string "" and with the isNull clause
- i added ( ) around my WHERE clause in both of the versions
- still returns one empty cell of NAME ...

[MMcCarthy]

 
SELECT Name ,  ([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9]) As W_Fields
FROM YoungAdults
WHERE (([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9] Not Like "*08/*")
And ([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9] Not Like "*09/*") 
And ([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9] Not Like "*10/*"))
OR IsNull([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9]);

Try this.....

[tdotsmiley]

 
SELECT Name ,  ([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9]) As W_Fields
FROM YoungAdults
WHERE (([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9] Not Like "*08/*")
And ([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9] Not Like "*09/*") 
And ([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9] Not Like "*10/*"))
OR IsNull([W1] & [W2] & [W3] & [W4] & [W5] & [W6] & [W7] & [W8] & [W9]);

Try this.....

thats mmcarthy - that does the job :-)