cannot open a table by todays date

[Trev]

Hi
Can anyone point me in the right direction here, I would like to open
a table in access 2003 by date. I have an asp web page which needs to
read data from a table with each days today's date (which ever day
that is) then a new table is created with today's date.

Example:
I have a table called 17-may-2007 my ASP page reads this table for
24hours then tomorrow (12:00 midnight 18th) I will have a new table
called 18-may-2007 and the old table is left behind (As 17-may-2007)
so I need my sql statement to automatically open table by today's
date.


This is what I have so far but it does not work. Any ideas

'---------------Start of code---------------
Call BarGraphSQLData( _
"SELECT id, DataReading FROM DAY(CURRENT_TIMESTAMP) ORDER BY id" ,
_
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Server.MapPath("db/data.mdb") & ";" , _

'---------------End of code---------------


Thanks


Trevor

 

[Douglas J. Steele]

Reconsider your design.

Having a separate table for each day isn't a good idea. Instead, have a
single table with a date field in it.

If you want to only see today's data, you can write a query that uses Date()
as the criteria, and use the query wherever you would otherwise have used
the table.

 

[Trev]

Reconsider your design.

Having a separate table for each day isn't a good idea. Instead, have a
single table with a date field in it.

If you want to only see today's data, you can write a query that uses Date()
as the criteria, and use the query wherever you would otherwise have used
the table.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)










- Show quoted text -

Thanks for the reply

Thats why i'm not a programmer or a db designer LOL, ok i get the hit
- what about this option......

todays ping stats which is when i posted this stuff, would be saved in
the
table as 17-may-2007 then tomorrows ping stats would be saved in the
same
table as 18-may-2007 and so on, then on each asp web page i would
select that days date, i guess i would use a stament

Something like:

"SELECT today=now(), DataReading FROM tbldata ORDER BY id" , _

or

"SELECT DAY(CURRENT_TIMESTAMP), DataReading FROM tbldata ORDER BYid" ,
_


Are any of these statments about right and which one would you
recomend?


Thanks


Trev

 

[Douglas J. Steele]

Thanks for the reply

Thats why i'm not a programmer or a db designer LOL, ok i get the hit
- what about this option......

todays ping stats which is when i posted this stuff, would be saved in
the
table as 17-may-2007 then tomorrows ping stats would be saved in the
same
table as 18-may-2007 and so on, then on each asp web page i would
select that days date, i guess i would use a stament

Something like:

"SELECT today=now(), DataReading FROM tbldata ORDER BY id" , _

or

"SELECT DAY(CURRENT_TIMESTAMP), DataReading FROM tbldata ORDER BYid" ,
_


Are any of these statments about right and which one would you
recomend?

First, if all you care about is the date, don't use the now function: it
returns date and time.

Assuming your table have fields ReadingDate and DataReading, your SQLto
return all of the data would look something like:

"SELECT ReadingDate, DataReading FROM tblData"

If you wanted that sorted chronologically from oldest to newest, you'd use:

"SELECT ReadingDate, DataReading FROM tblData ORDER BY ReadingDate"


If you wanted that sorted chronologically from newest to oldest, you'd use:

"SELECT ReadingDate, DataReading FROM tblData ORDER BY ReadingDate DESC"

If you only wanted today's reading, you'd use:

"SELECT ReadingDate, DataReading FROM tblData WHERE ReadingDate = Date()"


If you only wanted yesterday's reading, you'd use:

"SELECT ReadingDate, DataReading FROM tblData WHERE ReadingDate =
DateAdd("d", -1, Date())"

 

[Trev]

First, if all you care about is the date, don't use the now function: it
returns date and time.

Assuming your table have fields ReadingDate and DataReading, your SQLto
return all of the data would look something like:

"SELECT ReadingDate, DataReading FROM tblData"

If you wanted that sorted chronologically from oldest to newest, you'd use:

"SELECT ReadingDate, DataReading FROM tblData ORDER BY ReadingDate"

If you wanted that sorted chronologically from newest to oldest, you'd use:

"SELECT ReadingDate, DataReading FROM tblData ORDER BY ReadingDate DESC"

If you only wanted today's reading, you'd use:

"SELECT ReadingDate, DataReading FROM tblData WHERE ReadingDate = Date()"

If you only wanted yesterday's reading, you'd use:

"SELECT ReadingDate, DataReading FROM tblData WHERE ReadingDate =
DateAdd("d", -1, Date())"

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)- Hide quoted text -

- Show quoted text -

Hi,
Just to let you know

This was solved by using
"SELECT DateTaken, DataReading FROM tblData WHERE DateTaken =
Date()" , _

Thanks, for your help.

Trev

 

[Trev]

First, if all you care about is the date, don't use the now function: it
returns date and time.

Assuming your table have fields ReadingDate and DataReading, your SQLto
return all of the data would look something like:

"SELECT ReadingDate, DataReading FROM tblData"

If you wanted that sorted chronologically from oldest to newest, you'd use:

"SELECT ReadingDate, DataReading FROM tblData ORDER BY ReadingDate"

If you wanted that sorted chronologically from newest to oldest, you'd use:

"SELECT ReadingDate, DataReading FROM tblData ORDER BY ReadingDate DESC"

If you only wanted today's reading, you'd use:

"SELECT ReadingDate, DataReading FROM tblData WHERE ReadingDate = Date()"

If you only wanted yesterday's reading, you'd use:

"SELECT ReadingDate, DataReading FROM tblData WHERE ReadingDate =
DateAdd("d", -1, Date())"

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)- Hide quoted text -

- Show quoted text -

Hi,
Just to let you know

This was solved by using
"SELECT DateTaken, DataReading FROM tblData WHERE DateTaken =
Date()" , _

Thanks, for your help.

Trev