Access ASP Problem

[RFH Reseller: s80wkr]

I have an access db containing 2 date fields these are set to Date/Time Short Date

In my I want to select by date

The QueryString send the date encoded like so

payment_list.asp?EventDate=06%2F09%2F2005


SELECT *
FROM tbl_payments
WHERE pay_bank_date = 'varDate'

varDate = Request.QueryString("EventDate")

When I send this it says there is a Data type mismatch in criteria expression.

But where can it be as the db is set as date

Does it need to unencoded

I am using DMX

Thanks

[Staff: Warren Ashcroft]

Dont you intend to do this?

varDate = Request.QueryString("EventDate") 
SQL = "SELECT * FROM tbl_payments WHERE pay_bank_date = '" & varDate & "'"

[RFH Reseller: Mark Voss]

It's probably something to do with Access - I usually don't bother with Access date fields anymore.

Use a number field and convert the date to a number yyyymmdd instead - that way, Access/ASP can't mess it up.

[Staff: Warren Ashcroft]

It's probably something to do with Access - I usually don't bother with Access date fields anymore.

Use a number field and convert the date to a number yyyymmdd instead - that way, Access/ASP can't mess it up.

IMHO, the best way to handle a date is in the YYYY-MM-DD format, and you can use the date field for that.

[RFH Reseller: Mark Voss]

IMHO, the best way to handle a date is in the YYYY-MM-DD format, and you can use the date field for that.

True - it works the same on US and UK servers and you can use the DateDiff function etc.

The main reason I use a number is that my code is the same whatever the database - no syntax changes for different db types is necessary.

eg. instead of adding hashes for sql queries to Access:

SELECT * FROM tbl_payments WHERE pay_bank_date = #2005-09-07#

I can use:

SELECT * FROM tbl_payments WHERE pay_bank_date = 20050907

which works on MSSQL too.

[RFH Reseller: s80wkr]

I have swapped my rs to take the date YYYY/MM/DD format but where can I change the querystring from the calendar below

<%
Function ASPCalendar
If Request("EventDate") <> "" Then
EventDate = DateValue(Request("EventDate"))
Else
EventDate = date()
End if
CurMonth = Month(EventDate)
CurMonthName = MonthName(CurMonth)
CurYear = Year(EventDate)
FirstDayDate = DateSerial(CurYear, CurMonth, 1)
FirstDay = WeekDay(FirstDayDate, 0)
CurDay = FirstDayDate
Dim tmpHTML
tmpHTML=""
tmpHTML = tmpHTML & "<table summary=""Select Banking Date"" id=""calendar"" cellspacing=""0"">" & Chr(10)
tmpHTML = tmpHTML & "<caption>Select Banking Date</caption>" & Chr(10)
tmpHTML = tmpHTML & "<tr id=""title"">" & Chr(10)
tmpHTML = tmpHTML & "<th colspan=""7"">" & Chr(10)
tmpHTML = tmpHTML & "<a href=""?EventDate=" & Server.URLEncode(DateAdd("m",-1, EventDate)) & """>&lt;</a>" & CurMonthName & "<a href=""?EventDate=" & Server.URLEncode(DateAdd("m",1,EventDate)) & """>&gt;</a>"
tmpHTML = tmpHTML & "<a href=""?EventDate=" & Server.URLEncode(DateAdd("yyyy",-1, EventDate)) & """>&lt;</a>" & CurYear & "<a href=""?EventDate=" & Server.URLEncode(DateAdd("yyyy",1,EventDate)) & """>&gt;</a>"
tmpHTML = tmpHTML & "</th>" & Chr(10) & "</tr>" & Chr(10) & "<tr id=""days"">"
Response.Write(tmpHTML)
For DayLoop = 1 to 7
Response.Write("<th>" & WeekDayName(Dayloop, True, 0) & "</th>" & Chr(10))
Next
Response.Write("</tr>" & Chr(10) & "<tr class=""firstweek"">")
If FirstDay <> 1 Then
Response.Write("<td colspan=""" & (FirstDay -1) & """ class=""blank"">&nbsp;</td>" & Chr(10))
End if
DayCounter = FirstDay
CorrectMonth = True
Do While CorrectMonth = True
isEvent = FALSE
RsDates.filter = 0
Dim iCheck
Dim chkStr
chkStr = (RsDates.Fields.Item("pay_bank_date").Name)
iCheck = CurDay
RsDates.filter = chkStr & "=" & (iCheck)
If not(RsDates.EOF) Then isEvent = TRUE
If CurDay = EventDate Then
Response.Write("<td class=""today"">")
Else
Response.Write("<td class=""day" & DayCounter & """>")
End if
If isEvent = TRUE Then
Response.Write("<a href=""payment_list.asp?EventDate=" & Server.URLEncode(CurDay) & """>" & Day(CurDay)& "</a>")
Response.Write("</td>" & Chr(10))
Else
Response.Write(Day(CurDay) & "</td>" & Chr(10))
End If
DayCounter = DayCounter + 1
If DayCounter > 7 Then
DayCounter = 1
Response.Write("</tr>" & Chr(10))
Response.Write("<tr")
If Month(CurDay+8) <> CurMonth Then
Response.Write(" class=""lastweek""")
End If
Response.Write(">" & Chr(10))
End if
CurDay = DateAdd("d", 1, CurDay)
If Month(CurDay) <> CurMonth then
CorrectMonth = False
End if
Loop
IF DayCounter <> 1 Then
Response.Write("<td colspan=""" & (8-DayCounter) & """ class=""blank"">&nbsp;</td>")
Else
Response.Write("<td colspan=""7"" class=""blank"">&nbsp;</td>")
End if
Response.Write("</tr>" & Chr(10) & "</table>" & Chr(10))
End Function
%>

[RobvK]

I'm not sure what you mean exactly, but here goes:

The QueryString is retrieved on line three:
EventDate = DateValue(Request("EventDate"))

The control links to itself about a quarter down with a load of code:
tmpHTML = tmpHTML & "<a href=""?EventDate=" & Server.URLEncode(DateAdd("m",-1, EventDate)) & """>&lt;</a>" & CurMonthName & "<a href=""?EventDate=" & Server.URLEncode(DateAdd("m",1,EventDate)) & """>&gt;</a>"
tmpHTML = tmpHTML & "<a href=""?EventDate=" & Server.URLEncode(DateAdd("yyyy",-1, EventDate)) & """>&lt;</a>" & CurYear & "<a href=""?EventDate=" & Server.URLEncode(DateAdd("yyyy",1,EventDate)) & """>&gt;</a>"

The control links to another page, look about two thirds down:
Response.Write("<a href=""payment_list.asp?EventDate=" & Server.URLEncode(CurDay) & """>" & Day(CurDay)& "</a>")

Sorry if I horribly misunderstood you.
HTH

[Tanzy]

I have an access db containing 2 date fields these are set to Date/Time Short Date

In my I want to select by date

The QueryString send the date encoded like so

payment_list.asp?EventDate=06%2F09%2F2005


SELECT *
FROM tbl_payments
WHERE pay_bank_date = 'varDate'

varDate = Request.QueryString("EventDate")

When I send this it says there is a Data type mismatch in criteria expression.

But where can it be as the db is set as date

Does it need to unencoded

I am using DMX

Thanks

When looking for dates use it like the following if you are using SQL

SELECT *
FROM tbl_payments
WHERE pay_bank_date = #varDate#

Also never use a * in a query it is very inefficent. Also you need to search for dates in US format. (I know but this is the way it is). Using US format is more efficent since that is how MS implemented it. I also remember it being said in MSDN but don't have the time to find the artical I'm afraid. The # tells it that it is a date. You are best using a parametised query and sending the date in as a parameter.

Ken

[RFH Reseller: s80wkr]

Right I nearly have it sorted what I need to do now is change the way the date is sent from the calendar

EventDate = Date()

How can I make this US style ie mm/dd/yyyy

It is currently dd/mm/yyyy

Thanks

[RFH Reseller: s80wkr]

Sorted it at last stupid Access !!


Thanks for all your help