Sorting by time
Hi,
I am about to make a forum in ASP, using Access (don't laugh) as the main-database (that database will only be till I get something more stabile going on my servers).
What I need is a way to sort a list of threads/records by the time in a field.
Normally I would do something like
SELECT * FROM threads SORT BY lastpost DESC
but the problem is that this doesn't seem to work very good!
I don't know how I should save the information in the database; should I use the field as a date/time field, or only as normal text? When I update to such a field, what do I put around the date, so that the database understands it? Is there something like I should use # before the date, or how is it?
And finally, how do I sort it?
I would guess that I can just for updating the lastpost do like this
UPDATE threads SET lastpost = time WHERE blahh
But I don't know how this will work when I SORT it.
I hope you can help me here.
~Casper Bang
PS: If the code works with msSQL it would be perfect, since I will switch to that database soon
Peter J. Boettcher posted this at 12:57 — 25th June 2001.
They have: 812 posts
Joined: Feb 2000
If the data you're going to be saving in that field is always going to be a date/time, then you should definately use the date/time data type. I'm not sure how Access handles insert with date/time fields, but I'm pretty sure if you pass it the date in the format it's looking for (for example MM/DD/YYYY TIME) it should accept it. If it's a text field then sorting by date won't work, since it will sort alphabetically.
You can always use the CDate function to verify the date is in valid format:
TheDate = CDate("July 6,2001 5:43:47 PM")
You can then insert TheDate string into the database.
PJ | Are we there yet?
pjboettcher.com
hotcut posted this at 15:22 — 25th June 2001.
They have: 133 posts
Joined: Sep 2000
hmm... can't figure out why I had to ask this... I think that I have tried it several times, but now it just works!
Saving it as a date/time format, and then just sorting it after that... it works!
Thanks man...
One more thing though, will this format of field-type etc. work with an SQL-database? Have never tried that type of database before... will do it next week I guess.... will double the cost of what I pay for the servers now, but as far as I know, is should be worth it (with more than 1600 members signed up, an access database can't do)
~casper Bang
secretsofwar.net
Peter J. Boettcher posted this at 15:30 — 25th June 2001.
They have: 812 posts
Joined: Feb 2000
While there are some inconsistencies between Access and SQL regarding data-types, for the most part importing from one to the other works pretty well, just be sure to check all your primary keys and default values after to make sure the structure is ok.
Even though it's more expensive you're going to love all the benefits of using SQL Server over Access. It's going to be faster and more reliable, you'll have access to stored procedures, and with more than 1600 members, it sounds like you're going to need it.
PJ | Are we there yet?
pjboettcher.com
hotcut posted this at 15:36 — 25th June 2001.
They have: 133 posts
Joined: Sep 2000
heh, I am glad that you know a lot to this
Might need to ask you some more if you don't mind 
The main database for the game I am running (secretsofwar.net) is around 40 megs... it is kinda much to upload and download when the database crashes; access likes to crash the database so that it is all down... I believe it happens when the servers make an unekspeckted reboot; haven't happened since I changed servers.
I just hope that I will figure out how to make it all work... I might need to buy a book about SQL so that I can learn how to make new tables etc. without having to ask you each and every time (I like that you are helping me, but I can imagine how tired you must get of ansvering my "silly" quiestion).
Have fun
~Casper
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.