Unable to retrieve the date value from database

[giandeo]

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 :

<form name="hello" method="post" action="querytable.asp">
Please enter  date (mm/dd/yyyy)<input type="text" name="adate">
<input type="submit" name="submit" value="submit">
<form>

Here is my code for quering access database for the date

<% @language="VBScript" %>
<% option explicit %>

<%
dim strDate

strDate = request.form("adate")


dim adorst, adocon, strSQL, rst, sql

set adocon = server.createobject("ADODB.connection")
adocon.open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " & server.mapPath("sp.mdb"))

set adorst = server.createobject("ADODB.recordset")

strSQL = "SELECT * from approval where datecreated = # " & strDate & " #" 


adorst.open strSQL, adocon

%>

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

[DrBunchman]

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

[CroCrew]

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:
[code=asp]strSQL = "SELECT * from approval where datecreated = #" & strDate & "#"[/code]

If you’re looking for the records that fall BEFORE the date passed in:
[code=asp]strSQL = "SELECT * from approval where datecreated < #" & strDate & "#"[/code]

If you’re looking for the records that fall AFTER the date passed in:
[code=asp]strSQL = "SELECT * from approval where datecreated > #" & strDate & "#"[/code]

If you’re looking for the records that fall ON AND BEFORE the date passed in:
[code=asp]strSQL = "SELECT * from approval where datecreated =< #" & strDate & "#"[/code]

If you’re looking for the records that fall ON AND AFTER the date passed in:
[code=asp]strSQL = "SELECT * from approval where datecreated => #" & strDate & "#"[/code]

But like DrBunchman stated out if you’re not passing in a valid (date) value you’re going to have problems.

Hope that helps~

[giandeo]

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:
[code=asp]strSQL = "SELECT * from approval where datecreated = #" & strDate & "#"[/code]

If you’re looking for the records that fall BEFORE the date passed in:
[code=asp]strSQL = "SELECT * from approval where datecreated < #" & strDate & "#"[/code]

If you’re looking for the records that fall AFTER the date passed in:
[code=asp]strSQL = "SELECT * from approval where datecreated > #" & strDate & "#"[/code]

If you’re looking for the records that fall ON AND BEFORE the date passed in:
[code=asp]strSQL = "SELECT * from approval where datecreated =< #" & strDate & "#"[/code]

If you’re looking for the records that fall ON AND AFTER the date passed in:
[code=asp]strSQL = "SELECT * from approval where datecreated => #" & strDate & "#"[/code]

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:

dim strdatecreated

strdatecreated = date()

adorst("datecreated") = strdatecreated

[CroCrew]

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.

[markrawlingson]

This should do the trick.

[code=asp]
<%session.lcid= 2057%>
[/code]

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