Hey everybody,
I have an 2000 Access database that stores job listings from potential
employers for a school. I would like to be able to display the date
on a webpage the last time the database was updated (inputed through
asp natch'). The database is not updated everyday so that won't work
but something like the database was updated on Monday 11/24/2003, I
would want it to say "New job listings as of Monday, 11/24/2003" if it
were checked on Tuesday or whenever.
The database uses an "Entry Date" column for every job entered so I
figure that would be the key for this function (unless there is
something better to use).
Currently I have:
<%
Dim Connect, Jobs
Set Connect=Server.CreateObject("ADODB.Connection")
Connect.Open "careers1"
Set Jobs = Connect.Execute (_
"SELECT * FROM [JOB LIST] WHERE (([JOB LIST].[Entry Date])=Date())")
do until Jobs.EOF
%>
<p class="text"><font color="red">New job listings as of
<%=Jobs("Entry Date")%></font>
<% Jobs.MoveNext
loop %>
but that only displays new jobs if they have been entered on the
current day. I need to have the date of the last job entered to be
viewed. Any help would be appreciated.
If you have any questions, please ask.
Dan Matthews, MCP
Technology Manager
Green Hall, University of Kansas
(785) 864-9267 dm*******@ku.edu http://www.people.ku.edu/~dmatthew/ 8 4859
Just make another query that returns it:
SELECT TOP 1 [Entry Date] FROM [JOB LIST] ORDER BY [Entry Date] DESC
"Dan Matthews" <dm*******@ku.edu> wrote in message
news:ee************************@posting.google.com ... Hey everybody,
I have an 2000 Access database that stores job listings from potential employers for a school. I would like to be able to display the date on a webpage the last time the database was updated (inputed through asp natch'). The database is not updated everyday so that won't work but something like the database was updated on Monday 11/24/2003, I would want it to say "New job listings as of Monday, 11/24/2003" if it were checked on Tuesday or whenever.
The database uses an "Entry Date" column for every job entered so I figure that would be the key for this function (unless there is something better to use).
Currently I have:
<% Dim Connect, Jobs Set Connect=Server.CreateObject("ADODB.Connection") Connect.Open "careers1" Set Jobs = Connect.Execute (_ "SELECT * FROM [JOB LIST] WHERE (([JOB LIST].[Entry Date])=Date())")
do until Jobs.EOF %>
<p class="text"><font color="red">New job listings as of <%=Jobs("Entry Date")%></font>
<% Jobs.MoveNext loop %>
but that only displays new jobs if they have been entered on the current day. I need to have the date of the last job entered to be viewed. Any help would be appreciated.
If you have any questions, please ask.
Dan Matthews, MCP Technology Manager Green Hall, University of Kansas (785) 864-9267 dm*******@ku.edu http://www.people.ku.edu/~dmatthew/
"Dan Matthews" wrote ...
[..snip..]
I read that a couple of times but still couldn't quite work out what you
wanted to do...so here goes at me guessing...
If you want to return just the lastest date your database was updated - why
not something like this :
SELECT MAX(EntryDate) AS LastDate FROM JobsList
That should return the most recent date of the items added.
Hope this helps,
Regards
Rob
> Set Jobs = Connect.Execute (_ "SELECT * FROM [JOB LIST] WHERE (([JOB LIST].[Entry Date])=Date())")
What about sorting it by date desc?
SELECT TOP 1 [Entry Date] FROM [JOB LIST] ORDER BY [Entry Date] DESC
This should only return the last record
PS... I'm not sure it's a good idea to include spaces in your column and
table names.
"Dan Matthews" <dm*******@ku.edu> wrote in message
news:ee************************@posting.google.com ... Hey everybody,
I have an 2000 Access database that stores job listings from potential employers for a school. I would like to be able to display the date on a webpage the last time the database was updated (inputed through asp natch'). The database is not updated everyday so that won't work but something like the database was updated on Monday 11/24/2003, I would want it to say "New job listings as of Monday, 11/24/2003" if it were checked on Tuesday or whenever.
The database uses an "Entry Date" column for every job entered so I figure that would be the key for this function (unless there is something better to use).
Currently I have:
<% Dim Connect, Jobs Set Connect=Server.CreateObject("ADODB.Connection") Connect.Open "careers1" Set Jobs = Connect.Execute (_ "SELECT * FROM [JOB LIST] WHERE (([JOB LIST].[Entry Date])=Date())")
do until Jobs.EOF %>
<p class="text"><font color="red">New job listings as of <%=Jobs("Entry Date")%></font>
<% Jobs.MoveNext loop %>
but that only displays new jobs if they have been entered on the current day. I need to have the date of the last job entered to be viewed. Any help would be appreciated.
If you have any questions, please ask.
Dan Matthews, MCP Technology Manager Green Hall, University of Kansas (785) 864-9267 dm*******@ku.edu http://www.people.ku.edu/~dmatthew/
How about using an aggregate function???
SELECT MAX([Entry Date]) FROM [Job List]
Adjust the query accordingly when you fix your poorly-named objects.
"Dan Matthews" <dm*******@ku.edu> wrote in message
news:ee************************@posting.google.com ... Hey everybody,
I have an 2000 Access database that stores job listings from potential employers for a school. I would like to be able to display the date on a webpage the last time the database was updated (inputed through asp natch'). The database is not updated everyday so that won't work but something like the database was updated on Monday 11/24/2003, I would want it to say "New job listings as of Monday, 11/24/2003" if it were checked on Tuesday or whenever.
The database uses an "Entry Date" column for every job entered so I figure that would be the key for this function (unless there is something better to use).
Currently I have:
<% Dim Connect, Jobs Set Connect=Server.CreateObject("ADODB.Connection") Connect.Open "careers1" Set Jobs = Connect.Execute (_ "SELECT * FROM [JOB LIST] WHERE (([JOB LIST].[Entry Date])=Date())")
do until Jobs.EOF %>
<p class="text"><font color="red">New job listings as of <%=Jobs("Entry Date")%></font>
<% Jobs.MoveNext loop %>
but that only displays new jobs if they have been entered on the current day. I need to have the date of the last job entered to be viewed. Any help would be appreciated.
If you have any questions, please ask.
Dan Matthews, MCP Technology Manager Green Hall, University of Kansas (785) 864-9267 dm*******@ku.edu http://www.people.ku.edu/~dmatthew/
"Foo Man Chew" <fo*@man.chew> wrote in message news:<us**************@TK2MSFTNGP09.phx.gbl>... How about using an aggregate function???
SELECT MAX([Entry Date]) FROM [Job List]
Adjust the query accordingly when you fix your poorly-named objects.
"Poorly-named objects"?!? No reason to get personal! Their names are
arbitrary, what difference does it make?!?
Well I tried eveyone's suggestion and no joy. I'm thinking I did not
explain the problem correctly.
I wish to display the last record from a database on a web page. It
would display just the last record and none other. Say Joe Blow
enters a record on Monday, December 1, 2003 and the webpage would say
"Database updated as of Monday, December 1, 2003". No other updates
are made to the database afterwards. Tuesday, December 2, 2003, Joe
checks the web page and it still says "Database updated as of Monday,
December 1, 2003".
That's what I'm trying to do.
> "Poorly-named objects"?!? No reason to get personal!
Why do you think the comment was personal? Object names should not have
spaces in them. It can be problematic for certain database operations, and
requiring those square braces sure makes code harder to read. I can't see
the entire conversation, not sure if it's because it happened too long ago,
or if you purposely started a new thread and snipped the rest of the
conversation. I wish to display the last record from a database on a web page. It would display just the last record and none other. Say Joe Blow enters a record on Monday, December 1, 2003 and the webpage would say "Database updated as of Monday, December 1, 2003". No other updates are made to the database afterwards. Tuesday, December 2, 2003, Joe checks the web page and it still says "Database updated as of Monday, December 1, 2003".
I think the concept you're having difficulty with here is "last." There is
no such thing as the "last row entered" in a database (the database doesn't
track this for you). You can do so using a column with a default value
(assuming SQL Server; I have no idea what database you are using because,
again, the rest of the conversation is not here). And if you want to know
when the last update was, you can use a separate column and keep it updated
with a trigger (or in the stored procedure, if you control data manipulation
that way and don't allow ad hoc updates/inserts).
--
Aaron Bertrand
SQL Server MVP http://www.aspfaq.com/
Dan Matthews wrote: "Foo Man Chew" <fo*@man.chew> wrote in message news:<us**************@TK2MSFTNGP09.phx.gbl>... How about using an aggregate function???
SELECT MAX([Entry Date]) FROM [Job List]
Adjust the query accordingly when you fix your poorly-named objects.
"Poorly-named objects"?!? No reason to get personal! Their names are arbitrary, what difference does it make?!?
He's referring to the fact that your names include spaces. Just because the
database tool allows this (the use of non-standard characters in object
names) does not mean this should be done. Using non-standard names means you
need to adjust your code to handle those names (note the use of brackets []
in the above query. The brackets would not be needed if the names only
included the standard characters). Well I tried eveyone's suggestion and no joy. I'm thinking I did not explain the problem correctly.
I wish to display the last record from a database on a web page. It would display just the last record and none other. Say Joe Blow enters a record on Monday, December 1, 2003 and the webpage would say "Database updated as of Monday, December 1, 2003". No other updates are made to the database afterwards. Tuesday, December 2, 2003, Joe checks the web page and it still says "Database updated as of Monday, December 1, 2003".
That's what I'm trying to do.
I think this was pretty obvious, based on the prior responses, all of which
are valid solutions.
I think you need to tell us what "no joy" means. Error messages? Wrong
results? No results?
Give us a better idea of what your table structure is: datatypes, column
names etc. For example, is there a column identifying who entered the
record?
It's always helpful to show us some sample data in tabular format, along
with the results you wish your query to return - also in tabular format.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
> He's referring to the fact that your names include spaces. Just because the database tool allows this (the use of non-standard characters in object names) does not mean this should be done. Using non-standard names means you need to adjust your code to handle those names (note the use of brackets [] in the above query. The brackets would not be needed if the names only included the standard characters).
Yeah, I know it contains spaces. My excuse is that I didn't write
it... ;-)
I think you need to tell us what "no joy" means. Error messages? Wrong results? No results?
Yes, yes and yes! The select(max) seems to work the best but instead
of giving me the date for the last entry, it gives me all of the dates
for all of the last entries. I.e, if on Monday three entries were
made, the web page gives me all three entries. I just want the last
entry made, period.
Give us a better idea of what your table structure is: datatypes, column names etc. For example, is there a column identifying who entered the record?
MS Access on a Windows 2000 server running IIS with .asp pages. I'm
using a column titled "Entered Date" for the date that job info was
entered by an employer. I'm trying to get the last entry made by the
"Entered Date".
It's always helpful to show us some sample data in tabular format, along with the results you wish your query to return - also in tabular format.
Bob Barrows
Sorry, I thought I was being detailed enough... This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: JP SIngh |
last post by:
Hi All
I am in a very tricky situation wonder if you anyone can help.
We have a table where we store employee information. One of the fields in
the table stores the manager number which is the...
|
by: Carl |
last post by:
I have a main form with navigation buttons on it and a label showing
for example Record 1 of 15 using recordsetclone on it and eveything
works fine. When I move through the records the record...
|
by: Corepaul |
last post by:
As input is typed into a text box, I would like to search a recordset for the
first record that matches what has been typed so far. I would like to update
the text box to display the letters typed...
|
by: huela |
last post by:
Somehow i have to display the recordset in unbound form
When i use the following display the data in recordset to unbound
form.
I have a question:
i can only display the last record data, all...
|
by: Lyn |
last post by:
I have a form set to Single Form mode with which I can cycle through the
records in a table via Next and Previous buttons. To avoid users pressing
the Previous button on the first record and the...
|
by: Twanne |
last post by:
Hi,
I'm building an aplication and I need to display some fields from a recordset. When try this I only get the last one. I know this has to do with th orde of my code but I don't know how to fix...
|
by: sudermatt |
last post by:
This one is completely baffling.
DB Enviornment: MSSQL database
Code Environment: ASP / VBscript (obviously)
Problem: data from an nText field disappears from conn.execute(sql) recordset. ...
|
by: beebelbrox |
last post by:
Hi, I am new VB programming in Access and I am requesting help with the following code.
WIndows OS MSaccess 2003
This code is attached to an unbound form that will display a specific recordset...
|
by: Clint Stowers |
last post by:
Access 2003
I have a Continuous Form. On Open I force it to go to the Last
Record.
When the form opens only the last record is shown at the top. To view
any of the previous records you must...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
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...
|
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...
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
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...
|
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...
|
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)...
| |