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)
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"
Terry
"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message
news:O31NZa%230DH...@tk2msftngp13.phx.gbl...
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
Terry
"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message
news:eB68W9%230DH...@TK2MSFTNGP12.phx.gbl...
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.
Thanks
Terry
"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message
news:eK649aE1...@TK2MSFTNGP12.phx.gbl...