Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Displaying QueryDef parameters entered by user

Posted on 2006-11-02
4
Medium Priority
?
295 Views
Last Modified: 2008-02-01
I have a query that I export to a text file. The query has two parameters that ask the user to input a start date and an end date range. I would like to concatenate those two dates into the name of the exported file. The query is called from several forms in my application, so I can't just change the query to read these date parameters off of one form; everybody's happy with being prompted for the dates.

My code basically looks like this, which should give you the logic of what I'm trying to do:
************************************
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim strFileLocation

Set db = CurrentDb
Set qdf = db.QueryDefs("MyQuery")

'(this is the part I can't figure out)
strFileLocation="C:\MyFileAsOf" & qdf.Parameters("[Start Date]") & "to" & qdf.Parameters("[End Date]")

DoCmd.TransferText acExportDelim, , "MyQuery", strFileLocation
*************************************

Basically, the issue is: how do I access the parameters that someone has passed to my query _after_ they've passed them?

Many thanks,
-Ed
0
Comment
Question by:eheraux
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 39

Expert Comment

by:stevbe
ID: 17859895
qdf.Parameters("Start Date").Value
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17860042
stevbe & eheraux ...

Wouldn't you have to run the query first to populate the Start/End dates before you build the strFileLocation string???

You probably want to store the Start/End dates in variables from an Input Box then you can build the strFileLocation as well as modify the qdf to include the date parameters.

ET
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17860739
FWIW I agree.

The querydef you create in code and the query called by name that you export are two different things.
You could use a function or two to provide the parameters (from global variables or table values).
Then they can be called at will - and the query can be exported again without prompt.
0
 
LVL 39

Accepted Solution

by:
stevbe earned 375 total points
ID: 17861640
and the long way around, without using temp tables ...

Public Function ExportQDF()

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim strPath As String
Dim intFile As Integer
Dim strData As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qselQDFParams")

Set prm = qdf.Parameters("Start Date")
prm.Value = InputBox("Start Date")
strPath = "C:\MyFileAsOf_" & Format(prm.Value, "yyyymmdd")

Set prm = qdf.Parameters("End Date")
prm.Value = InputBox("End Date")
strPath = strPath & "_to_" & Format(prm.Value, "yyyymmdd") & ".txt"

Set rst = qdf.OpenRecordset

intFile = VBA.FreeFile
Open strPath For Append Access Write As #intFile
For Each fld In rst.Fields
    strData = strData & fld.Name & ""","""
Next
strData = Left$(strData, Len(strData) - 1)
Print #intFile, strData

Do While Not rst.EOF
    strData = vbNullString
    For Each fld In rst.Fields
        strData = strData & fld.Value & vbNullString & ""","""
    Next
    Print #intFile, strData
    rst.MoveNext
Loop

Close #intFile

'cleanup
Set fld = Nothing
rst.Close
Set rst = Nothing
Set prm = Nothing
Set qdf = Nothing
Set dbs = Nothing

End Function
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question