Last post Jul 26, 2009 08:49 AM by shyguyjeff
Jul 25, 2009 02:29 AM|shyguyjeff|LINK
Hi everyone, I have a problem regarding on getting the value of the string. For example I have '345' on my string and on my database I have field of tables that the strings are divisible by 3 like for example 934526345789 and it was stored on my msaccess'97
database. My problem is that I am comparing the strings i have, if it is existing or not but my problem is that on the database field it should be chunk 3 by 3. Like the example I have, it is 934,526,345,789 so only 1 number of 345 is there. But if you observed
there is another 345 there but chunk therefore it is not the strings same as what i have on my strings. So if in the database there is 934526, the string that i have that was '345' is not existing to that value because it is 934,526. Below is my query where
i used but seems like it is incorrect.
CSClass = '123'
strSQL = "SELECT ClassificationCode, ClassCode FROM Classification WHERE ClassificationCode LIKE '" & CSClass.ToString() & "%'"
Could anybody help me to solve this? The query or the syntax on how to get that. Thanks in advance.
Jul 25, 2009 07:30 AM|hans_v|LINK
If 934526345789 actually mean 4 different numbers (934, 526, 343, 789) why not store them in seperate fields, and when retrieving the data, just paste them back together?
Otherwise, you need to use the Mid (function):
SELECT ClassificationCode, ClassCode FROM Classification WHERE Mid(ClassificationCode, 1,3) = [@CsClass] OR Mid(ClassificationCode= [@CsClass], 4,3) OR Mid(ClassificationCode, 7,3)= [@CsClass] OR Mid(ClassificationCode, 10,3)= [@CsClass]
I used [@CsClass] AS a parameter. In case you don't know how to handle those, read:
Jul 25, 2009 09:33 AM|shyguyjeff|LINK
Thanks for the response hans_v. Is it possible to call that query for msaccess 97? Well, sometimes the classificationcode contains 3,6,9 character as long as it is divisible by 3. I cannot split them one by one because the data was already stored on my database.
Jul 25, 2009 09:54 AM|hans_v|LINK
Is it possible to call that query for msaccess 97?
I cannot split them one by one because the data was already stored on my database.
You don;t have to split them one by one. Using the Mid function it is very easy to create a new table with seperate columns using only 1 INSERT statement for all records!
Jul 26, 2009 02:58 AM|shyguyjeff|LINK
Thanks again hans_v but still i did not get it.
I try this query:
Dim myString As String
myString = Textbox1.text
strSQL = "SELECT cd.ClientCode, cd.CompanyName, ct.CityName, ct.CountryName, cd.POBox, cd.TelNo1, cd.TelNo2, cd.FaxNo, cd.Email, cd.Web, cd.Details, cd.ClassCode, ct.AreaCode FROM ClientDetails as cd, City as ct, Classification as cl " & _
"WHERE Mid(cd.ClassCode, 1,3) = [@myString] OR Mid(cd.ClassCode = [@myString], 4,3) OR Mid(cd.ClassCode, 7,3)= [@myString] OR Mid(cd.ClassCode, 10,3)= [@myString]AND cd.CityCode = ct.CityCode AND (ct.CountryName LIKE '" & lblLocation.Text.ToString() & "%') AND cl.Classification LIKE '" & lblKeyword.Text.ToString() & "' ORDER BY cd.CompanyName ASC"
but seems like nothing happen. I will make another try and update this post if ever i get it.
Jul 26, 2009 07:44 AM|hans_v|LINK
Do some further reading on Optional Parameters:
Is see that you're using 3 parameters (use parameters always! ):
[@mylocation] = lblLocation.Text.ToString
[@keyword] = lblkeyword.Text.ToString
with the last parameter you're using the Like parameter without a wildcard character, is that what you want? based on waht I see, the query should be someting like:
sSELECT cd.ClientCode, cd.CompanyName, ct.CityName, ct.CountryName, cd.POBox, cd.TelNo1, cd.TelNo2, cd.FaxNo, cd.Email, cd.Web, cd.Details, cd.ClassCode, ct.AreaCode
FROM ClientDetails as cd, City as ct, Classification as cl
WHERE ( Mid(cd.ClassCode, 1,3) = [@myString] OR Mid(cd.ClassCode = [@myString], 4,3) = [@myString]
OR Mid(cd.ClassCode, 7,3)= [@myString] OR Mid(cd.ClassCode, 10,3)= [@myString]
OR [@myString] Is Null )
AND ( ct.CountryName LIKE [@mylocation] + '%' OR [@mylocation] Is Null
AND ( cl.Classification LIKE [@mykeyword] + '%' OR [@mykeyword] Is Null
ORDER BY cd.CompanyName ASC"
So now, when all parameters are null, all records are returned. When [@mystring] has a value, only those records that contain the value will show. If you also enter a location, the remaining records are filtered with a location also, and then you can also
filter the remaining records on the keyword
Jul 26, 2009 08:49 AM|shyguyjeff|LINK
That what I really need hans_v. Thank you so much. Now I understand it. Thanks for the article hans_v, i really appreciate it. God Bless and have a nice day..