Hi Joe,



sorry to be a nuisance, I wonder if you could help with this problem:



I am trying to find records between two dates, this should be simple enough

however I cannot get rid of a data type mismatch.



date1=day1 & "/" & month1 & "/" & year1

response.write date1

date2=day2 & "/" & month2 & "/" & year2

if isdate(date1) then response.write "yes it is a date"%>



I use this to set the dates up from drop down menus on a form.

isdate confirms that the variable is a date.



set objconn=server.createobject("ADODB.Connection")

 objconn.open "DSN=Planet Blue"

 set orderscmd =server.createobject("ADODB.Command")

 set orderscmd.activeconnection=objconn

 Set oRSbrowse=Server.CreateObject ("ADODB.Recordset")

 orderscmd.commandtext="{CALL DateQuery(" & date1 & "," & date2 & ")}"

 response.write orderscmd.commandtext



   set oRSbrowse=orderscmd.execute



The data base field in access2000 is set as short date.

The procedure in the database works for entered dates manually. Why should

there be a type mismatch? Are date1 and date2 not date variables...if not

how can I make them date variables. I have tried using # but this then is

accepted but returns with no records all the time!!



Any ideas?





Steve

Try wrapping a cDate() around your concats.  Ie



date1= cDate(day1 & "/" & month1 & "/" & year1)



Generally when I filter on dates I will serialize my dates first, that

way you can treat them as numbers.  In my opinion this is easier to work

with.



You would have something like:



DateQuery:

SELECT field1, field2, Year(MyDate) & Month(MyDate) & Day(MyDate) as

SerialDate FROM MyTable



Then your command text could be like:



"SELECT * FROM DateQuery WHERE SerialDate > " & MyDate1 & " AND

SerialDate < " & MyDate2



Cheers,



Padgett



-----Original Message-----

From: shm@b... [mailto:shm@b...] 

Sent: Monday, 13 August 2001 6:08 PM

To: Access ASP

Subject: [access_asp] Problem with date returns with no records



Hi Joe,



sorry to be a nuisance, I wonder if you could help with this problem:



I am trying to find records between two dates, this should be simple

enough

however I cannot get rid of a data type mismatch.



date1=day1 & "/" & month1 & "/" & year1

response.write date1

date2=day2 & "/" & month2 & "/" & year2

if isdate(date1) then response.write "yes it is a date"%>



I use this to set the dates up from drop down menus on a form.

isdate confirms that the variable is a date.



set objconn=server.createobject("ADODB.Connection")

 objconn.open "DSN=Planet Blue"

 set orderscmd =server.createobject("ADODB.Command")

 set orderscmd.activeconnection=objconn

 Set oRSbrowse=Server.CreateObject ("ADODB.Recordset")

 orderscmd.commandtext="{CALL DateQuery(" & date1 & "," & date2 & ")}"

 response.write orderscmd.commandtext



   set oRSbrowse=orderscmd.execute



The data base field in access2000 is set as short date.

The procedure in the database works for entered dates manually. Why

should

there be a type mismatch? Are date1 and date2 not date variables...if

not

how can I make them date variables. I have tried using # but this then

is

accepted but returns with no records all the time!!



Any ideas?





Steve

Thanks for the help....I sussed the problem as:



The dates are stored in UK format but need to be searched in US format!

What a pain. By rearranging the date format in the query to US format, the

engine then searches the database dates (which are in UK format) and pulls

out the correct results. Weird!!





Steve