I am using Access 2000 and want to create a query using
VBA that is the data
source for a report. When I do this with Access 97 I have been very
successful. Unfortunately, Access 2000 does not add the SQL property to the
created query. All I get for the SQL statement is "SELECT; ". Reviewed many
references, including MSDN and "Access 2000 Developers Handbook" authored by
Ken Getz and Paul Litwin with no success.
Any assistance you can provide will be of great help.
The following example I wrote in Access 2000 does not work. The query is
created, but the SQL statement (qryDef.SQL = strSQL) has only the first word
from strSQL ("SELECT; ").
*******begin Access 2000 example*****************************
Function CreateVisitReport(BeginDt As Date, EndDt As Date)
Dim strSQL as String
Dim strSQL1 as String
Dim Dbm As DAO.Database
Set Dbm = CurrentDb()
On Error Resume Next
Dbm.QueryDefs.Delete "qryVisitReport" 'Just in case there is an old copy
in the DB
Dim qryDef As DAO.QueryDef
Set qryDef = Dbm.CreateQueryDef("qryVisitReport")
strSQL1 = " SELECT tblVisitData.Session_Date, tblVisitData.Visitor, "
strSQL1 = strSQL1 & " tblVisitData.Agency, tblVisitData.POC_PhoneNmr, "
strSQL1 = strSQL1 & " tblVisitData.POC_OrgNmr,
tblVisitData.Business_Grp, "
strSQL1 = strSQL1 & " tblVisitData.TypeUsage, tblVisitData.Qty_Visitors,
"
strSQL1 = strSQL1 & " tblVisitData.POC_LastName "
strSQL1 = strSQL1 & " tblVisitData.POC_FirstName "
strSQL1 = strSQL1 & " FROM tblVisitData "
strSQL1 = strSQL1 & " WHERE tblVisitData.Session_Date "
strSQL = strSQL1 & " Between #" & BeginDt & "# And #" & EndDt & "#; "
'The following line results in an SQL statement that contains only
"SELECT; "
qryDef.SQL = strSQL
Select Case Form_frmMenu!optRptGroup
Case 1
DoCmd.OpenReport "rptVisitDataAgency", acViewPreview,
"qryVisitReport"
Case 2
DoCmd.OpenReport "rptVisitDataBusGrp", acViewPreview,
"qryVisitReport"
Case 3
DoCmd.OpenReport "rptVisitDataType", acViewPreview,
"qryVisitReport"
End Select
Dbm.QueryDefs.Delete "qryVisitReport"
Set qryDef = Nothing
Dbm.Close
Set Dbm = Nothing
End Function
********end of Access 2000 example**************************************
The following example from my Access 97 database works just fine.
********begin Access 97 example************************************
Sub CreateAllRecordsReport(ProjStr As String, ProjTbl As String)
Dim strSQL as String
Dim strSQL1 as String
Dim qryDef As QueryDef
FindBookDate 'This is a routine to find specific book dates selected on
a form for the query
If Len(ProjStr) < 30 Then
ProjStr1 = ProjStr & ".*"
Else
ProjStr1 = ProjStr
End If
Set Db = CurrentDb '("DeptAcctAnal.mdb")
Set qryDef = Db.CreateQueryDef("qryAllRecords")
strSQL1 = " SELECT " & ProjTbl & ".[Book Date], " & ProjTbl & ".Account,
"
strSQL1 = strSQL1 & ProjTbl & ".Project, PL_LineNmr.LineNmr, " & ProjTbl
& ".Organization, "
strSQL1 = strSQL1 & ProjTbl & ".[Transaction Amount], " & ProjTbl &
".[Project Labor Category (PLC)], "
strSQL1 = strSQL1 & ProjTbl & ".Hours, " & ProjTbl & ".[Vend, Empl, Cust
or Subcontr ID], "
strSQL1 = strSQL1 & ProjTbl & ".Name, " & ProjTbl & ".[Timesheet Date],
PL_LineNmr.PLGrpNmr, ([PL_Acct].[LineNmr]) AS IntLineNmr, "
strSQL1 = strSQL1 & " PL_Acct.AccountDescription, PL_LineNmr.PL_Line,
tblDateNumber.BeginCalDt, "
strSQL1 = strSQL1 & " tblDateNumber.BeginNmrChk "
strSQL1 = strSQL1 & " FROM ((PL_Acct INNER JOIN PL_LineNmr ON
PL_Acct.LineNmr = PL_LineNmr.LineNmr) "
strSQL1 = strSQL1 & " INNER JOIN " & ProjTbl & " ON PL_Acct.Account = "
& ProjTbl & ".Account) "
strSQL1 = strSQL1 & " INNER JOIN tblDateNumber ON " & ProjTbl & ".[Book
Date] = tblDateNumber.BeginCalDt "
strSQL1 = strSQL1 & " WHERE ((( " & ProjTbl & ".Project) Like " &
conQuote & ProjStr1 & conQuote
strSQL1 = strSQL1 & " ) AND ((tblDateNumber.BeginNmrChk) Between " &
BeginBkDtChk & " And " & EndBkDtChk & " )) "
strSQL1 = strSQL1 & " AND PL_LineNmr.LineNmr Between 1 And 14 Or "
strSQL1 = strSQL1 & " ((( " & ProjTbl & ".Project) Like " & conQuote &
ProjStr & conQuote
strSQL1 = strSQL1 & " ) AND ((tblDateNumber.BeginNmrChk) Between " &
BeginBkDtChk & " And " & EndBkDtChk & " )) "
strSQL1 = strSQL1 & " AND PL_LineNmr.LineNmr Between 1 And 14 "
strSQL = strSQL1 & " ORDER BY " & ProjTbl & ".Organization;"
qryDef.SQL = strSQL
Select Case Form_frmMenu!optGroupBy
Case 1
DoCmd.OpenReport "rptAllRecords", acViewPreview, "qryAllRecords"
Case 2
DoCmd.OpenReport "rptAllRecordsProj", acViewPreview,
"qryAllRecords"
Case 3
DoCmd.OpenReport "rptAllRecordsPLC", acViewPreview,
"qryAllRecords"
End Select
Db.QueryDefs.Delete "qryAllRecords"
Set qryDef = Nothing
Set Db = Nothing
End Sub
**********end Access 97 example********************
CreatQueryDef In Access 2000 Doesn't Work?