473,695 Members | 1,871 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_TIM ESTAMP) ORDER BY id" ,
_
"Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" &
Server.MapPath( "db/data.mdb") & ";" , _

'---------------End of code---------------
Thanks
Trevor

May 17 '07 #1
8 2422
On 17 May 2007 06:21:36 -0700, Trev <tr***********@ lycos.co.ukwrot e:

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_TIM ESTAMP) ORDER BY id" ,
_
"Provider=Micro soft.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_TIM ESTAMP) ORDER BY id" ,
_
"Provider=Micro soft.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_TIM ESTAMP) ORDER BY id" ,
_
"Provider=Micro soft.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.c omwrote:
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_TIM ESTAMP) ORDER BY id" ,
_
"Provider=Micro soft.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_TIM ESTAMP), 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.c omwrote:
>>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_TIM ESTAMP) ORDER BY id" ,
_
"Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" &
Server.MapPa th("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_TIM ESTAMP), 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_TIMESTA MP
Between "#" & Date & "#" And "#" & Date + 1 & "#"
Order By Current_TimeSta mp

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.c omwrote:
Trev wrote:
On 17 May, 19:05, salad <o...@vinegar.c omwrote:
>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_TIM ESTAMP) ORDER BY id" ,
_
"Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" &
Server.MapPat h("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_TIM ESTAMP), 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_TIMESTA MP
Between "#" & Date & "#" And "#" & Date + 1 & "#"
Order By Current_TimeSta mp

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.c omwrote:
Trev wrote:
On 17 May, 19:05, salad <o...@vinegar.c omwrote:
>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_TIM ESTAMP) ORDER BY id" ,
_
"Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" &
Server.MapPat h("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_TIM ESTAMP), 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_TIMESTA MP
Between "#" & Date & "#" And "#" & Date + 1 & "#"
Order By Current_TimeSta mp

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.c omwrote:
Trev wrote:
On 17 May, 19:05, salad <o...@vinegar.c omwrote:
>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_TIM ESTAMP) ORDER BY id" ,
_
"Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" &
Server.MapPat h("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_TIM ESTAMP), 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_TIMESTA MP
Between "#" & Date & "#" And "#" & Date + 1 & "#"
Order By Current_TimeSta mp

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
2661
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
4025
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 have to display the next event for each college. That would give me 6 events listed on the page. I have been trying to figure out how to write a query statement in my ASP page to select just the most current event from each college. I have not had...
3
1913
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 retrieve the results for exactly one month later. E.G. I if I enter 10-05-04 to 14-05-04 I want to see the records for exactly 30/31 days later. I was thinking could I manipulate the "Today'sDate" function + 30days.
5
8343
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, while using =date() adds todays date, but this changes as the date changes. Please help. Janet Chapman
1
3167
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 date, then I want to update an Outofwarranty field. Is this possible? Thanks.
5
11982
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
2185
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 to create a form or a report or something that a user would be able to review and show that a day was missed doing the import (indicating they forgot to import that days information). The table the information is being imported into is called...
2
3912
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 textbox defualts to Date(). The SUB FORM has a bound textbox (Date Format). I am trying to create the code for the command button to compare the
2
11119
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
8568
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9115
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8982
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8845
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6491
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5839
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4340
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4579
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
1976
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.