| Author |
 Topic Search  Topic Options
|
urko 
Groupie
Joined: 23 September 2004
Location: Slovenia
Status: Offline
Points: 160
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Poblem with recordset
Posted: 10 February 2007 at 7:46am |
Hi guys, I have small problem. On may default page i have a calendar. So when u click od certain date on calendar, lets say today's date, it points u to the detail page and should show only records that were made today. However i get all results all the time. Here's my rs of detail page:
Dim MD
MD = Request.QueryString("Ent_Date")
SELECT * FROM TEME WHERE 'Ent_Date= "&MD&"' AND Author_ID = " & lngLoggedInUserID & ""
|
Im not exactly sure how to write it so that it will know how to display records for a certain date. When i changed the rs to select by certain ID it worked but when i change it to select by date i get all results from db.
Edited by urko - 10 February 2007 at 7:47am
|
|
Urko
|
 |
Scotty32
Moderator Group
Joined: 30 November 2002
Location: Manchester, UK
Status: Offline
Points: 1682
|
Post Options
Thanks(0)
Quote Reply
Posted: 10 February 2007 at 11:07am |
well looking at that, it seems the part in red is a tad bit off.
Dim MD
MD = Request.QueryString("Ent_Date")
SELECT * FROM TEME WHERE 'Ent_Date= "&MD&"' AND Author_ID = " & lngLoggedInUserID & "" |
if your going to do text it should be:
Dim MD
MD = Request.QueryString("Ent_Date")
SELECT * FROM TEME WHERE Ent_Date= '"&MD&"' AND Author_ID = " & lngLoggedInUserID & "" |
and i believe for a date it would be:
Dim MD
MD = Request.QueryString("Ent_Date")
SELECT * FROM TEME WHERE Ent_Date= #"&MD&"# AND Author_ID = " & lngLoggedInUserID & "" |
Edited by Scotty32 - 10 February 2007 at 11:07am
|
|
|
|
urko
Groupie
Joined: 23 September 2004
Location: Slovenia
Status: Offline
Points: 160
|
Post Options
Thanks(0)
Quote Reply
Posted: 10 February 2007 at 11:34am |
|
Fo some reason,
if i add # i get following error:
Syntax error in date in query expression Ent_Date = #9.2.2007#
if i add quotes i get:
Data type mismatch in criteria expression.
|
|
Urko
|
|
Scotty32
Moderator Group
Joined: 30 November 2002
Location: Manchester, UK
Status: Offline
Points: 1682
|
Post Options
Thanks(0)
Quote Reply
Posted: 10 February 2007 at 3:22pm |
you need to make the string a valid date, ie: 9/2/2007 you might want to add this:
MD = CDate(Request.QueryString("Ent_Date"))
|
|
|
|
|
urko
Groupie
Joined: 23 September 2004
Location: Slovenia
Status: Offline
Points: 160
|
Post Options
Thanks(0)
Quote Reply
Posted: 11 February 2007 at 10:12am |
|
Hi
Thanks for answer.
I did made it and page loads now, however i always get an empty result.
Example.
In my db i have datas from today, so when i click on todays date on calendar, i get empy result?
In my db, dates are written DMY 11.2.2007.
do i need to convert the dates on my page as well to get the needed results? and if so, how should i do this.
Not sure, how this works as i never did this kind of thing...I did found some articles agbout that, but no luck thus far.
Edited by urko - 11 February 2007 at 10:12am
|
|
Urko
|
|
MrMellie
Senior Member
Joined: 12 December 2006
Location: United Kingdom
Status: Offline
Points: 251
|
Post Options
Thanks(0)
Quote Reply
Posted: 11 February 2007 at 10:21am |
|
A # is only used for delimiting MS Access dates. For SQL Server, use apostrophies ('). You'll also want to put it in international format (20070209 for 9th Feb 2007) or risk having it default to US format when stored in the database.
|
|
urko
Groupie
Joined: 23 September 2004
Location: Slovenia
Status: Offline
Points: 160
|
Post Options
Thanks(0)
Quote Reply
Posted: 11 February 2007 at 10:25am |
|
ok...i have access db...if i understand you correctly, i need to change the date format to 20070209 in my db as this could be the problem why it's not showing the results ?
|
|
Urko
|
|
MrMellie
Senior Member
Joined: 12 December 2006
Location: United Kingdom
Status: Offline
Points: 251
|
Post Options
Thanks(0)
Quote Reply
Posted: 11 February 2007 at 11:05am |
No sorry, I wasn't clear in what I posted. It's the date format in the SQL string that needs setting that way when using MS SQL.
However, what data type is your date field in Access? If it is set to Date type, then using the format given by Scotty (9/2/2007) in your query should work. That is the format that is used by Access when using the Date data type.
[edit]I just checked an Access db of mine and noticed in a query, the date string is actually in US date format (mm/dd/yyyy) even though in the actual table the date is dd/mm/yyyy. I really hate that assumption that everyone in the world uses US format. 
Edited by MrMellie - 11 February 2007 at 11:13am
|
|
