asked on

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

Bill Ross

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

Rey Obrero (Capricorn1)

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

.

Gustav Brock

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

Joppa

ASKER

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

Rey Obrero (Capricorn1)

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]"

Joppa

ASKER

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

Joppa

ASKER

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

Joppa

ASKER

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

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bill Ross

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

Gustav Brock

> 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

Joppa

ASKER

I tried the others and this one worked the best for me.

thanks,
Ric