Still can't seem to get a working "Current Quarter" option!

  • Thread starter Thread starter Bayou BoB
  • Start date Start date
B

Bayou BoB

Hello!

I've been down this road before...but we'll try again. This is the
option group that contains the current month, quarter and year options
for a series of different reports that generate program hours. The
Current Month option (Case1) works perfectly, the current Year(Case3)
works perfectly. The Current Quarter however is turning out to be a
real pain in the side. Is there something wrong with the way this is
entered? When I run it, and choose the current quarter, it comes back
with the following error. Where am I going so wrong on this? Is there
something else I should have somewhere else to make this work?? Any
insight would be great. Many Thanks.

"Syntax Error (Missing Operator) in query expression
'(Year([ActDate])=&Year(Date) AND DatePart('q',[ActDate])=1)."


Select Case [Frame0]
Case 1 'Current month
stWhere = stWhere & "Month([ActDate]) = " & Month(Date)
stWhere = stWhere & "And Year([ActDate]) = " & Year(Date)

Case 2 'Current quarter
stWhere = stWhere & "Year([ActDate])=&Year(Date)"
stWhere = stWhere & " And DatePart('q',[ActDate])= " &
DatePart("q", Date)

Case 3 'Current year
stWhere = stWhere & "Year([ActDate]) = " & Year(Date)

End Select
DoCmd.OpenReport stDocName, acPreview, , stWhere

Kevin
 
You have a typo in your code.

Change these lines

Case 2 'Current quarter
stWhere = stWhere & "Year([ActDate])=&Year(Date)"
stWhere = stWhere & " And DatePart('q',[ActDate])= " &
DatePart("q", Date)


to these lines

Case 2 'Current quarter
stWhere = stWhere & "Year([ActDate])=" &Year(Date)
stWhere = stWhere & " And DatePart('q',[ActDate])= " &
DatePart("q", Date)

Typo is in the first strWhere = line.
 
Bayou said:
Hello!

I've been down this road before...but we'll try again. This is the
option group that contains the current month, quarter and year options
for a series of different reports that generate program hours. The
Current Month option (Case1) works perfectly, the current Year(Case3)
works perfectly. The Current Quarter however is turning out to be a
real pain in the side. Is there something wrong with the way this is
entered? When I run it, and choose the current quarter, it comes back
with the following error. Where am I going so wrong on this? Is there
something else I should have somewhere else to make this work?? Any
insight would be great. Many Thanks.

"Syntax Error (Missing Operator) in query expression
'(Year([ActDate])=&Year(Date) AND DatePart('q',[ActDate])=1)."


Select Case [Frame0]
Case 1 'Current month
stWhere = stWhere & "Month([ActDate]) = " & Month(Date)
stWhere = stWhere & "And Year([ActDate]) = " & Year(Date)

Case 2 'Current quarter
stWhere = stWhere & "Year([ActDate])=&Year(Date)"
stWhere = stWhere & " And DatePart('q',[ActDate])= " &
DatePart("q", Date)

Case 3 'Current year
stWhere = stWhere & "Year([ActDate]) = " & Year(Date)

End Select
DoCmd.OpenReport stDocName, acPreview, , stWhere

Kevin
Click to expand...

If the above is a cut and paste from your code, it looks like you have a
quote in the wrong place for case 2.

Try this:

Case 2
stWhere = stWhere & "Year([ActDate])= " & Year(Date) '<< moved quote
stWhere = stWhere & " And DatePart('q',[ActDate])= " & > DatePart("q",
Date)
 
Back
Top