Parameter Query - need previous 2 days worth of data

[vinfurnier]

Hi -

I've been struggling to produce a working parameter query that will
allow the end user to type in any date (mm/dd/yy) and obtain the
records of the previous 2 days. In other words, if the user types in
08/07/06, the records of 08/05/06, 08/06/06 and 08/07/06 will all
appear in the qry.

I have the following code, which is real basic, but I am running into
"the expression is typed incorrectly or is too complex etc".

SELECT [111].Start_Date
FROM 111
WHERE ((([111].[Start_Date])<=[Enter Start Date]-2));

Any help would greatly be appreciated.
Thanx in advance,
vf

[Nick 'The database Guy']

Hi Vin,

You need to use the Dateadd statement in the WHERE clause of the SQL.

The syntax of which is Dateadd("d", -2, [Enter Start Date])

So the where clause will look like this WHERE Start_Date [Enter Start
Date] AND Start_Date < Dateadd("d", -2, [Enter Start Date])

Good Luck

Nick
vinfurnier wrote:

Hi -

I've been struggling to produce a working parameter query that will
allow the end user to type in any date (mm/dd/yy) and obtain the
records of the previous 2 days. In other words, if the user types in
08/07/06, the records of 08/05/06, 08/06/06 and 08/07/06 will all
appear in the qry.

I have the following code, which is real basic, but I am running into
"the expression is typed incorrectly or is too complex etc".

SELECT [111].Start_Date
FROM 111
WHERE ((([111].[Start_Date])<=[Enter Start Date]-2));

Any help would greatly be appreciated.
Thanx in advance,
vf

[vinfurnier]

Hi Nick -
Thanx for the quick reply!!!

I tried this and, allthough I didn't get the "typed incorrectly" error
message, I didn't get any results. However, when I type "Between" in
for the criteria (between 08/05/06 and 08/07/06), I do obtain a number
of records. I really don't want to have the end users do this, (too
many keystokes = too many errors). But, it looks like the query that
you sent me should work.

Where this is a linked table from another db offsite (I have no control
to change any of the settings to it), could it be something in the
Date/time format? My Start_Date field is in Date/Time with no Time
associated with it, just Date.

Anymore thoughts?
Thanx again for your time,
Nick 'The database Guy' wrote:

Hi Vin,

You need to use the Dateadd statement in the WHERE clause of the SQL.

The syntax of which is Dateadd("d", -2, [Enter Start Date])

So the where clause will look like this WHERE Start_Date [Enter Start
Date] AND Start_Date < Dateadd("d", -2, [Enter Start Date])

Good Luck

Nick
vinfurnier wrote:

Hi -

I've been struggling to produce a working parameter query that will
allow the end user to type in any date (mm/dd/yy) and obtain the
records of the previous 2 days. In other words, if the user types in
08/07/06, the records of 08/05/06, 08/06/06 and 08/07/06 will all
appear in the qry.

I have the following code, which is real basic, but I am running into
"the expression is typed incorrectly or is too complex etc".

SELECT [111].Start_Date
FROM 111
WHERE ((([111].[Start_Date])<=[Enter Start Date]-2));

Any help would greatly be appreciated.
Thanx in advance,
vf

[Nick 'The database Guy']

Hello again Vin,

Maybe you should try with other values for instance, Dateadd("h", -48,
[Enter date])

It should work.

Good luck

Nick

vinfurnier wrote:

Hi Nick -
Thanx for the quick reply!!!

I tried this and, allthough I didn't get the "typed incorrectly" error
message, I didn't get any results. However, when I type "Between" in
for the criteria (between 08/05/06 and 08/07/06), I do obtain a number
of records. I really don't want to have the end users do this, (too
many keystokes = too many errors). But, it looks like the query that
you sent me should work.

Where this is a linked table from another db offsite (I have no control
to change any of the settings to it), could it be something in the
Date/time format? My Start_Date field is in Date/Time with no Time
associated with it, just Date.

Anymore thoughts?
Thanx again for your time,
Nick 'The database Guy' wrote:

Hi Vin,

You need to use the Dateadd statement in the WHERE clause of the SQL.

The syntax of which is Dateadd("d", -2, [Enter Start Date])

So the where clause will look like this WHERE Start_Date [Enter Start
Date] AND Start_Date < Dateadd("d", -2, [Enter Start Date])

Good Luck

Nick
vinfurnier wrote:

Hi -
>
I've been struggling to produce a working parameter query that will
allow the end user to type in any date (mm/dd/yy) and obtain the
records of the previous 2 days. In other words, if the user types in
08/07/06, the records of 08/05/06, 08/06/06 and 08/07/06 will all
appear in the qry.
>
I have the following code, which is real basic, but I am running into
"the expression is typed incorrectly or is too complex etc".
>
SELECT [111].Start_Date
FROM 111
WHERE ((([111].[Start_Date])<=[Enter Start Date]-2));
>
Any help would greatly be appreciated.
Thanx in advance,
vf

[Bob Quintal]

When using parameters, if you declare the parameter and its
type, you can use it in multiple places, but it will ask for
input only one time.

Even if you intend to use a parameter only once in the query,
it's a good idea to declare it, ESPECIALLY with dates.

This works:
PARAMETERS [Enter Start Date] DateTime;
SELECT Table2.calldate
FROM Table2
WHERE (((Table2.calld ate) Between [enter start date]-2 And
[enter start date]));

Q
"Nick 'The database Guy'" <ni***********@ eads.comwrote in
news:11******** **************@ h48g2000cwc.goo glegroups.com:

Hello again Vin,

Maybe you should try with other values for instance,
Dateadd("h", -48, [Enter date])

It should work.

Good luck

Nick

vinfurnier wrote:

>Hi Nick -
Thanx for the quick reply!!!

I tried this and, allthough I didn't get the "typed
incorrectly" error message, I didn't get any results.
However, when I type "Between" in for the criteria (between
08/05/06 and 08/07/06), I do obtain a number of records. I
really don't want to have the end users do this, (too many
keystokes = too many errors). But, it looks like the query
that you sent me should work.

Where this is a linked table from another db offsite (I have
no control to change any of the settings to it), could it be
something in the Date/time format? My Start_Date field is in
Date/Time with no Time associated with it, just Date.

Anymore thoughts?
Thanx again for your time,
Nick 'The database Guy' wrote:

Hi Vin,

You need to use the Dateadd statement in the WHERE clause
of the SQL.

The syntax of which is Dateadd("d", -2, [Enter Start Date])

So the where clause will look like this WHERE Start_Date >
[Enter Start Date] AND Start_Date < Dateadd("d", -2, [Enter
Start Date])

Good Luck

Nick
vinfurnier wrote:
Hi -

I've been struggling to produce a working parameter query
that will allow the end user to type in any date
(mm/dd/yy) and obtain the records of the previous 2 days.
In other words, if the user types in 08/07/06, the
records of 08/05/06, 08/06/06 and 08/07/06 will all
appear in the qry.

I have the following code, which is real basic, but I am
running into "the expression is typed incorrectly or is
too complex etc".

SELECT [111].Start_Date
FROM 111
WHERE ((([111].[Start_Date])<=[Enter Start Date]-2));

Any help would greatly be appreciated.
Thanx in advance,
vf

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[vinfurnier]

Hi Bob -

Perfect!!!!
Just what I was looking for.
Thanx again,
vf
Bob Quintal wrote:

When using parameters, if you declare the parameter and its
type, you can use it in multiple places, but it will ask for
input only one time.

Even if you intend to use a parameter only once in the query,
it's a good idea to declare it, ESPECIALLY with dates.

This works:
PARAMETERS [Enter Start Date] DateTime;
SELECT Table2.calldate
FROM Table2
WHERE (((Table2.calld ate) Between [enter start date]-2 And
[enter start date]));

Q
"Nick 'The database Guy'" <ni***********@ eads.comwrote in
news:11******** **************@ h48g2000cwc.goo glegroups.com:

Hello again Vin,

Maybe you should try with other values for instance,
Dateadd("h", -48, [Enter date])

It should work.

Good luck

Nick

vinfurnier wrote:

Hi Nick -
Thanx for the quick reply!!!

I tried this and, allthough I didn't get the "typed
incorrectly" error message, I didn't get any results.
However, when I type "Between" in for the criteria (between
08/05/06 and 08/07/06), I do obtain a number of records. I
really don't want to have the end users do this, (too many
keystokes = too many errors). But, it looks like the query
that you sent me should work.

Where this is a linked table from another db offsite (I have
no control to change any of the settings to it), could it be
something in the Date/time format? My Start_Date field is in
Date/Time with no Time associated with it, just Date.

Anymore thoughts?
Thanx again for your time,
Nick 'The database Guy' wrote:
Hi Vin,

You need to use the Dateadd statement in the WHERE clause
of the SQL.

The syntax of which is Dateadd("d", -2, [Enter Start Date])

So the where clause will look like this WHERE Start_Date >
[Enter Start Date] AND Start_Date < Dateadd("d", -2, [Enter
Start Date])

Good Luck

Nick
vinfurnier wrote:
Hi -
>
I've been struggling to produce a working parameter query
that will allow the end user to type in any date
(mm/dd/yy) and obtain the records of the previous 2 days.
In other words, if the user types in 08/07/06, the
records of 08/05/06, 08/06/06 and 08/07/06 will all
appear in the qry.
>
I have the following code, which is real basic, but I am
running into "the expression is typed incorrectly or is
too complex etc".
>
SELECT [111].Start_Date
FROM 111
WHERE ((([111].[Start_Date])<=[Enter Start Date]-2));
>
Any help would greatly be appreciated.
Thanx in advance,
vf

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com