lookup between dates

A

Annette

I'm making a query to look up parts purchased between different dates. It
doesn't pull up the information. This is what I have done so far.

SELECT [Purchasing Log Query].[Part#], [Purchasing Log Query].[NAI #],
[Purchasing Log Query].[Quantity Received], [Purchasing Log Query].[Date In],
[Purchasing Log Query].Description, [Purchasing Log Query].Comments
FROM [Purchasing Log Query]
WHERE ((([Purchasing Log Query].[Date In]) Between [Enter Start Date] And
[Enter End Date]));

Any suggestion would be greatly appreciated. Thank you in advance.
 
K

KARL DEWEY

Try this for WHERE --
WHERE DateValue([Purchasing Log Query].[Date In]) Between CVDate([Enter
Start Date]) And CVDate([Enter End Date]);

This will remove any time component and convert your text to date.
 
A

Annette

Hi Karl,
I entered the information and I get a error message. "This expression is
typed incorrectly, or it is too complex to be evaluated. For example, a
numeric expression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression to
variables." This is how I changed it.

SELECT [Purchasing Log Query].[Part#], [Purchasing Log Query].[NAI #],
[Purchasing Log Query].[Quantity Received], [Purchasing Log Query].[Date In],
[Purchasing Log Query].Description, [Purchasing Log Query].Comments
FROM [Purchasing Log Query]
WHERE DateValue([Purchasing Log Query.[Date In])Between CVDate([Enter Start
Date])And CVDate([Enter End Date]);
Thanks in advance.




KARL DEWEY said:
Try this for WHERE --
WHERE DateValue([Purchasing Log Query].[Date In]) Between CVDate([Enter
Start Date]) And CVDate([Enter End Date]);

This will remove any time component and convert your text to date.

--
KARL DEWEY
Build a little - Test a little


Annette said:
I'm making a query to look up parts purchased between different dates. It
doesn't pull up the information. This is what I have done so far.

SELECT [Purchasing Log Query].[Part#], [Purchasing Log Query].[NAI #],
[Purchasing Log Query].[Quantity Received], [Purchasing Log Query].[Date In],
[Purchasing Log Query].Description, [Purchasing Log Query].Comments
FROM [Purchasing Log Query]
WHERE ((([Purchasing Log Query].[Date In]) Between [Enter Start Date] And
[Enter End Date]));

Any suggestion would be greatly appreciated. Thank you in advance.
Click to expand...
Click to expand...
 
K

KARL DEWEY

Let's go back to your orignal problem - 'It doesn't pull up the information.'
Does pull some or none? Does it pull wrong records?
Is your [Date In] field a DateTime datatype?
What is the format that you enter on prompt by [Enter Start Date] And
[Enter End Date]?

--
KARL DEWEY
Build a little - Test a little


Annette said:
Hi Karl,
I entered the information and I get a error message. "This expression is
typed incorrectly, or it is too complex to be evaluated. For example, a
numeric expression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression to
variables." This is how I changed it.

SELECT [Purchasing Log Query].[Part#], [Purchasing Log Query].[NAI #],
[Purchasing Log Query].[Quantity Received], [Purchasing Log Query].[Date In],
[Purchasing Log Query].Description, [Purchasing Log Query].Comments
FROM [Purchasing Log Query]
WHERE DateValue([Purchasing Log Query.[Date In])Between CVDate([Enter Start
Date])And CVDate([Enter End Date]);
Thanks in advance.




KARL DEWEY said:
Try this for WHERE --
WHERE DateValue([Purchasing Log Query].[Date In]) Between CVDate([Enter
Start Date]) And CVDate([Enter End Date]);

This will remove any time component and convert your text to date.

--
KARL DEWEY
Build a little - Test a little


Annette said:
I'm making a query to look up parts purchased between different dates. It
doesn't pull up the information. This is what I have done so far.

SELECT [Purchasing Log Query].[Part#], [Purchasing Log Query].[NAI #],
[Purchasing Log Query].[Quantity Received], [Purchasing Log Query].[Date In],
[Purchasing Log Query].Description, [Purchasing Log Query].Comments
FROM [Purchasing Log Query]
WHERE ((([Purchasing Log Query].[Date In]) Between [Enter Start Date] And
[Enter End Date]));

Any suggestion would be greatly appreciated. Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
A

Annette

The enter start date and enter end date works fine but no records are coming
up.

KARL DEWEY said:
Let's go back to your orignal problem - 'It doesn't pull up the information.'
Does pull some or none? Does it pull wrong records?
Is your [Date In] field a DateTime datatype?
What is the format that you enter on prompt by [Enter Start Date] And
[Enter End Date]?

--
KARL DEWEY
Build a little - Test a little


Annette said:
Hi Karl,
I entered the information and I get a error message. "This expression is
typed incorrectly, or it is too complex to be evaluated. For example, a
numeric expression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression to
variables." This is how I changed it.

SELECT [Purchasing Log Query].[Part#], [Purchasing Log Query].[NAI #],
[Purchasing Log Query].[Quantity Received], [Purchasing Log Query].[Date In],
[Purchasing Log Query].Description, [Purchasing Log Query].Comments
FROM [Purchasing Log Query]
WHERE DateValue([Purchasing Log Query.[Date In])Between CVDate([Enter Start
Date])And CVDate([Enter End Date]);
Thanks in advance.




KARL DEWEY said:
Try this for WHERE --
WHERE DateValue([Purchasing Log Query].[Date In]) Between CVDate([Enter
Start Date]) And CVDate([Enter End Date]);

This will remove any time component and convert your text to date.

--
KARL DEWEY
Build a little - Test a little


:

I'm making a query to look up parts purchased between different dates. It
doesn't pull up the information. This is what I have done so far.

SELECT [Purchasing Log Query].[Part#], [Purchasing Log Query].[NAI #],
[Purchasing Log Query].[Quantity Received], [Purchasing Log Query].[Date In],
[Purchasing Log Query].Description, [Purchasing Log Query].Comments
FROM [Purchasing Log Query]
WHERE ((([Purchasing Log Query].[Date In]) Between [Enter Start Date] And
[Enter End Date]));

Any suggestion would be greatly appreciated. Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try opening the query in design view, delete criteria, and without saving run
the query to see if it pulls any records.

If it does then enter this in criteria row using a date of a record it
produced above but one day less and one day greater --
Between #7/21/2007# AND #8/30/2008#

--
KARL DEWEY
Build a little - Test a little


Annette said:
The enter start date and enter end date works fine but no records are coming
up.

KARL DEWEY said:
Let's go back to your orignal problem - 'It doesn't pull up the information.'
Does pull some or none? Does it pull wrong records?
Is your [Date In] field a DateTime datatype?
What is the format that you enter on prompt by [Enter Start Date] And
[Enter End Date]?

--
KARL DEWEY
Build a little - Test a little


Annette said:
Hi Karl,
I entered the information and I get a error message. "This expression is
typed incorrectly, or it is too complex to be evaluated. For example, a
numeric expression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression to
variables." This is how I changed it.

SELECT [Purchasing Log Query].[Part#], [Purchasing Log Query].[NAI #],
[Purchasing Log Query].[Quantity Received], [Purchasing Log Query].[Date In],
[Purchasing Log Query].Description, [Purchasing Log Query].Comments
FROM [Purchasing Log Query]
WHERE DateValue([Purchasing Log Query.[Date In])Between CVDate([Enter Start
Date])And CVDate([Enter End Date]);
Thanks in advance.




:

Try this for WHERE --
WHERE DateValue([Purchasing Log Query].[Date In]) Between CVDate([Enter
Start Date]) And CVDate([Enter End Date]);

This will remove any time component and convert your text to date.

--
KARL DEWEY
Build a little - Test a little


:

I'm making a query to look up parts purchased between different dates. It
doesn't pull up the information. This is what I have done so far.

SELECT [Purchasing Log Query].[Part#], [Purchasing Log Query].[NAI #],
[Purchasing Log Query].[Quantity Received], [Purchasing Log Query].[Date In],
[Purchasing Log Query].Description, [Purchasing Log Query].Comments
FROM [Purchasing Log Query]
WHERE ((([Purchasing Log Query].[Date In]) Between [Enter Start Date] And
[Enter End Date]));

Any suggestion would be greatly appreciated. Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
A

Annette

Thanks Karl I checked and my date field was a test. I changed it to
Date/time field and my query works. Thank you so much. The records that I'm
pulling also as a quantity of parts. Is there a way to total the quantity
when I pull these records?

KARL DEWEY said:
Try opening the query in design view, delete criteria, and without saving run
the query to see if it pulls any records.

If it does then enter this in criteria row using a date of a record it
produced above but one day less and one day greater --
Between #7/21/2007# AND #8/30/2008#

--
KARL DEWEY
Build a little - Test a little


Annette said:
The enter start date and enter end date works fine but no records are coming
up.

KARL DEWEY said:
Let's go back to your orignal problem - 'It doesn't pull up the information.'
Does pull some or none? Does it pull wrong records?
Is your [Date In] field a DateTime datatype?
What is the format that you enter on prompt by [Enter Start Date] And
[Enter End Date]?

--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,
I entered the information and I get a error message. "This expression is
typed incorrectly, or it is too complex to be evaluated. For example, a
numeric expression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression to
variables." This is how I changed it.

SELECT [Purchasing Log Query].[Part#], [Purchasing Log Query].[NAI #],
[Purchasing Log Query].[Quantity Received], [Purchasing Log Query].[Date In],
[Purchasing Log Query].Description, [Purchasing Log Query].Comments
FROM [Purchasing Log Query]
WHERE DateValue([Purchasing Log Query.[Date In])Between CVDate([Enter Start
Date])And CVDate([Enter End Date]);
Thanks in advance.




:

Try this for WHERE --
WHERE DateValue([Purchasing Log Query].[Date In]) Between CVDate([Enter
Start Date]) And CVDate([Enter End Date]);

This will remove any time component and convert your text to date.

--
KARL DEWEY
Build a little - Test a little


:

I'm making a query to look up parts purchased between different dates. It
doesn't pull up the information. This is what I have done so far.

SELECT [Purchasing Log Query].[Part#], [Purchasing Log Query].[NAI #],
[Purchasing Log Query].[Quantity Received], [Purchasing Log Query].[Date In],
[Purchasing Log Query].Description, [Purchasing Log Query].Comments
FROM [Purchasing Log Query]
WHERE ((([Purchasing Log Query].[Date In]) Between [Enter Start Date] And
[Enter End Date]));

Any suggestion would be greatly appreciated. Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

You can total in a form or report footer.
Otherwise you use a totals query but you can not total and at the same time
view individual records.
--
KARL DEWEY
Build a little - Test a little


Annette said:
Thanks Karl I checked and my date field was a test. I changed it to
Date/time field and my query works. Thank you so much. The records that I'm
pulling also as a quantity of parts. Is there a way to total the quantity
when I pull these records?

KARL DEWEY said:
Try opening the query in design view, delete criteria, and without saving run
the query to see if it pulls any records.

If it does then enter this in criteria row using a date of a record it
produced above but one day less and one day greater --
Between #7/21/2007# AND #8/30/2008#

--
KARL DEWEY
Build a little - Test a little


Annette said:
The enter start date and enter end date works fine but no records are coming
up.

:

Let's go back to your orignal problem - 'It doesn't pull up the information.'
Does pull some or none? Does it pull wrong records?
Is your [Date In] field a DateTime datatype?
What is the format that you enter on prompt by [Enter Start Date] And
[Enter End Date]?

--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,
I entered the information and I get a error message. "This expression is
typed incorrectly, or it is too complex to be evaluated. For example, a
numeric expression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression to
variables." This is how I changed it.

SELECT [Purchasing Log Query].[Part#], [Purchasing Log Query].[NAI #],
[Purchasing Log Query].[Quantity Received], [Purchasing Log Query].[Date In],
[Purchasing Log Query].Description, [Purchasing Log Query].Comments
FROM [Purchasing Log Query]
WHERE DateValue([Purchasing Log Query.[Date In])Between CVDate([Enter Start
Date])And CVDate([Enter End Date]);
Thanks in advance.




:

Try this for WHERE --
WHERE DateValue([Purchasing Log Query].[Date In]) Between CVDate([Enter
Start Date]) And CVDate([Enter End Date]);

This will remove any time component and convert your text to date.

--
KARL DEWEY
Build a little - Test a little


:

I'm making a query to look up parts purchased between different dates. It
doesn't pull up the information. This is what I have done so far.

SELECT [Purchasing Log Query].[Part#], [Purchasing Log Query].[NAI #],
[Purchasing Log Query].[Quantity Received], [Purchasing Log Query].[Date In],
[Purchasing Log Query].Description, [Purchasing Log Query].Comments
FROM [Purchasing Log Query]
WHERE ((([Purchasing Log Query].[Date In]) Between [Enter Start Date] And
[Enter End Date]));

Any suggestion would be greatly appreciated. Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
A

Annette

Thank you very much for all your help.

KARL DEWEY said:
You can total in a form or report footer.
Otherwise you use a totals query but you can not total and at the same time
view individual records.
--
KARL DEWEY
Build a little - Test a little


Annette said:
Thanks Karl I checked and my date field was a test. I changed it to
Date/time field and my query works. Thank you so much. The records that I'm
pulling also as a quantity of parts. Is there a way to total the quantity
when I pull these records?

KARL DEWEY said:
Try opening the query in design view, delete criteria, and without saving run
the query to see if it pulls any records.

If it does then enter this in criteria row using a date of a record it
produced above but one day less and one day greater --
Between #7/21/2007# AND #8/30/2008#

--
KARL DEWEY
Build a little - Test a little


:

The enter start date and enter end date works fine but no records are coming
up.

:

Let's go back to your orignal problem - 'It doesn't pull up the information.'
Does pull some or none? Does it pull wrong records?
Is your [Date In] field a DateTime datatype?
What is the format that you enter on prompt by [Enter Start Date] And
[Enter End Date]?

--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,
I entered the information and I get a error message. "This expression is
typed incorrectly, or it is too complex to be evaluated. For example, a
numeric expression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression to
variables." This is how I changed it.

SELECT [Purchasing Log Query].[Part#], [Purchasing Log Query].[NAI #],
[Purchasing Log Query].[Quantity Received], [Purchasing Log Query].[Date In],
[Purchasing Log Query].Description, [Purchasing Log Query].Comments
FROM [Purchasing Log Query]
WHERE DateValue([Purchasing Log Query.[Date In])Between CVDate([Enter Start
Date])And CVDate([Enter End Date]);
Thanks in advance.




:

Try this for WHERE --
WHERE DateValue([Purchasing Log Query].[Date In]) Between CVDate([Enter
Start Date]) And CVDate([Enter End Date]);

This will remove any time component and convert your text to date.

--
KARL DEWEY
Build a little - Test a little


:

I'm making a query to look up parts purchased between different dates. It
doesn't pull up the information. This is what I have done so far.

SELECT [Purchasing Log Query].[Part#], [Purchasing Log Query].[NAI #],
[Purchasing Log Query].[Quantity Received], [Purchasing Log Query].[Date In],
[Purchasing Log Query].Description, [Purchasing Log Query].Comments
FROM [Purchasing Log Query]
WHERE ((([Purchasing Log Query].[Date In]) Between [Enter Start Date] And
[Enter End Date]));

Any suggestion would be greatly appreciated. Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

running total between two dates 3
How to not include results starting with 99 3
Time from Date in Report 5
Counting results across worksheets/criteria... 1
Showing a report with records between two dates 18
Append query key violation 8
Dcount returning no results!!! 0
query to pull between two dates 4

Top