code_junkie

Access - What's wrong with this query

Nov 11th, 2011
73
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.52 KB | None | 0 0
  1. SELECT TabCustomers.*,
  2. TabCustomers.CustomerForname AS NameSearch,
  3. TabCustomers.CustomerSurname AS SurnameSearch,
  4. TabCustomers.CustomerDOB AS DOBSearch,
  5. TabCustomers.CustomerID AS MemberSearch
  6. FROM TabCustomers
  7. WHERE IIf([Forms]![FrmSearchCustomer]![SearchMember] Is Null
  8. ,True
  9. ,[Forms]![FrmSearchCustomer]![SearchMember]=[customerid])=True
  10. AND IIf([Forms]![FrmSearchCustomer].[SearchFore] Is Null
  11. ,True
  12. ,[Forms]![FrmSearchCustomer]![SearchFore] Like [customerforname] & "*")=True
  13. AND IIf([Forms]![FrmSearchCustomer]![SearchLast] Is Null
  14. ,True
  15. ,[Forms]![FrmSearchCustomer]![SearchLast] Like [customersurname] & "*")=True
  16. AND IIf([Forms]![FrmSearchCustomer]![Searchdate] Is Null
  17. ,True
  18. ,[Forms]![FrmSearchCustomer]![Searchdate] Like [customerDOB] & "*")=True;
  19.  
  20. selectClause = "SELECT TabCustomers.* FROM TabCustomers"
  21. if not isnull(Forms!FrmSearchCustomer!SearchMember) then
  22. whereClause = whereClause & application.buildCriteria(your field name, your field type, your control value) & " AND "
  23. endif
  24. if not isnull(Forms!FrmSearchCustomer!SearchFore) then
  25. whereClause = whereClause & application.buildCriteria(...) & " AND "
  26. endif
  27. if not isnull(Forms!FrmSearchCustomer!SearchLast) then
  28. whereClause = whereClause & application.buildCriteria(...) & " AND "
  29. endif
  30. if not isnull(Forms!FrmSearchCustomer!SearchDate) then
  31. whereClause = whereClause & application.buildCriteria(...) & " AND "
  32. endif
  33. --get rid of the last "AND"
  34. if len(whereClause) > 0 then
  35. whereClause = left(whereClause,len(whereClause)-5)
  36. selectClause = selectClause & " WHERE " & whereClause
  37. endif
  38. -- your SELECT instruction is ready ...
  39.  
  40. For each ctl in myForm.section(acHeader).controls
  41. if ctl.name like "search_"
  42. fld = myForm.recordset.fields(mid(ctl.name,8))
  43. if not isnull(ctl.value) then
  44. whereClause = whereClause & buildCriteria(fld.name ,fld.type, ctl.value) & " AND "
  45. endif
  46. endif
  47. next ctl
  48. if len(whereClause)> 0 then ...
  49.  
  50. SELECT TabCustomers.*
  51. FROM TabCustomers
  52. WHERE (Forms!FrmSearchCustomer!SearchMember Is Null Or Forms!FrmSearchCustomer!SearchMember=[customerid])
  53. And (Forms!FrmSearchCustomer.SearchFore Is Null Or [customerforname] Like Forms!FrmSearchCustomer!SearchFore & "*")
  54. And (Forms!FrmSearchCustomer!SearchLast Is Null Or [customersurname] Like Forms!FrmSearchCustomer!SearchLast & "*")
  55. And (Forms!FrmSearchCustomer!Searchdate Is Null Or [customerDOB] Like Forms!FrmSearchCustomer!Searchdate & "*");
  56.  
  57. ,[customerforname] Like """" & [Forms]![FrmSearchCustomer]![SearchFore] & "*""" )=True
  58.  
  59. ,[Forms]![FrmSearchCustomer]![SearchFore] Like ([customerforname] & "*"))=True
  60.  
  61. SELECT TabCustomers.CustomerForname AS NameSearch, TabCustomers.CustomerSurname AS SurnameSearch, TabCustomers.CustomerDOB AS DOBSearch, TabCustomers.customerid AS MemberSearch
  62. FROM TabCustomers
  63. WHERE TabCustomers.customerid Like IIf([Forms]![FrmSearchCustomer].[Searchmember] Is Null,"*",[Forms]![FrmSearchCustomer]![Searchmember])
  64. AND Trim(TabCustomers.CustomerForname & "") Like IIf([Forms]![FrmSearchCustomer].[SearchFore] Is Null,"*",[Forms]![FrmSearchCustomer]![SearchFore] & "*")
  65. AND Trim(TabCustomers.CustomerSurname & "") like IIf([Forms]![FrmSearchCustomer].[Searchlast] Is Null,"*",[Forms]![FrmSearchCustomer]![SearchLast] & "*")
  66. AND (TabCustomers.CustomerDOB Like IIf([Forms]![FrmSearchCustomer].[SearchDate] Is Null,"*",[Forms]![FrmSearchCustomer]![SearchDate] ) Or TabCustomers.CustomerDOB Is Null)
Add Comment
Please, Sign In to add comment