Using a user defined parameter in vba

Question

I have an access database that ouptuts a query to an excel file. The query uses a date parameter to run. This parameter is chosen by the user. I would like to use the parameter in the filename of the export. How can I accomplish that with the minimum amount of code.

01 01 09

Ok the new year has begun. sorry for the delay but I was on Holiday.

Details.

I have an access Database that outputs a report in excel format, the report is dependent on a date parameter that is chosen by the user. This parameter is selected via a textbox (text100) that has a pop up calendar. I would like to use the date in the text box(text100) in the filename.

Answer 1

You have to take responsibility for asking for the parameter. I like using global parameters that I can get/set via global functions - this way they can be set anywhere and the queries can have access to them as well.

Just need a couple subs/functions in module:

Some Module

Dim vParam1 as variant
Dim vParam1 as variant

Public Sub ParameterSet(byval pParamName as String, byval pParamValue as variant)

Select Case pParamName 
  Case "Param1": vParam1 = pParamValue 
  Case "Param2": vParam2 = pParamValue 
  Case Else
    msgbox pParamName & " parameter not defined"
End Select

End Sub

Public Function ParameterGet(byval pParamName as String) as variant

Select Case pParamName 
  Case "Param1": ParamGet = vParam1 
  Case "Param2": ParamGet = vParam2 
  Case Else
    msgbox pParamName & " parameter not defined"
End Select

End Sub

Then in your query (remove the date parameter)

WHERE Field1 = ParameterGet("Param1")

Then in your code where you run the export

Private Sub Export_Click()

  dim vParam1 as variant

  vParam1 = inputbox("Enter the parameter:")

  ParameterSet "param1", vParam1

  Transferspreadsheet blah, blah, FileName:= vParam1 & ".xls"

End Sub

Obviously this code needs a little tweaking to compile... :-)

Answer 2

It's apparent to me that you want to parse a date out of a filename, and then use this date to perform a query... if that's not what you want, please re-formulate the question.

I assume the last part is pretty obvious : concatenate the string converted date with the rest of your query string, and submit it to execution.

The part less obvious is the parsing. You need to define a filename format. Is it:

• "OUTDATA-YYYYMMDD"
• "OUTDATA _YY _MM _DD"
• "DD _MM _YYY _OUTDATA"
• or something else?

We need to know the tokens relative position (Year, Month, Day and ordinary file name).

Assuming "YYYY_ MM_ DD_ OUTDATA.DAT", here's a thought...

function toQueryDate(aFileName as String) as Date
Dim theQueryDate as Date
Dim theParsedDate as String

theParsedDate=Mid(aFileName,1,4)+"/"+Mid(aFileName,6,2)+"/"+Mid(aFileName,9,2)
'IMPROVE:there's no error checking here.... we could see if the individual tokens are numbers

On Error Resume Next
theQueryDate=Cdate(theParsedDate)
If err.number then
   Msgbox "Bad filename: "+aFilename
   End 'or something else less fatal 
Endif

'we should be OK so:
toQueryDate=theParsedDate
End function