An expression for dates from and to

R

Ronnie

I am trying to create an expression to select items from a date field falling
from a certain date to a certain date. I am obviously missing something in
my expression. The field name is DateComplete. How can I set up this
expression to pull date from a certain date to a certain date?
 
K

Kerry

Hi Ronnie,

An example of the SQL would be:

SELECT *
FROM [TABLE NAME]
WHERE DateComplete Between #1/1/2006# And #1/1/2007#
;
 
R

Ronnie

I tried this, but it did not pull the dates between the dates I requested.
It still pulls every record. I also need to be able to enter the dates from
and to.
--
Ronnie


Kerry said:
Hi Ronnie,

An example of the SQL would be:

SELECT *
FROM [TABLE NAME]
WHERE DateComplete Between #1/1/2006# And #1/1/2007#
;

I am trying to create an expression to select items from a date field falling
from a certain date to a certain date. I am obviously missing something in
my expression. The field name is DateComplete. How can I set up this
expression to pull date from a certain date to a certain date?
Click to expand...
Click to expand...
 
K

KARL DEWEY

Post your SQL.
--
KARL DEWEY
Build a little - Test a little


Ronnie said:
I tried this, but it did not pull the dates between the dates I requested.
It still pulls every record. I also need to be able to enter the dates from
and to.
--
Ronnie


Kerry said:
Hi Ronnie,

An example of the SQL would be:

SELECT *
FROM [TABLE NAME]
WHERE DateComplete Between #1/1/2006# And #1/1/2007#
;

I am trying to create an expression to select items from a date field falling
from a certain date to a certain date. I am obviously missing something in
my expression. The field name is DateComplete. How can I set up this
expression to pull date from a certain date to a certain date?
Click to expand...
Click to expand...
Click to expand...
 
R

Ronnie

SELECT PODet_wCustInfo_qry.ContactID, PODet_wCustInfo_qry.ContRactID,
PODet_wCustInfo_qry.POID, PODet_ItemInfo_qry.ItemPrice,
PODet_ItemInfo_qry.Quantity, PODet_ItemInfo_qry.Amount,
PODet_wCustInfo_qry.CustomerDate, Year(PODet_wCustInfo_qry.CustomerDate) AS
Expr1, PODet_wCustInfo_qry.DateComplete, ContRact_wPOID_qry.Extras,
ContRact_wPOID_qry.[Cost of Building], [DateComplete] Between #1/1/2006# And
#1/1/2007# AS Expr2
FROM PODet_ItemInfo_qry INNER JOIN (ContRact_wPOID_qry INNER JOIN
PODet_wCustInfo_qry ON ContRact_wPOID_qry.POID = PODet_wCustInfo_qry.POID) ON
PODet_ItemInfo_qry.POID = PODet_wCustInfo_qry.POID;

--
Ronnie


KARL DEWEY said:
Post your SQL.
--
KARL DEWEY
Build a little - Test a little


Ronnie said:
I tried this, but it did not pull the dates between the dates I requested.
It still pulls every record. I also need to be able to enter the dates from
and to.
--
Ronnie


Kerry said:
Hi Ronnie,

An example of the SQL would be:

SELECT *
FROM [TABLE NAME]
WHERE DateComplete Between #1/1/2006# And #1/1/2007#
;

I am trying to create an expression to select items from a date field falling
from a certain date to a certain date. I am obviously missing something in
my expression. The field name is DateComplete. How can I set up this
expression to pull date from a certain date to a certain date?
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

I am trying to create an expression to select items from a date field falling
from a certain date to a certain date. I am obviously missing something in
my expression. The field name is DateComplete. How can I set up this
expression to pull date from a certain date to a certain date?
Click to expand...

Evidently you are having some problem with the expression or with the results
it provides. However you have not posted the expression nor the problem.

Care to give us some help here?

Just for a shortcut, guessing at one (of many) possible issues: if the
date/time field you're searching has a Time component you may miss data on the
last day using the syntax BETWEEN [Start] AND [End]. The End date will search
up to midnight on the beginning of the last day, omitting later times. Try
= CDate([Enter start date:]) AND < DateAdd("d", 1, CDate([Enter end date:]))
Click to expand...

to a) ensure that user input is converted to a date using their computer's
regional date conventions and b) cover the last day's data.

John W. Vinson [MVP]
 
K

KARL DEWEY

Try this ---
SELECT PODet_wCustInfo_qry.ContactID, PODet_wCustInfo_qry.ContRactID,
PODet_wCustInfo_qry.POID, PODet_ItemInfo_qry.ItemPrice,
PODet_ItemInfo_qry.Quantity, PODet_ItemInfo_qry.Amount,
PODet_wCustInfo_qry.CustomerDate, Year(PODet_wCustInfo_qry.CustomerDate) AS
Expr1, PODet_wCustInfo_qry.DateComplete, ContRact_wPOID_qry.Extras,
ContRact_wPOID_qry.[Cost of Building]
FROM PODet_ItemInfo_qry INNER JOIN (ContRact_wPOID_qry INNER JOIN
PODet_wCustInfo_qry ON ContRact_wPOID_qry.POID = PODet_wCustInfo_qry.POID)ON
PODet_ItemInfo_qry.POID = PODet_wCustInfo_qry.POID
WHERE [DateComplete] Between #1/1/2006# And #1/1/2007#;

--
KARL DEWEY
Build a little - Test a little


Ronnie said:
SELECT PODet_wCustInfo_qry.ContactID, PODet_wCustInfo_qry.ContRactID,
PODet_wCustInfo_qry.POID, PODet_ItemInfo_qry.ItemPrice,
PODet_ItemInfo_qry.Quantity, PODet_ItemInfo_qry.Amount,
PODet_wCustInfo_qry.CustomerDate, Year(PODet_wCustInfo_qry.CustomerDate) AS
Expr1, PODet_wCustInfo_qry.DateComplete, ContRact_wPOID_qry.Extras,
ContRact_wPOID_qry.[Cost of Building], [DateComplete] Between #1/1/2006# And
#1/1/2007# AS Expr2
FROM PODet_ItemInfo_qry INNER JOIN (ContRact_wPOID_qry INNER JOIN
PODet_wCustInfo_qry ON ContRact_wPOID_qry.POID = PODet_wCustInfo_qry.POID) ON
PODet_ItemInfo_qry.POID = PODet_wCustInfo_qry.POID;

--
Ronnie


KARL DEWEY said:
Post your SQL.
--
KARL DEWEY
Build a little - Test a little


Ronnie said:
I tried this, but it did not pull the dates between the dates I requested.
It still pulls every record. I also need to be able to enter the dates from
and to.
--
Ronnie


:

Hi Ronnie,

An example of the SQL would be:

SELECT *
FROM [TABLE NAME]
WHERE DateComplete Between #1/1/2006# And #1/1/2007#
;

I am trying to create an expression to select items from a date field falling
from a certain date to a certain date. I am obviously missing something in
my expression. The field name is DateComplete. How can I set up this
expression to pull date from a certain date to a certain date?
Click to expand...
Click to expand...
Click to expand...
 
L

Larry Linson

Help me understand: what led you to believe that you could move the criteria
from the WHERE clause that Kerry specified into the list of fields, not use
a WHERE clause, and still have it work as though there were a WHERE clause?

Leave the [DateComplete] in the Field List and add a WHERE clause at the end
of your SQL, as Kerry showed, and I think you'll find it will work A LOT
better.

You'll have to provide a Form for the user to enter the dates and VBA code
to insert them into an SQL statement, or into the WhereCondition argument of
a DoCmd.OpenForm or DoCmd.OpenReport.

Larry Linson
Microsoft Access MVP

Ronnie said:
SELECT PODet_wCustInfo_qry.ContactID, PODet_wCustInfo_qry.ContRactID,
PODet_wCustInfo_qry.POID, PODet_ItemInfo_qry.ItemPrice,
PODet_ItemInfo_qry.Quantity, PODet_ItemInfo_qry.Amount,
PODet_wCustInfo_qry.CustomerDate, Year(PODet_wCustInfo_qry.CustomerDate)
AS
Expr1, PODet_wCustInfo_qry.DateComplete, ContRact_wPOID_qry.Extras,
ContRact_wPOID_qry.[Cost of Building], [DateComplete] Between #1/1/2006#
And
#1/1/2007# AS Expr2
FROM PODet_ItemInfo_qry INNER JOIN (ContRact_wPOID_qry INNER JOIN
PODet_wCustInfo_qry ON ContRact_wPOID_qry.POID = PODet_wCustInfo_qry.POID)
ON
PODet_ItemInfo_qry.POID = PODet_wCustInfo_qry.POID;

--
Ronnie


KARL DEWEY said:
Post your SQL.
--
KARL DEWEY
Build a little - Test a little


Ronnie said:
I tried this, but it did not pull the dates between the dates I
requested.
It still pulls every record. I also need to be able to enter the dates
from
and to.
--
Ronnie


:

Hi Ronnie,

An example of the SQL would be:

SELECT *
FROM [TABLE NAME]
WHERE DateComplete Between #1/1/2006# And #1/1/2007#
;

I am trying to create an expression to select items from a date
field falling
from a certain date to a certain date. I am obviously missing
something in
my expression. The field name is DateComplete. How can I set up
this
expression to pull date from a certain date to a certain date?
Click to expand...
Click to expand...
Click to expand...
 
R

Ronnie

Thanks for all your help. I will try all these things tomorrow when I am
back at work. Obviously, you are all much more Access savy than I. Thanks
again to all!
--
Ronnie


Larry Linson said:
Help me understand: what led you to believe that you could move the criteria
from the WHERE clause that Kerry specified into the list of fields, not use
a WHERE clause, and still have it work as though there were a WHERE clause?

Leave the [DateComplete] in the Field List and add a WHERE clause at the end
of your SQL, as Kerry showed, and I think you'll find it will work A LOT
better.

You'll have to provide a Form for the user to enter the dates and VBA code
to insert them into an SQL statement, or into the WhereCondition argument of
a DoCmd.OpenForm or DoCmd.OpenReport.

Larry Linson
Microsoft Access MVP

Ronnie said:
SELECT PODet_wCustInfo_qry.ContactID, PODet_wCustInfo_qry.ContRactID,
PODet_wCustInfo_qry.POID, PODet_ItemInfo_qry.ItemPrice,
PODet_ItemInfo_qry.Quantity, PODet_ItemInfo_qry.Amount,
PODet_wCustInfo_qry.CustomerDate, Year(PODet_wCustInfo_qry.CustomerDate)
AS
Expr1, PODet_wCustInfo_qry.DateComplete, ContRact_wPOID_qry.Extras,
ContRact_wPOID_qry.[Cost of Building], [DateComplete] Between #1/1/2006#
And
#1/1/2007# AS Expr2
FROM PODet_ItemInfo_qry INNER JOIN (ContRact_wPOID_qry INNER JOIN
PODet_wCustInfo_qry ON ContRact_wPOID_qry.POID = PODet_wCustInfo_qry.POID)
ON
PODet_ItemInfo_qry.POID = PODet_wCustInfo_qry.POID;

--
Ronnie


KARL DEWEY said:
Post your SQL.
--
KARL DEWEY
Build a little - Test a little


:

I tried this, but it did not pull the dates between the dates I
requested.
It still pulls every record. I also need to be able to enter the dates
from
and to.
--
Ronnie


:

Hi Ronnie,

An example of the SQL would be:

SELECT *
FROM [TABLE NAME]
WHERE DateComplete Between #1/1/2006# And #1/1/2007#
;

I am trying to create an expression to select items from a date
field falling
from a certain date to a certain date. I am obviously missing
something in
my expression. The field name is DateComplete. How can I set up
this
expression to pull date from a certain date to a certain date?
Click to expand...
Click to expand...
Click to expand...
 
R

Ronnie

THIS WORKED! I now see what I was doing wrong. I should have been putting
the information in the criteria field rather than trying to make an
expression. Thank you very much for your help!
--
Ronnie


KARL DEWEY said:
Try this ---
SELECT PODet_wCustInfo_qry.ContactID, PODet_wCustInfo_qry.ContRactID,
PODet_wCustInfo_qry.POID, PODet_ItemInfo_qry.ItemPrice,
PODet_ItemInfo_qry.Quantity, PODet_ItemInfo_qry.Amount,
PODet_wCustInfo_qry.CustomerDate, Year(PODet_wCustInfo_qry.CustomerDate) AS
Expr1, PODet_wCustInfo_qry.DateComplete, ContRact_wPOID_qry.Extras,
ContRact_wPOID_qry.[Cost of Building]
FROM PODet_ItemInfo_qry INNER JOIN (ContRact_wPOID_qry INNER JOIN
PODet_wCustInfo_qry ON ContRact_wPOID_qry.POID = PODet_wCustInfo_qry.POID)ON
PODet_ItemInfo_qry.POID = PODet_wCustInfo_qry.POID
WHERE [DateComplete] Between #1/1/2006# And #1/1/2007#;

--
KARL DEWEY
Build a little - Test a little


Ronnie said:
SELECT PODet_wCustInfo_qry.ContactID, PODet_wCustInfo_qry.ContRactID,
PODet_wCustInfo_qry.POID, PODet_ItemInfo_qry.ItemPrice,
PODet_ItemInfo_qry.Quantity, PODet_ItemInfo_qry.Amount,
PODet_wCustInfo_qry.CustomerDate, Year(PODet_wCustInfo_qry.CustomerDate) AS
Expr1, PODet_wCustInfo_qry.DateComplete, ContRact_wPOID_qry.Extras,
ContRact_wPOID_qry.[Cost of Building], [DateComplete] Between #1/1/2006# And
#1/1/2007# AS Expr2
FROM PODet_ItemInfo_qry INNER JOIN (ContRact_wPOID_qry INNER JOIN
PODet_wCustInfo_qry ON ContRact_wPOID_qry.POID = PODet_wCustInfo_qry.POID) ON
PODet_ItemInfo_qry.POID = PODet_wCustInfo_qry.POID;

--
Ronnie


KARL DEWEY said:
Post your SQL.
--
KARL DEWEY
Build a little - Test a little


:

I tried this, but it did not pull the dates between the dates I requested.
It still pulls every record. I also need to be able to enter the dates from
and to.
--
Ronnie


:

Hi Ronnie,

An example of the SQL would be:

SELECT *
FROM [TABLE NAME]
WHERE DateComplete Between #1/1/2006# And #1/1/2007#
;

I am trying to create an expression to select items from a date field falling
from a certain date to a certain date. I am obviously missing something in
my expression. The field name is DateComplete. How can I set up this
expression to pull date from a certain date to a certain date?
Click to expand...
Click to expand...
Click to expand...
 
Top