Having +3 different criteria on the same query

[Guest]

I have a query with several fields and want to use 3 or more criteria to make
it easier for the customer to find something. The fields I want to use
criteria are: Customer, Invoice No. and Invoice Date, but I want the customer
to write into 1, 2 or the 3 criteria and also look for any value or the
completed one.
Customer Invoice Date
EX: Promotora 245 10/04/06
Promotora 246 10/06/06
Pernod 247 10/05/06

The customer may select all the invoices for Promotora or the invoices from
04/06 to 05/06 or the invoices for Promotora from 04/06 to 04/06 or the
invoices 24? from a specific date.

I'm using Access 2003

 

[Guest]

Under customer, put:
Like [Enter Customer Name] & "*"

Under InvoiceNum, put:
Like [Enter Invoice number] & "*"

Under InvoiceDate, put:
Between [Enter Start Date] and [Enter End Date]

Barry

 

[Guest]

OK. I put the Customer criteria on the first line and also the date criteria
but the invoice one I put it on the OR line.
It works if I look for a special invoice on a special date
if I look for a special invoice
if I look for a special date
if I look for a special customer on a special date

But it doesn´t work just for a special customer not typing either the
invoice or date. Is it because I'm just typing part of the name and not all
of it? For EX: Pro in stead of typing Promotora (as the complete name) but it
doesn´t happen if I just type 24? in stead of typing 243.

 

[Guest]

For the customer criteria, do you have the:
& "*"
after the prompt? This allows you to enter the first two characters only.

Also, why do you put the criteria for the date on the or line? This means:
Return records that meet both Invoice and customer criteria or meet just the
date criteria.

Barry

 

[Guest]

I'm very sorry as I don´t understand 100% of English and maybe my written one
is not the best.
Here it is:

I have my query with these 3 fields and I have tried to follow up your
instructions:
Customer with the criteria: Like [Nombre Cliente] And Like "*" on the first
criteria row
Invoice with the criteria: Like [Fact] And Like "*" on the OR criteria row
Date with the criteria: Between [Inicio] And [Fin] on the first criteria row

I want my customers to enter either the customer (2 or 3 letters (or more)),
and/or the invoice number and/or the date. I would not want to have several
queries if I could have just one. It works just for the Invoice and for the
date but not just for the customer. It brings me a blank page.

I appreciate very much your support! Really!

 

[Guest]

Instead of
Like [Nombre Cliente] And Like "*"
you need:
Like [Nombre Cliente] & "*"

Also, all your criteria should be on the first line with no criteria on the
OR line.

Barry

I'm very sorry as I don´t understand 100% of English and maybe my written one
is not the best.
Here it is:

I have my query with these 3 fields and I have tried to follow up your
instructions:
Customer with the criteria: Like [Nombre Cliente] And Like "*" on the first
criteria row
Invoice with the criteria: Like [Fact] And Like "*" on the OR criteria row
Date with the criteria: Between [Inicio] And [Fin] on the first criteria row

I want my customers to enter either the customer (2 or 3 letters (or more)),
and/or the invoice number and/or the date. I would not want to have several
queries if I could have just one. It works just for the Invoice and for the
date but not just for the customer. It brings me a blank page.

I appreciate very much your support! Really!

For the customer criteria, do you have the:
& "*"
after the prompt? This allows you to enter the first two characters only.

Also, why do you put the criteria for the date on the or line? This means:
Return records that meet both Invoice and customer criteria or meet just the
date criteria.

Barry

 

[Guest]

I'm very sorry. I misstyped my answer.

# Field Criteria

1 Cliente Like [Nombre Cliente] & "*"
2 NumFact Like [Factura] & "*"
3 FechaFact Between [Inicio] And [Fin]

All on the same line (the first line on the criteria area)
But I still get the same problem. I may see the result entering data for the
3 fields, I may also see info if I enter info in the 2nd. and 3rd (and not
the 1st. one) and I am also to see info if I just enter info in the 3rd.
field (not the first and the second) BUT I'm not able to display info if I
just enter info on the 1st. OR just on the 2nd. or both on the 1st. and the
second. Is like a leather...

What happen if my customer only knows the first letters of the Customer
Field (1st) or the InvoiceNumber (2nd) or the Date (3rd) or 2 of them?????

THANKS A LOT!!!!!!!!!

Instead of
Like [Nombre Cliente] And Like "*"
you need:
Like [Nombre Cliente] & "*"

Also, all your criteria should be on the first line with no criteria on the
OR line.

Barry

I'm very sorry as I don´t understand 100% of English and maybe my written one
is not the best.
Here it is:

I have my query with these 3 fields and I have tried to follow up your
instructions:
Customer with the criteria: Like [Nombre Cliente] And Like "*" on the first
criteria row
Invoice with the criteria: Like [Fact] And Like "*" on the OR criteria row
Date with the criteria: Between [Inicio] And [Fin] on the first criteria row

I want my customers to enter either the customer (2 or 3 letters (or more)),
and/or the invoice number and/or the date. I would not want to have several
queries if I could have just one. It works just for the Invoice and for the
date but not just for the customer. It brings me a blank page.

I appreciate very much your support! Really!

For the customer criteria, do you have the:
& "*"
after the prompt? This allows you to enter the first two characters only.

Also, why do you put the criteria for the date on the or line? This means:
Return records that meet both Invoice and customer criteria or meet just the
date criteria.

Barry

:

OK. I put the Customer criteria on the first line and also the date criteria
but the invoice one I put it on the OR line.
It works if I look for a special invoice on a special date
if I look for a special invoice
if I look for a special date
if I look for a special customer on a special date

But it doesn´t work just for a special customer not typing either the
invoice or date. Is it because I'm just typing part of the name and not all
of it? For EX: Pro in stead of typing Promotora (as the complete name) but it
doesn´t happen if I just type 24? in stead of typing 243.

:

I have a query with several fields and want to use 3 or more criteria to make
it easier for the customer to find something. The fields I want to use
criteria are: Customer, Invoice No. and Invoice Date, but I want the customer
to write into 1, 2 or the 3 criteria and also look for any value or the
completed one.
Customer Invoice Date
EX: Promotora 245 10/04/06
Promotora 246 10/06/06
Pernod 247 10/05/06

The customer may select all the invoices for Promotora or the invoices from
04/06 to 05/06 or the invoices for Promotora from 04/06 to 04/06 or the
invoices 24? from a specific date.

I'm using Access 2003

 

[Guest]

Change the FechaFact prompt to:
Between Nz([Inicio],#1/1/1900#) And Nz([Fin],#12/31/9999#)

It cannot deal with null dates and there's no way to use a wildcard like in
the other fields.

Barry

I'm very sorry. I misstyped my answer.

# Field Criteria

1 Cliente Like [Nombre Cliente] & "*"
2 NumFact Like [Factura] & "*"
3 FechaFact Between [Inicio] And [Fin]

All on the same line (the first line on the criteria area)
But I still get the same problem. I may see the result entering data for the
3 fields, I may also see info if I enter info in the 2nd. and 3rd (and not
the 1st. one) and I am also to see info if I just enter info in the 3rd.
field (not the first and the second) BUT I'm not able to display info if I
just enter info on the 1st. OR just on the 2nd. or both on the 1st. and the
second. Is like a leather...

What happen if my customer only knows the first letters of the Customer
Field (1st) or the InvoiceNumber (2nd) or the Date (3rd) or 2 of them?????

THANKS A LOT!!!!!!!!!

Instead of
Like [Nombre Cliente] And Like "*"
you need:
Like [Nombre Cliente] & "*"

Also, all your criteria should be on the first line with no criteria on the
OR line.

Barry

I'm very sorry as I don´t understand 100% of English and maybe my written one
is not the best.
Here it is:

I have my query with these 3 fields and I have tried to follow up your
instructions:
Customer with the criteria: Like [Nombre Cliente] And Like "*" on the first
criteria row
Invoice with the criteria: Like [Fact] And Like "*" on the OR criteria row
Date with the criteria: Between [Inicio] And [Fin] on the first criteria row

I want my customers to enter either the customer (2 or 3 letters (or more)),
and/or the invoice number and/or the date. I would not want to have several
queries if I could have just one. It works just for the Invoice and for the
date but not just for the customer. It brings me a blank page.

I appreciate very much your support! Really!


:

For the customer criteria, do you have the:
& "*"
after the prompt? This allows you to enter the first two characters only.

Also, why do you put the criteria for the date on the or line? This means:
Return records that meet both Invoice and customer criteria or meet just the
date criteria.

Barry

:

OK. I put the Customer criteria on the first line and also the date criteria
but the invoice one I put it on the OR line.
It works if I look for a special invoice on a special date
if I look for a special invoice
if I look for a special date
if I look for a special customer on a special date

But it doesn´t work just for a special customer not typing either the
invoice or date. Is it because I'm just typing part of the name and not all
of it? For EX: Pro in stead of typing Promotora (as the complete name) but it
doesn´t happen if I just type 24? in stead of typing 243.

:

I have a query with several fields and want to use 3 or more criteria to make
it easier for the customer to find something. The fields I want to use
criteria are: Customer, Invoice No. and Invoice Date, but I want the customer
to write into 1, 2 or the 3 criteria and also look for any value or the
completed one.
Customer Invoice Date
EX: Promotora 245 10/04/06
Promotora 246 10/06/06
Pernod 247 10/05/06

The customer may select all the invoices for Promotora or the invoices from
04/06 to 05/06 or the invoices for Promotora from 04/06 to 04/06 or the
invoices 24? from a specific date.

I'm using Access 2003

 

[Guest]

Just excellent. Really.
It works excellent.
Should I use this criteria always on the date? or just because the situation
of using 3 different criterias and trying to bring info although I don´t use
the 3 of them?

Change the FechaFact prompt to:
Between Nz([Inicio],#1/1/1900#) And Nz([Fin],#12/31/9999#)

It cannot deal with null dates and there's no way to use a wildcard like in
the other fields.

Barry

I'm very sorry. I misstyped my answer.

# Field Criteria

1 Cliente Like [Nombre Cliente] & "*"
2 NumFact Like [Factura] & "*"
3 FechaFact Between [Inicio] And [Fin]

All on the same line (the first line on the criteria area)
But I still get the same problem. I may see the result entering data for the
3 fields, I may also see info if I enter info in the 2nd. and 3rd (and not
the 1st. one) and I am also to see info if I just enter info in the 3rd.
field (not the first and the second) BUT I'm not able to display info if I
just enter info on the 1st. OR just on the 2nd. or both on the 1st. and the
second. Is like a leather...

What happen if my customer only knows the first letters of the Customer
Field (1st) or the InvoiceNumber (2nd) or the Date (3rd) or 2 of them?????

THANKS A LOT!!!!!!!!!

Instead of
Like [Nombre Cliente] And Like "*"
you need:
Like [Nombre Cliente] & "*"

Also, all your criteria should be on the first line with no criteria on the
OR line.

Barry

:

I'm very sorry as I don´t understand 100% of English and maybe my written one
is not the best.
Here it is:

I have my query with these 3 fields and I have tried to follow up your
instructions:
Customer with the criteria: Like [Nombre Cliente] And Like "*" on the first
criteria row
Invoice with the criteria: Like [Fact] And Like "*" on the OR criteria row
Date with the criteria: Between [Inicio] And [Fin] on the first criteria row

I want my customers to enter either the customer (2 or 3 letters (or more)),
and/or the invoice number and/or the date. I would not want to have several
queries if I could have just one. It works just for the Invoice and for the
date but not just for the customer. It brings me a blank page.

I appreciate very much your support! Really!


:

For the customer criteria, do you have the:
& "*"
after the prompt? This allows you to enter the first two characters only.

Also, why do you put the criteria for the date on the or line? This means:
Return records that meet both Invoice and customer criteria or meet just the
date criteria.

Barry

:

OK. I put the Customer criteria on the first line and also the date criteria
but the invoice one I put it on the OR line.
It works if I look for a special invoice on a special date
if I look for a special invoice
if I look for a special date
if I look for a special customer on a special date

But it doesn´t work just for a special customer not typing either the
invoice or date. Is it because I'm just typing part of the name and not all
of it? For EX: Pro in stead of typing Promotora (as the complete name) but it
doesn´t happen if I just type 24? in stead of typing 243.

:

I have a query with several fields and want to use 3 or more criteria to make
it easier for the customer to find something. The fields I want to use
criteria are: Customer, Invoice No. and Invoice Date, but I want the customer
to write into 1, 2 or the 3 criteria and also look for any value or the
completed one.
Customer Invoice Date
EX: Promotora 245 10/04/06
Promotora 246 10/06/06
Pernod 247 10/05/06

The customer may select all the invoices for Promotora or the invoices from
04/06 to 05/06 or the invoices for Promotora from 04/06 to 04/06 or the
invoices 24? from a specific date.

I'm using Access 2003

 

[Guest]

The Nz is necessary because you have a range of dates. It's the same as
putting the & "*" after single value criteria. It's your way of saying "If I
don't enter a value, give me everything".

Barry

Just excellent. Really.
It works excellent.
Should I use this criteria always on the date? or just because the situation
of using 3 different criterias and trying to bring info although I don´t use
the 3 of them?

Change the FechaFact prompt to:
Between Nz([Inicio],#1/1/1900#) And Nz([Fin],#12/31/9999#)

It cannot deal with null dates and there's no way to use a wildcard like in
the other fields.

Barry

I'm very sorry. I misstyped my answer.

# Field Criteria

1 Cliente Like [Nombre Cliente] & "*"
2 NumFact Like [Factura] & "*"
3 FechaFact Between [Inicio] And [Fin]

All on the same line (the first line on the criteria area)
But I still get the same problem. I may see the result entering data for the
3 fields, I may also see info if I enter info in the 2nd. and 3rd (and not
the 1st. one) and I am also to see info if I just enter info in the 3rd.
field (not the first and the second) BUT I'm not able to display info if I
just enter info on the 1st. OR just on the 2nd. or both on the 1st. and the
second. Is like a leather...

What happen if my customer only knows the first letters of the Customer
Field (1st) or the InvoiceNumber (2nd) or the Date (3rd) or 2 of them?????

THANKS A LOT!!!!!!!!!







:

Instead of
Like [Nombre Cliente] And Like "*"
you need:
Like [Nombre Cliente] & "*"

Also, all your criteria should be on the first line with no criteria on the
OR line.

Barry

:

I'm very sorry as I don´t understand 100% of English and maybe my written one
is not the best.
Here it is:

I have my query with these 3 fields and I have tried to follow up your
instructions:
Customer with the criteria: Like [Nombre Cliente] And Like "*" on the first
criteria row
Invoice with the criteria: Like [Fact] And Like "*" on the OR criteria row
Date with the criteria: Between [Inicio] And [Fin] on the first criteria row

I want my customers to enter either the customer (2 or 3 letters (or more)),
and/or the invoice number and/or the date. I would not want to have several
queries if I could have just one. It works just for the Invoice and for the
date but not just for the customer. It brings me a blank page.

I appreciate very much your support! Really!


:

For the customer criteria, do you have the:
& "*"
after the prompt? This allows you to enter the first two characters only.

Also, why do you put the criteria for the date on the or line? This means:
Return records that meet both Invoice and customer criteria or meet just the
date criteria.

Barry

:

OK. I put the Customer criteria on the first line and also the date criteria
but the invoice one I put it on the OR line.
It works if I look for a special invoice on a special date
if I look for a special invoice
if I look for a special date
if I look for a special customer on a special date

But it doesn´t work just for a special customer not typing either the
invoice or date. Is it because I'm just typing part of the name and not all
of it? For EX: Pro in stead of typing Promotora (as the complete name) but it
doesn´t happen if I just type 24? in stead of typing 243.

:

I have a query with several fields and want to use 3 or more criteria to make
it easier for the customer to find something. The fields I want to use
criteria are: Customer, Invoice No. and Invoice Date, but I want the customer
to write into 1, 2 or the 3 criteria and also look for any value or the
completed one.
Customer Invoice Date
EX: Promotora 245 10/04/06
Promotora 246 10/06/06
Pernod 247 10/05/06

The customer may select all the invoices for Promotora or the invoices from
04/06 to 05/06 or the invoices for Promotora from 04/06 to 04/06 or the
invoices 24? from a specific date.

I'm using Access 2003