Flexible query

[Diogo]

Hi everyone
I've a form with 7 fields. This form is used to pass criteria to a query.
I want to give users the chance to search the DB with whatever criteria they
can remember, so that if a user rembers the date he can use the date or if he
remebers the document number he can use that criteria.
I'm using the following:

SELECT Saida_FSL.N_Remessa, Saida_FSL.Balcao_Origem,
Saida_FSL.Data_Recepcao, Saida_FSL.N_Cheque, Saida_FSL.Motivo,
Saida_FSL.Data_Devolucao, Saida_FSL.Valor
FROM Saida_FSL
WHERE (((Saida_FSL.N_Remessa) Like Forms![Form Pesquisa]![Nº de Remessa] &
"*" Or (Saida_FSL.N_Remessa) Is Null) And ((Saida_FSL.Balcao_Origem) Like
Forms![Form Pesquisa]![Codigo Balcao] & "*" Or (Saida_FSL.Balcao_Origem) Is
Null) And ((Saida_FSL.Data_Recepcao) Like Forms![Form Pesquisa]![Data de
Recepcao] & "*" Or (Saida_FSL.Data_Recepcao) Is Null) And
((Saida_FSL.N_Cheque) Like Forms![Form Pesquisa]![Valor de Registo] & "*" Or
(Saida_FSL.N_Cheque) Is Null) And ((Saida_FSL.Motivo) Like Forms![Form
Pesquisa]!Observacoes & "*" Or (Saida_FSL.Motivo) Is Null) And
((Saida_FSL.Data_Devolucao) Like Forms![Form Pesquisa]![Data Devolucao] & "*"
Or (Saida_FSL.Data_Devolucao) Is Null) And ((Saida_FSL.Valor) Like
Forms![Form Pesquisa]!Valor & "*" Or (Saida_FSL.Valor) Is Null));

But if I enter one of the criteria and none other it simply won´t return
anything.
Could someone help me please?

 

[Allen Browne]

Take a look at:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The article explains how to create a form with lots of unbound boxes where
the user can enter criteria. It then builds a Filter string from the boxes
where the user actually entered somehing.

It includes a sample database you can download and pull apart to see how it
works. Requires some VBA understanding.