DoCmd.OutputTo wrong data type

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access 2003.
Trying to an export of query using DoCmd.OutputTo.
The problem is passing the query paramters (I think)
The 1st query-takes in two prompt type parameters.
The 2nd query - pulls in the first query.
Thus if the user needs to enter a start & end date they are prompted a total
of 4 times.
I am trying to override that with input boxes and then setting the parms
equal to that.
However-when I put ht querydef variable in the"DoCmd" I receive an error
(Run-time 2498. "An expression you entered is the wrong data type for one of
the arguments) for an invalid data type-which I think is the query name.
Putting the query names in as literals works fine, but then we are prompted 4
times. Code below. Any help would appreciated.

Function ReconInvs()

Dim db As DAO.Database
Dim qdfQry As QueryDef
Dim qdfQry2 As QueryDef
Dim strStart As String
Dim strEnd As String


Set db = CurrentDb()
Set qdfQry = db.QueryDefs("ReconInvSvcDates")
Set qdfQry2 = db.QueryDefs("ReconInvSummary")

strStart = InputBox("Please enter Start date (mm/dd/yyyy)")
strEnd = InputBox("Please enter Start date (mm/dd/yyyy)")

qdfQry.Parameters(0) = strStart
qdfQry.Parameters(1) = strEnd

qdfQry2.Parameters(0) = strStart
qdfQry2.Parameters(1) = strEnd


DoCmd.OutputTo acOutputQuery, qdfQry, acFormatXLS, _
"C:\Documents and Settings\" & Environ("USERNAME") &
"\Desktop\ReconInvSvcDates" & Format(strEnd, "yyyymmdd") & ".xls", True, "",
0 _


DoCmd.OutputTo acOutputQuery, qdfQry2, acFormatXLS, _
"C:\Documents and Settings\" & Environ("USERNAME") &
"\Desktop\ReconInvSummary" & Format(strEnd, "yyyymmdd") & ".xls", True, "", 0
_


End Function
 
No. It's just a prompt from the QBE pane:
"Between [Start] AND [End]"

(those are dates)

I think I got the type issue-by using qdfQry.Name in the DoCmd, but still
can't
get the input boxes to override/replace the input parameters...
 
Try defining the parameter types:

PARAMETERS Start DateTime, End DateTime;

before what's currently there in the SQL view.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


No. It's just a prompt from the QBE pane:
"Between [Start] AND [End]"

(those are dates)

I think I got the type issue-by using qdfQry.Name in the DoCmd, but still
can't
get the input boxes to override/replace the input parameters...

Douglas J. Steele said:
Have you defined the data type of the parameters in the queries?
Click to expand...
Click to expand...
 
No dice. I am still prompted for each input box and parameter.

Douglas J. Steele said:
Try defining the parameter types:

PARAMETERS Start DateTime, End DateTime;

before what's currently there in the SQL view.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


No. It's just a prompt from the QBE pane:
"Between [Start] AND [End]"

(those are dates)

I think I got the type issue-by using qdfQry.Name in the DoCmd, but still
can't
get the input boxes to override/replace the input parameters...

Douglas J. Steele said:
Have you defined the data type of the parameters in the queries?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Access 2003.
Trying to an export of query using DoCmd.OutputTo.
The problem is passing the query paramters (I think)
The 1st query-takes in two prompt type parameters.
The 2nd query - pulls in the first query.
Thus if the user needs to enter a start & end date they are prompted a
total
of 4 times.
I am trying to override that with input boxes and then setting the
parms
equal to that.
However-when I put ht querydef variable in the"DoCmd" I receive an
error
(Run-time 2498. "An expression you entered is the wrong data type for
one
of
the arguments) for an invalid data type-which I think is the query
name.
Putting the query names in as literals works fine, but then we are
prompted 4
times. Code below. Any help would appreciated.

Function ReconInvs()

Dim db As DAO.Database
Dim qdfQry As QueryDef
Dim qdfQry2 As QueryDef
Dim strStart As String
Dim strEnd As String


Set db = CurrentDb()
Set qdfQry = db.QueryDefs("ReconInvSvcDates")
Set qdfQry2 = db.QueryDefs("ReconInvSummary")

strStart = InputBox("Please enter Start date (mm/dd/yyyy)")
strEnd = InputBox("Please enter Start date (mm/dd/yyyy)")

qdfQry.Parameters(0) = strStart
qdfQry.Parameters(1) = strEnd

qdfQry2.Parameters(0) = strStart
qdfQry2.Parameters(1) = strEnd


DoCmd.OutputTo acOutputQuery, qdfQry, acFormatXLS, _
"C:\Documents and Settings\" & Environ("USERNAME") &
"\Desktop\ReconInvSvcDates" & Format(strEnd, "yyyymmdd") & ".xls",
True,
"",
0 _


DoCmd.OutputTo acOutputQuery, qdfQry2, acFormatXLS, _
"C:\Documents and Settings\" & Environ("USERNAME") &
"\Desktop\ReconInvSummary" & Format(strEnd, "yyyymmdd") & ".xls", True,
"", 0
_


End Function
Click to expand...
Click to expand...
Click to expand...
 
Another option, then, would be to have a form with text boxes (or calendar
controls, or combo boxes) on it, and have the queries refer to those
controls rather than using named parameters.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


No dice. I am still prompted for each input box and parameter.

Douglas J. Steele said:
Try defining the parameter types:

PARAMETERS Start DateTime, End DateTime;

before what's currently there in the SQL view.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


No. It's just a prompt from the QBE pane:
"Between [Start] AND [End]"

(those are dates)

I think I got the type issue-by using qdfQry.Name in the DoCmd, but
still
can't
get the input boxes to override/replace the input parameters...

:

Have you defined the data type of the parameters in the queries?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Access 2003.
Trying to an export of query using DoCmd.OutputTo.
The problem is passing the query paramters (I think)
The 1st query-takes in two prompt type parameters.
The 2nd query - pulls in the first query.
Thus if the user needs to enter a start & end date they are prompted
a
total
of 4 times.
I am trying to override that with input boxes and then setting the
parms
equal to that.
However-when I put ht querydef variable in the"DoCmd" I receive an
error
(Run-time 2498. "An expression you entered is the wrong data type
for
one
of
the arguments) for an invalid data type-which I think is the query
name.
Putting the query names in as literals works fine, but then we are
prompted 4
times. Code below. Any help would appreciated.

Function ReconInvs()

Dim db As DAO.Database
Dim qdfQry As QueryDef
Dim qdfQry2 As QueryDef
Dim strStart As String
Dim strEnd As String


Set db = CurrentDb()
Set qdfQry = db.QueryDefs("ReconInvSvcDates")
Set qdfQry2 = db.QueryDefs("ReconInvSummary")

strStart = InputBox("Please enter Start date (mm/dd/yyyy)")
strEnd = InputBox("Please enter Start date (mm/dd/yyyy)")

qdfQry.Parameters(0) = strStart
qdfQry.Parameters(1) = strEnd

qdfQry2.Parameters(0) = strStart
qdfQry2.Parameters(1) = strEnd


DoCmd.OutputTo acOutputQuery, qdfQry, acFormatXLS, _
"C:\Documents and Settings\" & Environ("USERNAME")
&
"\Desktop\ReconInvSvcDates" & Format(strEnd, "yyyymmdd") & ".xls",
True,
"",
0 _


DoCmd.OutputTo acOutputQuery, qdfQry2, acFormatXLS, _
"C:\Documents and Settings\" & Environ("USERNAME")
&
"\Desktop\ReconInvSummary" & Format(strEnd, "yyyymmdd") & ".xls",
True,
"", 0
_


End Function
Click to expand...
Click to expand...
Click to expand...
 
Back
Top