Filter query from Report Field (1 Viewer)

kobiashi

Registered User.
Local time
Today, 10:09
Joined
May 11, 2018
Messages
258
hi

i have a report that is bound to a query, what im trying to do is open this report based on different dates.

so i have two buttons on a form, these buttons have some VBA to fill a field on the report, this field is then used in the query to filter the data

so the button vba is

Code:
Private Sub btnYesterday_Click()
On Error GoTo btnYesterday_Click_Err

    DoCmd.OpenReport "Report_ENG_MCU_Tracker", acViewReport, "", "", acNormal
    Reports!Report_ENG_MCU_Tracker.txtSecondDate = Date - 1 + #2:00:00 AM#
    Reports!Report_ENG_MCU_Tracker.Report.Requery
    CloseTrackerButton Me
    
    
btnYesterday_Click_Exit:
    Exit Sub

btnYesterday_Click_Err:
    MsgBox Error$
    Resume btnYesterday_Click_Exit

End Sub

this inserts the date and time into a field on the report called txtSecondDate
there is a field on the report called txtFirstDate with a default value of Date()-1 + #02:00:00#

in the query i have two fields called MCUDate and MCUTime, i have concatenated these two fields together like this
Code:
DateTime:[MCUDate] + [MCUTime]

then in the criteria of this field i have
Code:
Between [Reports]![Report_ENG_MCU_Tracker]![txtFirstDate] And [Reports]![Report_ENG_MCU_Tracker]![txtSecondDate]

but it doesnt filter the data

when i remove the time, it works fine, but i need to filter by time aswell.

does anyone have anysuggestions?
 

kobiashi

Registered User.
Local time
Today, 10:09
Joined
May 11, 2018
Messages
258
thanks for the reply, im not sure how that would work with between dates,

please could you explain?
 

smig

Registered User.
Local time
Today, 12:09
Joined
Nov 25, 2009
Messages
2,209
Filter the query itself, using the WHERE CLUES on the fields on the form.

= Forms(frm.Name).FieldName

Sent from my Redmi Note 4 using Tapatalk
 
Last edited:

smig

Registered User.
Local time
Today, 12:09
Joined
Nov 25, 2009
Messages
2,209
..... Where [MyReportDate] Between Forms("myForm").FromDate And Forms("myForm").ToDate

Hope it help

Sent from my Redmi Note 4 using Tapatalk
 

kobiashi

Registered User.
Local time
Today, 10:09
Joined
May 11, 2018
Messages
258
ok so i tried filtering through vba like this

Code:
    Dim FirstDate As Date
    Dim SecondDate As Date
    
    Forms!SubForm_ENG_MCU_Dashboard_MCUReportButtons.txtSecondDate = Date - 1
    FirstDate = Forms!SubForm_ENG_MCU_Dashboard_MCUReportButtons.txtFirstDate
    SecondDate = Forms!SubForm_ENG_MCU_Dashboard_MCUReportButtons.txtSecondDate
   

    DoCmd.OpenReport "Report_ENG_MCU_Tracker", acViewReport, , "[DateTime]='" & "Between #" & FirstDate & "# AND" & " #" & SecondDate & "#" & "'"
    CloseTrackerButton Me

but it still doesnt work , there is no error reported, but its not displaying the data required
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:09
Joined
Sep 21, 2011
Messages
14,038
I don't believe you should be using single quotes in there, just " & # ???

Code:
"[DateTime]= Between #" & FirstDate & "# AND" & " #" & SecondDate & "#"""

Tip: Put criteria into a string then you can debug.print it or hover over it in the debugger.

Code:
strWhere = "[DateTime]= Between #" & FirstDate & "# AND" & " #" & SecondDate & "#"""
Debug.Print strWhere
 

kobiashi

Registered User.
Local time
Today, 10:09
Joined
May 11, 2018
Messages
258
thanks for the advice

so i ve done that, and the error comes back with Syntax error ( missing operator) in query expression '[dateTime] = Between #27/10/2018 02:00:00# And #27/10/10/2018 02:00:00#

here is me sub
Code:
Private Sub btnYesterday_Click()
On Error GoTo btnYesterday_Click_Err
    Dim FirstDate As Date
    Dim SecondDate As Date
    Dim strWhere As String
    
    Forms!SubForm_ENG_MCU_Dashboard_MCUReportButtons.txtSecondDate = Date - 1 + #2:00:00 AM#
    FirstDate = Forms!SubForm_ENG_MCU_Dashboard_MCUReportButtons.txtFirstDate
    SecondDate = Forms!SubForm_ENG_MCU_Dashboard_MCUReportButtons.txtSecondDate
    strWhere = "[DateTime]= Between #" & FirstDate & "# And" & " #" & SecondDate & "#"
    Debug.Print strWhere
    
    
    DoCmd.OpenReport "Report_ENG_MCU_Tracker", acViewReport, , strWhere

    CloseTrackerButton Me
    
    
btnYesterday_Click_Exit:
    Exit Sub

btnYesterday_Click_Err:
    MsgBox Error$
    Resume btnYesterday_Click_Exit

End Sub

and it fails at the docmd.OpenReport
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 10:09
Joined
Sep 21, 2011
Messages
14,038
No you haven't as there is no # before the seconddate :banghead:
 

kobiashi

Registered User.
Local time
Today, 10:09
Joined
May 11, 2018
Messages
258
sorry i dont understand, do you mean in the variable?
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:09
Joined
Sep 21, 2011
Messages
14,038
Try
Code:
strWhere = """[DateTime]= Between #" & FirstDate & "# And #" & SecondDate & "#"""
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:09
Joined
Sep 21, 2011
Messages
14,038
sorry i dont understand, do you mean in the variable?
Yes, the whole idea of the debug.print is to see if the syntax is correct.
You can see that there is no # before the second date on what you posted.

Well there was not, until you edited the post? :confused:
 

smig

Registered User.
Local time
Today, 12:09
Joined
Nov 25, 2009
Messages
2,209
Why do you insist using vba?
Why not filter the query?

Sent from my Redmi Note 4 using Tapatalk
 

kobiashi

Registered User.
Local time
Today, 10:09
Joined
May 11, 2018
Messages
258
thanks, so code executed, but didnt filter the query, it displayed all records
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:09
Joined
Sep 21, 2011
Messages
14,038
Why do you insist using vba?
Why not filter the query?

Sent from my Redmi Note 4 using Tapatalk

That is what the o/p was trying to do, but from the report field.?
 

kobiashi

Registered User.
Local time
Today, 10:09
Joined
May 11, 2018
Messages
258
if thats a better way, then great, i had originally done it that way, but then someone said use vba
 

smig

Registered User.
Local time
Today, 12:09
Joined
Nov 25, 2009
Messages
2,209
if thats a better way, then great, i had originally done it that way, but then someone said use vba
I think this is the better way.
No need for vba in this case.
Filter the query based on the fields on the form (not the report)
If you close the form, before opening the report, you can either save the dates to the registery or save them to a public variables and use a functions to read them into the query.

Sent from my Redmi Note 4 using Tapatalk
 

kobiashi

Registered User.
Local time
Today, 10:09
Joined
May 11, 2018
Messages
258
thanks for all the help

i have gone back to my original method

Code:
Private Sub btnYesterday_Click()
On Error GoTo btnYesterday_Click_Err
    
    Forms!SubForm_ENG_MCU_Dashboard_MCUReportButtons.txtSecondDate = Date - 1 + #02:00:00#
  
    DoCmd.OpenReport "Report_ENG_MCU_Tracker", acViewReport
    
    CloseTrackerButton Me
    
    
btnYesterday_Click_Exit:
    Exit Sub

btnYesterday_Click_Err:
    MsgBox Error$
    Resume btnYesterday_Click_Exit

End Sub

but ive used two fields on a form, so the second form is filled based on the button operation

the issue i have seems to be to do with the time, i have concatenated two fields together, originally i have used MyDate + MyTime, but this was doing something strange to the output data, so instead i did this, MyDate + TimeValue([MyTime]), this gave me the correct format for date and time, but when i query this with the method in my sub, it doesnt work,

so the first field has a default value of Date()-1 #02:00:00# and the second field has a value from the above sub, but doesnt work, when i remove the time, and execute it with date only, it works fine.


any suggestions?
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:09
Joined
Sep 21, 2011
Messages
14,038
OK, having read this whole thread again, I *think* your problem is with your time values.?

I have added #02:00# using Date + #02:00# and it shows correct.?

Initially you were trying to filter a query on the report values of a report based on that query (or so least so I thought?)

In the progress of this thread, suddenly form controls come to be mentioned?, so the goalposts are being moved and we don't even have the ball yet?
However even with your form controls, you still have a problem when the time element is involved?

So I would inspect each value and see if it is what you *think* it should be.?
 

kobiashi

Registered User.
Local time
Today, 10:09
Joined
May 11, 2018
Messages
258
thanks for the reply,

for the time being, i can live with date, so ill re address this at a later date, but thank you for the help.
 

Users who are viewing this thread

Top Bottom