This is a multi-part message in MIME format.

------=_NextPart_000_0068_01C1CF90.5A16F9E0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I'm trying on my index page to show all employees who have a birthday 
based
on the server month and day in a table with a repeat region...I have a 
small
Access database with the employee- first name, last name, birthday month
(i.e. 3), birthday day (i.e. 19), an auto ID field, and a department
field...I can't seem to find a way to insert the month and day from the
server into a SQL statement to retrieve the employee's first name, last
name, and department...

When I check e_Month and e_Date subtype with TypeName they show as
Integers...In the database d_BDateMonth and d_BDateDay are numbers...If 
I manually put numbers in place of e_MONTH and e_Day everything is fine 
and the rsEmpBday returns the values for the numbers...I don't think the 
values are getting into the SQL statement...Is there any way to call 
them and insert them into the statement...It doesn't seem as if "Dim" is 
working in this case...Since this is my index page I want the values to 
load and go into the SQL when the page is accessed by the user...I don't 
have any way of
passing the values from another page...I've tried removing the single 
quotes around e_Month and e_Day but then I get:

Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters.

Where am I going wrong???...

<%
Dim e_Month, e_Day
e_Month =3D month(date)
e_Day =3D day(date)
set rsEmpBday =3D Server.CreateObject("ADODB.Recordset")
rsEmpBday.ActiveConnection =3D MM_connBday_STRING
rsEmpBday.Source =3D "SELECT d_BDateDay, d_BDateMonth, d_DeptNbr, 
d_FName,
d_ID, d_LName  FROM bdaysmstr  WHERE d_BDateMonth =3D 'e_Month' AND 
d_BDateDay
=3D 'e_Day' ORDER BY d_ID ASC"
rsEmpBday.CursorType =3D 2
rsEmpBday.CursorLocation =3D 2
rsEmpBday.LockType =3D 3
rsEmpBday.Open()
rsEmpBday_numRows =3D 0
%>

Using this statment gives me this error:
Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.

Thanks for any help.....
Dave Drake........

When you get the 80040e07 error you are using incorrect delimiters for
number type fields:
http://www.adopenstatic.com/faq/80040e07.asp
shows the correct delimiters to use.

When you take the ' marks out, Access thinks that are supposed to be
variables/queries *in Access* that are called e_Date etc, hence the error
you are getting about missing parameters.

This is because you are not embedding the VBScript variable into your
string.
You can see this if you do:
<%
strSQL = _
    "SELECT d_BDateDay, d_BDateMonth, d_DeptNbr, d_FName, " & _
    "d_ID, d_LName  FROM bdaysmstr  " & _
    "WHERE d_BDateMonth = 'e_Month' " & _
    "AND d_BDateDay= 'e_Day' ORDER BY d_ID ASC"
Response.Write(strSQL)
Response.End
%>

You need to do something like

<%
intMonth = Month(Date())
intDay = Day(Date())

strSQL = _
    "SELECT field1, field2, field3 " & _
    "FROM table1 " & _
    "WHERE d_Month = " & intMonth & " " & _
    "AND d_Day = " & intDay

Response.Write(strSQL)
Response.End
%>

Thirdly, Access has built in VBA functions that you can use, so you don't
need to get the values in ASP first:

<%
strSQL = _
    "SELECT field1, field2, field3 " & _
    "FROM table1 " & _
    "WHERE d_Month = Month(Date()) " & _
    "AND d_Day = Day(Date())"

Response.Write(strSQL)
Response.End
%>

Lastly, why don't you just store the birthday as a date field? That would
make everything so much easier. It's easier because you're sticking to
modelling (in your database) the actual attribute of the Employer entity
(their birthdate):

<%
strSQL = _
    "SELECT EmployerName " & _
    "FROM Employees " & _
    "WHERE Birthday = Date()"

Response.Write(strSQL)
Response.End
%>

Now, isn't that last statement simpler :-)

HTH

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: " Dave Drake" <davdrake@c...>
Subject: [asp_databases] Problem with using the server date in an Access
query.........


I'm trying on my index page to show all employees who have a birthday based
on the server month and day in a table with a repeat region...I have a small
Access database with the employee- first name, last name, birthday month
(i.e. 3), birthday day (i.e. 19), an auto ID field, and a department
field...I can't seem to find a way to insert the month and day from the
server into a SQL statement to retrieve the employee's first name, last
name, and department...

When I check e_Month and e_Date subtype with TypeName they show as
Integers...In the database d_BDateMonth and d_BDateDay are numbers...If I
manually put numbers in place of e_MONTH and e_Day everything is fine and
the rsEmpBday returns the values for the numbers...I don't think the values
are getting into the SQL statement...Is there any way to call them and
insert them into the statement...It doesn't seem as if "Dim" is working in
this case...Since this is my index page I want the values to load and go
into the SQL when the page is accessed by the user...I don't have any way of
passing the values from another page...I've tried removing the single quotes
around e_Month and e_Day but then I get:

Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters.

Where am I going wrong???...

<%
Dim e_Month, e_Day
e_Month = month(date)
e_Day = day(date)
set rsEmpBday = Server.CreateObject("ADODB.Recordset")
rsEmpBday.ActiveConnection = MM_connBday_STRING
rsEmpBday.Source = "SELECT d_BDateDay, d_BDateMonth, d_DeptNbr, d_FName,
d_ID, d_LName  FROM bdaysmstr  WHERE d_BDateMonth = 'e_Month' AND d_BDateDay
= 'e_Day' ORDER BY d_ID ASC"
rsEmpBday.CursorType = 2
rsEmpBday.CursorLocation = 2
rsEmpBday.LockType = 3
rsEmpBday.Open()
rsEmpBday_numRows = 0
%>

Using this statment gives me this error:
Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This is a multi-part message in MIME format.

------=_NextPart_000_04A2_01C1D047.CB645450
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

MessageThanks Ron, that solved the problem...I'm better at JSscript, 
I've still got a lot to learn about using VBscript on the 
server-side...Thanks again for getting me unstuck........

Dave...............

  ----- Original Message -----
  From: Drew, Ron
  To: ASP Databases
  Sent: Wednesday, March 20, 2002 5:41 PM
  Subject: [asp_databases] RE: Problem with using the server date in an 
Access query.........


  rsEmpBday.Source =3D "SELECT d_BDateDay, d_BDateMonth, d_DeptNbr, 
d_FName,
  d_ID, d_LName  FROM bdaysmstr  WHERE d_BDateMonth =3D " & e_Month & " 
AND d_BDateDay
  =3D " & e_Day & " ORDER BY d_ID ASC"

    -----Original Message-----
    From: Dave Drake [mailto:davdrake@c...]
    Sent: Tuesday, March 19, 2002 9:52 PM
    To: ASP Databases
    Subject: [asp_databases] Problem with using the server date in an 
Access query.........


    I'm trying on my index page to show all employees who have a 
birthday based
    on the server month and day in a table with a repeat region...I have 
a small
    Access database with the employee- first name, last name, birthday 
month
    (i.e. 3), birthday day (i.e. 19), an auto ID field, and a department
    field...I can't seem to find a way to insert the month and day from 
the
    server into a SQL statement to retrieve the employee's first name, 
last
    name, and department...

    When I check e_Month and e_Date subtype with TypeName they show as
    Integers...In the database d_BDateMonth and d_BDateDay are 
numbers...If I manually put numbers in place of e_MONTH and e_Day 
everything is fine and the rsEmpBday returns the values for the 
numbers...I don't think the values are getting into the SQL 
statement...Is there any way to call them and insert them into the 
statement...It doesn't seem as if "Dim" is working in this case...Since 
this is my index page I want the values to load and go into the SQL when 
the page is accessed by the user...I don't have any way of
    passing the values from another page...I've tried removing the 
single quotes around e_Month and e_Day but then I get:

    Microsoft JET Database Engine error '80040e10'
    No value given for one or more required parameters.

    Where am I going wrong???...

    <%
    Dim e_Month, e_Day
    e_Month =3D month(date)
    e_Day =3D day(date)
    set rsEmpBday =3D Server.CreateObject("ADODB.Recordset")
    rsEmpBday.ActiveConnection =3D MM_connBday_STRING
    rsEmpBday.Source =3D "SELECT d_BDateDay, d_BDateMonth, d_DeptNbr, 
d_FName,
    d_ID, d_LName  FROM bdaysmstr  WHERE d_BDateMonth =3D 'e_Month' AND 
d_BDateDay
    =3D 'e_Day' ORDER BY d_ID ASC"
    rsEmpBday.CursorType =3D 2
    rsEmpBday.CursorLocation =3D 2
    rsEmpBday.LockType =3D 3
    rsEmpBday.Open()
    rsEmpBday_numRows =3D 0
    %>

    Using this statment gives me this error:
    Microsoft JET Database Engine error '80040e07'
    Data type mismatch in criteria expression.

    Thanks for any help.....
    Dave Drake........

This is a multi-part message in MIME format.

------_=_NextPart_001_01C1D060.62FE9998
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

rsEmpBday.Source =3D "SELECT d_BDateDay, d_BDateMonth, d_DeptNbr, 
d_FName,
d_ID, d_LName  FROM bdaysmstr  WHERE d_BDateMonth =3D " & e_Month & " 
AND
d_BDateDay
=3D " & e_Day & " ORDER BY d_ID ASC"


-----Original Message-----
From: Dave Drake [mailto:davdrake@c...]
Sent: Tuesday, March 19, 2002 9:52 PM
To: ASP Databases
Subject: [asp_databases] Problem with using the server date in an Access
query.........


I'm trying on my index page to show all employees who have a birthday
based
on the server month and day in a table with a repeat region...I have a
small
Access database with the employee- first name, last name, birthday month
(i.e. 3), birthday day (i.e. 19), an auto ID field, and a department
field...I can't seem to find a way to insert the month and day from the
server into a SQL statement to retrieve the employee's first name, last
name, and department...


When I check e_Month and e_Date subtype with TypeName they show as
Integers...In the database d_BDateMonth and d_BDateDay are numbers...If
I manually put numbers in place of e_MONTH and e_Day everything is fine
and the rsEmpBday returns the values for the numbers...I don't think the
values are getting into the SQL statement...Is there any way to call
them and insert them into the statement...It doesn't seem as if "Dim" is
working in this case...Since this is my index page I want the values to
load and go into the SQL when the page is accessed by the user...I don't
have any way of
passing the values from another page...I've tried removing the single
quotes around e_Month and e_Day but then I get:

Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters.

Where am I going wrong???...

<%
Dim e_Month, e_Day
e_Month =3D month(date)
e_Day =3D day(date)
set rsEmpBday =3D Server.CreateObject("ADODB.Recordset")
rsEmpBday.ActiveConnection =3D MM_connBday_STRING
rsEmpBday.Source =3D "SELECT d_BDateDay, d_BDateMonth, d_DeptNbr, 
d_FName,
d_ID, d_LName  FROM bdaysmstr  WHERE d_BDateMonth =3D 'e_Month' AND
d_BDateDay
=3D 'e_Day' ORDER BY d_ID ASC"
rsEmpBday.CursorType =3D 2
rsEmpBday.CursorLocation =3D 2
rsEmpBday.LockType =3D 3
rsEmpBday.Open()
rsEmpBday_numRows =3D 0
%>

Using this statment gives me this error:
Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.

Thanks for any help.....
Dave Drake........

Ken thanks so much for all your help...Both of the solutions worked great,
and I'm going to stay with the Access VBA functions that seems the
cleanest...I can't use the birthday date alone because I don't know the
year...I guess I could put a bogus year on all months and dates and try
it...Anyway thanks again, you got me restarted because I'd been stuck on
this for over a day make no progress...I'm better at JavaScript, but if I'm
going to start doing more server-side I will have to get much better at VB
Script...Thanks for taking the time to square me away..........

Dave............

»»»»»»»»»»»»»»»»»»»»»»»»»»»»»~««««««««««««««««««««««««««««««

----- Original Message -----
From: "Ken Schaefer" <ken@a...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, March 20, 2002 2:29 AM
Subject: [asp_databases] Re: Problem with using the server date in an Access
query.........


> When you get the 80040e07 error you are using incorrect delimiters for
> number type fields:
> http://www.adopenstatic.com/faq/80040e07.asp
> shows the correct delimiters to use.
>
> When you take the ' marks out, Access thinks that are supposed to be
> variables/queries *in Access* that are called e_Date etc, hence the error
> you are getting about missing parameters.
>
> This is because you are not embedding the VBScript variable into your
> string.
> You can see this if you do:
> <%
> strSQL = _
>     "SELECT d_BDateDay, d_BDateMonth, d_DeptNbr, d_FName, " & _
>     "d_ID, d_LName  FROM bdaysmstr  " & _
>     "WHERE d_BDateMonth = 'e_Month' " & _
>     "AND d_BDateDay= 'e_Day' ORDER BY d_ID ASC"
> Response.Write(strSQL)
> Response.End
> %>
>
> You need to do something like
>
> <%
> intMonth = Month(Date())
> intDay = Day(Date())
>
> strSQL = _
>     "SELECT field1, field2, field3 " & _
>     "FROM table1 " & _
>     "WHERE d_Month = " & intMonth & " " & _
>     "AND d_Day = " & intDay
>
> Response.Write(strSQL)
> Response.End
> %>
>
> Thirdly, Access has built in VBA functions that you can use, so you don't
> need to get the values in ASP first:
>
> <%
> strSQL = _
>     "SELECT field1, field2, field3 " & _
>     "FROM table1 " & _
>     "WHERE d_Month = Month(Date()) " & _
>     "AND d_Day = Day(Date())"
>
> Response.Write(strSQL)
> Response.End
> %>
>
> Lastly, why don't you just store the birthday as a date field? That would
> make everything so much easier. It's easier because you're sticking to
> modelling (in your database) the actual attribute of the Employer entity
> (their birthdate):
>
> <%
> strSQL = _
>     "SELECT EmployerName " & _
>     "FROM Employees " & _
>     "WHERE Birthday = Date()"
>
> Response.Write(strSQL)
> Response.End
> %>
>
> Now, isn't that last statement simpler :-)
>
> HTH
>
> Cheers
> Ken
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: " Dave Drake" <davdrake@c...>
> Subject: [asp_databases] Problem with using the server date in an Access
> query.........
>
>
> I'm trying on my index page to show all employees who have a birthday
based
> on the server month and day in a table with a repeat region...I have a
small
> Access database with the employee- first name, last name, birthday month
> (i.e. 3), birthday day (i.e. 19), an auto ID field, and a department
> field...I can't seem to find a way to insert the month and day from the
> server into a SQL statement to retrieve the employee's first name, last
> name, and department...
>
> When I check e_Month and e_Date subtype with TypeName they show as
> Integers...In the database d_BDateMonth and d_BDateDay are numbers...If I
> manually put numbers in place of e_MONTH and e_Day everything is fine and
> the rsEmpBday returns the values for the numbers...I don't think the
values
> are getting into the SQL statement...Is there any way to call them and
> insert them into the statement...It doesn't seem as if "Dim" is working in
> this case...Since this is my index page I want the values to load and go
> into the SQL when the page is accessed by the user...I don't have any way
of
> passing the values from another page...I've tried removing the single
quotes
> around e_Month and e_Day but then I get:
>
> Microsoft JET Database Engine error '80040e10'
> No value given for one or more required parameters.
>
> Where am I going wrong???...
>
> <%
> Dim e_Month, e_Day
> e_Month = month(date)
> e_Day = day(date)
> set rsEmpBday = Server.CreateObject("ADODB.Recordset")
> rsEmpBday.ActiveConnection = MM_connBday_STRING
> rsEmpBday.Source = "SELECT d_BDateDay, d_BDateMonth, d_DeptNbr, d_FName,
> d_ID, d_LName  FROM bdaysmstr  WHERE d_BDateMonth = 'e_Month' AND
d_BDateDay
> = 'e_Day' ORDER BY d_ID ASC"
> rsEmpBday.CursorType = 2
> rsEmpBday.CursorLocation = 2
> rsEmpBday.LockType = 3
> rsEmpBday.Open()
> rsEmpBday_numRows = 0
> %>
>
> Using this statment gives me this error:
> Microsoft JET Database Engine error '80040e07'
> Data type mismatch in criteria expression.
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>
>