troubleshooting Question

Select not working

Avatar of Joppa
Joppa asked on
Microsoft AccessMicrosoft Applications
12 Comments1 Solution223 ViewsLast Modified:
I have this database where I need to check if the membership is expiring next month.  The field is a 4 character text field mmyy.  I have code to reverse the comparison data to yymm to make it easier to compare and to account for going from December to January:

            If Format(Now(), "mm") = 12 Then
               NextMonthYear = Format(Now(), "yy") + 1 & "01"
            Else: NextMonthYear = Format(Now(), "yy") & Format(Now(), "mm") + 1  'reversed for comparison
            End If
            strSQL = NormalSelect & ", [Expiration] FROM Table1 " & _
                 "WHERE [Life] <> Yes " & _
                 " AND (Right([Expiration],2) & (Left([Expiration],2) <= " & NextMonthYear & _
                 " ORDER BY [Last]"
            CurrentDb.QueryDefs("Membership Expiring Next Month").SQL = strSQL
            DoCmd.OpenQuery "Membership Expiring Next Month"
I know the [Life] <> Yes is working but this portion:

" AND (Right([Expiration],2) & (Left([Expiration],2) <= " & NextMonthYear & _

is giving me an error at runtime.

Any help is appreciated.

Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros