473,320 Members | 2,161 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 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 2406
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Gerry Viator | last post by:
Hi all, I have a textbox were a time is typed in like: upto 4 numbers 1900 300 1000 1425 I would like as they type the text to show todays date plus the time they
4
by: George Stout | last post by:
First off I do not know alot about writing queries to an Access Database from an ASP page. This is why I need help. I have an Events database for 6 colleges in our metro area. On the homepage I...
3
by: Cillies | last post by:
Does anyknow how to control dates in acess 2000, the thing is I want to search for records using a form by date. I will be using a between date search box. But i want to enter say todays date but...
5
by: Janet Chapman | last post by:
I am creating a database for a video shop and in the loans table want there to be a field for date taken, with a default value of today. Trying to write =today() as in the help gives an error,...
1
by: Stout | last post by:
I want to check to see if a computer is out of warranty. In my database I have a warrantyends date field and I want to compare that to todays date. If todays date is greater than warrantyends...
5
by: Vayse | last post by:
In VB6, I would have used Date() That no longer works. How do I find todays date in VB.net?
3
by: www.ttdown.com | last post by:
Each day a user is supposed to import a list of information into a database table. The user only has 30 days to import this information. After 30 days the information is lost. I need to be able...
2
by: Drum2001 | last post by:
Hello, I am having isues with the following: I have two forms, a MAIN FORM with a SUB FORM: Within the MAIN FORM, I have an unbound textbox (Date Format) and a command button. Onload, the...
2
by: mlevit | last post by:
Hey all, I'm trying to update a table using sql with todays date. I use this, Dim todaysDate As Date todaysDate = Date
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.