Re: form criteria

From: Gary Walter (garylwpleasenospam_at_wamego.net)
Date: 07/31/04 

Date: Sat, 31 Jul 2004 06:49:11 -0500

Hi Rodolfo,

The following is the what Joan was
referring to:

WHERE
(([T Ocorrencias].CodEvento=[Formulários]![F Relatorios]![cmbEvento]
Or
[Formulários]![F Relatorios]![cmbEvento] Is Null)

AND

([T Ocorrencias].CodBairro=[Formulários]![F Relatorios]![cmbBairro]
Or
[Formulários]![F Relatorios]![cmbBairro] Is Null)

AND

([T Ocorrencias].CodCidade=[Formulários]![F Relatorios]![cmbCidade]
Or
[Formulários]![F Relatorios]![cmbCidade] Is Null))

OR

(([T Ocorrencias].CodEvento=[Formulários]![F Relatorios]![cmbEvento]
Or
[Formulários]![F Relatorios]![cmbEvento] Is Null)

AND

([T Ocorrencias].CodBairro=[Formulários]![F Relatorios]![cmbBairro]
Or
[Formulários]![F Relatorios]![cmbBairro] Is Null)

AND

([T Ocorrencias].CodCidade=[Formulários]![F Relatorios]![cmbCidade]
Or
[Formulários]![F Relatorios]![cmbCidade] Is Null));

Of course you wouldn't have lines between the
operators...I did that so you could more easily
see how they are "paired up."

Good luck,

Gary Walter

"Rodolfo Fontes" wrote:
> My code now is like that:
> ###
> SELECT [T Ocorrencias].CodOcorrencia, [T Ocorrencias].CodEvento, [T
> Eventos].DescrEvento, [T Cidades].DescrCidade, [T Ocorrencias].CodBairro, [T
> Bairros].DescrBairro, [T Ocorrencias].Rua, [T Ocorrencias].Data, [T
> Ocorrencias].Hora, [T Ocorrencias].Observacao, [T Ocorrencias].Usuario
> FROM (([T Ocorrencias] INNER JOIN [T Eventos] ON [T Ocorrencias].CodEvento =
> [T Eventos].CodEvento) INNER JOIN [T Cidades] ON [T Ocorrencias].CodCidade =
> [T Cidades].CodCidade) INNER JOIN [T Bairros] ON [T Ocorrencias].CodBairro =
> [T Bairros].CodBairro
> WHERE
(
(
([T Ocorrencias].CodEvento)=[Formulários]![F Relatorios]![cmbEvento]
> Or ([T Ocorrencias].CodEvento)=IsNull([Formulários]![F
> Relatorios]![cmbEvento])
)
AND
(
([T Ocorrencias].CodBairro)=[Formulários]![F
> Relatorios]![cmbBairro] Or ([T
> Ocorrencias].CodBairro)=IsNull([Formulários]![F Relatorios]![cmbBairro])
)
> AND
(
([T Ocorrencias].CodCidade)=[Formulários]![F Relatorios]![cmbCidade] Or
> ([T Ocorrencias].CodCidade)=IsNull([Formulários]![F
> Relatorios]![cmbCidade])
)
)
OR
(
(
([T Ocorrencias].CodEvento)=[Formulários]![F
> Relatorios]![cmbEvento] Or ([T
> Ocorrencias].CodEvento)=IsNull([Formulários]![F Relatorios]![cmbEvento])
)
> AND
(
([T Ocorrencias].CodBairro)=[Formulários]![F Relatorios]![cmbBairro] Or
> ([T Ocorrencias].CodBairro)=IsNull([Formulários]![F
> Relatorios]![cmbBairro])
)
AND
(
([T Ocorrencias].CodCidade)=[Formulários]![F
> Relatorios]![cmbCidade] Or ([T
> Ocorrencias].CodCidade)=IsNull([Formulários]![F Relatorios]![cmbCidade])
)
);
> ###
>
> Following your aswer, with this, my query should not ask me any value,
> because in the case of a NULL value, it should return ALL VALUES, right?
> But it's still asking for a value for EVENTOS and OCORRENCIA (the ones that
> have a criteria).
> Is there anything wrong?
>
> Thanks,
> Rodolfo Fontes
>
> "Joan Wild" <jwild@nospamtyenet.com> escreveu na mensagem
> news:OF6rXWkdEHA.3732@TK2MSFTNGP11.phx.gbl...
> > Rodolfo Fontes wrote:
> > > WHERE ((([T Ocorrencias].Data)>[Formulários]![F
> > > Relatorios]![txtData_I] And ([T Ocorrencias].Data)<[Formulários]![F
> > > Relatorios]![txtData_F]) AND (([T
> > > Ocorrencias].CodEvento)=[Formulários]![F Relatorios]![cmbEvento]) AND
> > > (([T Ocorrencias].CodCidade)=[Formulários]![F
> > > Relatorios]![cmbCidade]));
> > >
> > > Where i set some value to be equal from a Form, i wanna to see all
> > > values, in the case it's null.
> > > Is there anyway to do that?
> >
> > Open your query in design view. Change each of the criteria from, for
> > example
> > [Formulários]![FRelatorios]![txtData_I]
> > to
> > [Formulários]![FRelatorios]![txtData_I] Or
> > [Formulários]![FRelatorios]![txtData_I] is null
> > (that's all on one line)
> >
> > Change each one, and save the query. When you reopen it design view,
> you'll
> > see Access will have added four more columns and rearranged things, but
> > it'll be the same.
> >
> > --
> > Joan Wild
> > Microsoft Access MVP
> >
> >
>
>

Relevant Pages

  • RE: Access Question
    ... Open a new query in design view based on the table in question. ... Jerry Whittle, Microsoft Access MVP ... Select Field1 & Field2 As TheFields ...
    (microsoft.public.access.queries)
  • RE: Cant access design view
    ... Can you actually run the query and just not open it in design view? ... Dave Hargis, Microsoft Access MVP ... I can't open half of my queries in design view. ...
    (microsoft.public.access.queries)
  • Re: Find - replace automatic
    ... I opened a new query in the design view. ... Microsoft Access MVP ... you can handle the ApprovalStatus updates ...
    (microsoft.public.access.gettingstarted)
  • Re: form criteria
    ... > Where i set some value to be equal from a Form, i wanna to see all ... Open your query in design view. ... Change each one, and save the query. ... Microsoft Access MVP ...
    (microsoft.public.access.queries)
  • Re: why wont switch function wont work
    ... I am trying to the results of filter a query with a set of controls--combo ... My issue is that i'm having trouble building an expression (in the criteria field of design view of my ... Doug Steele, Microsoft Access MVP ...
    (microsoft.public.access.forms)