Select not working

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.

Thanks,
Ric
JoppaAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
is the field [Life] a  YesNo field?

try this one


             strSQL = NormalSelect & ", [Expiration] FROM Table1 " & _
                 "WHERE [Life] <> -1 " & _
                 " AND Right([Expiration],2) & Left([Expiration],2) <= '" & NextMonthYear & "'" & _
                 " ORDER BY [Last]"



btw you have  two extra "("  before "Right" and "Left"
0
 
Bill RossCommented:
Hi,

I suggest you use built in date arithmetic.

No need for NextMonthYear

Just use Format(DateAdd("m",1,date()),"YYMM") which will always yield the following correct month.  No if... is required.

Compare that

...AND clng([Expiration]) <= clng(Format(DateAdd("m",1,date()),"YYMM"));

Regards,

Bill
0
 
Rey Obrero (Capricorn1)Commented:
what is  NornalSelect ?

try this

          strSQL = NormalSelect & ", [Expiration] FROM Table1 " & _
                 "WHERE [Life] <> Yes " & _
                 " AND (Right([Expiration],2) & (Left([Expiration],2) <= " & NextMonthYear & _
                 " ORDER BY [Last]"

' add this line
        Debug.Print strSQL


see what was printed in the  immediate window and post here


.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Gustav BrockCIOCommented:
Use real date valuess. It's much simpler.

           strSQL = NormalSelect & ", [Expiration] FROM Table1 " & _
                 "WHERE [Life] <> Yes " & _
                 "AND DateDiff('m', Date(), DateValue([Expiration])) = 1 " & _
                 "ORDER BY [Last]"

/gustav
0
 
JoppaAuthor Commented:
capricorn1:

from the immediate window -

SELECT First, Last, Street, City, State, Zip, [Phone #] , [Expiration] FROM Table1 WHERE [Life] <> Yes  AND (Right([Expiration],2) & (Left([Expiration],2) <= 1401 ORDER BY [Last]


NormalSelect is used so I won't have to keep typing this:

 NormalSelect = "SELECT First, Last, Street, City, State, Zip, [Phone #] "

Ric
0
 
Rey Obrero (Capricorn1)Commented:
change this

NormalSelect = "SELECT First, Last, Street, City, State, Zip, [Phone #] "


with

NormalSelect = "SELECT [First], [Last], Street, City, State, Zip, [Phone #] "


.
if you get a type mismatch error, use this
     
     strSQL = NormalSelect & ", [Expiration] FROM Table1 " & _
                 "WHERE [Life] <> Yes " & _
                 " AND (Right([Expiration],2) & (Left([Expiration],2) <= '" & NextMonthYear & "' _
                 " ORDER BY [Last]"
0
 
JoppaAuthor Commented:
Gustav,

  DateValue won't work in my case because the table was designed to have only 4 digits to represent the expiration date.  For example 1113 would be for Nov 2013.  There are no separators for DateValue to use.

Ric
0
 
JoppaAuthor Commented:
Bill,
 
  In order to get it to compile and recognize the CLng[Expiration] I had to take it out of the quotes but now it gives me a runtime error.  I've tried a lot of different formats but can't get it to work.

            strSQL = NormalSelect & ", [Expiration] FROM Table1 " & _
                 "WHERE [Life] <> Yes " & _
                 " AND " & CLng([ Expiration]) <= CLng(Format(DateAdd("m", 1, Date), "YYMM")) & " ORDER BY [Last]"

It doesn't even get to the debug.print statement to see what strSQL looks like.

Ric
0
 
JoppaAuthor Commented:
Capricorn1,

This also gave me a runtime error but I do get the string output.  I also made the suggested changes to NormalSelect.


            strSQL = NormalSelect & ", [Expiration] FROM Table1 " & _
                    "WHERE [Life] <> Yes " & _
                    " AND (Right([Expiration],2) & (Left([Expiration],2) <= " & NextMonthYear & _
                    " ORDER BY [Last]"
The output:

SELECT [First], [Last], Street, City, State, Zip, [Phone #] , [Expiration] FROM Table1 WHERE [Life] <> Yes  AND (Right([Expiration],2) & (Left([Expiration],2) <= 1401 ORDER BY [Last]

Ric
0
 
Bill RossCommented:
Hi,

Try this:

 strSQL =  "SELECT First, Last, Street, City, State, Zip, [Phone #], CLng([Expiration]) AS Expr1 FROM Table1 WHERE [Life] <> Yes AND Expr1 <= " & CLng(Format(DateAdd("m", 1, Date), "YYMM")) & " ORDER BY [Last]"

Note space between " and ORDER BY....

If that works we can modify again for the NormalSelect.

Regards,

Bill
0
 
Gustav BrockCIOCommented:
>  For example 1113 would be for Nov 2013

OK. Then still use date values or you easily get into trouble:


strSQL = NormalSelect & ", [Expiration] FROM Table1 " & _
    "WHERE [Life] <> Yes " & _
    "AND DateDiff('m', Date(), DateSerial(2000 + Val(Right([Expiration], 2)), Val(Left([Expiration], 2), 1)) = 1 " & _
    "ORDER BY [Last]"

/gustav
0
 
JoppaAuthor Commented:
I tried the others and this one worked the best for me.

thanks,
Ric
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.