Need Help with Date Search

[dutton]

I thought I had an SQL count searching for a "Today" coded correctly, but I keep getting a 0 count back.

Using Access Database, date format at dd/mm/yyyy

I have this:

<%
Function MyDate()
Dim dteCurrent, dteMonth, dteDay, dteYear
dteCurrent = Date()
dteDay = Day(dteCurrent)
dteMonth = Month(dteCurrent)
dteYear = Year(dteCurrent)
MyDate =  dteDay& "/" & dteMonth & "/" & dteYear
End Function

response.write MyDate

%>

I then have this as the SQL:

	'Response.write(SQL1)
	set rs1 = MyConn.execute("Select COUNT(*) as tpt from ppt_table where date_of_ppt="&MyDate&"")
	if rs1.EOF = false then
		TodayApp = rs1("tpt")
	else
		TodayApp = 0
	end if
	set rs1 = Nothing
	
	response.write TodayApp

But I am getting is "0" where I am actually calling TodayApp is displayed on the page - when it actually should be a number over "0".

Any ideas?

[r937]

msaccess requires hash marks (octothorps) to delimit date strings

... WHERE datecolumn = #17/01/2012#

[dutton]

Thanks I have tried:

where date_of_ppt=#"&MyDate&"#")
where date_of_ppt="#&MyDate&#"")
where date_of_ppt="&#MyDate#&"")

and all do not work, sorry - always learning

[r937]

taking another look at your function, i really don't think you need it at all

try this for your query --

SELECT COUNT(*) AS tpt FROM ppt_table WHERE date_of_ppt = Date()

[dutton]

Thanks but no joy on that one either.

[r937]

try running the query i posted right in the SQL query window

[webber123456]

try this:

dim db_date_delimiter: db_date_delimiter="#"
... where date_of_ppt="& db_date_delimiter & MyDate & db_date_delimiter )

[dutton]

Thanks for the help but still no joy. Going to post to a job site as I am at a loss myself.

[r937]

Thanks for the help but still no joy.

did you run my query directly in thw SQL window?