PerksP
asked on
.filter syntax
I am trying to apply a filter to a date field [ContactDate] on a datasheet subform based on a date field in the main form tmpDateFilter.
Both fields are date fields and there are definitely records that match the date entered on the main form. I am trying the following but although the datasheet subform does filter - it returns an empty list. Can anyone see what I have got wrong here.
With Forms!CC_ContactRecords_Ma
.Filter = "[ContactDate] = #" & Me.TmpDateFilter & "#"
.FilterOn = True
End With
Both fields are date fields and there are definitely records that match the date entered on the main form. I am trying the following but although the datasheet subform does filter - it returns an empty list. Can anyone see what I have got wrong here.
With Forms!CC_ContactRecords_Ma
.Filter = "[ContactDate] = #" & Me.TmpDateFilter & "#"
.FilterOn = True
End With
ASKER
Thank you
The first option still gives an empty list and I'm afraid that the second is giving a Data Type mismatch error.
The table that runs the main form is a local table, the table that runs the subform is a linked table. Would this throw anything out?
The first option still gives an empty list and I'm afraid that the second is giving a Data Type mismatch error.
The table that runs the main form is a local table, the table that runs the subform is a linked table. Would this throw anything out?
Try building the criteria string separately, then printing it to the immediate window.
strCriteria = "DateValue([ContactDate]) = #" & me.TmpDateFilter & "#"
debug.print strCriteria
.Filter = strCriteria
strCriteria = "DateValue([ContactDate]) = #" & me.TmpDateFilter & "#"
debug.print strCriteria
.Filter = strCriteria
ASKER
Thank you
However, that is also returning a data type mismatch error. Did I put it in the right place?
Dim strcriteria As String
strcriteria = "DateValue([ContactDate]) = #" & Me.TmpDateFilter & "#"
Debug.Print strcriteria
With Forms!CC_ContactRecords_Ma
.Filter = strcriteria
.FilterOn = True
End With
However, that is also returning a data type mismatch error. Did I put it in the right place?
Dim strcriteria As String
strcriteria = "DateValue([ContactDate]) = #" & Me.TmpDateFilter & "#"
Debug.Print strcriteria
With Forms!CC_ContactRecords_Ma
.Filter = strcriteria
.FilterOn = True
End With
put a breakpoint on the line that starts:
With Forms!
Then you should be able to see what strCriteria looks like and determine what is happening.
Are you certain that TmpDateFilter is a valid control name?
With Forms!
Then you should be able to see what strCriteria looks like and determine what is happening.
Are you certain that TmpDateFilter is a valid control name?
ASKER
Yes, I have checked and double checked that as I thought it must be where I was going wrong.
[ContactDate] = #12/09/2014#
this is what is showingi n the immediate window. That is the same date as is in the tmpDateFilter field so it looks as though it is picking that up.
[ContactDate] = #12/09/2014#
this is what is showingi n the immediate window. That is the same date as is in the tmpDateFilter field so it looks as though it is picking that up.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That is fantastic - thank you so much!
I would never have worked that out however many hours I had spent on it.
I would never have worked that out however many hours I had spent on it.
You are welcome!
/gustav
/gustav
.Filter = "[ContactDate] >= #" & Me.TmpDateFilter & "# AND " _
& "[ContactDate] < #" & DateAdd("d", 1, cdate(me.TmpDateFilter)) & "#"
or:
.Filter = "DateValue([ContactDate]) = #" & me.TmpDateFilter & "#"