By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,669 Members | 3,082 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,669 IT Pros & Developers. It's quick & easy.

can a table be opened in access by todays date

P: n/a
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
Share this Question
Share on Google+
8 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.