ASP 'No value given...' error

[Wirrrrrl]

Hello,
I'm hoping that someone can help me solve the mystery. This is the piece of code that's causing errors:

strSQL1 ="SELECT * FROM emp_directory WHERE Month(emp_dob) = "
		strSQL1= strSQL1 & month(todaysDate)

Without 'WHERE' clause everything works fine, as soon as I add it, I get an error- No value given for one or more required parameters. I tested the SQL expression using response.write strsql, and then testing it in Access, everything works perfect. But on the page, it produces error.

Thanks to everyone in advance!

Below is the entire code, just in case:

<% set conDirectory= Server.CreateObject("ADODB.Connection")

	strConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("emp_directory.mdb")
	conDirectory.Open strConnect,"admin", ""
	
dim todaysDate
 todaysDate=now()

	'strSQL1 ="SELECT * FROM emp_directory" 
		strSQL1 ="SELECT * FROM emp_directory WHERE Month(emp_dob) = "
		strSQL1= strSQL1 & month(todaysDate)
		
		'response.write strsql
'response.end

	
	
Set rsNSPE= Server.CreateObject("ADODB.Recordset")
	rsNSPE.Open strSQL1, conDirectory, adOpenStatic,, adCmdText
	
	If Not rsNSPE.EOF Then
	%>
	
<div id="topnav"></div>
<div id="navcontainer">
<ul>
<li><a href="../index.asp">main</a></li>
<li ><a href="../benefits/benefits.asp"><span>benefits</span></a></li>
<li><a href="../payroll/payroll.asp"><span>payroll</span></a></li>
<li><a href="../forms/forms.asp"><span>forms</span></a></li>
<li><a href="../calendar.asp"><span>calendar</span></a></li>
</ul>
</div>
<div id="time">
Today is <% 
Response.write FormatDateTime(todaysDate,1)
%>
&nbsp;&nbsp;
<%
Response.write FormatDateTime(todaysDate,4)
%>
</div>
<div id="sidebar">
<p><img src="../images/QuickLink.gif" border="0" alt="Quick Links"></p>
<ul id="navlist">
<!--<li><a href="../benefits/benefits.asp"><span>Benefits</span></a></li><li><a href="#"><span>Education</span></a></li>-->
<li><a href="../directory_web.asp"><span>Employee Directory</span></a></li><li><a href="../jf.asp"><span>Jefferson Houston</span></a></li><li><a href="#"><span>Orientation</span></a></li><li><a href="benefits/parking.asp"><span>Parking/Transportation</span></a></li><li><a href="../ccentral.asp"><span>Career Central</span></a></li></ul>
<ul id="navlist2">
<li><a href="#"><span>News&amp;Views Archive</span></a></li><li><a href="#"><span>Photogallery</span></a></li>
</ul>
<p></p>
<p><img src="../images/Bday.gif">
<ul style="display:block ">
<% 
	Do Until rsNSPE.EOF
	%>
<li><a href="../record.asp?emp_key=<%= rsNSPE.Fields("emp_key")%>"><%= rsNSPE.Fields("emp_first_name")%>&nbsp;<%= rsNSPE.Fields("emp_last_name")%>

 </a></li>
<%
	rsNSPE.MoveNext
	Loop
	%>
</ul>


</div>

<%
		rsNSPE.close
	set rsNSPE=nothing
		conDirectory.Close
	Set conDirectory=nothing
End If
%>

[bbolte]

in access, dates need # (or is it the %?)

trSQL1 ="SELECT * FROM emp_directory WHERE Month(emp_dob) = #"
strSQL1= strSQL1 & month(todaysDate) & "#"

[Wirrrrrl]

in access, dates need # (or is it the %?)

trSQL1 ="SELECT * FROM emp_directory WHERE Month(emp_dob) = #"
strSQL1= strSQL1 & month(todaysDate) & "#"

Thank you, I tried this already, and just tried now again, this is the error I'm getting: Syntax error in date in query expression 'Month(emp_dob) = #11#'.

[Wirrrrrl]

I did run this expression in Access, and it does work: select * from emp_directory where month(emp_dob)=11

[bbolte]

what is the data type of emp_dob?

[bbolte]

you might need to do some type conversions then possibly. this isn't right (can't remember exact syntax), but it would look something like:
...WHERE cint(Month(emp_dob)) = cint(month(todaysDate))...

[Wirrrrrl]

what is the data type of emp_dob?

Date/Time

[Wirrrrrl]

OK, the mystery is solved. Thanks, bbolte, for your help!
When I changed the path to DB to Server.MapPath("../emp_directory.mdb"), everything worked.
It still strange why exactly the WHERE statement was needing this. Because with just the basic Select statement the code ran just fine, without needing to change the path to the DB.

[bbolte]

that's weird...