Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT TabCustomers.*,
- TabCustomers.CustomerForname AS NameSearch,
- TabCustomers.CustomerSurname AS SurnameSearch,
- TabCustomers.CustomerDOB AS DOBSearch,
- TabCustomers.CustomerID AS MemberSearch
- FROM TabCustomers
- WHERE IIf([Forms]![FrmSearchCustomer]![SearchMember] Is Null
- ,True
- ,[Forms]![FrmSearchCustomer]![SearchMember]=[customerid])=True
- AND IIf([Forms]![FrmSearchCustomer].[SearchFore] Is Null
- ,True
- ,[Forms]![FrmSearchCustomer]![SearchFore] Like [customerforname] & "*")=True
- AND IIf([Forms]![FrmSearchCustomer]![SearchLast] Is Null
- ,True
- ,[Forms]![FrmSearchCustomer]![SearchLast] Like [customersurname] & "*")=True
- AND IIf([Forms]![FrmSearchCustomer]![Searchdate] Is Null
- ,True
- ,[Forms]![FrmSearchCustomer]![Searchdate] Like [customerDOB] & "*")=True;
- selectClause = "SELECT TabCustomers.* FROM TabCustomers"
- if not isnull(Forms!FrmSearchCustomer!SearchMember) then
- whereClause = whereClause & application.buildCriteria(your field name, your field type, your control value) & " AND "
- endif
- if not isnull(Forms!FrmSearchCustomer!SearchFore) then
- whereClause = whereClause & application.buildCriteria(...) & " AND "
- endif
- if not isnull(Forms!FrmSearchCustomer!SearchLast) then
- whereClause = whereClause & application.buildCriteria(...) & " AND "
- endif
- if not isnull(Forms!FrmSearchCustomer!SearchDate) then
- whereClause = whereClause & application.buildCriteria(...) & " AND "
- endif
- --get rid of the last "AND"
- if len(whereClause) > 0 then
- whereClause = left(whereClause,len(whereClause)-5)
- selectClause = selectClause & " WHERE " & whereClause
- endif
- -- your SELECT instruction is ready ...
- For each ctl in myForm.section(acHeader).controls
- if ctl.name like "search_"
- fld = myForm.recordset.fields(mid(ctl.name,8))
- if not isnull(ctl.value) then
- whereClause = whereClause & buildCriteria(fld.name ,fld.type, ctl.value) & " AND "
- endif
- endif
- next ctl
- if len(whereClause)> 0 then ...
- SELECT TabCustomers.*
- FROM TabCustomers
- WHERE (Forms!FrmSearchCustomer!SearchMember Is Null Or Forms!FrmSearchCustomer!SearchMember=[customerid])
- And (Forms!FrmSearchCustomer.SearchFore Is Null Or [customerforname] Like Forms!FrmSearchCustomer!SearchFore & "*")
- And (Forms!FrmSearchCustomer!SearchLast Is Null Or [customersurname] Like Forms!FrmSearchCustomer!SearchLast & "*")
- And (Forms!FrmSearchCustomer!Searchdate Is Null Or [customerDOB] Like Forms!FrmSearchCustomer!Searchdate & "*");
- ,[customerforname] Like """" & [Forms]![FrmSearchCustomer]![SearchFore] & "*""" )=True
- ,[Forms]![FrmSearchCustomer]![SearchFore] Like ([customerforname] & "*"))=True
- SELECT TabCustomers.CustomerForname AS NameSearch, TabCustomers.CustomerSurname AS SurnameSearch, TabCustomers.CustomerDOB AS DOBSearch, TabCustomers.customerid AS MemberSearch
- FROM TabCustomers
- WHERE TabCustomers.customerid Like IIf([Forms]![FrmSearchCustomer].[Searchmember] Is Null,"*",[Forms]![FrmSearchCustomer]![Searchmember])
- AND Trim(TabCustomers.CustomerForname & "") Like IIf([Forms]![FrmSearchCustomer].[SearchFore] Is Null,"*",[Forms]![FrmSearchCustomer]![SearchFore] & "*")
- AND Trim(TabCustomers.CustomerSurname & "") like IIf([Forms]![FrmSearchCustomer].[Searchlast] Is Null,"*",[Forms]![FrmSearchCustomer]![SearchLast] & "*")
- 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