Command Button Problem

  • Thread starter Thread starter Suzanne Wyatt
  • Start date Start date
S

Suzanne Wyatt

I have a form with 5 command buttons that have different functions they all
open one of 3 reports but in 2 different ways.

The first 3 buttons work fine, but the last 2 don't work properly. I keep
getting a syntax error in query expression. I am basically using the same
query expression on all just the linkcriteria is different on the last 2.

Here is the VB I am having a problem with:
Private Sub cmdCSSbtnall_Click()
On Error GoTo Err_cmdCSSbtnall_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "rptSurveyDetailReport"
stLinkCriteria = stLinkCriteria & " AND " & "([YearOfService] &
[MonthOfService]) >= " & Me![cboReportStart]
stLinkCriteria = stLinkCriteria & " AND " & "([YearOfService] &
[MonthOfService]) <= " & Me![cboReportEnd]

DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria


Exit_cmdCSSbtnall_Click:
Exit Sub

Err_cmdCSSbtnall_Click:
MsgBox Err.Description
Resume Exit_cmdCSSbtnall_Click
And here is one of the command buttons I am not having a problem with:
Private Sub btnCSSVer_Click()
On Error GoTo Err_btnCSSVer_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "rptSurveyDetailReport"
stLinkCriteria = "[ProgramID]=" & Me![lstGroup]
stLinkCriteria = stLinkCriteria & " AND " & "([YearOfService] &
[MonthOfService]) >= " & Me![cboReportStart]
stLinkCriteria = stLinkCriteria & " AND " & "([YearOfService] &
[MonthOfService]) <= " & Me![cboReportEnd]

DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_btnCSSVer_Click:
Exit Sub

Err_btnCSSVer_Click:
MsgBox Err.Description
Resume Exit_btnCSSVer_Click

End Sub
I keep getting this message on the cmdCSSbtnAll:
Syntax error (missing operator) in query expression '(AND ([YearOfService] &
[MonthOfService])>=200301 AND ([YearOfService] &[MonthOfService])<=200308
It doesn't matter what dates I choose it always comes up with this message
on this button,
It retrieves the same report, the only criteria it doesn't filter is the
program, because I want to retrieve all of the programs.

Here is the underlying query for the report:
SELECT tblSurvey.ID, tblSurvey.ProgramID, tblSurvey.ReceivedDate,
tblSurvey.MonthOfService, tblSurvey.YearOfService,
tblSurvey.SeenAndAdmitted, tblSurvey.QuestionsAnswered,
tblSurvey.ImproveAccess, tblSurvey.EducationalPrograms,
tblSurvey.GroupRelevance, tblSurvey.ProgramComments,
tblSurvey.KnowledgeableStaff, tblSurvey.StaffRunningGroup,
tblSurvey.StaffIndividualProbs, tblSurvey.StaffComments, tblSurvey.Return,
tblSurvey.UseSinceTreatment, tblSurvey.AdditionalComments,
tblSurvey.DateCreated, tblPrograms.Program, tblPrograms.ID
FROM tblPrograms LEFT JOIN tblSurvey ON tblPrograms.ID = tblSurvey.ProgramID
ORDER BY tblPrograms.Program, tblSurvey.DateCreated,
tblSurvey.YearOfService, tblSurvey.MonthOfService;
 
I solved it after I looked at it a little closer I just had to change the
first line of the LinkCriteria to:
stLinkCriteria="([YearOfService] &[MonthOfService])>="&Me![cboReportStart]
Suzanne Wyatt said:
I have a form with 5 command buttons that have different functions they all
open one of 3 reports but in 2 different ways.

The first 3 buttons work fine, but the last 2 don't work properly. I keep
getting a syntax error in query expression. I am basically using the same
query expression on all just the linkcriteria is different on the last 2.

Here is the VB I am having a problem with:
Private Sub cmdCSSbtnall_Click()
On Error GoTo Err_cmdCSSbtnall_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "rptSurveyDetailReport"
stLinkCriteria = stLinkCriteria & " AND " & "([YearOfService] &
[MonthOfService]) >= " & Me![cboReportStart]
stLinkCriteria = stLinkCriteria & " AND " & "([YearOfService] &
[MonthOfService]) <= " & Me![cboReportEnd]

DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria


Exit_cmdCSSbtnall_Click:
Exit Sub

Err_cmdCSSbtnall_Click:
MsgBox Err.Description
Resume Exit_cmdCSSbtnall_Click
And here is one of the command buttons I am not having a problem with:
Private Sub btnCSSVer_Click()
On Error GoTo Err_btnCSSVer_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "rptSurveyDetailReport"
stLinkCriteria = "[ProgramID]=" & Me![lstGroup]
stLinkCriteria = stLinkCriteria & " AND " & "([YearOfService] &
[MonthOfService]) >= " & Me![cboReportStart]
stLinkCriteria = stLinkCriteria & " AND " & "([YearOfService] &
[MonthOfService]) <= " & Me![cboReportEnd]

DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_btnCSSVer_Click:
Exit Sub

Err_btnCSSVer_Click:
MsgBox Err.Description
Resume Exit_btnCSSVer_Click

End Sub
I keep getting this message on the cmdCSSbtnAll:
Syntax error (missing operator) in query expression '(AND ([YearOfService] &
[MonthOfService])>=200301 AND ([YearOfService] &[MonthOfService])<=200308
It doesn't matter what dates I choose it always comes up with this message
on this button,
It retrieves the same report, the only criteria it doesn't filter is the
program, because I want to retrieve all of the programs.

Here is the underlying query for the report:
SELECT tblSurvey.ID, tblSurvey.ProgramID, tblSurvey.ReceivedDate,
tblSurvey.MonthOfService, tblSurvey.YearOfService,
tblSurvey.SeenAndAdmitted, tblSurvey.QuestionsAnswered,
tblSurvey.ImproveAccess, tblSurvey.EducationalPrograms,
tblSurvey.GroupRelevance, tblSurvey.ProgramComments,
tblSurvey.KnowledgeableStaff, tblSurvey.StaffRunningGroup,
tblSurvey.StaffIndividualProbs, tblSurvey.StaffComments, tblSurvey.Return,
tblSurvey.UseSinceTreatment, tblSurvey.AdditionalComments,
tblSurvey.DateCreated, tblPrograms.Program, tblPrograms.ID
FROM tblPrograms LEFT JOIN tblSurvey ON tblPrograms.ID = tblSurvey.ProgramID
ORDER BY tblPrograms.Program, tblSurvey.DateCreated,
tblSurvey.YearOfService, tblSurvey.MonthOfService;
Click to expand...
 
Back
Top