Can i run a query using two different date fields as criteria

[Guest]

I want to run a query that will bring up records using two date fields. One
is the service date and the second is next service date. the criteria i use
for both fields is Between [Forms]![frmReports]![txtdatefrom] And
[Forms]![frmReports]![txtDateTo]. The query doesn't work for me. Does
anyone know how i do this.

Natasha

 

[Guest]

Hi Natasha, this should work fine, I have looked at a couple of queries I use
based on dates from 2 fields on a form and they run ok, Are the 2 text boxes
(presuming it is text boxes you rae using) formatted as dates on the form?
Also is the field you are querying formatted as a date?

Is it running ok but giving you no data, if so are you sure there is data
that meets this criteria along with any other criteria in your query?

Stu

 

[Guest]

Do you have the criteria set on the same line in the grid or on two different
lines?

On the same line = AND query. Different lines = OR query.

So, if the dates entered were Today and Tomorrow the query would read either:

Service Date between Today and Tommorow AND Next Service Date between Today
and Tomorrow

Or

Service Date between Today and Tommorow OR Next Service Date between Today
and Tomorrow

Lauri

 

[Guest]

Hi

I have the criteria on the same line, i want an AND query. I want all the
records fron Service Date AND the Next Service Date. When i have the
criteria in both fields on the same line the query only brings me back
records from the Next Service Date. If i run the query with the criteria in
one field it runs fine. I don't know why it doesn't show all records when i
have criteria in both fields.
Natasha

Do you have the criteria set on the same line in the grid or on two different
lines?

On the same line = AND query. Different lines = OR query.

So, if the dates entered were Today and Tomorrow the query would read either:

Service Date between Today and Tommorow AND Next Service Date between Today
and Tomorrow

Or

Service Date between Today and Tommorow OR Next Service Date between Today
and Tomorrow

Lauri

I want to run a query that will bring up records using two date fields. One
is the service date and the second is next service date. the criteria i use
for both fields is Between [Forms]![frmReports]![txtdatefrom] And
[Forms]![frmReports]![txtDateTo]. The query doesn't work for me. Does
anyone know how i do this.

Natasha

 

[John Spencer]

In boolean logic, you want OR not AND.

If you want records that have a service date on Jan 1, 2006 or that have a
next service date on Jan 1, 2006 then you need an OR in boolean logic.

If you use AND then the record would have to have a service date of Jan 1,
2006 AND it would also have to have a Next Service date of Jan 1 2006 -
That is BOTH dates would have to be on Jan 1 2006.

Try using OR and see if that gives you the results you want.

Hi

I have the criteria on the same line, i want an AND query. I want all the
records fron Service Date AND the Next Service Date. When i have the
criteria in both fields on the same line the query only brings me back
records from the Next Service Date. If i run the query with the criteria
in
one field it runs fine. I don't know why it doesn't show all records when
i
have criteria in both fields.
Natasha

Do you have the criteria set on the same line in the grid or on two
different
lines?

On the same line = AND query. Different lines = OR query.

So, if the dates entered were Today and Tomorrow the query would read
either:

Service Date between Today and Tommorow AND Next Service Date between
Today
and Tomorrow

Or

Service Date between Today and Tommorow OR Next Service Date between
Today
and Tomorrow

Lauri

I want to run a query that will bring up records using two date fields.
One
is the service date and the second is next service date. the criteria
i use
for both fields is Between [Forms]![frmReports]![txtdatefrom] And
[Forms]![frmReports]![txtDateTo]. The query doesn't work for me. Does
anyone know how i do this.

Natasha

 

[Guest]

I tried that but it still only brings up the next service date. It doesn't
seem to see the service date criteria at all

In boolean logic, you want OR not AND.

If you want records that have a service date on Jan 1, 2006 or that have a
next service date on Jan 1, 2006 then you need an OR in boolean logic.

If you use AND then the record would have to have a service date of Jan 1,
2006 AND it would also have to have a Next Service date of Jan 1 2006 -
That is BOTH dates would have to be on Jan 1 2006.

Try using OR and see if that gives you the results you want.

Hi

I have the criteria on the same line, i want an AND query. I want all the
records fron Service Date AND the Next Service Date. When i have the
criteria in both fields on the same line the query only brings me back
records from the Next Service Date. If i run the query with the criteria
in
one field it runs fine. I don't know why it doesn't show all records when
i
have criteria in both fields.
Natasha

Do you have the criteria set on the same line in the grid or on two
different
lines?

On the same line = AND query. Different lines = OR query.

So, if the dates entered were Today and Tomorrow the query would read
either:

Service Date between Today and Tommorow AND Next Service Date between
Today
and Tomorrow

Or

Service Date between Today and Tommorow OR Next Service Date between
Today
and Tomorrow

Lauri

:

I want to run a query that will bring up records using two date fields.
One
is the service date and the second is next service date. the criteria
i use
for both fields is Between [Forms]![frmReports]![txtdatefrom] And
[Forms]![frmReports]![txtDateTo]. The query doesn't work for me. Does
anyone know how i do this.

Natasha

 

[Guest]

OK PROBLEM SORTED THANKS FOR YOUR HELP
NATASHA

I tried that but it still only brings up the next service date. It doesn't
seem to see the service date criteria at all

In boolean logic, you want OR not AND.

If you want records that have a service date on Jan 1, 2006 or that have a
next service date on Jan 1, 2006 then you need an OR in boolean logic.

If you use AND then the record would have to have a service date of Jan 1,
2006 AND it would also have to have a Next Service date of Jan 1 2006 -
That is BOTH dates would have to be on Jan 1 2006.

Try using OR and see if that gives you the results you want.

Hi

I have the criteria on the same line, i want an AND query. I want all the
records fron Service Date AND the Next Service Date. When i have the
criteria in both fields on the same line the query only brings me back
records from the Next Service Date. If i run the query with the criteria
in
one field it runs fine. I don't know why it doesn't show all records when
i
have criteria in both fields.
Natasha

:

Do you have the criteria set on the same line in the grid or on two
different
lines?

On the same line = AND query. Different lines = OR query.

So, if the dates entered were Today and Tomorrow the query would read
either:

Service Date between Today and Tommorow AND Next Service Date between
Today
and Tomorrow

Or

Service Date between Today and Tommorow OR Next Service Date between
Today
and Tomorrow

Lauri

:

I want to run a query that will bring up records using two date fields.
One
is the service date and the second is next service date. the criteria
i use
for both fields is Between [Forms]![frmReports]![txtdatefrom] And
[Forms]![frmReports]![txtDateTo]. The query doesn't work for me. Does
anyone know how i do this.

Natasha