Show Last Weeks Data

[AshParr]

Hi all,

I would appreciate any help you guys can offer with my problem.

I have a database where records are entered from a SCADA system, on of
the fields in the table is called C1 which contains the date of the
entry.

What i would like to do is show the records entered in the last week/
seven days.


Any help like i said would be appreciated

Many Thanks,

Ashley Parr

 

[Allen Browne]

In your query, put this under the date field:

= Date() - 7

 

[Ken Snell \(MVP\)]

Assuming that the date value is stored in the normal ACCESS format:

SELECT * FROM TableName
WHERE [DateField] Between
DateAdd("d",-7, Date()) And
Date();

 

[AshParr]

Assuming that the date value is stored in the normal ACCESS format:

SELECT * FROM TableName
WHERE [DateField] Between
DateAdd("d",-7, Date()) And
Date();

--

Ken Snell
<MS ACCESS MVP>

Hi all,

I would appreciate any help you guys can offer with my problem.

I have a database where records are entered from a SCADA system, on of
the fields in the table is called C1 which contains the date of the
entry.

What i would like to do is show the records entered in the last week/
seven days.

Any help like i said would be appreciated

Many Thanks,

Ashley Parr- Hide quoted text -

- Show quoted text -

Thankyou for both of your replies but i still cant seem to get it to
work.

i have used:

SELECT
*
< All Of my fields
FROM
Maintenance
< Table Name
WHERE [C1] Between DateAdd("d",-7, Date()) And
Date(); < C1 = Date Field (dd/mm/
yyyy)

And used:

= Date() - 7

still data is shown from before those 7 days, any ideas?

 

[Allen Browne]

What is Maintenance?
Is it a table in this Access database? A query? An attached table?

If it is a local Access table, open it in design view. What is the Data Type
of the C1 field?

If it is an attached table, what is it? An Excel spreadsheet? A text file?

I'm guessing that JET is not understanding the data type as you expect, and
so it may be necessary to typecast the field and/or the criteria expression.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Assuming that the date value is stored in the normal ACCESS format:

SELECT * FROM TableName
WHERE [DateField] Between
DateAdd("d",-7, Date()) And
Date();

--

Ken Snell
<MS ACCESS MVP>

Hi all,

I would appreciate any help you guys can offer with my problem.

I have a database where records are entered from a SCADA system, on of
the fields in the table is called C1 which contains the date of the
entry.

What i would like to do is show the records entered in the last week/
seven days.

Any help like i said would be appreciated

Many Thanks,

Ashley Parr- Hide quoted text -

- Show quoted text -

Thankyou for both of your replies but i still cant seem to get it to
work.

i have used:

SELECT
*
< All Of my fields
FROM
Maintenance
< Table Name
WHERE [C1] Between DateAdd("d",-7, Date()) And
Date(); < C1 = Date Field (dd/mm/
yyyy)

And used:

= Date() - 7

still data is shown from before those 7 days, any ideas?

 

[AshParr]

What is Maintenance?
Is it a table in this Access database? A query? An attached table?

If it is a local Access table, open it in design view. What is the Data Type
of the C1 field?

If it is an attached table, what is it? An Excel spreadsheet? A text file?

I'm guessing that JET is not understanding the data type as you expect, and
so it may be necessary to typecast the field and/or the criteria expression.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Assuming that the date value is stored in the normal ACCESS format:
SELECT * FROM TableName
WHERE [DateField] Between
DateAdd("d",-7, Date()) And
Date();
--
Ken Snell
<MS ACCESS MVP>

Hi all,
I would appreciate any help you guys can offer with my problem.
I have a database where records are entered from a SCADA system, on of
the fields in the table is called C1 which contains the date of the
entry.
What i would like to do is show the records entered in the last week/
seven days.
Any help like i said would be appreciated
Many Thanks,
Ashley Parr- Hide quoted text -
- Show quoted text -

Thankyou for both of your replies but i still cant seem to get it to
work.

i have used:

SELECT
*
< All Of my fields
FROM
Maintenance
< Table Name
WHERE [C1] Between DateAdd("d",-7, Date()) And
Date(); < C1 = Date Field (dd/mm/
yyyy)

And used:

still data is shown from before those 7 days, any ideas?- Hide quoted text -

- Show quoted text -

Sorry for the lack of information posted, i didnt realise that it
would be posted differently.

But thanks for the reply, your a top man

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The Problem Resolved:

Maintenance is a local table in access, but i did not create it. It
was created by my third party system, so i took for granted that in
that system when i sent my data through sql that the date field would
be automatically set to date.

So i went in and changed the field type and there you go, my last
weeks data wrks