Scope issue? Variables passed from form to report with OpenArgs

Access 2003.  I'm trying to pass values for two variables to a report
from a form but the controls on the report are giving me "#Name?"
errors wherever the passed variables are used.

Here's the code on the form
Private Sub cmdPreviewClaim_Click()
 Dim strOpenArgs As String
 strOpenArgs = Forms!frmMain.Form.cboxPlanYear & "|" & Forms!
frmMain.Form.currSetAside
 DoCmd.OpenReport "rptClaim", acViewPreview, , , , strOpenArgs
End Sub

Here's the Open event of the report (ParseText() is a module level
function to parse strings with a "|" delimiter).  The variables are
DIMmed as public variables in the Declarations section of the report
code.
Private Sub Report_Open(Cancel As Integer)
 If Not IsNull(Me.OpenArgs) Then
  intCAFEPlanYear = ParseText(Me.OpenArgs, 0)
  currCAFESetAside = ParseText(Me.OpenArgs, 1)
 End If
End Sub

There aren't any name conflicts between controls in the report and
names for the passed variables.  It doesn't seem to matter where
(module vs. report) that the variables are DIMmed.  If Access pops up
the window that asks for values for these variables and one enters
values for them, the report does what it's supposed to do.  But if
values for the variables come from code, the report controls (which
are text boxes with experssions as the ControlSource) show #Name?
errors.

It's gotta be simple but I'm stumped.

Richard

0
rwfreeman
5/24/2007 12:20:04 PM
access.formscoding 7493 articles. 0 followers. Follow

6 Replies
885 Views

Similar Articles

[PageSpeed] 7

How are you using the named variables in the report? What are the control 
source property values for the controls that show the error?

What data type is ParseText function declared as?

-- 

        Ken Snell
<MS ACCESS MVP>


"rwfreeman" <deltabeans@comcast.net> wrote in message 
news:1180009204.338063.316560@w5g2000hsg.googlegroups.com...
> Access 2003.  I'm trying to pass values for two variables to a report
> from a form but the controls on the report are giving me "#Name?"
> errors wherever the passed variables are used.
>
> Here's the code on the form
> Private Sub cmdPreviewClaim_Click()
> Dim strOpenArgs As String
> strOpenArgs = Forms!frmMain.Form.cboxPlanYear & "|" & Forms!
> frmMain.Form.currSetAside
> DoCmd.OpenReport "rptClaim", acViewPreview, , , , strOpenArgs
> End Sub
>
> Here's the Open event of the report (ParseText() is a module level
> function to parse strings with a "|" delimiter).  The variables are
> DIMmed as public variables in the Declarations section of the report
> code.
> Private Sub Report_Open(Cancel As Integer)
> If Not IsNull(Me.OpenArgs) Then
>  intCAFEPlanYear = ParseText(Me.OpenArgs, 0)
>  currCAFESetAside = ParseText(Me.OpenArgs, 1)
> End If
> End Sub
>
> There aren't any name conflicts between controls in the report and
> names for the passed variables.  It doesn't seem to matter where
> (module vs. report) that the variables are DIMmed.  If Access pops up
> the window that asks for values for these variables and one enters
> values for them, the report does what it's supposed to do.  But if
> values for the variables come from code, the report controls (which
> are text boxes with experssions as the ControlSource) show #Name?
> errors.
>
> It's gotta be simple but I'm stumped.
>
> Richard
> 


0
Ken
5/24/2007 12:41:29 PM
On May 24, 8:41 am, "Ken Snell \(MVP\)"
<kthsneisll...@ncoomcastt.renaetl> wrote:
> How are you using the named variables in the report? What are the control
> source property values for the controls that show the error?
>
> What data type is ParseText function declared as?
>
> --
>
>         Ken Snell
> <MS ACCESS MVP>
>
> "rwfreeman" <deltabe...@comcast.net> wrote in message
>
> news:1180009204.338063.316560@w5g2000hsg.googlegroups.com...
>
>
>
> > Access 2003.  I'm trying to pass values for two variables to a report
> > from a form but the controls on the report are giving me "#Name?"
> > errors wherever the passed variables are used.
>
> > Here's the code on the form
> > Private Sub cmdPreviewClaim_Click()
> > Dim strOpenArgs As String
> > strOpenArgs = Forms!frmMain.Form.cboxPlanYear & "|" & Forms!
> > frmMain.Form.currSetAside
> > DoCmd.OpenReport "rptClaim", acViewPreview, , , , strOpenArgs
> > End Sub
>
> > Here's the Open event of the report (ParseText() is a module level
> > function to parse strings with a "|" delimiter).  The variables are
> > DIMmed as public variables in the Declarations section of the report
> > code.
> > Private Sub Report_Open(Cancel As Integer)
> > If Not IsNull(Me.OpenArgs) Then
> >  intCAFEPlanYear = ParseText(Me.OpenArgs, 0)
> >  currCAFESetAside = ParseText(Me.OpenArgs, 1)
> > End If
> > End Sub
>
> > There aren't any name conflicts between controls in the report and
> > names for the passed variables.  It doesn't seem to matter where
> > (module vs. report) that the variables are DIMmed.  If Access pops up
> > the window that asks for values for these variables and one enters
> > values for them, the report does what it's supposed to do.  But if
> > values for the variables come from code, the report controls (which
> > are text boxes with experssions as the ControlSource) show #Name?
> > errors.
>
> > It's gotta be simple but I'm stumped.
>
> > Richard- Hide quoted text -
>
> - Show quoted text -

Named variables are used in expressions in textbox controls.  An
example for the currClaimedToDate control (formatted as Currency) the
ControlSource is
=iif(Dcount("[ID]","tblExpenses","[Claimed] AND Year([ExpDate])=" &
intCafePlanYear)=0,0,DSUM("[Amount]","tblExpenses","[Claimed] AND
Year([ExpDate])=" & intCafePlanYear)  The intent of the expression is
to see if there are any records in the tblExpenses table for the
selected plan year that are marked as being claimed.  If so, sum the
amounts for the plan year, otherwise show a 0 value.  Each time I try
to fully enclose the Criteria in quotes (i.e. "criteria") I get an
error.

ParseText returns a Variant which when put into the declared variable
follows its declared variable type - integer for intCAFEPlanYear and
currency for currCAFESetAside (I checked this when stepping through
the code; their VarType()s were 2 and 6, respectively.

BTW, if it's easier to do this with a function as the control source,
could you indicate for me how to call the function from the
control's .ControlSource property and pass the intCAFEPlanYear
variable?

There's a line from one of my favorite Jimmy Buffett songs (he's
describing his attempts to mimic the bear dance, "It's so simple it
plum evaded me") that pretty well describes my level of angst over to
being unable to make Access do this apparently simple task.  Thanks
again for your time and patience.

Richard


0
rwfreeman
5/24/2007 2:34:37 PM
You cannot reference a VBA variable within a Control Source expression. You 
must write a public function that will read the value of the variable and 
return it as the function's value - -for example,:

Public Function GetMyCafePlanYearVariable() As Integer
GetMyCafePlanYearVariable = intCafePlanYear
End Function


This function should be put in the report's module so that it can see the 
variable, and you must declare the variable as global for the report module 
by putting this statement in the report's module's Declarations section:

        Private intCafePlanYear As Integer


Do the same for all such variables that you want to use.


Then, use the function in your control source expression:

=iif(Dcount("[ID]","tblExpenses","[Claimed] AND Year([ExpDate])=" &
GetMyCafePlanYearVariable())=0,0,DSUM("[Amount]","tblExpenses","[Claimed] 
AND
Year([ExpDate])=" & GetMyCafePlanYearVariable())


-- 

        Ken Snell
<MS ACCESS MVP>


"rwfreeman" <deltabeans@comcast.net> wrote in message 
news:1180017277.250047.229470@q66g2000hsg.googlegroups.com...
> On May 24, 8:41 am, "Ken Snell \(MVP\)"
> <kthsneisll...@ncoomcastt.renaetl> wrote:
>> How are you using the named variables in the report? What are the control
>> source property values for the controls that show the error?
>>
>> What data type is ParseText function declared as?
>>
>> --
>>
>>         Ken Snell
>> <MS ACCESS MVP>
>>
>> "rwfreeman" <deltabe...@comcast.net> wrote in message
>>
>> news:1180009204.338063.316560@w5g2000hsg.googlegroups.com...
>>
>>
>>
>> > Access 2003.  I'm trying to pass values for two variables to a report
>> > from a form but the controls on the report are giving me "#Name?"
>> > errors wherever the passed variables are used.
>>
>> > Here's the code on the form
>> > Private Sub cmdPreviewClaim_Click()
>> > Dim strOpenArgs As String
>> > strOpenArgs = Forms!frmMain.Form.cboxPlanYear & "|" & Forms!
>> > frmMain.Form.currSetAside
>> > DoCmd.OpenReport "rptClaim", acViewPreview, , , , strOpenArgs
>> > End Sub
>>
>> > Here's the Open event of the report (ParseText() is a module level
>> > function to parse strings with a "|" delimiter).  The variables are
>> > DIMmed as public variables in the Declarations section of the report
>> > code.
>> > Private Sub Report_Open(Cancel As Integer)
>> > If Not IsNull(Me.OpenArgs) Then
>> >  intCAFEPlanYear = ParseText(Me.OpenArgs, 0)
>> >  currCAFESetAside = ParseText(Me.OpenArgs, 1)
>> > End If
>> > End Sub
>>
>> > There aren't any name conflicts between controls in the report and
>> > names for the passed variables.  It doesn't seem to matter where
>> > (module vs. report) that the variables are DIMmed.  If Access pops up
>> > the window that asks for values for these variables and one enters
>> > values for them, the report does what it's supposed to do.  But if
>> > values for the variables come from code, the report controls (which
>> > are text boxes with experssions as the ControlSource) show #Name?
>> > errors.
>>
>> > It's gotta be simple but I'm stumped.
>>
>> > Richard- Hide quoted text -
>>
>> - Show quoted text -
>
> Named variables are used in expressions in textbox controls.  An
> example for the currClaimedToDate control (formatted as Currency) the
> ControlSource is
> =iif(Dcount("[ID]","tblExpenses","[Claimed] AND Year([ExpDate])=" &
> intCafePlanYear)=0,0,DSUM("[Amount]","tblExpenses","[Claimed] AND
> Year([ExpDate])=" & intCafePlanYear)  The intent of the expression is
> to see if there are any records in the tblExpenses table for the
> selected plan year that are marked as being claimed.  If so, sum the
> amounts for the plan year, otherwise show a 0 value.  Each time I try
> to fully enclose the Criteria in quotes (i.e. "criteria") I get an
> error.
>
> ParseText returns a Variant which when put into the declared variable
> follows its declared variable type - integer for intCAFEPlanYear and
> currency for currCAFESetAside (I checked this when stepping through
> the code; their VarType()s were 2 and 6, respectively.
>
> BTW, if it's easier to do this with a function as the control source,
> could you indicate for me how to call the function from the
> control's .ControlSource property and pass the intCAFEPlanYear
> variable?
>
> There's a line from one of my favorite Jimmy Buffett songs (he's
> describing his attempts to mimic the bear dance, "It's so simple it
> plum evaded me") that pretty well describes my level of angst over to
> being unable to make Access do this apparently simple task.  Thanks
> again for your time and patience.
>
> Richard
>
> 


0
Ken
5/24/2007 2:49:14 PM
On May 24, 10:49 am, "Ken Snell \(MVP\)"
<kthsneisll...@ncoomcastt.renaetl> wrote:
> You cannot reference a VBA variable within a Control Source expression. You
> must write a public function that will read the value of the variable and
> return it as the function's value - -for example,:
>
> Public Function GetMyCafePlanYearVariable() As Integer
> GetMyCafePlanYearVariable = intCafePlanYear
> End Function
>
> This function should be put in the report's module so that it can see the
> variable, and you must declare the variable as global for the report module
> by putting this statement in the report's module's Declarations section:
>
>         Private intCafePlanYear As Integer
>
> Do the same for all such variables that you want to use.
>
> Then, use the function in your control source expression:
>
> =iif(Dcount("[ID]","tblExpenses","[Claimed] AND Year([ExpDate])=" &
> GetMyCafePlanYearVariable())=0,0,DSUM("[Amount]","tblExpenses","[Claimed]
> AND
> Year([ExpDate])=" & GetMyCafePlanYearVariable())
>
> --
>
>         Ken Snell
> <MS ACCESS MVP>
>
> "rwfreeman" <deltabe...@comcast.net> wrote in message
>
> news:1180017277.250047.229470@q66g2000hsg.googlegroups.com...
>
>
>
> > On May 24, 8:41 am, "Ken Snell \(MVP\)"
> > <kthsneisll...@ncoomcastt.renaetl> wrote:
> >> How are you using the named variables in the report? What are the control
> >> source property values for the controls that show the error?
>
> >> What data type is ParseText function declared as?
>
> >> --
>
> >>         Ken Snell
> >> <MS ACCESS MVP>
>
> >> "rwfreeman" <deltabe...@comcast.net> wrote in message
>
> >>news:1180009204.338063.316560@w5g2000hsg.googlegroups.com...
>
> >> > Access 2003.  I'm trying to pass values for two variables to a report
> >> > from a form but the controls on the report are giving me "#Name?"
> >> > errors wherever the passed variables are used.
>
> >> > Here's the code on the form
> >> > Private Sub cmdPreviewClaim_Click()
> >> > Dim strOpenArgs As String
> >> > strOpenArgs = Forms!frmMain.Form.cboxPlanYear & "|" & Forms!
> >> > frmMain.Form.currSetAside
> >> > DoCmd.OpenReport "rptClaim", acViewPreview, , , , strOpenArgs
> >> > End Sub
>
> >> > Here's the Open event of the report (ParseText() is a module level
> >> > function to parse strings with a "|" delimiter).  The variables are
> >> > DIMmed as public variables in the Declarations section of the report
> >> > code.
> >> > Private Sub Report_Open(Cancel As Integer)
> >> > If Not IsNull(Me.OpenArgs) Then
> >> >  intCAFEPlanYear = ParseText(Me.OpenArgs, 0)
> >> >  currCAFESetAside = ParseText(Me.OpenArgs, 1)
> >> > End If
> >> > End Sub
>
> >> > There aren't any name conflicts between controls in the report and
> >> > names for the passed variables.  It doesn't seem to matter where
> >> > (module vs. report) that the variables are DIMmed.  If Access pops up
> >> > the window that asks for values for these variables and one enters
> >> > values for them, the report does what it's supposed to do.  But if
> >> > values for the variables come from code, the report controls (which
> >> > are text boxes with experssions as the ControlSource) show #Name?
> >> > errors.
>
> >> > It's gotta be simple but I'm stumped.
>
> >> > Richard- Hide quoted text -
>
> >> - Show quoted text -
>
> > Named variables are used in expressions in textbox controls.  An
> > example for the currClaimedToDate control (formatted as Currency) the
> > ControlSource is
> > =iif(Dcount("[ID]","tblExpenses","[Claimed] AND Year([ExpDate])=" &
> > intCafePlanYear)=0,0,DSUM("[Amount]","tblExpenses","[Claimed] AND
> > Year([ExpDate])=" & intCafePlanYear)  The intent of the expression is
> > to see if there are any records in the tblExpenses table for the
> > selected plan year that are marked as being claimed.  If so, sum the
> > amounts for the plan year, otherwise show a 0 value.  Each time I try
> > to fully enclose the Criteria in quotes (i.e. "criteria") I get an
> > error.
>
> > ParseText returns a Variant which when put into the declared variable
> > follows its declared variable type - integer for intCAFEPlanYear and
> > currency for currCAFESetAside (I checked this when stepping through
> > the code; their VarType()s were 2 and 6, respectively.
>
> > BTW, if it's easier to do this with a function as the control source,
> > could you indicate for me how to call the function from the
> > control's .ControlSource property and pass the intCAFEPlanYear
> > variable?
>
> > There's a line from one of my favorite Jimmy Buffett songs (he's
> > describing his attempts to mimic the bear dance, "It's so simple it
> > plum evaded me") that pretty well describes my level of angst over to
> > being unable to make Access do this apparently simple task.  Thanks
> > again for your time and patience.
>
> > Richard- Hide quoted text -
>
> - Show quoted text -

Ken - I'm making slow progress.  The assignment function and variable
declarations are just the ticket and now I can get some of the
controls on the report to use the passed value for the plan year.
However, where a control's .ControlSource is an expression such as the
DCOUNT() and DSUM() functions above, an #Error is returned; so it's
gotta be a syntaxt error, right?
If I wanted to sum the Amount field in the tblExpenses table where the
ExpDate (format medium date) is within the plan year 2007 and the
Claimed field (boolean) is true, wouldn't the Criteria clause of the
DSUM function have to literally be (i.e. the pseudo-WHERE clause is
bounded by double quotes)?

"Year([ExpDate])=2007 AND [claimed]"

Why is it that - double quote double quote YEAR([ExpDate])=double
quote & GetCAFEPlanYearVariable() & double quote AND [Claim] double
quote double quote - won't make it past the expression editor.  It
seems to me that such syntax would preserve the bounding double quotes
yet let one use a variable for one of the parts of the criteria
clause.  I tried single quotes to set off the variable but no going
there either.

One thing I have found out is that the DCOUNT() function could return
a Null value so I've set its tru-false condition to IsNull(DCOUNT()).

Thanks in advance for the help
Richard

0
rwfreeman
5/24/2007 7:45:37 PM
On May 24, 3:45 pm, rwfreeman <deltabe...@comcast.net> wrote:
> On May 24, 10:49 am, "Ken Snell \(MVP\)"
>
>
>
>
>
> <kthsneisll...@ncoomcastt.renaetl> wrote:
> > You cannot reference a VBA variable within a Control Source expression. You
> > must write a public function that will read the value of the variable and
> > return it as the function's value - -for example,:
>
> > Public Function GetMyCafePlanYearVariable() As Integer
> > GetMyCafePlanYearVariable = intCafePlanYear
> > End Function
>
> > This function should be put in the report's module so that it can see the
> > variable, and you must declare the variable as global for the report module
> > by putting this statement in the report's module's Declarations section:
>
> >         Private intCafePlanYear As Integer
>
> > Do the same for all such variables that you want to use.
>
> > Then, use the function in your control source expression:
>
> > =iif(Dcount("[ID]","tblExpenses","[Claimed] AND Year([ExpDate])=" &
> > GetMyCafePlanYearVariable())=0,0,DSUM("[Amount]","tblExpenses","[Claimed]
> > AND
> > Year([ExpDate])=" & GetMyCafePlanYearVariable())
>
> > --
>
> >         Ken Snell
> > <MS ACCESS MVP>
>
> > "rwfreeman" <deltabe...@comcast.net> wrote in message
>
> >news:1180017277.250047.229470@q66g2000hsg.googlegroups.com...
>
> > > On May 24, 8:41 am, "Ken Snell \(MVP\)"
> > > <kthsneisll...@ncoomcastt.renaetl> wrote:
> > >> How are you using the named variables in the report? What are the control
> > >> source property values for the controls that show the error?
>
> > >> What data type is ParseText function declared as?
>
> > >> --
>
> > >>         Ken Snell
> > >> <MS ACCESS MVP>
>
> > >> "rwfreeman" <deltabe...@comcast.net> wrote in message
>
> > >>news:1180009204.338063.316560@w5g2000hsg.googlegroups.com...
>
> > >> > Access 2003.  I'm trying to pass values for two variables to a report
> > >> > from a form but the controls on the report are giving me "#Name?"
> > >> > errors wherever the passed variables are used.
>
> > >> > Here's the code on the form
> > >> > Private Sub cmdPreviewClaim_Click()
> > >> > Dim strOpenArgs As String
> > >> > strOpenArgs = Forms!frmMain.Form.cboxPlanYear & "|" & Forms!
> > >> > frmMain.Form.currSetAside
> > >> > DoCmd.OpenReport "rptClaim", acViewPreview, , , , strOpenArgs
> > >> > End Sub
>
> > >> > Here's the Open event of the report (ParseText() is a module level
> > >> > function to parse strings with a "|" delimiter).  The variables are
> > >> > DIMmed as public variables in the Declarations section of the report
> > >> > code.
> > >> > Private Sub Report_Open(Cancel As Integer)
> > >> > If Not IsNull(Me.OpenArgs) Then
> > >> >  intCAFEPlanYear = ParseText(Me.OpenArgs, 0)
> > >> >  currCAFESetAside = ParseText(Me.OpenArgs, 1)
> > >> > End If
> > >> > End Sub
>
> > >> > There aren't any name conflicts between controls in the report and
> > >> > names for the passed variables.  It doesn't seem to matter where
> > >> > (module vs. report) that the variables are DIMmed.  If Access pops up
> > >> > the window that asks for values for these variables and one enters
> > >> > values for them, the report does what it's supposed to do.  But if
> > >> > values for the variables come from code, the report controls (which
> > >> > are text boxes with experssions as the ControlSource) show #Name?
> > >> > errors.
>
> > >> > It's gotta be simple but I'm stumped.
>
> > >> > Richard- Hide quoted text -
>
> > >> - Show quoted text -
>
> > > Named variables are used in expressions in textbox controls.  An
> > > example for the currClaimedToDate control (formatted as Currency) the
> > > ControlSource is
> > > =iif(Dcount("[ID]","tblExpenses","[Claimed] AND Year([ExpDate])=" &
> > > intCafePlanYear)=0,0,DSUM("[Amount]","tblExpenses","[Claimed] AND
> > > Year([ExpDate])=" & intCafePlanYear)  The intent of the expression is
> > > to see if there are any records in the tblExpenses table for the
> > > selected plan year that are marked as being claimed.  If so, sum the
> > > amounts for the plan year, otherwise show a 0 value.  Each time I try
> > > to fully enclose the Criteria in quotes (i.e. "criteria") I get an
> > > error.
>
> > > ParseText returns a Variant which when put into the declared variable
> > > follows its declared variable type - integer for intCAFEPlanYear and
> > > currency for currCAFESetAside (I checked this when stepping through
> > > the code; their VarType()s were 2 and 6, respectively.
>
> > > BTW, if it's easier to do this with a function as the control source,
> > > could you indicate for me how to call the function from the
> > > control's .ControlSource property and pass the intCAFEPlanYear
> > > variable?
>
> > > There's a line from one of my favorite Jimmy Buffett songs (he's
> > > describing his attempts to mimic the bear dance, "It's so simple it
> > > plum evaded me") that pretty well describes my level of angst over to
> > > being unable to make Access do this apparently simple task.  Thanks
> > > again for your time and patience.
>
> > > Richard- Hide quoted text -
>
> > - Show quoted text -
>
> Ken - I'm making slow progress.  The assignment function and variable
> declarations are just the ticket and now I can get some of the
> controls on the report to use the passed value for the plan year.
> However, where a control's .ControlSource is an expression such as the
> DCOUNT() and DSUM() functions above, an #Error is returned; so it's
> gotta be a syntaxt error, right?
> If I wanted to sum the Amount field in the tblExpenses table where the
> ExpDate (format medium date) is within the plan year 2007 and the
> Claimed field (boolean) is true, wouldn't the Criteria clause of the
> DSUM function have to literally be (i.e. the pseudo-WHERE clause is
> bounded by double quotes)?
>
> "Year([ExpDate])=2007 AND [claimed]"
>
> Why is it that - double quote double quote YEAR([ExpDate])=double
> quote & GetCAFEPlanYearVariable() & double quote AND [Claim] double
> quote double quote - won't make it past the expression editor.  It
> seems to me that such syntax would preserve the bounding double quotes
> yet let one use a variable for one of the parts of the criteria
> clause.  I tried single quotes to set off the variable but no going
> there either.
>
> One thing I have found out is that the DCOUNT() function could return
> a Null value so I've set its tru-false condition to IsNull(DCOUNT()).
>
> Thanks in advance for the help
> Richard- Hide quoted text -
>
> - Show quoted text -

I finally gave up on the expressions as controlsource property for the
textbox. Instead, I calculate the value of the ClaimsToDate in the
report's Open Form event then use a GetClaimsToDateVariable function
to pass the value to the report.  Works fine and is error free.
Thanks again for your insights.

0
rwfreeman
5/25/2007 2:13:31 AM
Not sure of exactly the syntax you tried to use, but here is how a DCount 
function would look in a control source expression when using a function to 
get the value of a module-level variable:

=DCount("*", "QueryTableName", "Year([ExpDate])=" & 
GetCAFEPlanYearVariable() & " And [Claim] = True")


-- 

        Ken Snell
<MS ACCESS MVP>



"rwfreeman" <deltabeans@comcast.net> wrote in message 
news:1180035937.584395.256550@z28g2000prd.googlegroups.com...
> Ken - I'm making slow progress.  The assignment function and variable
> declarations are just the ticket and now I can get some of the
> controls on the report to use the passed value for the plan year.
> However, where a control's .ControlSource is an expression such as the
> DCOUNT() and DSUM() functions above, an #Error is returned; so it's
> gotta be a syntaxt error, right?
> If I wanted to sum the Amount field in the tblExpenses table where the
> ExpDate (format medium date) is within the plan year 2007 and the
> Claimed field (boolean) is true, wouldn't the Criteria clause of the
> DSUM function have to literally be (i.e. the pseudo-WHERE clause is
> bounded by double quotes)?
>
> "Year([ExpDate])=2007 AND [claimed]"
>
> Why is it that - double quote double quote YEAR([ExpDate])=double
> quote & GetCAFEPlanYearVariable() & double quote AND [Claim] double
> quote double quote - won't make it past the expression editor.  It
> seems to me that such syntax would preserve the bounding double quotes
> yet let one use a variable for one of the parts of the criteria
> clause.  I tried single quotes to set off the variable but no going
> there either.
>
> One thing I have found out is that the DCOUNT() function could return
> a Null value so I've set its tru-false condition to IsNull(DCOUNT()).
>
> Thanks in advance for the help
> Richard
> 


0
Ken
5/26/2007 9:49:18 PM
Reply:

Similar Artilces:

Report Header Lookup Question
I have a report running from a parameter query where a start and end date are entered and then the report opens within those dates. I want the report name to look something like this... Jobs Worked from ________ to _________. How would I make the dates entered in the parameter boxes populate to the header title? Any help is much appreciated. On Tue, 18 Mar 2008 15:29:00 -0700, Adam wrote: > I have a report running from a parameter query where a start and end date are > entered and then the report opens within those dates. I want the report name > to look something like t...

acess form: how can I have more than 2 people modify it?
someone told me about the bcm v2 - what is that? I've only found it for outlook... it should make 5 people open and modify the form I've created in Access, is this right? I'd like to know how I can have a shared folder with an Access Form be used by several people at the same time, not just one. Is this possible? If someone could help me on this I'd greatly appreciate it. Never heard of bcm v2, but the rest of your question is fairly common. With an Access application you want to be available for multiple users, here is the correct way to deploy it. First, let me say D...

Export report to pdf
I have a report that I want to sent to someone on email as a pdf attachement I go to file - sent to as an attachment and there a window opened with a number of differnt formats to select such as Text, snapshort but no pdf file But my friend's computer when he did the same there is a pdf file in the box for selection What am I mising in my omputer. My computer has ADobe reader version 8 How can get pdf file formate? I read stepen's site but I could not understand because I am a novice. klam Hi, have a look here: http://www.lebans.com/reporttopdf.htm -- Best regards, ___________...

OWA-Exchange-Outlook 2003 sync issue
I've got a weird issue with one of my users that I can't figure out. Mail and Contacts are working fine and synch with our 2003 SP2 server. If an appointment is added via the Outlook MAPI client, it is reflected in the OWA calendar. However, the inverse is not true. If you add an appointment in OWA, it is not propagated through to the desktop MAPI client. It is not confined to just his computer as we have tested it on several, all with the same result. You can add contacts in either place and they are propagated just fine. Mail is received and sent in both without an issue. I wo...

HELP RE: DISPLAY CHECK NUMBER ON PAYEE REPORT WITH
How do I get a historic payment reoport for a specific payee including check number, date, amount, and payee name? ie: "Car Loan Payment history for the period 1/1/01 thru today" Payee: Check # : Date: Amount: (and perhaps a running total or a final total) I think I have tried all the built in reports and can't find one to do this or modify existing to do this. I see this information in the "view payee details" window, listed in the format I require but when I select PRINT all that prints is the bar graph that is also displayed on this page. I ca...

How can I make my Headers print on all pages of my report?
Could anyone tell me how I can have the header of my report to print on all of the pages of the document and not just the first page? Thank you. On Tue, 5 Feb 2008 14:29:22 -0800, SIRSTEVE wrote: > Could anyone tell me how I can have the header of my report to print on all > of the pages of the document and not just the first page? > > Thank you. What header? There is a Report Header, a Page Header, each group in the report (if you are grouping your data) can have a Header. If you mean the Report Header, you can't. The Report Header, by definition, only prints on th...

Default value for a field in the create form
Hi all, It's possible to define a default value for a field, in the create form ? Thanks, Hugo You can default a picklist value with out of the box customization but that's it. >-----Original Message----- >Hi all, > >It's possible to define a default value for a field, in the create form ? > >Thanks, >Hugo > > >. > ...

Report Writer Conditional Field Question
I should know the answer to this but my head is working on a different problem at the moment. In Report Writer, on a SOP Blank Invoice, I want to conditionally show the SOP_Line_Work_PrimaryShipToAddressCode ONLY when it is not equal to 'Primary' or 'Ship To'. In essence, I need to show the SOP Line Level Address when it's an outlier from their two standard address codes. I built a conditional field in RW that checks for Primary and returns blank ("") or the address. Works great. But now I need to add an OR condition and I'm stuck. I can't seem to be able...

Any issues with Windows Service Pack 3 (XP) and GP v. 8.0? Thanks
My client is looking to see if there are any issues with this combination. Thank you. No issues that I have heard about. However, check the system requirements page as it is updated frequently to address some of these questions at https://mbs.microsoft.com/partnersource/products/greatplains/documentation/systemrequirements/compatibility_gp8.0_lowmidhi.htm?printpage=false Just make sure you are running the latest GP 8 service pack (Service Pack 5) available at https://mbs.microsoft.com/partnersource/products/greatplains/downloads/servicepack/greatplains80_servicepacks.htm?printpage=fal...

Reports for Orders
Hello! I am trying to create a report that is based on Orders. That is, the report will display Orders and group its underlying Activities and summarise their durations. An order named A has 4 activities. Two of them have the Category field set to A and have 3 hours duration each. Two of them have the Category field set to B and have 4 hours duration each. The report would look like this: Order Name A Activity Category A duration 6 hours Activity Category B duration 8 hours So I went to Crystal Reports and started playing with it. First thing I find is that ...

Third Party Report
Hi, We are using a Third Party with GP 10 and it has it's own POP purchase order blank form and can see by clicking on gp tools Customize Report Writer and selecting the third party product name. I go to Alternate/modified forms and reports to give access to it by selecting the third party product in the product drop down menu and selecting reports unfortunately I don't see it there any idea how to make the system print the third party POP in place of the standart one? thanks for the help Well, have you generated a purchase order? If you are using a third party add-on, you w...

MS Access Copy record to another table based on a form input 10-08-07
Hello. I have 6 Tables, all with identical fields. I want to copy a record from one table into another table, which table depends on what table is selected in a form. So if the table names are One, Two, Three, Four, Five, Six, and the record that I want to copy and insert into another table has been selected by a query in a form. I want the form to direct where to insert the table. So if the form has table Two selected (from the drop down list) then it should insert the record into table Two. If the form has table Four selected then it should insert it into table Four. The table the record is...

Full Page X Report
I hope someone might find this useful. This formats the X/Z report to fit on one Full Page. Slapped together quickly but you can format it how you like using XML... http://www.newestech.com/downloads/FullPageXReport.zip Casey Hanson New West Technologies ...

Distribution/Security Group Address Issue
Several weeks ago, I noticed within active directory, there were two groups with the same name set up; one distribution and one security. Both had the same members. I deleted the distribution group and assigned the security group an email address which was the same as the old distribution group address. Now, in Outlook 2003, when you begin typing the name of the group, the option pops up, but when you send the message or click the plus-sign next to the name, you get the error "Cannot perform the requested operation. The command selected is not valid for this recipient. The operation f...

ProcessStart(textfile) focus issue
just learning dotnet so i write a lot of debug lines to text file so i can track where errors occur Private Sub Logentry(ByVal strMsg As String) File.AppendAllText("Path to debug file\Debug.txt", strMsg + Environment.NewLine) End Sub the app is a vbnet app running inside autoCad, so it acts like i'm entering autocad command inside autocad(autocad has focus) Main app creates worker class that does the work worker class creates utility class that has general functions used over various projects utility class provides logging etc at end of program run utility ...

Sorting issue with frozen panes
When I freeze one or more rows, and then I click a single column header and sort by that column, sometimes the frozen rows are included in the sort, and sometimes they are not. I don't ever want them to be included, so I'm trying to figure out what is causing this to happen and how to prevent it. For example, say I have a "Name" column containing three rows: Tom, Dick, and Harry. The top row (containing the header) is frozen. If I sort by that column, I'd expect "Name" to stay in the top row, and the other rows to be sorted Dick, Harry, and Tom...

Default email address and OWA authenitcation issue
We have an Exchange server setup for multiple companies to use. AD Domain is say “company.com”. I create a new user and remove them from the recipient update so they don’t pick up the default email address of xyz@company.com I add manually their email address as xyz@anothercompany.com. They can logon to domain and sent email etc. When they access email via OWA it just bounces the password back. If I create them an address in the @ company.com it works. Event log indicated unknown user or invalid password. Im not sure I understand why this is happening? is it that the presence of...

Remove sort and filters programmatically on form
I have a form based on a 2 table query. User will use shortcut menu to filter records down to what they want to work on and sort accordingly. I have a button to remove all filters but I also want to remove any sorts that were applied and I can't seem to get that part to work. I use the following to remove all the filters. I just need help with the code to get the form back to the original underlying query sort. On_Click event of button control Me.Filter = "" FilterOn = False Like I said, this brings back all records but the sort stays. I am using Access 200...

OPenign an Excel sheet form Access
Hello everyone, I would like to open a shared excel spreadsheet on our server by using a button from Access. Can I use the runApp macro for this? If so what do I need to do...I have little experience with macros. Thanks. Add a button to your form add an onclick event and then enter the following code application.followhyperlink "c:\...\...\...\excel.xls" obviously replace the "c:\...\...\...\excel.xls" with the path and filename of the excel file you wish to open. -- Hope this helps, Daniel Pineault "Amanda" wrote: > Hello everyone, > &g...

Upgrade issues
I just upgraded my 1.2 CRM to 3.0 and I am having some serous issues but first I guess I should tell you how I have thing set up. I have exchange on its own box, sql on its own as well as the CRM and DC on their own servers. I did not receive any errors during the upgrade of the CRM or exchange router but since the upgrade I can only access the the CRM with the administrator account. All the other users I get the CRM screen but I also get an error stating that an error has occurred, for more information, contact your system administrator. I confirmed that these users are present in ...

List ctrl issues
I have an MDI Child window that is using a ListView. I am running the list view in report mode with one column. I want the column to take up the full width of the window, but I am running into some problems. I cannot ask the list view for its client rect in the OnCreate function because that always returns and empty rectangle. I don't know at what stage the window rect will become valid so it is difficult to determine where to put my column sizing code. Obviously it will be valid after the window is painted, but there is no message for "after first paint". Any tips on how/wh...

How to integrate crystal report with vc++
Hi all I am very new in the field forsoftware development. I have to integrate crystal reports with my vc++ application, which will distribute to clients. I find some stuff on net but i have some confusions. 1. what is best method(easy, quick to develop, and flexible in genrating reports) to integrate it, i.e crystal activex control, crystal automation server, crystal report engine, or some other else. 2. Is we can make crystal project(EXE) and integrate with my vc++ application, like data reports of vb. If yes, how can I, and for this what version of crystal report i have to use(i.e standar...

CRM 4.0 reports #2
Is anybody know how to create new SSRS report for crm 4.0 and deploy? On May 21, 2:32=A0am, lily <uma_natara...@hotmail.com> wrote: > Is anybody know how to create new SSRS report for crm 4.0 and deploy? For a simple report you have the report wizard within the client. Otherwise you will need to use an SRS report writer such as the one in Visual Studio. If you create one in Visual Studio, log into CRM as Administrator and you should be able to add in the report through the client. Leon Tribe ...

In Account Overview report, increase display of Contacts from 10 to 20
Hi all, I am making a custom version of the Account Overview report and, among other changes, increasing the number of Contacts displayed from 10 to 20. This is the code that I found that does it: "=IIF(Fields!fullname.Value = "(Limited to 10 contacts) - - ",ReportItems!txtlimited10con.Value,Fields!fullname.Value)" Can anyone please tell me how to change it to do what I want? If I change the 10s to 20s and I build I get this error message: "The value expression for the textbox 'txtAddcontacts' refers to the report item 'txtlimited20con'. Report item...

Issues with Rounding (or lack thereof)
Hello, I've got a question with an Access 2000 Database. I've got a query with a calculated field: Der_Percent_Good. Der_Percent_Good: Nz((100*(1-[Num_Out_Tol]/[Shipment_Sample_Size])),"N/ A") This field is supposed to be a percentage, and I'd like this field to be able to round to 2 decimal places, for right now it does not. Right now it has a large amount of decimal places if the situation arises, like 1/3 (goes out of view on my textbox on the form), and it will have 0 decimal places if it turns out to be a whole number (0/100). I've tried selecting Properties-...