 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / January 2008Many strSQL
Hello Forum Members: I have many strSQl lines as follows: ~ ~ ~ ~ ~ ~ ~ ~ strSQL = "SELECT * FROM Declension where Preposition_I like " & strCriteria & " or Instrumental_I like " & strCriteria & " or Possessive_I like " & strCriteria & " or Preposition_You like " & strCriteria & " or Instrumental_You like " & strCriteria & " or Possessive_You like " & strCriteria & " or Preposition_You_F like " & strCriteria & " or Instrumental_You_F like " & strCriteria & " or Possessive_You_F like " & strCriteria & " or Preposition_He like " & strCriteria & " or Instrumental_He like " & strCriteria & " or Possessive_He like " & strCriteria & " or Preposition_She like " & strCriteria & " or Instrumental_She like " & strCriteria & " or Possessive_She like " & strCriteria & " or Preposition_We like " & strCriteria & " or Instrumental_We like " & strCriteria & " or Possessive_We like " & strCriteria & " or Preposition_They like " & strCriteria & " or Instrumental_They like " & strCriteria & " or Possessive_They like " & strCriteria " & _ strSQL = strSQL & "where Preposition_Russian_I like " & strCriteria & " or Instrumental_Russian_I like " & strCriteria & " or Possessive_Russian_I like " & strCriteria & " or Preposition_Russian_You like " & strCriteria & " or Instrumental_Russian_You like " & strCriteria & " or Possessive_Russian_You like " & strCriteria & " or Preposition_Russian_You_F like " & strCriteria & " or Instrumental_Russian_You_F like " & strCriteria & " or Possessive_Russian_You_F like " & strCriteria & "; " strSQL = strSQL & "where Preposition_Russian_He like " & strCriteria & " or Instrumental_Russian_He like " & strCriteria & " or Possessive_Russian_He like " & strCriteria & " or Preposition_Russian_She like " & strCriteria & " or Instrumental_Russian_She like " & strCriteria & " or Possessive_Russian_She like " & strCriteria & " or Preposition_Russian_We like " & strCriteria & " or Instrumental_Russian_We like " & strCriteria & " or Possessive_Russian_We like " & strCriteria & " or Preposition_Russian_They like " & strCriteria & " or Instrumental_Russian_They like " & strCriteria & " or Possessive_Russian_They like " & strCriteria ~ ~ ~ ~ ~ ~ ~ ~ It seems that the {" & _} on the first strSQL is not working. Does anyone here know how to connect many strSQL lines? Any help will be appreciated. At the end of the first strsql grouping, you have a continuation character, which you don't need, since you are using "strsql = strsql &" on the next line. These are separate lines and should not be continued. > Hello Forum Members: > [quoted text clipped - 37 lines] > > Any help will be appreciated. Hello mray29: Thanks for your very quick responce. I removed the {" & _} and I get the Run-Time error 3075 on the following: DoCmd.OpenForm "Declension_results", acFormFS, , , , acHidden Forms!Declension_results.RecordSource = strSQL (This is where the error is.) DoCmd.OpenForm "Declension_results", acFormFS What did I do wrong? Do you recommend that I use the {strSQL = "SELECT * FROM} and the {strSQL = strSQL & "} sequentially? Such as" {strSQL = "SELECT * FROM} {strSQL = strSQL & "} {strSQL = "SELECT * FROM} {strSQL = strSQL & "} in order to avoid the {" & _}? Is this a good practice? Once again, any suggestions are greatly appreciated. > At the end of the first strsql grouping, you have a continuation character, > which you don't need, since you are using "strsql = strsql &" on the next [quoted text clipped - 41 lines] > > > > Any help will be appreciated. >Hello Forum Members: > [quoted text clipped - 14 lines] >strCriteria & " or Instrumental_They like " & strCriteria & " or >Possessive_They like " & strCriteria " & _ Well... One reason you're having trouble is that your tables do not appear to be correctly normalized. You're storing data - grammatical cases - in fieldnames. I'd say that the Declension table should be tall and thin, rather than wide and flat, with "Preposition_I", "Instrumental_I", and so on as *data values* in a field, and as many rows as there are such cases. That would give you ONE field to search instead of dozens! John W. Vinson [MVP] > Hello Forum Members: > > I have many strSQl lines as follows: > ~ ~ ~ ~ ~ ~ ~ ~ That sql looks VERY VERY un-normailed. You should deisg this such that you have 3 collums: Catagory WordType WordValue Preposition I Like text value Possessive I like test value With the above desing, you could add as many new "types" to seach for with ease..and your final seach would be ONLY one query like: select * from tblData where WordValue like "your criteria" The above is SOOO easy to maintin..... With what you have, you have a real difficult assigned to work with. I would suggest that you can sit are some type of coding approach in which you can feed a routine that list to the fields you want to search eg, something like: Dim strSql As String Dim strFields As String Dim fList() As String Dim strCrit As String Dim i As Integer strCrit = " like 'abc*'" strFields = "Possessive_I,Preposition_You,Instrumental_You," & _ "Possessive_You,Preposition_You_F,Instrumental_You_F,Possessive_You_F," & _ "Preposition_He,Instrumental_He,Possessive_He,Preposition_She,Instrumental_She," & _ "Possessive_She,Preposition_We,Instrumental_We,Possessive_We,Preposition_They," & _ "Instrumental_They,Possessive_They" fList = Split(strFields, ",") For i = 0 To UBound(fList) If strSql <> "" Then strSql = strSql & " or " strSql = strSql & fList(i) & strCrit Next i strSql = "SELECT * FROM Declension where " & strSql Debug.Print strSql At least the above allows you to have a "list" of fields. furthermore I would not even place that list of fields in the code as that's way too hard coded, you might make the field list and actual mammal on reform and you just type in the fields and then run the above code (that since the typing the list into star fields, you use a text box on the form called stir fields). When you see such a "large" amount of difficult sql and code as you posted you just *know* that the design you have is becoming too hard to work with.... If the code is becoming hard and difficult...the solution is not to code with more effort and complexity, but to find a design that makes the whole thing very easy in the first place.  SignatureAlbert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com Thank you for all your replies and suggestions. Now, I've learned a design approach and I should do this for the new table. But the current table that I am working on already has many records, so I need to keep this table. If I have only one strSQL, I have no problem with the folloing output. DoCmd.OpenForm "Declension_results", acFormFS, , , , acHidden Forms!Declension_results.RecordSource = strSQL DoCmd.OpenForm "Declension_results", acFormFS But when I use the combination of {strSQL = "SELECT * FROM} and the {strSQL = strSQL & "}, I need to modify this {Forms!Declension_results.RecordSource = strSQL}, which I don't know how. Do you know how or any place where I can look at? Once again, thank you for looking into this question. Hiro > > Hello Forum Members: > > [quoted text clipped - 63 lines] > with more effort and complexity, but to find a design that makes the whole > thing very easy in the first place. >Thank you for all your replies and suggestions. > >Now, I've learned a design approach and I should do this for the new table. >But the current table that I am working on already has many records, so I >need to keep this table. Well... actually you don't. It's not too hard to move data from a wide-flat table into a tall-thin one. YOu can use a "Normalizing Union Query" such as SELECT <primary key field> "Preposition_I" AS WordType, Preposition_I AS WordValue FROM Declension WHERE Preposition_I IS NOT NULL UNION ALL SELECT <primary key field> "Instrumental_I" AS WordType, Instrumental_I AS WordValue FROM Declension WHERE Instrumental_I IS NOT NULL UNION ALL <etc. etc. through all the fields> >If I have only one strSQL, I have no problem with the folloing output. > [quoted text clipped - 7 lines] >Do you know how or any place where I can look at? Once again, thank you for >looking into this question. You don't need multiple strSQLs. Even with your current structure, you need *one* strSQL with many OR clauses. John W. Vinson [MVP] Hello John: Thanks for your advice, but I am still struggling on this. This table is easier for me to place new records, so I am still using the same table. But I changed the code from strSQL to stLinkCriteria = stLinkCriteria. It is still not working. I am totally purplexed. Can you help me on this? Here is the code: Dim stDocName As String Dim stLinkCriteria As String stDocName = "Declension_results" If Not IsNull(Me![Preposition_I]) Then stLinkCriteria = stLinkCriteria & "[Preposition_I]=" & "'" & Me![Preposition_I] & "'" End If ' The similar values are continuing here. ' If Not IsNull(Me![Possessive_Russian_They]) Then stLinkCriteria = stLinkCriteria & "[Possessive_Russian_They]=" & "'" & Me![Possessive_Russian_They] & "'" End If DoCmd.OpenForm stDocName, , , stLinkCriteria Exit_Command108_Click: Exit Sub Err_Command108_Click: MsgBox Err.Description Resume Exit_Command108_Click End Sub > >Thank you for all your replies and suggestions. > > [quoted text clipped - 34 lines] > > John W. Vinson [MVP] >Thanks for your advice, but I am still struggling on this. This table is >easier for me to place new records, so I am still using the same table. But I >changed the code from strSQL to stLinkCriteria = stLinkCriteria. I'm sorry, that makes no sense to me at all. If you step through the code, what is the actual value of stLinkCriteria when you've built it? What are you DOING with stLinkCriteria when it';s built? stLinkCriteria = stLinkCriteria is meaningless (setting a string to itself???) John W. Vinson [MVP] cHello John: Once again, thank you for your reply and I have to apologize for my poor understanding on Acess. Now, I have gone back to the original strSQL with some changes as follows. I think I am getting a little closer because I am getting a result on the 2nd strSQL. However, the 1st strSQL is not working correctly. Once again, it will be greatly appreciated if you could look into this and provide a solution at your earliest convenience. strSQL = "SELECT * FROM Declension where Prep_I like " & strCriteria & " or Inst_I like " & strCriteria & " or Posse_I like " & strCriteria & " or Prep_You like " & strCriteria & " or Inst_You like " & strCriteria & " or Posse_You like " & strCriteria & " or Prep_You_F like " & strCriteria & " or Inst_You_F like " & strCriteria & " or Posse_You_F like " & strCriteria & " or Prep_He like " & strCriteria & " or Inst_He like " & strCriteria & " or Posse_He like " & strCriteria & " or Prep_She like " & strCriteria & " or Inst_She like " & strCriteria & " or Posse_She like " & strCriteria & " or Prep_We like " & strCriteria & " or Inst_We like " & strCriteria & " or Posse_We like " & strCriteria & " or Prep_They like " & strCriteria & " or Inst_They like " & strCriteria & " or Posse_They like " & strCriteria strSQL = "SELECT * FROM Declension where Prep_Russian_I like " & strCriteria & " or Inst_Russian_I like " & strCriteria & " or Posse_Russian_I like " & strCriteria & " or Prep_Russian_You like " & strCriteria & " or Inst_Russian_You like " & strCriteria & " or Posse_Russian_You like " & strCriteria & " or Prep_Russian_You_F like " & strCriteria & " or Inst_Russian_You_F like " & strCriteria & " or Posse_Russian_You_F like " & strCriteria & " or Prep_Russian_He like " & strCriteria & " or Inst_Russian_He like " & strCriteria & " or Posse_Russian_He like " & strCriteria & " or Prep_Russian_She like " & strCriteria & " or Inst_Russian_She like " & strCriteria & " or Posse_Russian_She like " & strCriteria & " or Prep_Russian_We like " & strCriteria & " or Inst_Russian_We like " & strCriteria & " or Posse_Russian_We like " & strCriteria & " or Prep_Russian_They like " & strCriteria & " or Inst_Russian_They like " & strCriteria & " or Posse_Russian_They like " & strCriteria DoCmd.OpenForm "Declension_results", acFormFS, , , , acHidden Forms!Declension_results.Form.RecordSource = strSQL DoCmd.OpenForm "Declension_results", acFormFS > >Thanks for your advice, but I am still struggling on this. This table is > >easier for me to place new records, so I am still using the same table. But I [quoted text clipped - 9 lines] > > John W. Vinson [MVP] >cHello John: > [quoted text clipped - 4 lines] >I think I am getting a little closer because I am getting a result on the >2nd strSQL. However, the 1st strSQL is not working correctly. I cannot debug this. I cannot see what is actually being put into strSQL. You can, however! Put a breakpoint in the code, and step through it until strSQL has been defined, right after this statement. In the Immediate Window type ?strSQL It will display what has actually been put into this variable. You can copy and paste this query into the SQL window of a new Query, and see what errors occur; or you can post it to a message here, where I or another volunteer might be able to diagnose the problem. John W. Vinson [MVP] Hello John: Thanks again for looking into this. Under View menu and Immediate Windows, I typed ?strSQL, but nothing happens. This is the entire code that I have on this click control. And only the 2nd {strSQL = "SELECT *} is working. Do you see anything wrong? Private Sub cmdFind_Click() Dim strCriteria As String Dim strSQL As String If IsNull(Me.txtFindWhat) Then MsgBox "Please enter a text in the Find What text box first!" Exit Sub Else 'Chr(34) means the quotation mark strCriteria = Chr(34) & "*" & Me.txtFindWhat & "*" & Chr(34) End If 'strSQL = " SELECT * FROM Declension where Preposition I like " & strCriteria strSQL = "SELECT * FROM Declension where Prep_I like " & strCriteria & " or Inst_I like " & strCriteria & " or Posse_I like " & strCriteria & " or Prep_You like " & strCriteria & " or Inst_You like " & strCriteria & " or Posse_You like " & strCriteria & " or Prep_You_F like " & strCriteria & " or Inst_You_F like " & strCriteria & " or Posse_You_F like " & strCriteria & " or Prep_He like " & strCriteria & " or Inst_He like " & strCriteria & " or Posse_He like " & strCriteria & " or Prep_She like " & strCriteria & " or Inst_She like " & strCriteria & " or Posse_She like " & strCriteria & " or Prep_We like " & strCriteria & " or Inst_We like " & strCriteria & " or Posse_We like " & strCriteria & " or Prep_They like " & strCriteria & " or Inst_They like " & strCriteria & " or Posse_They like " & strCriteria strSQL = "SELECT * FROM Declension where Prep_Russian_I like " & strCriteria & " or Inst_Russian_I like " & strCriteria & " or Posse_Russian_I like " & strCriteria & " or Prep_Russian_You like " & strCriteria & " or Inst_Russian_You like " & strCriteria & " or Posse_Russian_You like " & strCriteria & " or Prep_Russian_You_F like " & strCriteria & " or Inst_Russian_You_F like " & strCriteria & " or Posse_Russian_You_F like " & strCriteria & " or Prep_Russian_He like " & strCriteria & " or Inst_Russian_He like " & strCriteria & " or Posse_Russian_He like " & strCriteria & " or Prep_Russian_She like " & strCriteria & " or Inst_Russian_She like " & strCriteria & " or Posse_Russian_She like " & strCriteria & " or Prep_Russian_We like " & strCriteria & " or Inst_Russian_We like " & strCriteria & " or Posse_Russian_We like " & strCriteria & " or Prep_Russian_They like " & strCriteria & " or Inst_Russian_They like " & strCriteria & " or Posse_Russian_They like " & strCriteria DoCmd.OpenForm "Declension_results", acFormFS, , , , acHidden Forms!Declension_results.Form.RecordSource = strSQL DoCmd.OpenForm "Declension_results", acFormFS End Sub > >cHello John: > > [quoted text clipped - 23 lines] > > John W. Vinson [MVP] >Hello John: > >Thanks again for looking into this. > >Under View menu and Immediate Windows, I typed ?strSQL, but nothing happens. Because you did not follow my instructions. You need to type ?StrSQL *AFTER* the value of strSQL has been set. >This is the entire code that I have on this click control. And only the 2nd >{strSQL = "SELECT *} is working. Do you see anything wrong? No, I cannot, because I do not know what's in Me.txtFindWhat, because I do not know the fieldnames in your table, becase *I cannot see your computer*. I'm not a magician, Hiro. >Private Sub cmdFind_Click() > [quoted text clipped - 10 lines] > > 'strSQL = " SELECT * FROM Declension where Preposition I like " & strCriteria Open the VBA code in the module design window. Use the mouse and click in the vertical grey bar to the left of the code window next to this next line. Run the code; it will stop executing at this line (a "breakpoint" is what it's called). > strSQL = "SELECT * FROM Declension where Prep_I like " & strCriteria & " or >Inst_I like " & strCriteria & " or Posse_I like " & strCriteria & " or [quoted text clipped - 7 lines] >Posse_We like " & strCriteria & " or Prep_They like " & strCriteria & " or >Inst_They like " & strCriteria & " or Posse_They like " & strCriteria Press the F8 key to step through the code. The code shortcuts can be seen by selecting the Debug menu option. > strSQL = "SELECT * FROM Declension where Prep_Russian_I like " & >strCriteria & " or Inst_Russian_I like " & strCriteria & " or Posse_Russian_I [quoted text clipped - 11 lines] >Inst_Russian_They like " & strCriteria & " or Posse_Russian_They like " & >strCriteria What you're doing here is setting a value of strSQL in the line two above - the one with Prep_I and Inst_I and so on; then you are OVERWRITING and destroying all of that SQL with this new SQL. What are you intending the first SELECT clause to do? As it stands, it's doing nothing at all. > DoCmd.OpenForm "Declension_results", acFormFS, , , , acHidden > Forms!Declension_results.Form.RecordSource = strSQL > DoCmd.OpenForm "Declension_results", acFormFS You will certainly need to change this last line (which will erase all the work you did to set its recordsource) to Forms!Declension_results.Visible = True The steps would be to open the form, hidden; set its Recordsource; and then make the form visible. John W. Vinson [MVP] Hello John: Thank you for your continued efforts to look into this problem. No matter how I do it {?strSQL}, it does not produce anything. The 2nd strSQL = "SELECT * FROM always returns the correct results. The 1st strSQL = "SELECT * FROM does not return any results and no error message. The form "Declension_results" comes back with a blank page. Is this why I don't see anything after typing {?strSQL}? I am wondering if there is any way to send this small file via e-mail or send it to ftp site where you may be able to look at? I know I am taking so much of your time on this and apologize. > >Hello John: > > [quoted text clipped - 83 lines] > > John W. Vinson [MVP] >I am wondering if there is any way to send this small file via e-mail or >send it to ftp site where you may be able to look at? I know I am taking so >much of your time on this and apologize. Well, ordinarily I leave such services for paying clients but I'm sufficiently perplexed that I would like to take a look. Please Zip the file (in Windows Explorer, rightclick and select Send To... Compressed (Zipped) Folder). Email the folder to me at jvinson <at> wysard of info <dot> com - edit out the blanks and replace the punctuation. Let me know in the email the name of the form and the relevant modules. John W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.