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("Membe
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
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("Membe
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
Microsoft ApplicationsMicrosoft Access
Last Comment
Joppa
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
.
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
.
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
strSQL = NormalSelect & ", [Expiration] FROM Table1 " & _
"WHERE [Life] <> Yes " & _
"AND DateDiff('m', Date(), DateValue([Expiration])) = 1 " & _
"ORDER BY [Last]"
/gustav
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
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
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]"
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]"
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
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
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
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
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
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
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
> 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
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
ASKER
I tried the others and this one worked the best for me.
thanks,
Ric
thanks,
Ric
I suggest you use built in date arithmetic.
No need for NextMonthYear
Just use Format(DateAdd("m",1,date(
Compare that
...AND clng([Expiration]) <= clng(Format(DateAdd("m",1,
Regards,
Bill