471,073 Members | 1,112 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,073 software developers and data experts.

can a table be opened in access by todays date

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

May 17 '07 #1
8 2257
On 17 May 2007 06:21:36 -0700, Trev <tr***********@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
May 17 '07 #2
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?

May 17 '07 #3
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
May 17 '07 #4
On 17 May, 19:05, salad <o...@vinegar.comwrote:
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- Hide quoted text -

- 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

May 17 '07 #5
Trev wrote:
On 17 May, 19:05, salad <o...@vinegar.comwrote:
>>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- Hide quoted text -

- 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
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.
May 18 '07 #6
On 18 May, 22:30, salad <o...@vinegar.comwrote:
Trev wrote:
On 17 May, 19:05, salad <o...@vinegar.comwrote:
>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- Hide quoted text -
>- 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

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

May 22 '07 #7
On 18 May, 22:30, salad <o...@vinegar.comwrote:
Trev wrote:
On 17 May, 19:05, salad <o...@vinegar.comwrote:
>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- Hide quoted text -
>- 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

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

May 22 '07 #8
On 18 May, 22:30, salad <o...@vinegar.comwrote:
Trev wrote:
On 17 May, 19:05, salad <o...@vinegar.comwrote:
>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- Hide quoted text -
>- 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

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

May 22 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Gerry Viator | last post: by
3 posts views Thread by Cillies | last post: by
5 posts views Thread by Janet Chapman | last post: by
1 post views Thread by Stout | last post: by
5 posts views Thread by Vayse | last post: by
3 posts views Thread by www.ttdown.com | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.