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

On 17 May 2007 06:21:36 -0700, Trev <trevor-dustan@lycos.co.ukwrote:

Horrible database designs lead to horrible sql statements.
Why not have a single table, with an additional TransactionDate
column?

-Tom.

>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

On May 17, 9:21 am, Trev <trevor-dus...@lycos.co.ukwrote:

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

Whats the point of having a different table name each day?

Trev wrote:

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.

What is "today"? Since this is ASP, you might get hits from anywhere in
the world. So is "today" the same day as the surfer or is today the
network date where the file is stored?

You are opening a can of worms that will bite you in the butt later on.

>
>
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
>

On 17 May, 19:05, salad <o...@vinegar.comwrote:

Trev wrote:

>

>
What is "today"? Since this is ASP, you might get hits from anywhere in
the world. So is "today" the same day as the surfer or is today the
network date where the file is stored?
>
You are opening a can of worms that will bite you in the butt later on.
>
>
>
>
>

>

>

>

>

>
- Show quoted text -

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 page 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

Trev wrote:

On 17 May, 19:05, salad <o...@vinegar.comwrote:
>

>
>
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 page 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
>

One thing about Now() vs Date. Now is a true time stamp; day hr mn sec
whereas Date is simply the date. So sometimes people will ask why no
records were returned when the do something like
Where NowField = Date

Date is the day with a time of 00:00:00. Midnight. So the only records
returned would be those that have a date/time stamp of exacly midnight.
You would need to check for records between today and tomorrow to
catch all records in today since 05/18/2005 will never equal a record
with 05/18/2007 00:00:01

In Access, it might be something like

Select ID, DataReading From YourTableName
Where CURRENT_TIMESTAMP
Between "#" & Date & "#" And "#" & Date + 1 & "#"
Order By Current_TimeStamp

This would select the record ID, the reading from your table where the
timestamp is between today and tomorrow and you are sorting it on the
timestamp field. You might need to make some adjustments for your ASP.

On 18 May, 22:30, salad <o...@vinegar.comwrote:

Trev wrote:

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>
One thing about Now() vs Date. Now is a true time stamp; day hr mn sec
whereas Date is simply the date. So sometimes people will ask why no
records were returned when the do something like
Where NowField = Date
>
Date is the day with a time of 00:00:00. Midnight. So the only records
returned would be those that have a date/time stamp of exacly midnight.
You would need to check for records between today and tomorrow to
catch all records in today since 05/18/2005 will never equal a record
with 05/18/2007 00:00:01
>
In Access, it might be something like
>
Select ID, DataReading From YourTableName
Where CURRENT_TIMESTAMP
Between "#" & Date & "#" And "#" & Date + 1 & "#"
Order By Current_TimeStamp
>
This would select the record ID, the reading from your table where the
timestamp is between today and tomorrow and you are sorting it on the
timestamp field. You might need to make some adjustments for your ASP.- Hide quoted text -
>
- Show quoted text -

Hi,
Just to let you know

I have now solved this by the extra bit of info from Salad


Thanks, for everyone's help.

Trev

On 18 May, 22:30, salad <o...@vinegar.comwrote:

Trev wrote:

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>
One thing about Now() vs Date. Now is a true time stamp; day hr mn sec
whereas Date is simply the date. So sometimes people will ask why no
records were returned when the do something like
Where NowField = Date
>
Date is the day with a time of 00:00:00. Midnight. So the only records
returned would be those that have a date/time stamp of exacly midnight.
You would need to check for records between today and tomorrow to
catch all records in today since 05/18/2005 will never equal a record
with 05/18/2007 00:00:01
>
In Access, it might be something like
>
Select ID, DataReading From YourTableName
Where CURRENT_TIMESTAMP
Between "#" & Date & "#" And "#" & Date + 1 & "#"
Order By Current_TimeStamp
>
This would select the record ID, the reading from your table where the
timestamp is between today and tomorrow and you are sorting it on the
timestamp field. You might need to make some adjustments for your ASP.- Hide quoted text -
>
- Show quoted text -

Hi,
Just to let you know

I have now solved this by the extra bit of info from Salad


Thanks, for everyone's help.

Trev

On 18 May, 22:30, salad <o...@vinegar.comwrote:

Trev wrote:

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>

>
One thing about Now() vs Date. Now is a true time stamp; day hr mn sec
whereas Date is simply the date. So sometimes people will ask why no
records were returned when the do something like
Where NowField = Date
>
Date is the day with a time of 00:00:00. Midnight. So the only records
returned would be those that have a date/time stamp of exacly midnight.
You would need to check for records between today and tomorrow to
catch all records in today since 05/18/2005 will never equal a record
with 05/18/2007 00:00:01
>
In Access, it might be something like
>
Select ID, DataReading From YourTableName
Where CURRENT_TIMESTAMP
Between "#" & Date & "#" And "#" & Date + 1 & "#"
Order By Current_TimeStamp
>
This would select the record ID, the reading from your table where the
timestamp is between today and tomorrow and you are sorting it on the
timestamp field. You might need to make some adjustments for your ASP.- Hide quoted text -
>
- Show quoted text -

Hi,
Just to let you know

I have now solved this by the extra bit of info from Salad


Thanks, for everyone's help.

Trev