Re: Dcount error

Code to set SQL variable:
-------------------------------------
Private Function CreateQry()
Dim strSQL As String
Dim dbs As Database
Dim qdf As QueryDef
Dim qryName As String

If ObjectExists("Query", "qryTempRptQry") Then
DoCmd.DeleteObject acQuery, "qryTempRptQry"
End If
If Me!optDevsel = 1 Then
'All devices
strSQL = "SELECT Customers.[Customer ID], Customers.[Company Name],
Customers.Address, Customers.City,Customers.State, Customers.[Zip Code],
tblFireAlarmDevices.Zone, tblFireAlarmDevices.[Model No],
tblFireAlarmDevices.Description, tblFireAlarmDevices.Quantity,
tblFireAlarmDevices.Location, [Test Date] AS Expr1, [Comments] AS Expr2 FROM
tblFireAlarmDevices LEFT JOIN Customers ON tblFireAlarmDevices.[Customer ID]
= Customers.[Customer ID] ORDER BY tblFireAlarmDevices.Zone DESC ,
tblFireAlarmDevices.Location DESC;"
Else
'If Me!optDevsel = 2 Then
'Smoke Detectors only
strSQL = "SELECT Customers.[Customer ID], Customers.[Company Name],
Customers.Address, Customers.City, Customers.State, Customers.[Zip Code],
tblFireAlarmDevices.Zone, tblFireAlarmDevices.[Model No],
tblFireAlarmDevices.Description, tblFireAlarmDevices.Quantity,
tblFireAlarmDevices.Location, [Test Date] AS Expr1, [Comments] AS Expr2 FROM
tblFireAlarmDevices LEFT JOIN Customers ON tblFireAlarmDevices.[Customer ID]
= Customers.[Customer ID] WHERE (((tblFireAlarmDevices.Description) = 'Smoke
Detector')) ORDER BY tblFireAlarmDevices.Zone DESC ,
tblFireAlarmDevices.Location DESC;"
End If

Set dbs = CurrentDb()
Set qdf = dbs.CreateQueryDef("qryTempRptQry", strSQL)
'RC = DCount("*", "qryTempRptQry") ' **** error highlight
********
' If RC = 0 Then
'MsgBox "THERE WERE NO RECORDS MATCHING YOUR CRITERIA...", 16, "Data
Status"
'ElseIf RC > 0 Then
'End If
Set qdf = Nothing
Set dbs = Nothing
End Function
-----------end code --------------------------
Sidney Hepp President/Systems Engineer Hepptech, Inc. (618)628-7468
(727)417-3363 Fax (775)264-7450 email: sid@xxxxxxxxxxxx www.hepptech.com
"Ken Snell (MVP)" <kthsneisllis9@xxxxxxxxxxxxxxxxxx> wrote in message
news:OyrwS1QLHHA.2232@xxxxxxxxxxxxxxxxxxxxxxx
Show us the code that sets the value of the strSQL variable. It would
appear that that variable's SQL string is not what you may think it is.

--

Ken Snell
<MS ACCESS MVP>

"Aaron" <nomail@xxxxxxxxxx> wrote in message
news:JQQlh.15$Pq6.12@xxxxxxxxxxxxxxx
Yes

The query work as expected, no errors, by itself or with a report.

"Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote in message
news:e$1CmxNLHHA.3952@xxxxxxxxxxxxxxxxxxxxxxx
Is the SQL in strSQL correct? (can you run the query afterwards without
an error)?

The misleading error message you're getting is common if a field or
table name is mistyped.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Aaron" <nomail@xxxxxxxxxx> wrote in message
news:V%Ilh.236$nk6.82@xxxxxxxxxxxxxxx
In a Dcount statement I have been using, I'm suddenly getting a:

"Runtime Error: 2001, You Cancelled the previous operation".

The context is:
Set dbs = CurrentDb()
Set qdf = dbs.CreateQueryDef("qryTempRptQry", strSQL)
RC = DCount("*", "qryTempRptQry") ' <--**** Highlighted error
***
If RC = 0 Then

I'm getting this error in one App & not the other using exact same
syntax, sames references. The query is being created and contains
records in the faiuling app,(with the Dcount test commented).

Any suggestions appreciated.









.

Relevant Pages

  • Re: Export by group to a specific folder and name
    ... Dim qdf As DAO.QueryDef ... Dim strSQL As String, strTemp As String, strMgr As String ... ' Get list of ManagerID values -- note: replace my generic table and field ...
    (microsoft.public.access.externaldata)
  • Re: Export by group to a specific folder and name
    ... Dim qdf As DAO.QueryDef ... Dim strSQL As String, strTemp As String, strMgr As String ... ' Get list of ManagerID values -- note: replace my generic table and field ...
    (microsoft.public.access.externaldata)
  • Re: Export Data in Access Table to Mulitple Excel Files
    ... Dim qdf As DAO.QueryDef ... Dim strSQL As String, strTemp As String, strMgr As String ...
    (microsoft.public.access.externaldata)
  • Re: Importing Long Text String
    ... Dim strChr As String, strValue1 As String, strValue2 As String ... Dim dbs As DAO.Database ... ' first record already skipped, ...
    (microsoft.public.access.externaldata)
  • Re: Replica
    ... Dim strTable As String ... Dim dbs As DAO.Database ... Exit Function ...
    (comp.databases.ms-access)