469,333 Members | 4,241 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,333 developers. It's quick & easy.

Unable to retrieve the date value from database

46
Hello Experts.

I am unable to retrieve records base on a date. Could you please help me.

Here is my code for user to insert a date :

Expand|Select|Wrap|Line Numbers
  1.  
  2. <form name="hello" method="post" action="querytable.asp">
  3. Please enter  date (mm/dd/yyyy)<input type="text" name="adate">
  4. <input type="submit" name="submit" value="submit">
  5. <form>
  6.  
  7.  
Here is my code for quering access database for the date

Expand|Select|Wrap|Line Numbers
  1.  
  2. <% @language="VBScript" %>
  3. <% option explicit %>
  4.  
  5. <%
  6. dim strDate
  7.  
  8. strDate = request.form("adate")
  9.  
  10.  
  11. dim adorst, adocon, strSQL, rst, sql
  12.  
  13. set adocon = server.createobject("ADODB.connection")
  14. adocon.open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " & server.mapPath("sp.mdb"))
  15.  
  16. set adorst = server.createobject("ADODB.recordset")
  17.  
  18. strSQL = "SELECT * from approval where datecreated = # " & strDate & " #" 
  19.  
  20.  
  21. adorst.open strSQL, adocon
  22.  
  23. %>
  24.  
  25.  
I get an error message Microsoft JET Database Engine (0x80040E07)
Syntax error in date in query expression 'datecreated = # #'.

When I use a constant date e.g
strSQL = "SELECT * from approval where datecreated = #01/01/2001#"
Then, the code works well. But I want to search the database on a date condition, i.e from the date the user has entered
Feb 25 '08 #1
5 1662
DrBunchman
979 Expert 512MB
Hi Giandeo,

It looks to me like your variable strDate is not getting a value assigned to it. Can you check whether this is the case by putting a

response.write("strDate: " & strDate)

in your code after you've assigned it (Use a constant date like you mentioned you have done before to stop it from erroring). If a date isn't displayed then something is wrong with the way the value is being assigned.

Hope this helps,

Dr b
Feb 25 '08 #2
CroCrew
564 Expert 512MB
Hello giandeo,

If the field “datecreated” in your database is a Date/Time field then…

If you’re looking for the records that fall ON the date passed in:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * from approval where datecreated = #" & strDate & "#"
If you’re looking for the records that fall BEFORE the date passed in:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * from approval where datecreated < #" & strDate & "#"
If you’re looking for the records that fall AFTER the date passed in:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * from approval where datecreated > #" & strDate & "#"
If you’re looking for the records that fall ON AND BEFORE the date passed in:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * from approval where datecreated =< #" & strDate & "#"
If you’re looking for the records that fall ON AND AFTER the date passed in:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * from approval where datecreated => #" & strDate & "#"
But like DrBunchman stated out if you’re not passing in a valid (date) value you’re going to have problems.

Hope that helps~
Feb 25 '08 #3
giandeo
46
Hello giandeo,

If the field “datecreated” in your database is a Date/Time field then…

If you’re looking for the records that fall ON the date passed in:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * from approval where datecreated = #" & strDate & "#"
If you’re looking for the records that fall BEFORE the date passed in:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * from approval where datecreated < #" & strDate & "#"
If you’re looking for the records that fall AFTER the date passed in:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * from approval where datecreated > #" & strDate & "#"
If you’re looking for the records that fall ON AND BEFORE the date passed in:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * from approval where datecreated =< #" & strDate & "#"
If you’re looking for the records that fall ON AND AFTER the date passed in:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * from approval where datecreated => #" & strDate & "#"
But like DrBunchman stated out if you’re not passing in a valid (date) value you’re going to have problems.

Hope that helps~
Hello Sir.

Your solutions are fantastic. I have been able to solve the problem. Thank you so much.

Actually, when I retrieve the data from the table I could see the date in the format mm/dd/yyyy

Sir, Is it possible to store date in access database in the British format:
e.g dd/mm/yyyyy

I am updating the database date/time field with the following code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. dim strdatecreated
  3.  
  4. strdatecreated = date()
  5.  
  6. adorst("datecreated") = strdatecreated
  7.  
  8.  
Feb 27 '08 #4
CroCrew
564 Expert 512MB
I am not an expert in Access but you can change the “Format” of your Date/Time filed to “Medium Date”. Medium Date falls more inline with Euro dates.
Feb 27 '08 #5
markrawlingson
346 Expert 100+
This should do the trick.

Expand|Select|Wrap|Line Numbers
  1. <%session.lcid=2057%>
  2.  
Here's a references online.

http://www.codetoad.com/asp/format_date_time.asp

Scroll down on the page, it will show you all the LCIDs (Local IDs) for all the international locales - as the website says, be aware that these will also change your currency formatting.

[edit]
I should also mention that this won't store the date in dd/mm/yyyy format in your database but will rather use ASP to translate any date into dd/mm/yyyy format - So you store it in your db as mm/dd/yyyy and once it's spat onto the page it should appear dd/mm/yyyy instead because of the locale id.

Sincerely,
Mark
Feb 27 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

16 posts views Thread by Daniel Tonks | last post: by
2 posts views Thread by Uthuras | last post: by
3 posts views Thread by den 2005 | last post: by
2 posts views Thread by michele | last post: by
4 posts views Thread by Simon Gare | last post: by
2 posts views Thread by Josh T | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by haryvincent176 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.