May 9, 2012 at 1:03 pm
one thing I noticed - if you're using MSAccess, then this line
WHERE qryUC.Lyrics Like "%climb%"
needs to be
WHERE qryUC.Lyrics Like "*climb*"
in SQL Server it would be
WHERE qryUC.Lyrics Like '%climb%'
(single quote not double)
May 10, 2012 at 6:43 pm
Uripedes Pants (5/9/2012)
one thing I noticed - if you're using MSAccess, then this lineWHERE qryUC.Lyrics Like "%climb%"
needs to be
WHERE qryUC.Lyrics Like "*climb*"
Yes, but for reasons I know not, the ADO connection requires you to use the same format as SQL Server. Although I think both single and double work the same.
May 11, 2012 at 2:08 am
Hi Vinu,
I had a good read of the link you suggested. If anything its put me off asking anymore questions here, what I'm hoping to do doesn't sit with any of that.
I probasbly need a less-advanced forum. Those examples - or whatever that were -are nothing like I've ever seen.
But was worth a shot, thanks. 🙂
Cheers, Kirk
May 11, 2012 at 2:17 am
kirkm 55368 (5/7/2012)
Sure, thanks. But it's still being designed (badly!)SELECT qryUC.Serial, qryUC.Title, qryUC.Artist, qryUC.Lyrics
FROM qryUC INNER JOIN tblAAA ON qryUC.Serial = tblAAA.Prefix
WHERE (((qryUC.Lyrics) Like "*climb*"));
So it's where instr(qryUC.Serial,tblAAA.Prefix) > 0.
I'm not even sure if I should have the join.
i think what you mean is change your join
to
INNER JOIN tblAAA ON qryUC.Serial like '%'+tblAAA.Prefix+'%'
this will be quite slow though
MVDBA
May 11, 2012 at 5:43 am
kirkm 55368 (5/11/2012)
Hi Vinu,I had a good read of the link you suggested. If anything its put me off asking anymore questions here, what I'm hoping to do doesn't sit with any of that.
I probasbly need a less-advanced forum. Those examples - or whatever that were -are nothing like I've ever seen.
But was worth a shot, thanks. 🙂
Cheers, Kirk
I'm not sure what link you are talking about that Vinu suggested. If it was the link to the Etiquette article written by Jeff Moden, the purpose behind that article was to show you what you need to post and how to post it so that you get the best help possible. It was't a possible solution to your problem.
May 11, 2012 at 7:13 am
It was that link Lynn, and fair enough too if that's what's needed. Its just the rigid structure for questions, and request to format data as per examples are rather daunting. I couldn't see how to relate that to my data, or question.The implication you might be ignored if not done is a bit discouraging.
Heck, for me just phrasing the question can be a real challenge and take a long time! C'est la vie!
Mike, thank you. That didn't error but found zero records. Never ever seen that '+' character. used before.
May 11, 2012 at 8:07 am
kirkm 55368 (5/11/2012)
It was that link Lynn, and fair enough too if that's what's needed. Its just the rigid structure for questions, and request to format data as per examples are rather daunting. I couldn't see how to relate that to my data, or question.The implication you might be ignored if not done is a bit discouraging.Heck, for me just phrasing the question can be a real challenge and take a long time! C'est la vie!
Mike, thank you. That didn't error but found zero records. Never ever seen that '+' character. used before.
Here is part of our problem, we can't see what you are doing, and many times the questions asked just don't give enough information to allow us to really help.
Also, there is more to what we asking for if you take the time to think about it. As you go through the steps of setting up the tables, the sample data, what your expected results should be , you actually give yourself the opportunity to think about your problem some more. We have had people actually solve their own problems by going through this process as it made them think about it in a different way.
We aren't trying to be ridged, we are trying to provide you with the best possible answers, and give you tested code in return. Our goal is to help you learn and expand your knowledge. One day you may be the one trying to answer someones question and find yourself asking them for the same information we have asked from you.
May 11, 2012 at 8:16 am
+1 lynn
the more info you give us and the clearer the question, the more likely you are to get a well formed and correct response.
look at it this way - we like to help but we don't like people wasting our time because they are "lazy" (i'm not saying you are) - imaging you went for a college exam and the questions lacked enough detail for you to correctly answer..
by the way
maybe i got the fields the wrong way round in the response i sent
try it the other way and see if you get the answer you want - (i don't know which way round the instr parameters are used - hence i may have been wrong)
INNER JOIN tblAAA ON tblAAA.Prefixlike '%'+qryUC.Serial+'%'
MVDBA
May 11, 2012 at 8:31 pm
Mike, appreciate the suggestion. I think the first was right as qryUC.serial has the full string and tblAAA.Prefix part of it. Both ways worked, but gave zero output. Maybe it's right... if I leave that join out and just use the following, there's 31 records output.
SELECT qryUC.Serial, qryUC.Title, qryUC.Artist, qryUC.Lyrics
FROM qryUC
WHERE qryUC.Lyrics Like "*climb*";
Mike & Lynn, thanks for the replies. I understand you need the data and structure to see the whole picture. But there's commands in those instructions I haven't got to use, and my data consists of 3 quite large tables. The biggest has 28000 records and over 90 fields.
Reading the article again, I think the instructions are to create a table and sample data and Vinu said also an example of wanted output.
I can make one of each table - with say, 2 records in each and (hopefully) convert to a different format, if .mdb is no good. Plus screenshot some dummy output that would be the result if/when this is working.
Is that worth a try? I can give a link to the tables files if I'm able to create the right format. On the other hand, I may be asking too much, so please ignore me if that's the case! I will get there eventually !
Regards, Kirk
May 12, 2012 at 5:04 am
I can make one of each table - with say, 2 records in each and (hopefully) convert to a different format, if .mdb is no good. Plus screenshot some dummy output that would be the result if/when this is working.
We all understand SQL, right? Please provide us the SQL statements for create table, insert into table and your query (you already did). Screenshots and *.mdb file won't be good.
Is that worth a try? I can give a link to the tables files if I'm able to create the right format.
errr... Please don't share those files. We discourage this practice for security reasons.
On the other hand, I may be asking too much, so please ignore me if that's the case! I will get there eventually !
hey... when I was new to SQL (well comparatively), I was asking hell lot of questions to my friends, colleagues, teachers. You are far behind than my record so keep asking questions. 🙂
May 12, 2012 at 7:48 am
Thanks for the reply Dev.
I do appreciate everyone's comments.
Cheers - Kirk
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply