Sql Statement errors

  • Thread starter LeftyLeo via AccessMonster.com
  • Start date
L

LeftyLeo via AccessMonster.com

I am having trouble with the following code, it gives me a missing operator
error on the first line can anyone help me figure it out.

Private Sub cmdFocusArea_Click()
Dim strSql As String
Dim qdf As QueryDef
Dim blWhere As Boolean

'Create query definitions for qryReports recordsource of rptStoreBudget
DoCmd.Hourglass True
DoCmd.SetWarnings False
If Not IsNull(Me.cboQtr.Value) Then
strQtrFilter = PeriodsPerQtr(Me.cboQtr.Value)
End If

Errors here I am suspecting the Is Not Null line
'Write begining of SQL statement
strSql = "SELECT tblMasterTable.fldDate, tblMasterTable.fldLocation,
tblMasterTable.fldZone, tblQuarters.fldQuarter, tblMasterTable.fldPeriod,
tblMasterTable.fldYear, tblMasterTable.fldOrganization, tblMasterTable.[Focus
Area], tblMasterTable.fldAmount FROM tblMasterTable INNER JOIN tblQuarters ON
tblMasterTable.fldPeriod=tblQuarters.fldPeriod WHERE (tblMasterTable.[Focus
Area]) Is Not Null "

'Set Where Clause variable to False to initialize
blWhere = False

'User has entered criteria for the Year
If Not Me.cboYear.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & "(tblMasterTable.fldYear)=[forms]![frmAdminReport]!
[cboYear]"

End If

'User has entered criteria for the Quarter, add to Where clause
If Not Me.cboQtr.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & " (tblQuarters.fldQuarter)=[forms]![frmAdminReport]!
[cboQtr]"
' strSql = strSql & "(tblMasterTable.fldPeriod) =" & strQtrFilter
End If

'User has entered criteria for the Period
If Not Me.cboPeriod.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & " (tblMasterTable.fldPeriod)=[forms]![frmAdminReport]!
[cboPeriod]"
End If

'User has entered criteria for the Zone
If Not Me.cboZone.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & " (tblMasterTable.fldZone)=[forms]![frmAdminReport]!
[cboZone]"
End If

'User has entered criteria for the Location
If Not Me.cboLocation.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & " (tblMasterTable.fldLocation)=[forms]![frmAdminReport]
![cboLocation]"
End If

strSql = strSql & ";"


Set qdf = CurrentDb.QueryDefs("qryFocusAreas")
qdf.SQL = strSql
qdf.Close

If DCount("*", "qryFocusAreas") > 0 Then
DoCmd.OpenReport "rptFocusAreas", acViewPreview
Else
MsgBox "There is no data available for criteria selected. Revise and
try again. Thank-you!", vbExclamation, "No Data"
End If
DoCmd.Hourglass False
DoCmd.SetWarnings True

End Sub
 
L

LeftyLeo via AccessMonster.com

Never mind I figured it out the blWhere needs to be True right after the
first strSQL statement.
I am having trouble with the following code, it gives me a missing operator
error on the first line can anyone help me figure it out.

Private Sub cmdFocusArea_Click()
Dim strSql As String
Dim qdf As QueryDef
Dim blWhere As Boolean

'Create query definitions for qryReports recordsource of rptStoreBudget
DoCmd.Hourglass True
DoCmd.SetWarnings False
If Not IsNull(Me.cboQtr.Value) Then
strQtrFilter = PeriodsPerQtr(Me.cboQtr.Value)
End If

Errors here I am suspecting the Is Not Null line
'Write begining of SQL statement
strSql = "SELECT tblMasterTable.fldDate, tblMasterTable.fldLocation,
tblMasterTable.fldZone, tblQuarters.fldQuarter, tblMasterTable.fldPeriod,
tblMasterTable.fldYear, tblMasterTable.fldOrganization, tblMasterTable.[Focus
Area], tblMasterTable.fldAmount FROM tblMasterTable INNER JOIN tblQuarters ON
tblMasterTable.fldPeriod=tblQuarters.fldPeriod WHERE (tblMasterTable.[Focus
Area]) Is Not Null "

'Set Where Clause variable to False to initialize
blWhere = False 'This needs to be True

'User has entered criteria for the Year
If Not Me.cboYear.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & "(tblMasterTable.fldYear)=[forms]![frmAdminReport]!
[cboYear]"

End If

'User has entered criteria for the Quarter, add to Where clause
If Not Me.cboQtr.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & " (tblQuarters.fldQuarter)=[forms]![frmAdminReport]!
[cboQtr]"
' strSql = strSql & "(tblMasterTable.fldPeriod) =" & strQtrFilter
End If

'User has entered criteria for the Period
If Not Me.cboPeriod.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & " (tblMasterTable.fldPeriod)=[forms]![frmAdminReport]!
[cboPeriod]"
End If

'User has entered criteria for the Zone
If Not Me.cboZone.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & " (tblMasterTable.fldZone)=[forms]![frmAdminReport]!
[cboZone]"
End If

'User has entered criteria for the Location
If Not Me.cboLocation.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & " (tblMasterTable.fldLocation)=[forms]![frmAdminReport]
![cboLocation]"
End If

strSql = strSql & ";"


Set qdf = CurrentDb.QueryDefs("qryFocusAreas")
qdf.SQL = strSql
qdf.Close

If DCount("*", "qryFocusAreas") > 0 Then
DoCmd.OpenReport "rptFocusAreas", acViewPreview
Else
MsgBox "There is no data available for criteria selected. Revise and
try again. Thank-you!", vbExclamation, "No Data"
End If
DoCmd.Hourglass False
DoCmd.SetWarnings True

End Sub
Click to expand...
 
G

Guest

Actually, you can delete anything that deals with the variable "blWhere".

Your first "strSql" line already has a "WHERE" clause. Then in "IF"
statements, if blWhere is false , the another "WHERE" is added which results
in an error.

But there is no line that sets blWhere to False. If blWhere is never False,
you don't need to check if it is true.

Another thing I noticed. The value property is the default property, so you
don't need to add it; ie you can use "Me.cboQtr" instead of
"Me.cboQtr.Value". They are the same.

I edited your code..... here's what it looks like:
(watch for line wrap)

'------beg code-----------
Private Sub cmdFocusArea_Click()
Dim strSql As String
Dim qdf As QueryDef

'Create query definitions for qryReports recordsource of rptStoreBudget
DoCmd.Hourglass True
DoCmd.SetWarnings False

' if you don't use this, it can be deleted
If Not IsNull(Me.cboQtr.Value) Then
strQtrFilter = PeriodsPerQtr(Me.cboQtr.Value)
End If

'Write begining of SQL statement
strSql = "SELECT tblMasterTable.fldDate, tblMasterTable.fldLocation,
tblMasterTable.fldZone, tblQuarters.fldQuarter, tblMasterTable.fldPeriod,
tblMasterTable.fldYear, tblMasterTable.fldOrganization, tblMasterTable.[Focus
Area], tblMasterTable.fldAmount"

strSql = strSql & " FROM tblMasterTable INNER JOIN tblQuarters ON
tblMasterTable.fldPeriod=tblQuarters.fldPeriod "

strSql = strSql & " WHERE (tblMasterTable.[Focus Area]) Is Not Null "

'User has entered criteria for the Year
If Not Me.cboYear.Value = "" Then
strSql = strSql & " AND
(tblMasterTable.fldYear)=[forms]![frmAdminReport]![cboYear]"
End If

'User has entered criteria for the Quarter, add to Where clause
If Not Me.cboQtr.Value = "" Then
strSql = strSql & " AND
(tblQuarters.fldQuarter)=[forms]![frmAdminReport]![cboQtr]"
' strSql = strSql & "(tblMasterTable.fldPeriod) =" & strQtrFilter
End If

'User has entered criteria for the Period
If Not Me.cboPeriod.Value = "" Then
strSql = strSql & " AND
(tblMasterTable.fldPeriod)=[forms]![frmAdminReport]![cboPeriod]"
End If

'User has entered criteria for the Zone
If Not Me.cboZone.Value = "" Then
strSql = strSql & " AND
(tblMasterTable.fldZone)=[forms]![frmAdminReport]![cboZone]"
End If

'User has entered criteria for the Location
If Not Me.cboLocation.Value = "" Then
strSql = strSql & " AND
(tblMasterTable.fldLocation)=[forms]![frmAdminReport]![cboLocation]"
End If

strSql = strSql & ";"

Set qdf = CurrentDb.QueryDefs("qryFocusAreas")
qdf.SQL = strSql
qdf.Close

DoCmd.Hourglass False
DoCmd.SetWarnings True

If DCount("*", "qryFocusAreas") > 0 Then
DoCmd.OpenReport "rptFocusAreas", acViewPreview
Else
MsgBox "There is no data available for criteria selected. Revise and
try again. Thank-you!", vbExclamation, "No Data"
End If

End Sub
'------end code-----------

I never set warnings off. Instead, you should have an Error handling routine.

And instead of using the DCount() function, you could open the report after
setting the SQL for the QueryDefs. You would put the message box in the No
Data event of the report to inform you that no data was found.

Just my thoughts......

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


LeftyLeo via AccessMonster.com said:
Never mind I figured it out the blWhere needs to be True right after the
first strSQL statement.
I am having trouble with the following code, it gives me a missing operator
error on the first line can anyone help me figure it out.

Private Sub cmdFocusArea_Click()
Dim strSql As String
Dim qdf As QueryDef
Dim blWhere As Boolean

'Create query definitions for qryReports recordsource of rptStoreBudget
DoCmd.Hourglass True
DoCmd.SetWarnings False
If Not IsNull(Me.cboQtr.Value) Then
strQtrFilter = PeriodsPerQtr(Me.cboQtr.Value)
End If

Errors here I am suspecting the Is Not Null line
'Write begining of SQL statement
strSql = "SELECT tblMasterTable.fldDate, tblMasterTable.fldLocation,
tblMasterTable.fldZone, tblQuarters.fldQuarter, tblMasterTable.fldPeriod,
tblMasterTable.fldYear, tblMasterTable.fldOrganization, tblMasterTable.[Focus
Area], tblMasterTable.fldAmount FROM tblMasterTable INNER JOIN tblQuarters ON
tblMasterTable.fldPeriod=tblQuarters.fldPeriod WHERE (tblMasterTable.[Focus
Area]) Is Not Null "

'Set Where Clause variable to False to initialize
blWhere = False 'This needs to be True

'User has entered criteria for the Year
If Not Me.cboYear.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & "(tblMasterTable.fldYear)=[forms]![frmAdminReport]!
[cboYear]"

End If

'User has entered criteria for the Quarter, add to Where clause
If Not Me.cboQtr.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & " (tblQuarters.fldQuarter)=[forms]![frmAdminReport]!
[cboQtr]"
' strSql = strSql & "(tblMasterTable.fldPeriod) =" & strQtrFilter
End If

'User has entered criteria for the Period
If Not Me.cboPeriod.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & " (tblMasterTable.fldPeriod)=[forms]![frmAdminReport]!
[cboPeriod]"
End If

'User has entered criteria for the Zone
If Not Me.cboZone.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & " (tblMasterTable.fldZone)=[forms]![frmAdminReport]!
[cboZone]"
End If

'User has entered criteria for the Location
If Not Me.cboLocation.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & " (tblMasterTable.fldLocation)=[forms]![frmAdminReport]
![cboLocation]"
End If

strSql = strSql & ";"


Set qdf = CurrentDb.QueryDefs("qryFocusAreas")
qdf.SQL = strSql
qdf.Close

If DCount("*", "qryFocusAreas") > 0 Then
DoCmd.OpenReport "rptFocusAreas", acViewPreview
Else
MsgBox "There is no data available for criteria selected. Revise and
try again. Thank-you!", vbExclamation, "No Data"
End If
DoCmd.Hourglass False
DoCmd.SetWarnings True

End Sub
Click to expand...
Click to expand...
 
P

Pieter Wijnen

I disagree, Me.cboQtr & Me.cboQtr.Value are not the same.
Me.cboQtr, means "invoke the default property (if any)" which happens to be
the .Value property for some types of controls.
Using the explicit property is faster & is also useful for documenting your
code.
I Agree it's been a long time since Microsoft have been tossing default
properties around, but still.

Pieter

Steve Sanford said:
Actually, you can delete anything that deals with the variable "blWhere".

Your first "strSql" line already has a "WHERE" clause. Then in "IF"
statements, if blWhere is false , the another "WHERE" is added which
results
in an error.

But there is no line that sets blWhere to False. If blWhere is never
False,
you don't need to check if it is true.

Another thing I noticed. The value property is the default property, so
you
don't need to add it; ie you can use "Me.cboQtr" instead of
"Me.cboQtr.Value". They are the same.

I edited your code..... here's what it looks like:
(watch for line wrap)

'------beg code-----------
Private Sub cmdFocusArea_Click()
Dim strSql As String
Dim qdf As QueryDef

'Create query definitions for qryReports recordsource of rptStoreBudget
DoCmd.Hourglass True
DoCmd.SetWarnings False

' if you don't use this, it can be deleted
If Not IsNull(Me.cboQtr.Value) Then
strQtrFilter = PeriodsPerQtr(Me.cboQtr.Value)
End If

'Write begining of SQL statement
strSql = "SELECT tblMasterTable.fldDate, tblMasterTable.fldLocation,
tblMasterTable.fldZone, tblQuarters.fldQuarter, tblMasterTable.fldPeriod,
tblMasterTable.fldYear, tblMasterTable.fldOrganization,
tblMasterTable.[Focus
Area], tblMasterTable.fldAmount"

strSql = strSql & " FROM tblMasterTable INNER JOIN tblQuarters ON
tblMasterTable.fldPeriod=tblQuarters.fldPeriod "

strSql = strSql & " WHERE (tblMasterTable.[Focus Area]) Is Not Null "

'User has entered criteria for the Year
If Not Me.cboYear.Value = "" Then
strSql = strSql & " AND
(tblMasterTable.fldYear)=[forms]![frmAdminReport]![cboYear]"
End If

'User has entered criteria for the Quarter, add to Where clause
If Not Me.cboQtr.Value = "" Then
strSql = strSql & " AND
(tblQuarters.fldQuarter)=[forms]![frmAdminReport]![cboQtr]"
' strSql = strSql & "(tblMasterTable.fldPeriod) =" & strQtrFilter
End If

'User has entered criteria for the Period
If Not Me.cboPeriod.Value = "" Then
strSql = strSql & " AND
(tblMasterTable.fldPeriod)=[forms]![frmAdminReport]![cboPeriod]"
End If

'User has entered criteria for the Zone
If Not Me.cboZone.Value = "" Then
strSql = strSql & " AND
(tblMasterTable.fldZone)=[forms]![frmAdminReport]![cboZone]"
End If

'User has entered criteria for the Location
If Not Me.cboLocation.Value = "" Then
strSql = strSql & " AND
(tblMasterTable.fldLocation)=[forms]![frmAdminReport]![cboLocation]"
End If

strSql = strSql & ";"

Set qdf = CurrentDb.QueryDefs("qryFocusAreas")
qdf.SQL = strSql
qdf.Close

DoCmd.Hourglass False
DoCmd.SetWarnings True

If DCount("*", "qryFocusAreas") > 0 Then
DoCmd.OpenReport "rptFocusAreas", acViewPreview
Else
MsgBox "There is no data available for criteria selected. Revise and
try again. Thank-you!", vbExclamation, "No Data"
End If

End Sub
'------end code-----------

I never set warnings off. Instead, you should have an Error handling
routine.

And instead of using the DCount() function, you could open the report
after
setting the SQL for the QueryDefs. You would put the message box in the No
Data event of the report to inform you that no data was found.

Just my thoughts......

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


LeftyLeo via AccessMonster.com said:
Never mind I figured it out the blWhere needs to be True right after the
first strSQL statement.
I am having trouble with the following code, it gives me a missing
operator
error on the first line can anyone help me figure it out.

Private Sub cmdFocusArea_Click()
Dim strSql As String
Dim qdf As QueryDef
Dim blWhere As Boolean

'Create query definitions for qryReports recordsource of rptStoreBudget
DoCmd.Hourglass True
DoCmd.SetWarnings False
If Not IsNull(Me.cboQtr.Value) Then
strQtrFilter = PeriodsPerQtr(Me.cboQtr.Value)
End If

Errors here I am suspecting the Is Not Null line
'Write begining of SQL statement
strSql = "SELECT tblMasterTable.fldDate, tblMasterTable.fldLocation,
tblMasterTable.fldZone, tblQuarters.fldQuarter,
tblMasterTable.fldPeriod,
tblMasterTable.fldYear, tblMasterTable.fldOrganization,
tblMasterTable.[Focus
Area], tblMasterTable.fldAmount FROM tblMasterTable INNER JOIN
tblQuarters ON
tblMasterTable.fldPeriod=tblQuarters.fldPeriod WHERE
(tblMasterTable.[Focus
Area]) Is Not Null "

'Set Where Clause variable to False to initialize
blWhere = False 'This needs to be True

'User has entered criteria for the Year
If Not Me.cboYear.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & "(tblMasterTable.fldYear)=[forms]![frmAdminReport]!
[cboYear]"

End If

'User has entered criteria for the Quarter, add to Where clause
If Not Me.cboQtr.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & "
(tblQuarters.fldQuarter)=[forms]![frmAdminReport]!
[cboQtr]"
' strSql = strSql & "(tblMasterTable.fldPeriod) =" & strQtrFilter
End If

'User has entered criteria for the Period
If Not Me.cboPeriod.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & "
(tblMasterTable.fldPeriod)=[forms]![frmAdminReport]!
[cboPeriod]"
End If

'User has entered criteria for the Zone
If Not Me.cboZone.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & "
(tblMasterTable.fldZone)=[forms]![frmAdminReport]!
[cboZone]"
End If

'User has entered criteria for the Location
If Not Me.cboLocation.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & "
(tblMasterTable.fldLocation)=[forms]![frmAdminReport]
![cboLocation]"
End If

strSql = strSql & ";"


Set qdf = CurrentDb.QueryDefs("qryFocusAreas")
qdf.SQL = strSql
qdf.Close

If DCount("*", "qryFocusAreas") > 0 Then
DoCmd.OpenReport "rptFocusAreas", acViewPreview
Else
MsgBox "There is no data available for criteria selected.
Revise and
try again. Thank-you!", vbExclamation, "No Data"
End If
DoCmd.Hourglass False
DoCmd.SetWarnings True

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

OK, I should have said "....Me.cboQtr & Me.cboQtr.Value gives you the same
result".

With 3 GHz computers, you will save more time relying on the default
property than typing ".Value".

I've been reading this NG for several years and many of the MVP's have
stated is is OK not to type ".Value" since the value property is the default
for a control (that has a value property).

But using ".Value" might be required in .Net or VB. So far, I only use VBA
(Access).

In any case, I never type ".Value"; personal preference.. :)

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Pieter Wijnen said:
I disagree, Me.cboQtr & Me.cboQtr.Value are not the same.
Me.cboQtr, means "invoke the default property (if any)" which happens to be
the .Value property for some types of controls.
Using the explicit property is faster & is also useful for documenting your
code.
I Agree it's been a long time since Microsoft have been tossing default
properties around, but still.

Pieter

Steve Sanford said:
Actually, you can delete anything that deals with the variable "blWhere".

Your first "strSql" line already has a "WHERE" clause. Then in "IF"
statements, if blWhere is false , the another "WHERE" is added which
results
in an error.

But there is no line that sets blWhere to False. If blWhere is never
False,
you don't need to check if it is true.

Another thing I noticed. The value property is the default property, so
you
don't need to add it; ie you can use "Me.cboQtr" instead of
"Me.cboQtr.Value". They are the same.

I edited your code..... here's what it looks like:
(watch for line wrap)

'------beg code-----------
Private Sub cmdFocusArea_Click()
Dim strSql As String
Dim qdf As QueryDef

'Create query definitions for qryReports recordsource of rptStoreBudget
DoCmd.Hourglass True
DoCmd.SetWarnings False

' if you don't use this, it can be deleted
If Not IsNull(Me.cboQtr.Value) Then
strQtrFilter = PeriodsPerQtr(Me.cboQtr.Value)
End If

'Write begining of SQL statement
strSql = "SELECT tblMasterTable.fldDate, tblMasterTable.fldLocation,
tblMasterTable.fldZone, tblQuarters.fldQuarter, tblMasterTable.fldPeriod,
tblMasterTable.fldYear, tblMasterTable.fldOrganization,
tblMasterTable.[Focus
Area], tblMasterTable.fldAmount"

strSql = strSql & " FROM tblMasterTable INNER JOIN tblQuarters ON
tblMasterTable.fldPeriod=tblQuarters.fldPeriod "

strSql = strSql & " WHERE (tblMasterTable.[Focus Area]) Is Not Null "

'User has entered criteria for the Year
If Not Me.cboYear.Value = "" Then
strSql = strSql & " AND
(tblMasterTable.fldYear)=[forms]![frmAdminReport]![cboYear]"
End If

'User has entered criteria for the Quarter, add to Where clause
If Not Me.cboQtr.Value = "" Then
strSql = strSql & " AND
(tblQuarters.fldQuarter)=[forms]![frmAdminReport]![cboQtr]"
' strSql = strSql & "(tblMasterTable.fldPeriod) =" & strQtrFilter
End If

'User has entered criteria for the Period
If Not Me.cboPeriod.Value = "" Then
strSql = strSql & " AND
(tblMasterTable.fldPeriod)=[forms]![frmAdminReport]![cboPeriod]"
End If

'User has entered criteria for the Zone
If Not Me.cboZone.Value = "" Then
strSql = strSql & " AND
(tblMasterTable.fldZone)=[forms]![frmAdminReport]![cboZone]"
End If

'User has entered criteria for the Location
If Not Me.cboLocation.Value = "" Then
strSql = strSql & " AND
(tblMasterTable.fldLocation)=[forms]![frmAdminReport]![cboLocation]"
End If

strSql = strSql & ";"

Set qdf = CurrentDb.QueryDefs("qryFocusAreas")
qdf.SQL = strSql
qdf.Close

DoCmd.Hourglass False
DoCmd.SetWarnings True

If DCount("*", "qryFocusAreas") > 0 Then
DoCmd.OpenReport "rptFocusAreas", acViewPreview
Else
MsgBox "There is no data available for criteria selected. Revise and
try again. Thank-you!", vbExclamation, "No Data"
End If

End Sub
'------end code-----------

I never set warnings off. Instead, you should have an Error handling
routine.

And instead of using the DCount() function, you could open the report
after
setting the SQL for the QueryDefs. You would put the message box in the No
Data event of the report to inform you that no data was found.

Just my thoughts......

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


LeftyLeo via AccessMonster.com said:
Never mind I figured it out the blWhere needs to be True right after the
first strSQL statement.

LeftyLeo wrote:
I am having trouble with the following code, it gives me a missing
operator
error on the first line can anyone help me figure it out.

Private Sub cmdFocusArea_Click()
Dim strSql As String
Dim qdf As QueryDef
Dim blWhere As Boolean

'Create query definitions for qryReports recordsource of rptStoreBudget
DoCmd.Hourglass True
DoCmd.SetWarnings False
If Not IsNull(Me.cboQtr.Value) Then
strQtrFilter = PeriodsPerQtr(Me.cboQtr.Value)
End If

Errors here I am suspecting the Is Not Null line
'Write begining of SQL statement
strSql = "SELECT tblMasterTable.fldDate, tblMasterTable.fldLocation,
tblMasterTable.fldZone, tblQuarters.fldQuarter,
tblMasterTable.fldPeriod,
tblMasterTable.fldYear, tblMasterTable.fldOrganization,
tblMasterTable.[Focus
Area], tblMasterTable.fldAmount FROM tblMasterTable INNER JOIN
tblQuarters ON
tblMasterTable.fldPeriod=tblQuarters.fldPeriod WHERE
(tblMasterTable.[Focus
Area]) Is Not Null "

'Set Where Clause variable to False to initialize
blWhere = False 'This needs to be True

'User has entered criteria for the Year
If Not Me.cboYear.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & "(tblMasterTable.fldYear)=[forms]![frmAdminReport]!
[cboYear]"

End If

'User has entered criteria for the Quarter, add to Where clause
If Not Me.cboQtr.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & "
(tblQuarters.fldQuarter)=[forms]![frmAdminReport]!
[cboQtr]"
' strSql = strSql & "(tblMasterTable.fldPeriod) =" & strQtrFilter
End If

'User has entered criteria for the Period
If Not Me.cboPeriod.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & "
(tblMasterTable.fldPeriod)=[forms]![frmAdminReport]!
[cboPeriod]"
End If

'User has entered criteria for the Zone
If Not Me.cboZone.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & "
(tblMasterTable.fldZone)=[forms]![frmAdminReport]!
[cboZone]"
End If

'User has entered criteria for the Location
If Not Me.cboLocation.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & "
(tblMasterTable.fldLocation)=[forms]![frmAdminReport]
![cboLocation]"
End If

strSql = strSql & ";"


Set qdf = CurrentDb.QueryDefs("qryFocusAreas")
qdf.SQL = strSql
qdf.Close

If DCount("*", "qryFocusAreas") > 0 Then
DoCmd.OpenReport "rptFocusAreas", acViewPreview
Else
MsgBox "There is no data available for criteria selected.
Revise and
try again. Thank-you!", vbExclamation, "No Data"
End If
DoCmd.Hourglass False
DoCmd.SetWarnings True

End Sub
Click to expand...
Click to expand...
Click to expand...
 
P

Pieter Wijnen

Fair enough <g>
But saving time has little to do with what processor you have, always test
(as far as possible: with the worst bandwidth, crummiest computer, most data
you can)
I learnt that the hard way with 200+ users on a 64k link

Pieter

Steve Sanford said:
OK, I should have said "....Me.cboQtr & Me.cboQtr.Value gives you the
same
result".

With 3 GHz computers, you will save more time relying on the default
property than typing ".Value".

I've been reading this NG for several years and many of the MVP's have
stated is is OK not to type ".Value" since the value property is the
default
for a control (that has a value property).

But using ".Value" might be required in .Net or VB. So far, I only use VBA
(Access).

In any case, I never type ".Value"; personal preference.. :)

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Pieter Wijnen said:
I disagree, Me.cboQtr & Me.cboQtr.Value are not the same.
Me.cboQtr, means "invoke the default property (if any)" which happens to
be
the .Value property for some types of controls.
Using the explicit property is faster & is also useful for documenting
your
code.
I Agree it's been a long time since Microsoft have been tossing default
properties around, but still.

Pieter

Steve Sanford said:
Actually, you can delete anything that deals with the variable
"blWhere".

Your first "strSql" line already has a "WHERE" clause. Then in "IF"
statements, if blWhere is false , the another "WHERE" is added which
results
in an error.

But there is no line that sets blWhere to False. If blWhere is never
False,
you don't need to check if it is true.

Another thing I noticed. The value property is the default property, so
you
don't need to add it; ie you can use "Me.cboQtr" instead of
"Me.cboQtr.Value". They are the same.

I edited your code..... here's what it looks like:
(watch for line wrap)

'------beg code-----------
Private Sub cmdFocusArea_Click()
Dim strSql As String
Dim qdf As QueryDef

'Create query definitions for qryReports recordsource of
rptStoreBudget
DoCmd.Hourglass True
DoCmd.SetWarnings False

' if you don't use this, it can be deleted
If Not IsNull(Me.cboQtr.Value) Then
strQtrFilter = PeriodsPerQtr(Me.cboQtr.Value)
End If

'Write begining of SQL statement
strSql = "SELECT tblMasterTable.fldDate, tblMasterTable.fldLocation,
tblMasterTable.fldZone, tblQuarters.fldQuarter,
tblMasterTable.fldPeriod,
tblMasterTable.fldYear, tblMasterTable.fldOrganization,
tblMasterTable.[Focus
Area], tblMasterTable.fldAmount"

strSql = strSql & " FROM tblMasterTable INNER JOIN tblQuarters ON
tblMasterTable.fldPeriod=tblQuarters.fldPeriod "

strSql = strSql & " WHERE (tblMasterTable.[Focus Area]) Is Not Null "

'User has entered criteria for the Year
If Not Me.cboYear.Value = "" Then
strSql = strSql & " AND
(tblMasterTable.fldYear)=[forms]![frmAdminReport]![cboYear]"
End If

'User has entered criteria for the Quarter, add to Where clause
If Not Me.cboQtr.Value = "" Then
strSql = strSql & " AND
(tblQuarters.fldQuarter)=[forms]![frmAdminReport]![cboQtr]"
' strSql = strSql & "(tblMasterTable.fldPeriod) =" &
strQtrFilter
End If

'User has entered criteria for the Period
If Not Me.cboPeriod.Value = "" Then
strSql = strSql & " AND
(tblMasterTable.fldPeriod)=[forms]![frmAdminReport]![cboPeriod]"
End If

'User has entered criteria for the Zone
If Not Me.cboZone.Value = "" Then
strSql = strSql & " AND
(tblMasterTable.fldZone)=[forms]![frmAdminReport]![cboZone]"
End If

'User has entered criteria for the Location
If Not Me.cboLocation.Value = "" Then
strSql = strSql & " AND
(tblMasterTable.fldLocation)=[forms]![frmAdminReport]![cboLocation]"
End If

strSql = strSql & ";"

Set qdf = CurrentDb.QueryDefs("qryFocusAreas")
qdf.SQL = strSql
qdf.Close

DoCmd.Hourglass False
DoCmd.SetWarnings True

If DCount("*", "qryFocusAreas") > 0 Then
DoCmd.OpenReport "rptFocusAreas", acViewPreview
Else
MsgBox "There is no data available for criteria selected. Revise
and
try again. Thank-you!", vbExclamation, "No Data"
End If

End Sub
'------end code-----------

I never set warnings off. Instead, you should have an Error handling
routine.

And instead of using the DCount() function, you could open the report
after
setting the SQL for the QueryDefs. You would put the message box in the
No
Data event of the report to inform you that no data was found.

Just my thoughts......

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

Never mind I figured it out the blWhere needs to be True right after
the
first strSQL statement.

LeftyLeo wrote:
I am having trouble with the following code, it gives me a missing
operator
error on the first line can anyone help me figure it out.

Private Sub cmdFocusArea_Click()
Dim strSql As String
Dim qdf As QueryDef
Dim blWhere As Boolean

'Create query definitions for qryReports recordsource of
rptStoreBudget
DoCmd.Hourglass True
DoCmd.SetWarnings False
If Not IsNull(Me.cboQtr.Value) Then
strQtrFilter = PeriodsPerQtr(Me.cboQtr.Value)
End If

Errors here I am suspecting the Is Not Null line
'Write begining of SQL statement
strSql = "SELECT tblMasterTable.fldDate, tblMasterTable.fldLocation,
tblMasterTable.fldZone, tblQuarters.fldQuarter,
tblMasterTable.fldPeriod,
tblMasterTable.fldYear, tblMasterTable.fldOrganization,
tblMasterTable.[Focus
Area], tblMasterTable.fldAmount FROM tblMasterTable INNER JOIN
tblQuarters ON
tblMasterTable.fldPeriod=tblQuarters.fldPeriod WHERE
(tblMasterTable.[Focus
Area]) Is Not Null "

'Set Where Clause variable to False to initialize
blWhere = False 'This needs to be True

'User has entered criteria for the Year
If Not Me.cboYear.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql &
"(tblMasterTable.fldYear)=[forms]![frmAdminReport]!
[cboYear]"

End If

'User has entered criteria for the Quarter, add to Where clause
If Not Me.cboQtr.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & "
(tblQuarters.fldQuarter)=[forms]![frmAdminReport]!
[cboQtr]"
' strSql = strSql & "(tblMasterTable.fldPeriod) =" & strQtrFilter
End If

'User has entered criteria for the Period
If Not Me.cboPeriod.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & "
(tblMasterTable.fldPeriod)=[forms]![frmAdminReport]!
[cboPeriod]"
End If

'User has entered criteria for the Zone
If Not Me.cboZone.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & "
(tblMasterTable.fldZone)=[forms]![frmAdminReport]!
[cboZone]"
End If

'User has entered criteria for the Location
If Not Me.cboLocation.Value = "" Then
If blWhere = True Then
strSql = strSql & " AND "
Else
strSql = strSql & " WHERE "
blWhere = True
End If

strSql = strSql & "
(tblMasterTable.fldLocation)=[forms]![frmAdminReport]
![cboLocation]"
End If

strSql = strSql & ";"


Set qdf = CurrentDb.QueryDefs("qryFocusAreas")
qdf.SQL = strSql
qdf.Close

If DCount("*", "qryFocusAreas") > 0 Then
DoCmd.OpenReport "rptFocusAreas", acViewPreview
Else
MsgBox "There is no data available for criteria selected.
Revise and
try again. Thank-you!", vbExclamation, "No Data"
End If
DoCmd.Hourglass False
DoCmd.SetWarnings True

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Export Filtered Form Data To Excel 0
VBA code issue...Would love expert help. 5
What's wrong w/ this SQL stmt? 1
Modify sql statement 2
Syntax error in Update statement 3
Pls help, my VBA does not work 3
Help with code "Type Mismatch" 6
Syntax error (missing operator) 4

Top