Solved Need to filter a query based on variables set in vba

[gakiss2]

I have some code which sets begin date (BegDate) and end date (EndDate). I have a query (qryOutput) which filters properly however I don't filter on the field create_date (yet?). I want to use BegDate and EndDate as the condition to filter create_date. Later there is a command to export the results of the query to an Excel sheet and it works calling qryOutput as its source. I want to be able to have the query filter out results that are not between (and including) BegDate and EndDate. I tried to use the variables (BegDate, EndDate) in the Query Design grid but that didn't work. At least I don't know how, it just thinks they are text and says I have the wrong format which should be a date. THANK YOU

Private Sub CmdNewExport_Click()
Dim datestring As Date
Dim BegDate, EndDate As Date
Dim SelQry As String

datestring = MonthName(Month(Date - 28)) & " " & Year(Date - 28)

datestring = InputBox("Please Enter Alternate Date if needed", , datestring)


BegDate = Month(datestring) & "/" & "1" & "/" & Year(datestring)
EndDate = DateSerial(Year(datestring), Month(datestring) + 1, 1) - 1
' MsgBox BegDate & " " & EndDate

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryOutput", "C:\Users\gkissick\Desktop\u.xlsx"

End Sub

To clarify. this code 'works', I just haven't figured out how to filter down to the dates I want (between BegDate and EndDate.

Thanks in advance

 

[MajP]

You cannot use a variable directly in a query but you can call a function. I would not do it this way, but the technique to use a variable
In a standard module

Public dEndDate as date
Public dEndDate as date

Public Function BegDate() as Date
  BegDate = dBegDate
end function

Public Function EndDate() as Date
 EndDate = dEndDate
end function

Before calling the query set the public variables.

 

[gakiss2]

MajP Thank You. I had found some of this on line and was tinkering with it. hadn't gotten the syntax just right yet and I was trying to pass a string ">" & BegDate. But it looks like I just pass a date. I can try that but I noticed you stated that you would not do it that way. I am certainly open to other ways to accomplish it. Another thing I saw was to put a text box on the form and put BegDate in it. Is that what you are talking about?

 

[MajP]

Public Function GetBetween() As String
'In SQL date literals need to be in the format "MM/DD/YYYY" regardless of regional settings
Dim dtmDate As Date
Dim dateString As String 'I think you want a string here in form of Apr 2020 or May 2021
Dim begDate As Date
Dim EndDate As Date
dateString = Format(DateSerial(Year(Date), Month(Date - 28), 1), "MMM YYYY")
'Debug.Print dateString
dateString = InputBox("Please Enter Alternate Date if needed", , dateString)
begDate = CDate("1 " & dateString)
'Debug.Print begDate
EndDate = DateSerial(Year(dateString), Month(dateString) + 1, 1) - 1
'Debug.Print EndDate
GetBetween = "BETWEEN #" & Format(begDate, "mm/dd/yyyy") & "# AND #" & Format(EndDate, "mm/dd/yyyy") & "#"
End Function

Try this in the criteria.
Input box is in the form Jan 2020 and the output returned is
BETWEEN #01/01/2020# AND #01/31/2020#

 

[The_Doc_Man]

If this filtration by dates is all you want to do, you could also try a parameter query, perhaps, using the dates as two parameters for your query, using them in a WHERE clause.

Use parameters in queries, forms, and reports - Microsoft Support

This article explains how to use forms in Access to enhance your use of parameters in queries, forms, and reports.

support.office.com

 

[theDBguy]

Hi. Sorry for jumping in; as an alternative, you could also look into TempVars. Cheers!

 

[gakiss2]

I ended up putting a textbox on the form and holding the value there. Ultimately it seems to be the most straightforward way to do it. At some point I would like to understand the intricacies of private, public, temporary etc. variables but for not the quick and dirty invisible text box did the trick. thanks to all who helped.