By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,008 Members | 2,963 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,008 IT Pros & Developers. It's quick & easy.

Run-time error '3011' in select statement in access2002

P: n/a
I had a macro that ran a parameter query and created and opened an
Excel file with the system date as part of the file name, but I had to
change the file name by hand. So I converted the macro to code using
tools-->references.

The converted macro included the following statement:
DoCmd.OutputTo acQuery, "qselLabelsBloodLog_output",
"MicrosoftExcelBiff8(*.xls)", "S:\susan_cheree\lb041129.xls", True,
"", 0

I have an input box for the date used in the query and to use in the
excel file name.

Instead of "qselLabelsBloodLog_output" I put in the SQL statement
variable. Also, I wrote code for an input box and put the SQL query in
as a variable, but the program stops at the DoCmd line with the
following error message:

Run-time error '3011':

The Microsoft Jet database engine could not find the object 'SELECT
qselBsln.FN, qselBsln.LN, tblSelectLabels.PID,
tblSelectLabels.dPossibleDraw AS date_of_Possible_Draw,
qmaxTubeId_Pid.MaxOftubeLogid AS DrawId, qselMaxD'. Make sure
the object exists and that you spell its name and the path name
correctly.

Here's my code:
Private Sub cmdOutputPossDrawToExcel_Click()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim dPossDraw As Date

'inputbox variables
'+++++++++++++++++++++++++++++
Dim strResponse As String
Dim strMessage As String
Dim strTitle As String
Dim strDefault, strSql As String
Dim strFileName As String
'+++++++++++++++++++++++++++++++

strMessage = "date of possible draws"
strTitle = "Enter date of possible draws"
strDefault = str(Date)

strResponse = InputBox(strMessage, strTitle, strDefault)
'capture date from input box as a string year-mon-day
strFileName = Format(strResponse, "yyyymmdd")
MsgBox strFileName

'convert input box date to date to use in SQL query
dPossDraw = CDate(strResponse)

Set db = CurrentDb

strSql = "SELECT qselBsln.FN, qselBsln.LN, tblSelectLabels.PID, "
strSql = strSql & "tblSelectLabels.dPossibleDraw AS
date_of_Possible_Draw, "
strSql = strSql & "qmaxTubeId_Pid.MaxOftubeLogid AS DrawId, "
strSql = strSql & "qselMaxDrawId.MaxOftubeLogid AS MaxDrawID "
strSql = strSql & "FROM qselMaxDrawId, "
strSql = strSql & "(tblSelectLabels INNER JOIN qmaxTubeId_Pid ON "
strSql = strSql & "tblSelectLabels.PID = qmaxTubeId_Pid.pid) INNER
JOIN "
strSql = strSql & "qselBsln ON tblSelectLabels.PID = qselBsln.PID "
strSql = strSql & "WHERE "
strSql = strSql & "(tblSelectLabels.dPossibleDraw = #" & dPossDraw &
"# and "
strSql = strSql & "tblSelectLabels.chkSelectForLabel = Yes) "
strSql = strSql & "ORDER BY qselBsln.LN, tblSelectLabels.PID"

Set rs1 = db.OpenRecordset(strSql)

DoCmd.OutputTo acOutputQuery, strSql, "MicrosoftExcelBiff8(*.xls)",
"S:\susan_cheree\lb" & strFileName & ".xls", True, "", 0

Set rs1 = Nothing

End Sub

If someone could hlep sort this out, I would appreciate it!

Polly
ca*****************@hotmail.com
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
HJ
The object name causes the error. You use a string variable strSQL as the
object name whereas the OutputTo method (in your case) expects the name of
an existing query; see also Access Help.

Therefor you can first redefine an existing query with the composed SQL
string and then use the query name with the OutputTo method:

Dim qd As DAO.QueryDef
Set qd = db.QueryDefs("YourQueryName")
qd.SQL = strSQL

Furthermore, you are now not doing anything with the Recordset object. You
may well delete all code referring that object in this procedure.

HJ

"Polly" <ca******@hotmail.com> wrote in message
news:76**************************@posting.google.c om...
I had a macro that ran a parameter query and created and opened an
Excel file with the system date as part of the file name, but I had to
change the file name by hand. So I converted the macro to code using
tools-->references.

The converted macro included the following statement:
DoCmd.OutputTo acQuery, "qselLabelsBloodLog_output",
"MicrosoftExcelBiff8(*.xls)", "S:\susan_cheree\lb041129.xls", True,
"", 0

I have an input box for the date used in the query and to use in the
excel file name.

Instead of "qselLabelsBloodLog_output" I put in the SQL statement
variable. Also, I wrote code for an input box and put the SQL query in
as a variable, but the program stops at the DoCmd line with the
following error message:

Run-time error '3011':

The Microsoft Jet database engine could not find the object 'SELECT
qselBsln.FN, qselBsln.LN, tblSelectLabels.PID,
tblSelectLabels.dPossibleDraw AS date_of_Possible_Draw,
qmaxTubeId_Pid.MaxOftubeLogid AS DrawId, qselMaxD'. Make sure
the object exists and that you spell its name and the path name
correctly.

Here's my code:
Private Sub cmdOutputPossDrawToExcel_Click()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim dPossDraw As Date

'inputbox variables
'+++++++++++++++++++++++++++++
Dim strResponse As String
Dim strMessage As String
Dim strTitle As String
Dim strDefault, strSql As String
Dim strFileName As String
'+++++++++++++++++++++++++++++++

strMessage = "date of possible draws"
strTitle = "Enter date of possible draws"
strDefault = str(Date)

strResponse = InputBox(strMessage, strTitle, strDefault)
'capture date from input box as a string year-mon-day
strFileName = Format(strResponse, "yyyymmdd")
MsgBox strFileName

'convert input box date to date to use in SQL query
dPossDraw = CDate(strResponse)

Set db = CurrentDb

strSql = "SELECT qselBsln.FN, qselBsln.LN, tblSelectLabels.PID, "
strSql = strSql & "tblSelectLabels.dPossibleDraw AS
date_of_Possible_Draw, "
strSql = strSql & "qmaxTubeId_Pid.MaxOftubeLogid AS DrawId, "
strSql = strSql & "qselMaxDrawId.MaxOftubeLogid AS MaxDrawID "
strSql = strSql & "FROM qselMaxDrawId, "
strSql = strSql & "(tblSelectLabels INNER JOIN qmaxTubeId_Pid ON "
strSql = strSql & "tblSelectLabels.PID = qmaxTubeId_Pid.pid) INNER
JOIN "
strSql = strSql & "qselBsln ON tblSelectLabels.PID = qselBsln.PID "
strSql = strSql & "WHERE "
strSql = strSql & "(tblSelectLabels.dPossibleDraw = #" & dPossDraw &
"# and "
strSql = strSql & "tblSelectLabels.chkSelectForLabel = Yes) "
strSql = strSql & "ORDER BY qselBsln.LN, tblSelectLabels.PID"

Set rs1 = db.OpenRecordset(strSql)

DoCmd.OutputTo acOutputQuery, strSql, "MicrosoftExcelBiff8(*.xls)",
"S:\susan_cheree\lb" & strFileName & ".xls", True, "", 0

Set rs1 = Nothing

End Sub

If someone could hlep sort this out, I would appreciate it!

Polly
ca*****************@hotmail.com

Nov 13 '05 #2

P: n/a
Dear HJ,
Thanks for your post.

You clarified what I was afraid was the reason I was getting an error,
the the SQL statemtnt, although it does query the data is not a query
is the query object sense.

If I was not clear, the intention of this procedure was to take a date
from the input box to select records using the SQL statement that
queries the data and use the date in the output file (an excel file)
for other users to access, so they can identify the file for a
particular date. I wanted to manipulate more than is available in a
query.

Is there a way, then, write the data from the SQL statement to Excel?

Polly

"HJ" <hj********@spamhotmail.com> wrote in message news:<41***********************@news.xs4all.nl>...
The object name causes the error. You use a string variable strSQL as the
object name whereas the OutputTo method (in your case) expects the name of
an existing query; see also Access Help.

Therefor you can first redefine an existing query with the composed SQL
string and then use the query name with the OutputTo method:

Dim qd As DAO.QueryDef
Set qd = db.QueryDefs("YourQueryName")
qd.SQL = strSQL

Furthermore, you are now not doing anything with the Recordset object. You
may well delete all code referring that object in this procedure.

HJ

"Polly" <ca******@hotmail.com> wrote in message
news:76**************************@posting.google.c om...
I had a macro that ran a parameter query and created and opened an
Excel file with the system date as part of the file name, but I had to
change the file name by hand. So I converted the macro to code using
tools-->references.

The converted macro included the following statement:
DoCmd.OutputTo acQuery, "qselLabelsBloodLog_output",
"MicrosoftExcelBiff8(*.xls)", "S:\susan_cheree\lb041129.xls", True,
"", 0

I have an input box for the date used in the query and to use in the
excel file name.

Instead of "qselLabelsBloodLog_output" I put in the SQL statement
variable. Also, I wrote code for an input box and put the SQL query in
as a variable, but the program stops at the DoCmd line with the
following error message:

Run-time error '3011':

The Microsoft Jet database engine could not find the object 'SELECT
qselBsln.FN, qselBsln.LN, tblSelectLabels.PID,
tblSelectLabels.dPossibleDraw AS date_of_Possible_Draw,
qmaxTubeId_Pid.MaxOftubeLogid AS DrawId, qselMaxD'. Make sure
the object exists and that you spell its name and the path name
correctly.

Here's my code:
Private Sub cmdOutputPossDrawToExcel_Click()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim dPossDraw As Date

'inputbox variables
'+++++++++++++++++++++++++++++
Dim strResponse As String
Dim strMessage As String
Dim strTitle As String
Dim strDefault, strSql As String
Dim strFileName As String
'+++++++++++++++++++++++++++++++

strMessage = "date of possible draws"
strTitle = "Enter date of possible draws"
strDefault = str(Date)

strResponse = InputBox(strMessage, strTitle, strDefault)
'capture date from input box as a string year-mon-day
strFileName = Format(strResponse, "yyyymmdd")
MsgBox strFileName

'convert input box date to date to use in SQL query
dPossDraw = CDate(strResponse)

Set db = CurrentDb

strSql = "SELECT qselBsln.FN, qselBsln.LN, tblSelectLabels.PID, "
strSql = strSql & "tblSelectLabels.dPossibleDraw AS
date_of_Possible_Draw, "
strSql = strSql & "qmaxTubeId_Pid.MaxOftubeLogid AS DrawId, "
strSql = strSql & "qselMaxDrawId.MaxOftubeLogid AS MaxDrawID "
strSql = strSql & "FROM qselMaxDrawId, "
strSql = strSql & "(tblSelectLabels INNER JOIN qmaxTubeId_Pid ON "
strSql = strSql & "tblSelectLabels.PID = qmaxTubeId_Pid.pid) INNER
JOIN "
strSql = strSql & "qselBsln ON tblSelectLabels.PID = qselBsln.PID "
strSql = strSql & "WHERE "
strSql = strSql & "(tblSelectLabels.dPossibleDraw = #" & dPossDraw &
"# and "
strSql = strSql & "tblSelectLabels.chkSelectForLabel = Yes) "
strSql = strSql & "ORDER BY qselBsln.LN, tblSelectLabels.PID"

Set rs1 = db.OpenRecordset(strSql)

DoCmd.OutputTo acOutputQuery, strSql, "MicrosoftExcelBiff8(*.xls)",
"S:\susan_cheree\lb" & strFileName & ".xls", True, "", 0

Set rs1 = Nothing

End Sub

If someone could hlep sort this out, I would appreciate it!

Polly
ca*****************@hotmail.com

Nov 13 '05 #3

P: n/a
UPDATE
Dear HJ,
Since you pretty much confirmed that I needed to have an actual query,
I found some examples in the newsgroup file. I ended up using
transferspreadsheet. In order to do this, I had to have an actual
query. I used CreateQueryDef, then
did the transferspreadsheet.

Here's my new code:

Private Sub cmdOutputPossDrawToExcel_Click()

MsgBox "in process"
Dim db As DAO.Database

Dim qdfTemp As DAO.QueryDef
Dim qdfnew As DAO.QueryDef

Dim dPossDraw As Date

'inputbox variables
'+++++++++++++++++++++++++++++
Dim strResponse As String
Dim strMessage As String
Dim strTitle As String
Dim strDefault, strSql As String
Dim strFileName As String
'+++++++++++++++++++++++++++++++

strMessage = "date of possible draws"
strTitle = "Enter date of possible draws"
strDefault = str(Date)

strResponse = InputBox(strMessage, strTitle, strDefault)
'capture date from input box as a string year-mon-day
strFileName = Format(strResponse, "yymmdd")
MsgBox strFileName

'convert input box date to date to use in SQL query
dPossDraw = CDate(strResponse)

Set db = CurrentDb

'create permanent query to reference later
Set qdfnew = db.CreateQueryDef("NewQueryDef", _
"SELECT qselBsln.FN,
qselBsln.LN,tblSelectLabels.PID,tblSelectLabels.dP ossibleDraw AS
date_of_Possible_Draw,qmaxTubeId_Pid.MaxOftubeLogi d AS
DrawId,qselMaxDrawId.MaxOftubeLogid AS MaxDrawID FROM
qselMaxDrawId,(tblSelectLabels INNER JOIN qmaxTubeId_Pid ON
tblSelectLabels.PID = qmaxTubeId_Pid.pid) INNER JOIN qselBsln ON
tblSelectLabels.PID = qselBsln.PID WHERE
(tblSelectLabels.dPossibleDraw = #" & dPossDraw & "# and
tblSelectLabels.chkSelectForLabel = Yes) ORDER BY qselBsln.LN,
tblSelectLabels.PID")

'create excel file of possible draws using new query definition
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"newquerydef", "S:\susan_cheree\lb" & strFileName & ".xls", True, "",
0

'delete newly created query
db.QueryDefs.Delete qdfnew.NAME

MsgBox "done"
End Sub

Thanks, HJ!

Polly
ca******@hotmail.com (Polly) wrote in message news:<76*************************@posting.google.c om>...
Dear HJ,
Thanks for your post.

You clarified what I was afraid was the reason I was getting an error,
the the SQL statemtnt, although it does query the data is not a query
is the query object sense.

If I was not clear, the intention of this procedure was to take a date
from the input box to select records using the SQL statement that
queries the data and use the date in the output file (an excel file)
for other users to access, so they can identify the file for a
particular date. I wanted to manipulate more than is available in a
query.

Is there a way, then, write the data from the SQL statement to Excel?

Polly

"HJ" <hj********@spamhotmail.com> wrote in message news:<41***********************@news.xs4all.nl>...
The object name causes the error. You use a string variable strSQL as the
object name whereas the OutputTo method (in your case) expects the name of
an existing query; see also Access Help.

Therefor you can first redefine an existing query with the composed SQL
string and then use the query name with the OutputTo method:

Dim qd As DAO.QueryDef
Set qd = db.QueryDefs("YourQueryName")
qd.SQL = strSQL

Furthermore, you are now not doing anything with the Recordset object. You
may well delete all code referring that object in this procedure.

HJ

"Polly" <ca******@hotmail.com> wrote in message
news:76**************************@posting.google.c om...
I had a macro that ran a parameter query and created and opened an
Excel file with the system date as part of the file name, but I had to
change the file name by hand. So I converted the macro to code using
tools-->references.

The converted macro included the following statement:
DoCmd.OutputTo acQuery, "qselLabelsBloodLog_output",
"MicrosoftExcelBiff8(*.xls)", "S:\susan_cheree\lb041129.xls", True,
"", 0

I have an input box for the date used in the query and to use in the
excel file name.

Instead of "qselLabelsBloodLog_output" I put in the SQL statement
variable. Also, I wrote code for an input box and put the SQL query in
as a variable, but the program stops at the DoCmd line with the
following error message:

Run-time error '3011':

The Microsoft Jet database engine could not find the object 'SELECT
qselBsln.FN, qselBsln.LN, tblSelectLabels.PID,
tblSelectLabels.dPossibleDraw AS date_of_Possible_Draw,
qmaxTubeId_Pid.MaxOftubeLogid AS DrawId, qselMaxD'. Make sure
the object exists and that you spell its name and the path name
correctly.

Here's my code:
Private Sub cmdOutputPossDrawToExcel_Click()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim dPossDraw As Date

'inputbox variables
'+++++++++++++++++++++++++++++
Dim strResponse As String
Dim strMessage As String
Dim strTitle As String
Dim strDefault, strSql As String
Dim strFileName As String
'+++++++++++++++++++++++++++++++

strMessage = "date of possible draws"
strTitle = "Enter date of possible draws"
strDefault = str(Date)

strResponse = InputBox(strMessage, strTitle, strDefault)
'capture date from input box as a string year-mon-day
strFileName = Format(strResponse, "yyyymmdd")
MsgBox strFileName

'convert input box date to date to use in SQL query
dPossDraw = CDate(strResponse)

Set db = CurrentDb

strSql = "SELECT qselBsln.FN, qselBsln.LN, tblSelectLabels.PID, "
strSql = strSql & "tblSelectLabels.dPossibleDraw AS
date_of_Possible_Draw, "
strSql = strSql & "qmaxTubeId_Pid.MaxOftubeLogid AS DrawId, "
strSql = strSql & "qselMaxDrawId.MaxOftubeLogid AS MaxDrawID "
strSql = strSql & "FROM qselMaxDrawId, "
strSql = strSql & "(tblSelectLabels INNER JOIN qmaxTubeId_Pid ON "
strSql = strSql & "tblSelectLabels.PID = qmaxTubeId_Pid.pid) INNER
JOIN "
strSql = strSql & "qselBsln ON tblSelectLabels.PID = qselBsln.PID "
strSql = strSql & "WHERE "
strSql = strSql & "(tblSelectLabels.dPossibleDraw = #" & dPossDraw &
"# and "
strSql = strSql & "tblSelectLabels.chkSelectForLabel = Yes) "
strSql = strSql & "ORDER BY qselBsln.LN, tblSelectLabels.PID"

Set rs1 = db.OpenRecordset(strSql)

DoCmd.OutputTo acOutputQuery, strSql, "MicrosoftExcelBiff8(*.xls)",
"S:\susan_cheree\lb" & strFileName & ".xls", True, "", 0

Set rs1 = Nothing

End Sub

If someone could hlep sort this out, I would appreciate it!

Polly
ca*****************@hotmail.com

Nov 13 '05 #4

P: n/a
HJ
You're welcome. I see that you use the date variable dPossDraw in your SQL
statement. That may lead to unexpected results if the date does not get
formatted correctly. For more information see
http://www.mvps.org/access/datetime/date0005.htm

HJ

"Polly" <ca******@hotmail.com> wrote in message
news:76**************************@posting.google.c om...
UPDATE
Dear HJ,
Since you pretty much confirmed that I needed to have an actual query,
I found some examples in the newsgroup file. I ended up using
transferspreadsheet. In order to do this, I had to have an actual
query. I used CreateQueryDef, then
did the transferspreadsheet.

Here's my new code:

Private Sub cmdOutputPossDrawToExcel_Click()

MsgBox "in process"
Dim db As DAO.Database

Dim qdfTemp As DAO.QueryDef
Dim qdfnew As DAO.QueryDef

Dim dPossDraw As Date

'inputbox variables
'+++++++++++++++++++++++++++++
Dim strResponse As String
Dim strMessage As String
Dim strTitle As String
Dim strDefault, strSql As String
Dim strFileName As String
'+++++++++++++++++++++++++++++++

strMessage = "date of possible draws"
strTitle = "Enter date of possible draws"
strDefault = str(Date)

strResponse = InputBox(strMessage, strTitle, strDefault)
'capture date from input box as a string year-mon-day
strFileName = Format(strResponse, "yymmdd")
MsgBox strFileName

'convert input box date to date to use in SQL query
dPossDraw = CDate(strResponse)

Set db = CurrentDb

'create permanent query to reference later
Set qdfnew = db.CreateQueryDef("NewQueryDef", _
"SELECT qselBsln.FN,
qselBsln.LN,tblSelectLabels.PID,tblSelectLabels.dP ossibleDraw AS
date_of_Possible_Draw,qmaxTubeId_Pid.MaxOftubeLogi d AS
DrawId,qselMaxDrawId.MaxOftubeLogid AS MaxDrawID FROM
qselMaxDrawId,(tblSelectLabels INNER JOIN qmaxTubeId_Pid ON
tblSelectLabels.PID = qmaxTubeId_Pid.pid) INNER JOIN qselBsln ON
tblSelectLabels.PID = qselBsln.PID WHERE
(tblSelectLabels.dPossibleDraw = #" & dPossDraw & "# and
tblSelectLabels.chkSelectForLabel = Yes) ORDER BY qselBsln.LN,
tblSelectLabels.PID")

'create excel file of possible draws using new query definition
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"newquerydef", "S:\susan_cheree\lb" & strFileName & ".xls", True, "",
0

'delete newly created query
db.QueryDefs.Delete qdfnew.NAME

MsgBox "done"
End Sub

Thanks, HJ!

Polly
ca******@hotmail.com (Polly) wrote in message

news:<76*************************@posting.google.c om>...
Dear HJ,
Thanks for your post.

You clarified what I was afraid was the reason I was getting an error,
the the SQL statemtnt, although it does query the data is not a query
is the query object sense.

If I was not clear, the intention of this procedure was to take a date
from the input box to select records using the SQL statement that
queries the data and use the date in the output file (an excel file)
for other users to access, so they can identify the file for a
particular date. I wanted to manipulate more than is available in a
query.

Is there a way, then, write the data from the SQL statement to Excel?

Polly

"HJ" <hj********@spamhotmail.com> wrote in message news:<41***********************@news.xs4all.nl>...
The object name causes the error. You use a string variable strSQL as the object name whereas the OutputTo method (in your case) expects the name of an existing query; see also Access Help.

Therefor you can first redefine an existing query with the composed SQL string and then use the query name with the OutputTo method:

Dim qd As DAO.QueryDef
Set qd = db.QueryDefs("YourQueryName")
qd.SQL = strSQL

Furthermore, you are now not doing anything with the Recordset object. You may well delete all code referring that object in this procedure.

HJ

"Polly" <ca******@hotmail.com> wrote in message
news:76**************************@posting.google.c om...
> I had a macro that ran a parameter query and created and opened an
> Excel file with the system date as part of the file name, but I had to > change the file name by hand. So I converted the macro to code using
> tools-->references.
>
> The converted macro included the following statement:
> DoCmd.OutputTo acQuery, "qselLabelsBloodLog_output",
> "MicrosoftExcelBiff8(*.xls)", "S:\susan_cheree\lb041129.xls", True,
> "", 0
>
> I have an input box for the date used in the query and to use in the
> excel file name.
>
> Instead of "qselLabelsBloodLog_output" I put in the SQL statement
> variable. Also, I wrote code for an input box and put the SQL query in > as a variable, but the program stops at the DoCmd line with the
> following error message:
>
> Run-time error '3011':
>
> The Microsoft Jet database engine could not find the object 'SELECT > qselBsln.FN, qselBsln.LN, tblSelectLabels.PID,
> tblSelectLabels.dPossibleDraw AS date_of_Possible_Draw,
> qmaxTubeId_Pid.MaxOftubeLogid AS DrawId, qselMaxD'. Make sure
> the object exists and that you spell its name and the path name
> correctly.
>
> Here's my code:
> Private Sub cmdOutputPossDrawToExcel_Click()
> Dim db As DAO.Database
> Dim rs1 As DAO.Recordset
> Dim dPossDraw As Date
>
> 'inputbox variables
> '+++++++++++++++++++++++++++++
> Dim strResponse As String
> Dim strMessage As String
> Dim strTitle As String
> Dim strDefault, strSql As String
> Dim strFileName As String
> '+++++++++++++++++++++++++++++++
>
> strMessage = "date of possible draws"
> strTitle = "Enter date of possible draws"
> strDefault = str(Date)
>
> strResponse = InputBox(strMessage, strTitle, strDefault)
> 'capture date from input box as a string year-mon-day
> strFileName = Format(strResponse, "yyyymmdd")
> MsgBox strFileName
>
> 'convert input box date to date to use in SQL query
> dPossDraw = CDate(strResponse)
>
> Set db = CurrentDb
>
> strSql = "SELECT qselBsln.FN, qselBsln.LN, tblSelectLabels.PID, "
> strSql = strSql & "tblSelectLabels.dPossibleDraw AS
> date_of_Possible_Draw, "
> strSql = strSql & "qmaxTubeId_Pid.MaxOftubeLogid AS DrawId, "
> strSql = strSql & "qselMaxDrawId.MaxOftubeLogid AS MaxDrawID "
> strSql = strSql & "FROM qselMaxDrawId, "
> strSql = strSql & "(tblSelectLabels INNER JOIN qmaxTubeId_Pid ON "
> strSql = strSql & "tblSelectLabels.PID = qmaxTubeId_Pid.pid) INNER
> JOIN "
> strSql = strSql & "qselBsln ON tblSelectLabels.PID = qselBsln.PID "
> strSql = strSql & "WHERE "
> strSql = strSql & "(tblSelectLabels.dPossibleDraw = #" & dPossDraw &
> "# and "
> strSql = strSql & "tblSelectLabels.chkSelectForLabel = Yes) "
> strSql = strSql & "ORDER BY qselBsln.LN, tblSelectLabels.PID"
>
> Set rs1 = db.OpenRecordset(strSql)
>
> DoCmd.OutputTo acOutputQuery, strSql, "MicrosoftExcelBiff8(*.xls)",
> "S:\susan_cheree\lb" & strFileName & ".xls", True, "", 0
>
> Set rs1 = Nothing
>
> End Sub
>
> If someone could hlep sort this out, I would appreciate it!
>
> Polly
> ca*****************@hotmail.com

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.