Filter a Form by Date Field in Subform

[Katherine]

I'm trying to filter the records on the mainform (MailingList) of my
database using a field contained in a subform (Donations). I was
basing my code off Allen Browne's Access Tips page (here:
http://allenbrowne.com/ser-28.html) since it has worked so well in the
past. However, now I'm trying to filter by date instead of by a
string or a number, and I'm getting nowhere. My goal is to have users
type a date into the unbound combo box and have that search a
particular field (RequestDue, also a date) in Donations and return all
records that match.

I have an unbound combo box on the mainform where users can type in
the date they want to search for. Here are the (hopefully) relevant
properties:
Name: cboRequestDueSe arch
Control Source: <blank>
Format: Short Date
Row Source Type: Table/Query
Row Source: Donations
Column Count: 30
Bound Column: 25

I have all of the column widths set to 0", save for the column I
actually care about.

Here is the code I currently have running as the AfterUpdate Event:
Private Sub cboRequestDueSe arch_AfterUpdat e()
Dim strSQL As String
If IsNull(Me.cboRe questDueSearch) Then
' If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = "MailingLis t"
Else
strSQL = "SELECT DISTINCTROW MailingList.* FROM MailingList " & _
"INNER JOIN Donations ON " & _
"MailingList.Ma ilingListID = Donations.Maili ngListID " & _
"WHERE Donations.Reque stDue = " & Me.cboRequestDu eSearch & ";"
Me.RecordSource = strSQL
End If
End Sub

I could type in all the variations of this I have tried, but it would
be very long. The combo box is looking in the right field of the
Donations table and I am no longer getting errors when I try to search
for a date. However, when I do search, Access says 'calculating... '
for a while, but then returns all records. No records are filtered
out.

Can anyone help me out? Thanks in advance for the advice!

Katherine

[Allen Browne]

When you put the date into the string, you need to add # around it. If your
date format is non-US, you need to tell Access to use the US format.

So, the code will contain:
"WHERE Donations.Reque stDue = " & _
Format(Me.cboRe questDueSearch, "\#mm\/dd\/yyyy\#" & ";"

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Katherine" <Oa************ *************** *@gmail.comwrot e in message
news:11******** *************@2 2g2000hsm.googl egroups.com...

I'm trying to filter the records on the mainform (MailingList) of my
database using a field contained in a subform (Donations). I was
basing my code off Allen Browne's Access Tips page (here:
http://allenbrowne.com/ser-28.html) since it has worked so well in the
past. However, now I'm trying to filter by date instead of by a
string or a number, and I'm getting nowhere. My goal is to have users
type a date into the unbound combo box and have that search a
particular field (RequestDue, also a date) in Donations and return all
records that match.

I have an unbound combo box on the mainform where users can type in
the date they want to search for. Here are the (hopefully) relevant
properties:
Name: cboRequestDueSe arch
Control Source: <blank>
Format: Short Date
Row Source Type: Table/Query
Row Source: Donations
Column Count: 30
Bound Column: 25

I have all of the column widths set to 0", save for the column I
actually care about.

Here is the code I currently have running as the AfterUpdate Event:
Private Sub cboRequestDueSe arch_AfterUpdat e()
Dim strSQL As String
If IsNull(Me.cboRe questDueSearch) Then
' If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = "MailingLis t"
Else
strSQL = "SELECT DISTINCTROW MailingList.* FROM MailingList " & _
"INNER JOIN Donations ON " & _
"MailingList.Ma ilingListID = Donations.Maili ngListID " & _
"WHERE Donations.Reque stDue = " & Me.cboRequestDu eSearch & ";"
Me.RecordSource = strSQL
End If
End Sub

I could type in all the variations of this I have tried, but it would
be very long. The combo box is looking in the right field of the
Donations table and I am no longer getting errors when I try to search
for a date. However, when I do search, Access says 'calculating... '
for a while, but then returns all records. No records are filtered
out.

Can anyone help me out? Thanks in advance for the advice!

Katherine

[Katherine]

On Jul 13, 5:23 am, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

When you put the date into the string, you need to add # around it. If your
date format is non-US, you need to tell Access to use the US format.

So, the code will contain:
"WHERE Donations.Reque stDue = " & _
Format(Me.cboRe questDueSearch, "\#mm\/dd\/yyyy\#" & ";"

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Katherine" <OatBoatBaBoatB oatMcBoatNut... [e-mail] e in message

news:11******** *************@2 2g2000hsm.googl egroups.com...

I'm trying to filter the records on the mainform (MailingList) of my
database using a field contained in a subform (Donations). I was
basing my code off Allen Browne's Access Tips page (here:
http://allenbrowne.com/ser-28.html) since it has worked so well in the
past. However, now I'm trying to filter by date instead of by a
string or a number, and I'm getting nowhere. My goal is to have users
type a date into the unbound combo box and have that search a
particular field (RequestDue, also a date) in Donations and return all
records that match.

I have an unbound combo box on the mainform where users can type in
the date they want to search for. Here are the (hopefully) relevant
properties:
Name: cboRequestDueSe arch
Control Source: <blank>
Format: Short Date
Row Source Type: Table/Query
Row Source: Donations
Column Count: 30
Bound Column: 25

I have all of the column widths set to 0", save for the column I
actually care about.

Here is the code I currently have running as the AfterUpdate Event:
Private Sub cboRequestDueSe arch_AfterUpdat e()
Dim strSQL As String
If IsNull(Me.cboRe questDueSearch) Then
' If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = "MailingLis t"
Else
strSQL = "SELECT DISTINCTROW MailingList.* FROM MailingList " & _
"INNER JOIN Donations ON " & _
"MailingList.Ma ilingListID = Donations.Maili ngListID " & _
"WHERE Donations.Reque stDue = " & Me.cboRequestDu eSearch & ";"
Me.RecordSource = strSQL
End If
End Sub

I could type in all the variations of this I have tried, but it would
be very long. The combo box is looking in the right field of the
Donations table and I am no longer getting errors when I try to search
for a date. However, when I do search, Access says 'calculating... '
for a while, but then returns all records. No records are filtered
out.

Can anyone help me out? Thanks in advance for the advice!

Katherine

Thanks for the reply. I tried out the code you gave, but it gave me
an error about a missing ')', so I altered the code as follows to end
the parathetical statement:

"WHERE Donations.Reque stDue = " & _
Format(Me.cboRe questDueSearch, "\#mm\/dd\/yyyy\#") & ";"

The result didn't change. The records still aren't filtered in any
way after entering a date in the combo box.

Do you have any other suggestions?

Katherine

[bobh]

Hi,
try adding a Me.Requery after the 'Me.RecordSourc e = strSQL' and
before the 'End If' statement
Me.RecordSource = strSQL
End If

bobh.

On Jul 13, 11:45 am, Katherine
<OatBoatBaBoatB oatMcBoatNut... [e-mail] e:

On Jul 13, 5:23 am, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

When you put the date into the string, you need to add # around it. If your
date format is non-US, you need to tell Access to use the US format.

So, the code will contain:
"WHERE Donations.Reque stDue = " & _
Format(Me.cboRe questDueSearch, "\#mm\/dd\/yyyy\#" & ";"

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Katherine" <OatBoatBaBoatB oatMcBoatNut... [e-mail] e in message

news:11******** *************@2 2g2000hsm.googl egroups.com...

I'm trying to filter the records on the mainform (MailingList) of my
database using a field contained in a subform (Donations). I was
basing my code off Allen Browne's Access Tips page (here:
>http://allenbrowne.com/ser-28.html) since it has worked so well in the
past. However, now I'm trying to filter by date instead of by a
string or a number, and I'm getting nowhere. My goal is to have users
type a date into the unbound combo box and have that search a
particular field (RequestDue, also a date) in Donations and return all
records that match.

I have an unbound combo box on the mainform where users can type in
the date they want to search for. Here are the (hopefully) relevant
properties:
Name: cboRequestDueSe arch
Control Source: <blank>
Format: Short Date
Row Source Type: Table/Query
Row Source: Donations
Column Count: 30
Bound Column: 25

I have all of the column widths set to 0", save for the column I
actually care about.

Here is the code I currently have running as the AfterUpdate Event:
Private Sub cboRequestDueSe arch_AfterUpdat e()
Dim strSQL As String
If IsNull(Me.cboRe questDueSearch) Then
' If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = "MailingLis t"
Else
strSQL = "SELECT DISTINCTROW MailingList.* FROM MailingList " & _
"INNER JOIN Donations ON " & _
"MailingList.Ma ilingListID = Donations.Maili ngListID " & _
"WHERE Donations.Reque stDue = " & Me.cboRequestDu eSearch & ";"
Me.RecordSource = strSQL
End If
End Sub

I could type in all the variations of this I have tried, but it would
be very long. The combo box is looking in the right field of the
Donations table and I am no longer getting errors when I try to search
for a date. However, when I do search, Access says 'calculating... '
for a while, but then returns all records. No records are filtered
out.

Can anyone help me out? Thanks in advance for the advice!

Katherine

Thanks for the reply. I tried out the code you gave, but it gave me
an error about a missing ')', so I altered the code as follows to end
the parathetical statement:

"WHERE Donations.Reque stDue = " & _
Format(Me.cboRe questDueSearch, "\#mm\/dd\/yyyy\#") & ";"

The result didn't change. The records still aren't filtered in any
way after entering a date in the combo box.

Do you have any other suggestions?

Katherine- Hide quoted text -

- Show quoted text -

[Katherine]

On Jul 13, 1:26 pm, bobh <vulca...@isp.c omwrote:

Hi,
try adding a Me.Requery after the 'Me.RecordSourc e = strSQL' and
before the 'End If' statement
Me.RecordSource = strSQL
End If

bobh.

On Jul 13, 11:45 am, Katherine

<OatBoatBaBoatB oatMcBoatNut... [e-mail] e:

On Jul 13, 5:23 am, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

When you put the date into the string, you need to add # around it. If your
date format is non-US, you need to tell Access to use the US format.

So, the code will contain:
"WHERE Donations.Reque stDue = " & _
Format(Me.cboRe questDueSearch, "\#mm\/dd\/yyyy\#" & ";"

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Katherine" <OatBoatBaBoatB oatMcBoatNut... [e-mail] e in message

>news:11******* **************@ 22g2000hsm.goog legroups.com...

I'm trying to filter the records on the mainform (MailingList) of my
database using a field contained in a subform (Donations). I was
basing my code off Allen Browne's Access Tips page (here:
http://allenbrowne.com/ser-28.html) since it has worked so well in the
past. However, now I'm trying to filter by date instead of by a
string or a number, and I'm getting nowhere. My goal is to have users
type a date into the unbound combo box and have that search a
particular field (RequestDue, also a date) in Donations and return all
records that match.

I have an unbound combo box on the mainform where users can type in
the date they want to search for. Here are the (hopefully) relevant
properties:
Name: cboRequestDueSe arch
Control Source: <blank>
Format: Short Date
Row Source Type: Table/Query
Row Source: Donations
Column Count: 30
Bound Column: 25

I have all of the column widths set to 0", save for the column I
actually care about.

Here is the code I currently have running as the AfterUpdate Event:
Private Sub cboRequestDueSe arch_AfterUpdat e()
Dim strSQL As String
If IsNull(Me.cboRe questDueSearch) Then
' If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = "MailingLis t"
Else
strSQL = "SELECT DISTINCTROW MailingList.* FROM MailingList " & _
"INNER JOIN Donations ON " & _
"MailingList.Ma ilingListID = Donations.Maili ngListID " & _
"WHERE Donations.Reque stDue = " & Me.cboRequestDu eSearch & ";"
Me.RecordSource = strSQL
End If
End Sub

I could type in all the variations of this I have tried, but it would
be very long. The combo box is looking in the right field of the
Donations table and I am no longer getting errors when I try to search
for a date. However, when I do search, Access says 'calculating... '
for a while, but then returns all records. No records are filtered
out.

Can anyone help me out? Thanks in advance for the advice!

Katherine

Thanks for the reply. I tried out the code you gave, but it gave me
an error about a missing ')', so I altered the code as follows to end
the parathetical statement:

"WHERE Donations.Reque stDue = " & _
Format(Me.cboRe questDueSearch, "\#mm\/dd\/yyyy\#") & ";"

The result didn't change. The records still aren't filtered in any
way after entering a date in the combo box.

Do you have any other suggestions?

Katherine- Hide quoted text -

- Show quoted text -

Thanks for the suggestion. I did try it, but still no change. The
records are not being filtered at all.

Katherine

[CDMAPoster]

On Jul 13, 4:05 pm, Katherine
<OatBoatBaBoatB oatMcBoatNut... [e-mail] e:

Thanks for the suggestion. I did try it, but still no change. The
records are not being filtered at all.

Katherine

I just tried an example with

"WHERE Donations.Reque stDue = #" & Me.cboRequestDu eSearch & "#;"

It worked. That might make yours work.

If not, try a separate query using the following SQL:

SELECT Donations.Reque stDue, Forms!MailingLi st.cboRequestDu eSearch AS
FormDate FROM MailingList INNER JOIN Donations ON
MailingList.Mai lingListID = Donations.Maili ngListID;

Note: Do this with the MailingList form open and with a value selected
for cboRequestDueSe arch. I used:

SELECT DISTINCT RequestDue FROM Donations WHERE RequestDue IS NOT
NULL;

as the RowSource of the combobox on the MailingList form.

Compare the date values.

Having time information in the date field would not cause a lack of
filtering. A Null value equates to everything, but a spelling mistake
should cause a prompt to come up.

James A. Fortune
CD********@Fort uneJames.com

[Katherine]

On Jul 13, 5:15 pm, CDMAPos...@Fort uneJames.com wrote:

On Jul 13, 4:05 pm, Katherine

<OatBoatBaBoatB oatMcBoatNut... [e-mail] e:

Thanks for the suggestion. I did try it, but still no change. The
records are not being filtered at all.

Katherine

I just tried an example with

"WHERE Donations.Reque stDue = #" & Me.cboRequestDu eSearch & "#;"

It worked. That might make yours work.

If not, try a separate query using the following SQL:

SELECT Donations.Reque stDue, Forms!MailingLi st.cboRequestDu eSearch AS
FormDate FROM MailingList INNER JOIN Donations ON
MailingList.Mai lingListID = Donations.Maili ngListID;

Note: Do this with the MailingList form open and with a value selected
for cboRequestDueSe arch. I used:

SELECT DISTINCT RequestDue FROM Donations WHERE RequestDue IS NOT
NULL;

as the RowSource of the combobox on the MailingList form.

Compare the date values.

Having time information in the date field would not cause a lack of
filtering. A Null value equates to everything, but a spelling mistake
should cause a prompt to come up.

James A. Fortune
CDMAPos...@Fort uneJames.com

James,

I tried the first thing you suggested (with the # signs), and the
results didn't change. Still no filtering.

The second suggestion you had may be leading me down the right path,
but I'm not sure what the next step is. I altered the RowSource of
the combo box as you said and then ran the query you suggested. The
date results for RequestDue look like they are formatted wrong in the
results - they all have extra spaces in them. Some examples are 12/1 /
07 and 9 /7 /07 and 1 /10/10.

This may help explain why the filtering is not working as I'd like,
but, if that is the case, I'm not sure what steps to take to correct
the problem. Any thoughts?

Katherine

[CDMAPoster]

On Jul 16, 12:43 pm, Katherine
<OatBoatBaBoatB oatMcBoatNut... [e-mail] e:

James,

I tried the first thing you suggested (with the # signs), and the
results didn't change. Still no filtering.

The second suggestion you had may be leading me down the right path,
but I'm not sure what the next step is. I altered the RowSource of
the combo box as you said and then ran the query you suggested. The
date results for RequestDue look like they are formatted wrong in the
results - they all have extra spaces in them. Some examples are 12/1 /
07 and 9 /7 /07 and 1 /10/10.

This may help explain why the filtering is not working as I'd like,
but, if that is the case, I'm not sure what steps to take to correct
the problem. Any thoughts?

Katherine

Katherine,

You've certainly thrown some curveballs.

I think the example I tried worked with RequestDue formatted as either
Date/Time or as Text, but without any spaces. If the spaces are
stored in a text field you can open the table, click on the field
header and use Edit...Replace. .. with 'Match Whole Field' unchecked to
replace spaces with nothing or, alternatively for later versions of
Access, run an update query using the Replace function. Some ways
that the spaces can get there in the first place include:

1) It was stored in the mainframe that way.

2) The number was written into a string variable initialized as two
spaces causing a single digit to left-justify before being written out
to a file.

If you are unable to change the formatting or edit the original
values, try:

Private Sub cboRequestDueSe arch_AfterUpdat e()
Dim strSQL As String

If IsNull(Me.cboRe questDueSearch) Then
' If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = "MailingLis t"
Else
strSQL = "SELECT Donations.Reque stDue FROM MailingList " & _
"INNER JOIN Donations ON " & _
"MailingList.Ma ilingListID = Donations.Maili ngListID " & _
"WHERE Donations.Reque stDue = " & Chr(34) & Me.cboRequestDu eSearch
& Chr(34) & ";"
Me.RecordSource = strSQL
End If
End Sub

That will cause a text match of your "spaced-out" dates from the table
with the "spaced-out" list in your combobox. Be sure to take out the
'Short Date' format from cboRequestDueSe arch before trying it that
way. If you are able to remove all the spaces from RequestDue,
consider converting the text field into a Date/Time data type. If
RequestDue is truly a Date/Time field already then you need to find
out where the strange formatting is coming from. Maybe your 'Short
Date' format got messed up somehow.

James A. Fortune
CD********@Fort uneJames.com