Just starting, be gentle... (1 Viewer)

[Craized]

Hello,

I need to write a query that shows all records if any 'L' field starts with D. I have written this, but it's only pulling records if L1 starts with D.

SELECT Item, Description, L1, L2, L3, L4, L5, L6, L7, L8, L9, L10, L11, L12
FROM Table5
WHERE ((L1 LIKE 'D*') OR (L2 LIKE 'D*') OR (L3 LIKE 'D*') OR (L4 LIKE 'D*') OR (L5 LIKE 'D*') OR (L6 LIKE 'D*') OR (L7 LIKE 'D*') OR (L8 LIKE 'D*') OR (L9 LIKE 'D*') OR (L10 LIKE 'D*') OR (L11 LIKE 'D*') OR (L12 LIKE 'D*'));

Any help is appreciated!

 

[themurph2000]

HOW COULD YOU WRITE @#$%@# LIKE THIS??? THIS IS THE WORST PIECE OF CODING I'VE EVER...

(just kidding )

Not sure what's going on. I just tried it myself and added the corresponding data. It took the correct reords.
Here it is when I entered it into my database.

SELECT Item, Description, L1, L2, L3, L4, L5, L6, L7, L8, L9, L10, L11, L12
FROM Table1
WHERE ((L1 LIKE 'D*') OR (L2 LIKE 'D*') OR (L3 LIKE 'D*') OR (L4 LIKE 'D*') OR (L5 LIKE 'D*') OR (L6 LIKE 'D*') OR (L7 LIKE 'D*') OR (L8 LIKE 'D*') OR (L9 LIKE 'D*') OR (L10 LIKE 'D*') OR (L11 LIKE 'D*') OR (L12 LIKE 'D*'));

This is the sort of thing where it's good to start it in the QBE grid in Access, then right-click and choose SQL View because you can then see the pattern and C&P it the rest of the way.

 

[Craized]

Thanks for replying Murph.

The QBE grid is more confusing to me than just writing code. I cannot, however, figure out what the problem is here. And it's working for you? I don't know what to do...

 

[ConnorGiles]

Hello,

I need to write a query that shows all records if any 'L' field starts with D. I have written this, but it's only pulling records if L1 starts with D.

SELECT Item, Description, L1, L2, L3, L4, L5, L6, L7, L8, L9, L10, L11, L12
FROM Table5
WHERE ((L1 LIKE 'D*') OR (L2 LIKE 'D*') OR (L3 LIKE 'D*') OR (L4 LIKE 'D*') OR (L5 LIKE 'D*') OR (L6 LIKE 'D*') OR (L7 LIKE 'D*') OR (L8 LIKE 'D*') OR (L9 LIKE 'D*') OR (L10 LIKE 'D*') OR (L11 LIKE 'D*') OR (L12 LIKE 'D*'));

Any help is appreciated!

Welcome to AWF!

If you would post a sample database of yours - I would be able to show you (I find it easier )

 

[Craized]

Not sure what the correct protocol for sharing tables on here is, but here it is in Excel. Thank you.

 

[ConnorGiles]

I should have attached the working one, That was what you asked for wasn't it - Everything in L1 starting with the letter D - In design instead of SQL use

Like 'D*'

Try that bud - feel free to ask if its not

 

[Craized]

Connor,

I need it to return records if any of the L fields start with D, not just L1. My problem is that with the OR statement I wrote, it still seems to be checking only L1.

 

[ConnorGiles]

Hows This?

Hope it works!

If it doesn't leave me a message, I'll be with you tomorrow

 

[Craized]

I don't see a difference, even the query is the same.

 

[ConnorGiles]

Im just going to give you the SQL

SELECT Table5.ID, Table5.Item, Table5.Description, Table5.L1, Table5.L2, Table5.L3, Table5.L4, Table5.L5, Table5.L6, Table5.L7, Table5.L8, Table5.L9, Table5.L10, Table5.L11, Table5.L12
FROM Table5
WHERE (((Table5.L1) Like 'D*')) OR (((Table5.L2) Like 'D*')) OR (((Table5.L3) Like 'D*')) OR (((Table5.L4) Like 'D*')) OR (((Table5.L5) Like 'D*')) OR (((Table5.L6) Like 'D*')) OR (((Table5.L7) Like 'D*')) OR (((Table5.L8) Like 'D*')) OR (((Table5.L9) Like 'D*')) OR (((Table5.L10) Like 'D*')) OR (((Table5.L11) Like 'D*')) OR (((Table5.L12) Like 'D*'));

That works in my query

 

[Craized]

Your SQL works when I put it into your database, but still gets the same results in mine???

 

[ConnorGiles]

What version of Access do you run?

 

[Craized]

2013 (adding words to satisfy the forum )

 

[ConnorGiles]

My database is run via Access 2007 - Maybe it is just a problem with Access 2013?

If my database I have supplied does the trick and it holds all of the required data for your database.

Why not use that database?

 

[BlueIshDan]

try removing all brackets

 

[BlueIshDan]

Also, maybe send us a screen shot of your table1 definition design view.

 

[BlueIshDan]

SELECT 
	Item, 
	Description, 
	[L1], 
	[L2], 
	[L3], 
	[L4], 
	[L5], 
	[L6], 
	[L7], 
	[L8], 
	[L9], 
	[L10], 
	[L11], 
	[L12]

FROM Table5

WHERE 
	[L1] LIKE "D*" OR 
	[L2] LIKE "D*" OR 
	[L3] LIKE "D*" OR 
	[L4] LIKE "D*" OR 
	[L5] LIKE "D*" OR 
	[L6] LIKE "D*" OR 
	[L7] LIKE "D*" OR 
	[L8] LIKE "D*" OR 
	[L9] LIKE "D*" OR 
	[L10] LIKE "D*" OR 
	[L11] LIKE "D*" OR 
	[L12] LIKE "D*"

 

[Craized]

My database is run via Access 2007 - Maybe it is just a problem with Access 2013?

If my database I have supplied does the trick and it holds all of the required data for your database.

Why not use that database?

I can and I have, but would like to know what's wrong with my database so I can avoid it in the future.

 

[ConnorGiles]

That's Great Crazied, Glad its sorted.

As BlueIshDan has said maybe it was a problem with the brackets.

I would need to be there Crazied for a trial and error basis because I'm more of a visual man myself, just reading doesn't really paint a picture

 

[Craized]

I would attach my database but it's 8MB and the forum won't allow it. I can't figure out why it's so much bigger than yours. It only has the one table just like yours.