Data room Charging System (II) -- Combined Query

A combined query has been done for two days, and it has been entangled for two days. Now let's share the experiences of these two days. When I first saw this form, I thought it was very complicated. It was much more complicated than the combined query in the student information management system. So I chose to finalize this form.

First, let's talk about the method I used at the beginning:

First, select the field name, operator, and query content, and then look at the composite relationship. I want to write the code according to the operation sequence of the form. But at the end, I found a problem, the first combined query is made, but other combined queries can also be made, but the code is large, repeated, and the loop relationship is complex, there is no new idea. This made me very depressed and gave up writing code in the order of form operations.

The following method is used:

Later I heard another friend explain that I had a new idea.

Based on the composite relationship, SQL statements are classified into three types:

Category 1: If no composite relationship is selected;

Category 2: Only one composite link is selected;

Category 3: Select both links.

If the first combination is selected, blnjudge1 = true. If the second combination is selected, blnjudge2 = true.

Then splice the SQL statement, which is much easier than the previous method.

The Code is as follows:

Private sub into query_click () 'query dim objrs as ADODB. recordset dim strtxtsql as string dim strmsgtext as string dim blnjudge1 as Boolean dim complete as Boolean dim strstdinfo (3) as string 'field name dim stroperator (3) as string' operator dim strquery (3) as string 'query statement dim strrelationship (3) as string' combination relationship strtxtsql = "select * From studentinfo where" 'field name select if not testtext (trim $ (cmbstdinfo1.text) Then msg Box "select field name! ", Vbokonly + vbexclamation," warning "cmbstdinfo1.setfocus exit sub else select case cmbstdinfo1.listindex case 0 strstdinfo (0) =" cardno "Case 1 strstdinfo (0) = "studentid" Case 2 strstdinfo (0) = "studentname" Case 3 strstdinfo (0) = "sex" case 4 strstdinfo (0) = "dapartment" case 5 strstdinfo (0) = "Grade" case 6 strstdinfo (0) = "class" End select end if 'operator select if not testtext (trim $ (cmboperator1.text )) Then msgbox "select the operator! ", Vbokonly + vbexclamation," warning "cmboperator1.setfocus exit sub else select case cmboperator1.listindex case 0 stroperator (0) =" <"Case 1 stroperator (0) = ">" Case 2 stroperator (0) = "=" Case 3 stroperator (0) = "<>" End select end if 'enter if not testtext (trim $ (txtquery1.text) Then msgbox "to query content! "Export exit sub else strquery (0) = trim $ (txtquery1.text) end if not testtext (trim $ (cmbrelationship1.text) Then strtxtsql = strtxtsql &" "& strstdinfo (0) & "" & stroperator (0) & "'" & strquery (0) & "'" set objrs = executesql (strtxtsql, strmsgtext) 'If the first identifier is null, if objrs. EOF = true then msgbox "No such record! ", Vbokonly + vbexclamation," warning "msflexgrid1.clear call viewdata exit sub else call viewdata do while not objrs. EOF with msflexgrid1. rows =. rows + 1. cellalignment = 4. textmatrix (. rows-1, 0) = trim $ (objrs. fields (0 )). textmatrix (. rows-1, 1) = trim $ (objrs. fields (1 )). textmatrix (. rows-1, 2) = trim $ (objrs. fields (2 )). textmatrix (. rows-1, 3) = trim $ (objrs. fields (3 )). textmatrix (. rows-1, 4) = Trim $ (objrs. fields (4 )). textmatrix (. rows-1, 5) = trim $ (objrs. fields (5 )). textmatrix (. rows-1, 6) = trim $ (objrs. fields (6 )). textmatrix (. rows-1, 7) = trim $ (objrs. fields (7 )). textmatrix (. rows-1, 8) = trim $ (objrs. fields (8 )). textmatrix (. rows-1, 9) = trim $ (objrs. fields (9) objrs. movenext end with loop objrs. close exit sub end if else 'the first identifier is not empty. blnjudge2 = true select case cmbrelationship1.l Istindex case 0 strrelationship (0) = "or" Case 1 strrelationship (0) = "and" End select "select the field if not testtext (trim $ (cmbstdinfo2.text )) then msgbox "select the field name! ", Vbokonly + vbexclamation," warning "cmbstdinfo2.setfocus exit sub else select case cmbstdinfo2.listindex case 0 strstdinfo (1) =" cardno "Case 1 strstdinfo (1) = "studentid" Case 2 strstdinfo (1) = "studentname" Case 3 strstdinfo (1) = "sex" case 4 strstdinfo (1) = "dapartment" case 5 strstdinfo (1) = "Grade" case 6 strstdinfo (1) = "class" End select end if 'operator select if not testtext (trim $ (cmboperator2.text )) Then msgbox "select the operator! ", Vbokonly + vbexclamation," warning "cmboperator2.setfocus exit sub else select case cmboperator2.listindex case 0 stroperator (1) =" <"Case 1 stroperator (1) = ">" Case 2 stroperator (1) = "=" Case 3 stroperator (1) = "<>" End select end if 'input if not testtext (trim $ (txtquery2.text) Then msgbox "Enter the content to query! "Txtquery2.setfocus exit sub else strquery (1) = trim $ (txtquery2.text) end if 'If the second criterion is selected if testtext (trim $ (cmbrelationship2.text )) then blnjudge1 = true select case cmbrelationship2.listindex case 0 strrelationship (1) = "or" Case 1 strrelationship (1) = "and" End select "field select if not testtext (trim $ (cmbstdinfo3.text) Then msgbox" select the field name! "Cmbstdinfo3.setfocus exit sub else select case cmbstdinfo3.listindex case 0 strstdinfo (2) =" cardno "Case 1 strstdinfo (2) =" studentid "Case 2 strstdinfo (2) = "studentname" Case 3 strstdinfo (2) = "sex" case 4 strstdinfo (2) = "dapartment" case 5 strstdinfo (2) = "Grade" case 6 strstdinfo (2) = "class" End select end if 'operator select if not testtext (trim $ (cmboperator3.text) Then msgbox "select operator! ", Vbokonly + vbexclamation," warning "cmboperator3.setfocus exit sub else select case cmboperator3.listindex case 0 stroperator (2) =" <"Case 1 stroperator (2) = ">" Case 2 stroperator (2) = "=" Case 3 stroperator (2) = "<>" End select end if 'input if not testtext (trim $ (txtquery3.text) Then msgbox "Enter the content! "Txtquery3.setfocus exit sub else strquery (2) = trim $ (txtquery3.text) end if blnjudge2 = true and blnjudge1 = false then' true or false, and the link is and if strrelationship (0) = "and" then strtxtsql = strtxtsql & "" & strstdinfo (0) & "& stroperator (0) & "'" & strquery (0) & "' and" & "" & strstdinfo (1) & "& stroperator (1) &" '"& strquery (1) & "'" set objrs = executesql (strtxtsql, strmsgtext) If O Bjrs. EOF = true then msgbox "No such record! ", Vbokonly + vbexclamation," warning "msflexgrid1.clear call viewdata 'blnjudge = true exit sub else call viewdata do while not objrs. EOF with msflexgrid1. rows =. rows + 1. cellalignment = 4. textmatrix (. rows-1, 0) = trim $ (objrs. fields (0 )). textmatrix (. rows-1, 1) = trim $ (objrs. fields (1 )). textmatrix (. rows-1, 2) = trim $ (objrs. fields (2 )). textmatrix (. rows-1, 3) = trim $ (objrs. fields (3 )). textmatr IX (. rows-1, 4) = trim $ (objrs. fields (4 )). textmatrix (. rows-1, 5) = trim $ (objrs. fields (5 )). textmatrix (. rows-1, 6) = trim $ (objrs. fields (6 )). textmatrix (. rows-1, 7) = trim $ (objrs. fields (7 )). textmatrix (. rows-1, 8) = trim $ (objrs. fields (8 )). textmatrix (. rows-1, 9) = trim $ (objrs. fields (9) objrs. movenext end with loop objrs. close exit sub end if else 'is true or false, and the relationship is or strtxtsql = strtxtsql &"" & Strstdinfo (0) & "& stroperator (0) &" '"& strquery (0) &"' or "&" "& strstdinfo (1) & "" & stroperator (1) & "'" & strquery (1) & "'" set objrs = executesql (strtxtsql, strmsgtext) If objrs. EOF = true then msgbox "No such record! ", Vbokonly + vbexclamation," warning "msflexgrid1.clear call viewdata 'blnjudge = true exit sub else call viewdata do while not objrs. EOF with msflexgrid1. rows =. rows + 1. cellalignment = 4. textmatrix (. rows-1, 0) = trim $ (objrs. fields (0 )). textmatrix (. rows-1, 1) = trim $ (objrs. fields (1 )). textmatrix (. rows-1, 2) = trim $ (objrs. fields (2 )). textmatrix (. rows-1, 3) = trim $ (objrs. fields (3 )). textmatr IX (. rows-1, 4) = trim $ (objrs. fields (4 )). textmatrix (. rows-1, 5) = trim $ (objrs. fields (5 )). textmatrix (. rows-1, 6) = trim $ (objrs. fields (6 )). textmatrix (. rows-1, 7) = trim $ (objrs. fields (7 )). textmatrix (. rows-1, 8) = trim $ (objrs. fields (8 )). textmatrix (. rows-1, 9) = trim $ (objrs. fields (9) objrs. movenext end with loop objrs. close exit sub end if blnjudge1 = true and blnjudge 2 = true then' both are true and both are and if strrelationship (1) = "and" and strrelationship (0) = "and" then strtxtsql = strtxtsql & "" & strstdinfo (0) & "& stroperator (0) &" '"& strquery (0) & "'and" & "" & strstdinfo (1) & "& stroperator (1) &"' "& strquery (1) & "'and" & "" & strstdinfo (2) & "& stroperator (2) &"' "& strquery (2) & "'" set objrs = executesql (strtxtsql, strmsgtext) If objrs. EOF = True then msgbox "No such record! ", Vbokonly + vbexclamation," warning "msflexgrid1.clear call viewdata 'blnjudge = true exit sub else call viewdata do while not objrs. EOF with msflexgrid1. rows =. rows + 1. cellalignment = 4. textmatrix (. rows-1, 0) = trim $ (objrs. fields (0 )). textmatrix (. rows-1, 1) = trim $ (objrs. fields (1 )). textmatrix (. rows-1, 2) = trim $ (objrs. fields (2 )). textmatrix (. rows-1, 3) = trim $ (objrs. fields (3 )). textmatr IX (. rows-1, 4) = trim $ (objrs. fields (4 )). textmatrix (. rows-1, 5) = trim $ (objrs. fields (5 )). textmatrix (. rows-1, 6) = trim $ (objrs. fields (6 )). textmatrix (. rows-1, 7) = trim $ (objrs. fields (7 )). textmatrix (. rows-1, 8) = trim $ (objrs. fields (8 )). textmatrix (. rows-1, 9) = trim $ (objrs. fields (9) objrs. movenext end with loop objrs. close exit sub end if 'both are or if strrelationship (1) = "or" D strrelationship (0) = "or" then strtxtsql = strtxtsql & "" & strstdinfo (0) & "" & stroperator (0) & "'" & strquery (0) & "'or" & "" & strstdinfo (1) & "& stroperator (1) &"' "& strquery (1) & "'or" & "" & strstdinfo (2) & "" & stroperator (2) & "'" & strquery (2) & "'" set objrs = executesql (strtxtsql, strmsgtext) If objrs. EOF = true then msgbox "No such record! ", Vbokonly + vbexclamation," warning "msflexgrid1.clear call viewdata 'blnjudge = true exit sub else call viewdata do while not objrs. EOF with msflexgrid1. rows =. rows + 1. cellalignment = 4. textmatrix (. rows-1, 0) = trim $ (objrs. fields (0 )). textmatrix (. rows-1, 1) = trim $ (objrs. fields (1 )). textmatrix (. rows-1, 2) = trim $ (objrs. fields (2 )). textmatrix (. rows-1, 3) = trim $ (objrs. fields (3 )). textmatr IX (. rows-1, 4) = trim $ (objrs. fields (4 )). textmatrix (. rows-1, 5) = trim $ (objrs. fields (5 )). textmatrix (. rows-1, 6) = trim $ (objrs. fields (6 )). textmatrix (. rows-1, 7) = trim $ (objrs. fields (7 )). textmatrix (. rows-1, 8) = trim $ (objrs. fields (8 )). textmatrix (. rows-1, 9) = trim $ (objrs. fields (9) objrs. movenext end with loop objrs. close exit sub end if 'one is and one is or if strrelationship (1) =" Nd "and strrelationship (0) =" or "then strtxtsql = strtxtsql &" "& strstdinfo (0) &" "& stroperator (0) &" & strquery (0) & "'or" & "" & strstdinfo (1) & "& stroperator (1) &"' "& strquery (1) & "'and" & "" & strstdinfo (2) & "& stroperator (2) &"' "& strquery (2) & "'" set objrs = executesql (strtxtsql, strmsgtext) If objrs. EOF = true then msgbox "No such record! ", Vbokonly + vbexclamation," warning "msflexgrid1.clear call viewdata 'blnjudge = true exit sub else call viewdata do while not objrs. EOF with msflexgrid1. rows =. rows + 1. cellalignment = 4. textmatrix (. rows-1, 0) = trim $ (objrs. fields (0 )). textmatrix (. rows-1, 1) = trim $ (objrs. fields (1 )). textmatrix (. rows-1, 2) = trim $ (objrs. fields (2 )). textmatrix (. rows-1, 3) = trim $ (objrs. fields (3 )). textmatr IX (. rows-1, 4) = trim $ (objrs. fields (4 )). textmatrix (. rows-1, 5) = trim $ (objrs. fields (5 )). textmatrix (. rows-1, 6) = trim $ (objrs. fields (6 )). textmatrix (. rows-1, 7) = trim $ (objrs. fields (7 )). textmatrix (. rows-1, 8) = trim $ (objrs. fields (8 )). textmatrix (. rows-1, 9) = trim $ (objrs. fields (9) objrs. movenext end with loop objrs. close exit sub end if 'One is or the other is and if strrelationship (1) = "O R "and strrelationship (0) =" and "then strtxtsql = strtxtsql &" "& strstdinfo (0) &" "& stroperator (0) &" & strquery (0) & "'and" & "" & strstdinfo (1) & "& stroperator (1) &"' "& strquery (1) & "'or" & "" & strstdinfo (2) & "" & stroperator (2) & "'" & strquery (2) & "'" set objrs = executesql (strtxtsql, strmsgtext) If objrs. EOF = true then msgbox "No such record! ", Vbokonly + vbexclamation," warning "msflexgrid1.clear call viewdata 'blnjudge = true exit sub else call viewdata do while not objrs. EOF with msflexgrid1. rows =. rows + 1. cellalignment = 4. textmatrix (. rows-1, 0) = trim $ (objrs. fields (0 )). textmatrix (. rows-1, 1) = trim $ (objrs. fields (1 )). textmatrix (. rows-1, 2) = trim $ (objrs. fields (2 )). textmatrix (. rows-1, 3) = trim $ (objrs. fields (3 )). textmatrix (. rows-1, 4) = trim $ (objrs. fields (4 )). textmatrix (. rows-1, 5) = trim $ (objrs. fields (5 )). textmatrix (. rows-1, 6) = trim $ (objrs. fields (6 )). textmatrix (. rows-1, 7) = trim $ (objrs. fields (7 )). textmatrix (. rows-1, 8) = trim $ (objrs. fields (8 )). textmatrix (. rows-1, 9) = trim $ (objrs. fields (9) objrs. movenext end with loop objrs. close exit sub end if end sub

Sub viewdata () with msflexgrid1. rows = 1. cols = 10. cellalignment = 4. textmatrix (0, 0) = "card number ". textmatrix (0, 1) = "student ID ". textmatrix (0, 2) = "name ". textmatrix (0, 3) = "gender ". textmatrix (0, 4) = "don't ". textmatrix (0, 5) = "Grade ". textmatrix (0, 6) = "class ". textmatrix (0, 7) = "amount ". textmatrix (0, 8) = "status ". textmatrix (0, 9) = "Remarks" End withend sub