Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

Access 2010 selective export

Access 2010

I found code to do what I want but it does not work.  The code is:

[embed=snippet 8267437]Dim nLowKey As Integer
Dim nHighKey As Integer
Dim sKey As String
nLowKey = txtLowKey
nHighKey = txtHighKey
sKey = "HOLDER"
Dim qry As QueryDef
Dim sqlStmt As String
sqlStmt = "SELECT * FROM CARDS WHERE (" & sKey & " = " & nLowKey & ") OR (" & sKey & " = " & nHighKey & ");"
Set qry = CurrentDb().QueryDefs("qryTemp")
qry.SQL = sqlStmt
DoCmd.TransferText acExportDelim, "ExportCards", qry.Name, "D:\IBMCollection\Cards.csv"

Open in new window



It goes fine until the Set qry statement.  Then I get a Run-time 3265 Item not found in this collection

Where is qryTemp defined?  
In the DoCmd where is qry.Name defined?
Dim nLowKey As Integer
Dim nHighKey As Integer
Dim sKey As String
nLowKey = txtLowKey
nHighKey = txtHighKey
sKey = "HOLDER"
Dim qry As QueryDef
Dim sqlStmt As String
sqlStmt = "SELECT * FROM CARDS WHERE (" & sKey & " = " & nLowKey & ") OR (" & sKey & " = " & nHighKey & ");"
Set qry = CurrentDb().QueryDefs("qryTemp")
qry.SQL = sqlStmt
DoCmd.TransferText acExportDelim, "ExportCards", qry.Name, "D:\IBMCollection\Cards.csv"

Open in new window

0
IBMJunkman
Asked:
IBMJunkman
2 Solutions
 
pdebaetsCommented:
Just create a query named "qryTemp". Put any old SQL statement in it. The SQL within will be replaced by the code you posted above.
0
 
Nick67Commented:
If you create a dummy saved query named qryTemp with "Select 1 as stuff" your cose would then work.  You are overwriting the SQL of an EXISTING query.  If it doesn't exist, well BANG! is the result.
0
 
IBMJunkmanAuthor Commented:
It worked!  The overwriting was mentioned but the fact it was an existing query escaped me.  Many thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now