Solved Need to filter a query based on variables set in vba (1 Viewer)

gakiss2

Registered User.
Local time
Today, 08:50
Joined
Nov 21, 2018
Messages
168
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
1580908718769.png

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've got your good things, and you've got mine.
Local time
Today, 11:50
Joined
May 21, 2018
Messages
8,463
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
Code:
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

Registered User.
Local time
Today, 08:50
Joined
Nov 21, 2018
Messages
168
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

You've got your good things, and you've got mine.
Local time
Today, 11:50
Joined
May 21, 2018
Messages
8,463
Code:
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

Immoderate Moderator
Staff member
Local time
Today, 10:50
Joined
Feb 28, 2001
Messages
26,999
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.

 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:50
Joined
Oct 29, 2018
Messages
21,358
Hi. Sorry for jumping in; as an alternative, you could also look into TempVars. Cheers!
 

gakiss2

Registered User.
Local time
Today, 08:50
Joined
Nov 21, 2018
Messages
168
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.
 

Users who are viewing this thread

Top Bottom