Having problems with a date field

[Terry Murray]

Happy New Year Everybody!

I am having problems when trying query an access database that has a couple
of date/time fields.

If I construct my query like :

qry = "SELECT count(tournId) as duplicates " & _
"from tournInfo " & _
"where tournName = '" & request.form("tournName") & "' and " & _
"tournCityName = '" & request.form("city") & "' and " & _
"tournProvId = " & provId & " and " & _
"tournStartDate = '" & request.form("tournStartDate") & "' and " & _
"tournEndDate = '" & request.form("tournEndDate") & "' and " & _
"tournAddress1 = '" & request.form("address1") & "' and " & _
"tournAddress2 = '" & request.form("address2") & "' and " & _
"tournPCode = '" & request.form("pcode") & "' and " & _
"tournWebEmail = '" & request.form("webemail") & "' and " & _
"tournStatus = '" & status & "'"

I get the error message:

Data type mismatch in criteria expression.

If I change my query so that it omits the single apostrophe around the
dates:

qry = "SELECT count(tournId) as duplicates " & _
"from tournInfo " & _
"where tournName = '" & request.form("tournName") & "' and " & _
"tournCityName = '" & request.form("city") & "' and " & _
"tournProvId = " & provId & " and " & _
"tournStartDate = " & request.form("tournStartDate") & " and " & _
"tournEndDate = " & request.form("tournEndDate") & " and " & _
"tournAddress1 = '" & request.form("address1") & "' and " & _
"tournAddress2 = '" & request.form("address2") & "' and " & _
"tournPCode = '" & request.form("pcode") & "' and " & _
"tournWebEmail = '" & request.form("webemail") & "' and " & _
"tournStatus = '" & status & "'"

I get the error message:

Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression
'tournName = 'Tournament A' and tournCityName = 'Toronto' and
tournProvId = 1 and tournStartDate = January 5, 2004 and
tournEndDate = January 6, 2004 and tournAddress1 = '1 A Street'
and tournAddress2 = '' and tournPCode = 'M6M4P1' and tournWebEmail = 'inf'.


Can someone explain to me how to correct this.

Thanks in advance

Terry

[Bob Barrows]

Terry Murray wrote:

> Happy New Year Everybody!
>
> I am having problems when trying query an access database that has a
> couple of date/time fields.
>
> If I construct my query like :
>
> qry = "SELECT count(tournId) as duplicates " & _
> "from tournInfo " & _
> "where tournName = '" & request.form("tournName") & "' and " & _
> "tournCityName = '" & request.form("city") & "' and " & _
> "tournProvId = " & provId & " and " & _
> "tournStartDate = '" & request.form("tournStartDate") & "' and "
> & _ "tournEndDate = '" & request.form("tournEndDate") & "' and "
> & _ "tournAddress1 = '" & request.form("address1") & "' and " & _
> "tournAddress2 = '" & request.form("address2") & "' and " & _
> "tournPCode = '" & request.form("pcode") & "' and " & _
> "tournWebEmail = '" & request.form("webemail") & "' and " & _
> "tournStatus = '" & status & "'"
>
> I get the error message:
>
> Data type mismatch in criteria expression.
>
> If I change my query so that it omits the single apostrophe around the
> dates:
>
> qry = "SELECT count(tournId) as duplicates " & _
> "from tournInfo " & _
> "where tournName = '" & request.form("tournName") & "' and " & _
> "tournCityName = '" & request.form("city") & "' and " & _
> "tournProvId = " & provId & " and " & _
> "tournStartDate = " & request.form("tournStartDate") & " and " & _
> "tournEndDate = " & request.form("tournEndDate") & " and " & _
> "tournAddress1 = '" & request.form("address1") & "' and " & _
> "tournAddress2 = '" & request.form("address2") & "' and " & _
> "tournPCode = '" & request.form("pcode") & "' and " & _
> "tournWebEmail = '" & request.form("webemail") & "' and " & _
> "tournStatus = '" & status & "'"
>
> I get the error message:
>
> Microsoft JET Database Engine (0x80040E14)
> Syntax error (missing operator) in query expression
> 'tournName = 'Tournament A' and tournCityName = 'Toronto' and
> tournProvId = 1 and tournStartDate = January 5, 2004 and
> tournEndDate = January 6, 2004 and tournAddress1 = '1 A Street'
> and tournAddress2 = '' and tournPCode = 'M6M4P1' and tournWebEmail =
> 'inf'.
>
>
> Can someone explain to me how to correct this.
>
> Thanks in advance
>
> Terry

In JetSQL, you need to delimit date literals with #'s:
"tournStartDate = #" & request.form("tournStartDate") & "# and " & _

Of course, you could avoid all this delimiter nonsense by using a saved
parameter query...

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Chris Hohmann]

"Terry Murray" <tgmurray@rogers.com> wrote in message
news:7AmKb.230$AJB.215@news04.bloor.is.net.cable.r ogers.com...

> Happy New Year Everybody!
>
> I am having problems when trying query an access database that has a

couple

> of date/time fields.
>
> If I construct my query like :
>
> qry = "SELECT count(tournId) as duplicates " & _
> "from tournInfo " & _
> "where tournName = '" & request.form("tournName") & "' and " & _
> "tournCityName = '" & request.form("city") & "' and " & _
> "tournProvId = " & provId & " and " & _
> "tournStartDate = '" & request.form("tournStartDate") & "' and " &

_

> "tournEndDate = '" & request.form("tournEndDate") & "' and " & _
> "tournAddress1 = '" & request.form("address1") & "' and " & _
> "tournAddress2 = '" & request.form("address2") & "' and " & _
> "tournPCode = '" & request.form("pcode") & "' and " & _
> "tournWebEmail = '" & request.form("webemail") & "' and " & _
> "tournStatus = '" & status & "'"
>
> I get the error message:
>
> Data type mismatch in criteria expression.
>
> If I change my query so that it omits the single apostrophe around the
> dates:
>
> qry = "SELECT count(tournId) as duplicates " & _
> "from tournInfo " & _
> "where tournName = '" & request.form("tournName") & "' and " & _
> "tournCityName = '" & request.form("city") & "' and " & _
> "tournProvId = " & provId & " and " & _
> "tournStartDate = " & request.form("tournStartDate") & " and " & _
> "tournEndDate = " & request.form("tournEndDate") & " and " & _
> "tournAddress1 = '" & request.form("address1") & "' and " & _
> "tournAddress2 = '" & request.form("address2") & "' and " & _
> "tournPCode = '" & request.form("pcode") & "' and " & _
> "tournWebEmail = '" & request.form("webemail") & "' and " & _
> "tournStatus = '" & status & "'"
>
> I get the error message:
>
> Microsoft JET Database Engine (0x80040E14)
> Syntax error (missing operator) in query expression
> 'tournName = 'Tournament A' and tournCityName = 'Toronto' and
> tournProvId = 1 and tournStartDate = January 5, 2004 and
> tournEndDate = January 6, 2004 and tournAddress1 = '1 A Street'
> and tournAddress2 = '' and tournPCode = 'M6M4P1' and tournWebEmail =

'inf'.

>
>
> Can someone explain to me how to correct this.
>
> Thanks in advance
>
> Terry
>
>

Please post the results of Response.Write(qry)

[Terry Murray]

Thank you Bob. Making the change worked. I am curious about the saved
parameter query. Can you explain what it is and how it works.

Terry

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:O31NZa%230DHA.3436@tk2msftngp13.phx.gbl...

> Terry Murray wrote:

> > Happy New Year Everybody!
> >
> > I am having problems when trying query an access database that has a
> > couple of date/time fields.
> >
> > If I construct my query like :
> >
> > qry = "SELECT count(tournId) as duplicates " & _
> > "from tournInfo " & _
> > "where tournName = '" & request.form("tournName") & "' and " & _
> > "tournCityName = '" & request.form("city") & "' and " & _
> > "tournProvId = " & provId & " and " & _
> > "tournStartDate = '" & request.form("tournStartDate") & "' and "
> > & _ "tournEndDate = '" & request.form("tournEndDate") & "' and "
> > & _ "tournAddress1 = '" & request.form("address1") & "' and " & _
> > "tournAddress2 = '" & request.form("address2") & "' and " & _
> > "tournPCode = '" & request.form("pcode") & "' and " & _
> > "tournWebEmail = '" & request.form("webemail") & "' and " & _
> > "tournStatus = '" & status & "'"
> >
> > I get the error message:
> >
> > Data type mismatch in criteria expression.
> >
> > If I change my query so that it omits the single apostrophe around the
> > dates:
> >
> > qry = "SELECT count(tournId) as duplicates " & _
> > "from tournInfo " & _
> > "where tournName = '" & request.form("tournName") & "' and " & _
> > "tournCityName = '" & request.form("city") & "' and " & _
> > "tournProvId = " & provId & " and " & _
> > "tournStartDate = " & request.form("tournStartDate") & " and " & _
> > "tournEndDate = " & request.form("tournEndDate") & " and " & _
> > "tournAddress1 = '" & request.form("address1") & "' and " & _
> > "tournAddress2 = '" & request.form("address2") & "' and " & _
> > "tournPCode = '" & request.form("pcode") & "' and " & _
> > "tournWebEmail = '" & request.form("webemail") & "' and " & _
> > "tournStatus = '" & status & "'"
> >
> > I get the error message:
> >
> > Microsoft JET Database Engine (0x80040E14)
> > Syntax error (missing operator) in query expression
> > 'tournName = 'Tournament A' and tournCityName = 'Toronto' and
> > tournProvId = 1 and tournStartDate = January 5, 2004 and
> > tournEndDate = January 6, 2004 and tournAddress1 = '1 A Street'
> > and tournAddress2 = '' and tournPCode = 'M6M4P1' and tournWebEmail =
> > 'inf'.
> >
> >
> > Can someone explain to me how to correct this.
> >
> > Thanks in advance
> >
> > Terry

>
> In JetSQL, you need to delimit date literals with #'s:
> "tournStartDate = #" & request.form("tournStartDate") & "# and " & _
>
> Of course, you could avoid all this delimiter nonsense by using a saved
> parameter query...
>
> HTH,
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

[Bob Barrows]

Terry Murray wrote:

> Thank you Bob. Making the change worked. I am curious about the
> saved parameter query. Can you explain what it is and how it works.
>

Sure. Open your database in Access, go to the Queries tab and create a new
query in Design View. Ordinarily, at this point, you would select your
tables/queries from the dialog and begin creating your query. But in this
case, let's use your existing sql statement. Close the Choose Tables dialog
and switch to SQL View (using the toolbar button, or the right-click menu,
or the View menu)

Copy and paste this sql into the SQL window:

SELECT count(tournId) as duplicates
from tournInfo
where tournName = [qTournName] and
tournCityName = [qCity] and tournProvId = [qProv]
and tournStartDate = [qStDate] and
tournEndDate = [qEndDate] and tournAddress1 = [qAddr1]
and tournAddress2 = [qAddr2] and tournPCode = [qPCode]
and tournWebEmail = [qEmail] and tournStatus = [qStatus]

Run it to make sure it works. Notice that Access prompts you for the
parameter values. Notice the order in which it prompts for the values: it
should be in the same order in which the paramaters appear in the sql. You
need to supply the values in the same order in your ADO code.

So now you've achieved the first goal of proper query design: build and
debug your queries using the native query tool for your database. Only when
you have the query working in its native environment should you attempt to
run it from an external program.

Save the query as qGetDupCount.

To run this query in vbscript is the height of simplicity (cn is an open
Connection object):
Dim rs, dStart, dEnd
dStart = CDate(request.form("tournStartDate"))
dEnd= CDate(request.form("tournEndDate"))
Set rs=server.createobject("adodb.recordset")
cn.qGetDupCount request.form("tournName"), request.form("city"), _
provId, dStart, dEnd, request.form("address1"), _
request.form("address2"), request.form("pcode"), _
request.form("webemail"), status, rs

You now have an open recordset containing the results returned from the
saved query. If you create a saved query that returns no records, just
eliminate the recordset variable:

cn.qNoRecords parm1, ..., parmN

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Terry Murray]

Thanks once again Bob. I am wondering, however, about the open
Connection object, cn. When does it get created? I did not notice any line
in your code snippet that shows where it is instantiated.

Terry


"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eB68W9%230DHA.2456@TK2MSFTNGP12.phx.gbl...

> Terry Murray wrote:

> > Thank you Bob. Making the change worked. I am curious about the
> > saved parameter query. Can you explain what it is and how it works.
> >

>
> Sure. Open your database in Access, go to the Queries tab and create a new
> query in Design View. Ordinarily, at this point, you would select your
> tables/queries from the dialog and begin creating your query. But in this
> case, let's use your existing sql statement. Close the Choose Tables

dialog

> and switch to SQL View (using the toolbar button, or the right-click menu,
> or the View menu)
>
> Copy and paste this sql into the SQL window:
>
> SELECT count(tournId) as duplicates
> from tournInfo
> where tournName = [qTournName] and
> tournCityName = [qCity] and tournProvId = [qProv]
> and tournStartDate = [qStDate] and
> tournEndDate = [qEndDate] and tournAddress1 = [qAddr1]
> and tournAddress2 = [qAddr2] and tournPCode = [qPCode]
> and tournWebEmail = [qEmail] and tournStatus = [qStatus]
>
> Run it to make sure it works. Notice that Access prompts you for the
> parameter values. Notice the order in which it prompts for the values: it
> should be in the same order in which the paramaters appear in the sql. You
> need to supply the values in the same order in your ADO code.
>
> So now you've achieved the first goal of proper query design: build and
> debug your queries using the native query tool for your database. Only

when

> you have the query working in its native environment should you attempt to
> run it from an external program.
>
> Save the query as qGetDupCount.
>
> To run this query in vbscript is the height of simplicity (cn is an open
> Connection object):
> Dim rs, dStart, dEnd
> dStart = CDate(request.form("tournStartDate"))
> dEnd= CDate(request.form("tournEndDate"))
> Set rs=server.createobject("adodb.recordset")
> cn.qGetDupCount request.form("tournName"), request.form("city"), _
> provId, dStart, dEnd, request.form("address1"), _
> request.form("address2"), request.form("pcode"), _
> request.form("webemail"), status, rs
>
> You now have an open recordset containing the results returned from the
> saved query. If you create a saved query that returns no records, just
> eliminate the recordset variable:
>
> cn.qNoRecords parm1, ..., parmN
>
> HTH,
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

[Bob Barrows]

Terry Murray wrote:

> Thanks once again Bob. I am wondering, however, about the open
> Connection object, cn. When does it get created? I did not notice
> any line in your code snippet that shows where it is instantiated.
>

Well, I did not think I'd have to show you that, but:
set cn=server.createobject("adodb.connection")
cn.open "provider=microsoft.jet.oledb.4.0;data source=" & _
"p:\ath\to\database.mdb"
etc.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Terry Murray]

You are right, Bob. You should'nt have had to show me that. I am not sure
what I was thinking.

Thanks
Terry

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eK649aE1DHA.1684@TK2MSFTNGP12.phx.gbl...

> Terry Murray wrote:

> > Thanks once again Bob. I am wondering, however, about the open
> > Connection object, cn. When does it get created? I did not notice
> > any line in your code snippet that shows where it is instantiated.
> >

>
> Well, I did not think I'd have to show you that, but:
> set cn=server.createobject("adodb.connection")
> cn.open "provider=microsoft.jet.oledb.4.0;data source=" & _
> "p:\ath\to\database.mdb"
> etc.
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>