return function into text box

[GavinP]

Hi all, urgent problem.

I've got a report which shows the last 12 months sales figures, which I'm now automating so that manual update of the field names isn't needed. My problem is that I have a function which will return a string with the formula I want as the text box source, but when you preview it, the data is the string, rather than the sum.
eg.
strComplete = "[SumOf" & strField & " Sales]"

How can I prevent this?

Gavin

 

[Remou]

Hi
I have tried:
strComplete = Me("[SumOf" & strField & " Sales]")
and it seems ok.

 

[GavinP]

Thanks, but I am getting an invalid use of Me message.

The function is as below:
Function ReportsFields(month As Integer) As String

Dim strComplete As String
Dim strField As String
Dim intfigure As Integer

If month < 0 Then
intfigure = month + 12
strField = "LY " & MonthName(intfigure, True)

Else
If month > 0 Then
intfigure = month
strField = MonthName(intfigure, True)
End If
End If
If month = 0 Then
intfigure = 12
strField = "LY " & MonthName(intfigure, True)
End If

strComplete = ("[SumOf" & strField & " Sales]")

ReportsFields = strComplete


End Function

The 1st report field is as follows:
=reportsfields(Month(Int(Now()))-13)

Please help! ;-)

 

[Remou]

Hi
I guess you have a field or fields called something like [SumOfLY Jun sales], with the month portion changing from time to time. This works for me:
[tt]strComplete = Reports!MyReport("[SumOf" & strField & " Sales]")[/tt]

Error coding for nulls is needed.

 

[dhookom]

You might want to check the FAQs of this forum. faq703-5466 might be what you are looking for.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GavinP]

Thanks both. I've sorted the problem similarly to dhookums suggestion, by changing the query that the report is based on, and using mth1, mth2 etc.

Cheers,

Gavin