I have a query to list the date of birth of all the customers and it works fine.

Now I have a form to show the user of all customers birthday due today, tomorrow, next month and so on, and is the DateAdd function I used to do that.

Unfortunately, once after the user made a selection, the list always shows no record. If the user selection is no condition, meaning ‘show all record’, it works fine.

I just couldn’t spot the mistake and appreciate any utter member can assist me.

Thanks.

My code:

Note: [CPDOB] is my Customer Profile Date Of Birth in the query.

Private Sub ogShowWhen_AfterUpdate()

'Apply a filter based on the setting of ogShowWhen
Select Case Me.ogShowWhen
Case 1 'Last 30 days
'
Case 2 ' Last 7 Days
'
Case 3 ' Yesterday
'
Case 4 'Today
Me.Filter = "#" & Format([CPDOB], "dd-mmm") & "# = #" & Format(Date, "dd-mmm") & "#"
Me.FilterOn = True
Case Else
'Clear my filter
Me.Filter = ""
'Make sure the filter is off
Me.FilterOn = False
End Select
End Sub

Hi!!!!!

have a look at this method that I use. Works OK.

Before running the code, check that you do not have any missing object libraries.
In the VBA editor you need to set the MS Object Library.

To do this go to Tools>References... and keep an eye out for any references marked as MISSING.


Cheers!!!!!

lou

I have checked the reference and is ok.

I try to follow your method, putting query property in the code but no luck, is not working still, show no record.

What I hv changed...

Me.Filter = "qryReminder.[varCPDOB] = #" & Format(Date, "dd-mmm") & "#"

and in the query column....

varCPDOB: Format([CPDOB],"dd-mmm") (FYI, the query works ok and just show like 01-Sep)

Hi!!!!!!


This line

Me.Filter = "qryReminder.[varCPDOB] = #" & Format(Date, "dd-mmm") & "#"


should read like this if you are also wanting the year

Me.Filter = "qryReminder.[varCPDOB] = #" & Format(Date, "dd-mmm-yyyy") & "#"


Cheers!!!!


Lou