I have a SQL table called "EXAMPLE" with 2 columns; personId and reportDate. personId consists of a 36 character GUID. reportDate has a date such as 1/1/2005, 2/1/2005,3/1/2005, etc. The date is always the first of the month. Sometimes they are skipping months, ie; 6/1/2006,7/1/2006,11/1/2006,12/1/2006, etc.
Now to my long winded question:
I have an ASP page that displays the selected month and associated personId data based on passing the personId and reportDate in the querystring. If I am on a page looking at personId '123' and reportDate '2/1/2006', for example, I want to place 2 hyperlinks on the page for the reportDate before and after for that personId. So, using my example, the page should show a link to 1/1/2006 and 3/1/2006. The problem is, if 3/1/2006 is not in the DB, it should go to the next available date in the DB. Also, if 2/1/2006 is the last date, or similarly 1/1/2006 is the first, their should not be a link to the dates before and/or after due to this.
Any ideas?
2 1142
The problem is, if 3/1/2006 is not in the DB, it should go to the next available date in the DB.
this one is easy if answered by itself. I assume you are linking with something like:[html]<a href="samePage.asp?date=3/1/2006">next month</a>[/html] right? just open the db with this query: -
query = "SELECT * FROM EXAMPLE WHERE reportDate >= " & request("date") & " ORDER BY reportDate"
-
then just use the first record. The big problem is you can't tell with this method if there is anything before the record you pulled up, so it won't work to try this and solve the second question.
Also, if 2/1/2006 is the last date, or similarly 1/1/2006 is the first, their should not be a link to the dates before and/or after due to this.
Any ideas?
This one is tougher. To solve both together you need to do something like this: -
query = "SELECT * FROM EXAMPLE ORDER BY reportDate"
-
objRS.open query, objConn, adOpenDynamic
-
prevMonth = objRS("reportMonth")
-
if prevMonth = request("date") then
-
'date requested is first in db, so handle accordingly
-
'you might not actually need anything here at all
-
else
-
objRS.moveNext
-
do until dateDiff("d", objRS("reportMonth"), request("date")) <=0
-
prevMonth = objRS("reportDate")
-
objRS.moveNext
-
loop
-
'the exact date of the previous
-
'month is saved as "prevMonth"
-
'I would create the link to the previous month here
-
end if
-
'you are currently on the record requested, the link to the previous
-
'month was create if needed. I would try "objRS.moveNext" to find
-
'the exact date of the next record so that I can write the link to
-
'the exact date needed next, but that's just me
-
Let me know if this helps.
Jared
Jared,
Thank you very much. You solved the problem for me. This was a mental block that I could not figure out.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Mekon |
last post by:
I have never written a line of script in my life but I need some help with
it now.
I have this auto generated code which I want to modify if possible
The script generates a strip of...
|
by: Tommy Lang |
last post by:
I am working on this project and I need some help/pointers/comments to
get me started, I am stuck.
The program will be used to store information in an array while it is
running.
I need to store...
|
by: Jackson Yap |
last post by:
can someone kind enough to help me look at the attached html and js
file? Why is it that the javascript menu could not work at
www.apchosting.net but could work at...
|
by: semooo |
last post by:
hi Im waseem
I need an immediate help as soon as possible,
I have three simple question with coding problems in data structure in
c++
thess are :
1)suppose that you are finanicier and purchase...
|
by: vinod.bhavnani |
last post by:
Hello all,
I need desperate help
Here is the problem:
My problem today is with multidimensional arrays.
Lets say i have an array A this is a 4 dimensional
static array.
|
by: nephish |
last post by:
Hello there,
i am getting to need to make my web stuff more OO.
i have a project at work that we are porting to the internet, and i
started learning php to do so.
the project is now mamoth is...
|
by: James |
last post by:
Is this possible? I want to pass an array into a function that contains
txtBox.Text properties... I was thinking something like this, but I
know it won't work
Dim vendorFields(9) As String
...
|
by: Ouwet5775 |
last post by:
Hey guys
i have been working on a C++ program as a revision for an upcoming final exam.
One of the main point is to know how to create a function that would invert the elements in your array. That...
|
by: ChillyRoll |
last post by:
Hello guys,
I am looking for a parser in PHP that can return all the
attributes of XML entities. I know how to read the XML Entities, but I
have got a problem with reading attributes. So I will...
|
by: falconsx23 |
last post by:
I am making a game called Set, it is a card game: here is a brief description of the rules:
The game of Set is played with 81 cards, each of which has 4 properties (number, pattern,
color and...
|
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: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
| |