In reports can I refer to variables in modules ?

[Tony Girgenti]

When designing reports, can i refer to variables(not form controls) in a
module ?

Any help appreciated

Thanks,
Tony

 

[Steve Schapel]

Tony,

Yes.

- Steve Schapel, Microsoft Access MVP

 

[Tony Girgenti]

Real cute Steve.

Would it be much trouble for you to provide a little info on how it would be
done (hehehe)

Seriously, it it as simple as using "modules!module1!varname ?

Thanks,
Tony

 

[Bruce M. Thompson]

Would it be much trouble for you to provide a little info on how it would be

done (hehehe)

Seriously, it it as simple as using "modules!module1!varname ?

If the modules is a standard (global) module and the variable was dimmed as
Public, you could reference it just as you would a local variable:

strReturnValue = strMyPublicVariable

 

[Steve Schapel]

Tony,

Well, I can't really, my friend, without knowing what you are trying
to do. Even this little extra info you have provided is enough to
show that I completely misunderstood your original question, which I
had assumed was referring to the report's class module.

- Steve Schapel, Microsoft Access MVP

 

[Tony Girgenti]

I'd like to know if i have a variable dimmed in a form module, when putting
in a criteria for a report, can i refer to that variable in the form module
as a field for caomparison ?

Thnaks,
Tony

 

[Tom]

Tony,
If it is just one variable you are looking for (actually you can do it with
more and parse) you could send it to the report as an OpenArg - one key
problem to keep in mind though, you cannot send open args and a WHERE clause
to a report in the same command - hope that's something they are going to
fix.
As Bruce has said, it may be better to create a public function which you
can then call from the form when you need it, from the report when you need
it, a query - you name it. Otherwise, you have to dim the variable(s) in
your form as Public and the form has to be open while the report tries to
access the variable.
HTH
Tom

 

[Tony Girgenti]

Maybe i'm going about this in the wrong way and maybe you can give me a
better way of doing what i need.

I want to use Access XP with externally linked tables, get user input
through a form, extract data form the externally linked tables, create an
ascii file with the data and optionally allow the user to print the data.
So far it all works except for the printing part.

The data in the tables is stored as mostly data type text including date
fields except for dollar amounts which are stored as number. The date
fields are all stored as YYYYMMDD. I'm not that great at VBA(old COBOL
programmer) so, when it came to trying to compare the user entered
date(short date) to the table dates, i had to reformat the user entered date
to YYYYMMDD into another variable so that it could be compared to a table
date. Now if i try to develop a report that uses the same WHERE parameters
as the recordset WHERE, which is used to get the data to create the output
file, i wanted to try to use the variable with the stored date in YYYYMMDD
format.

If there is some better way to do this, i hope that someone can give me some
pointers on what to do.

I hope all this makes sense and any help that you can provide would be
greatly appreciated.

Thanks,
Tony

 

[Steve Schapel]

Tony,

Thanks for the further explanation of what you are trying to do.

To me, it would seem simpler to directly reference the form criteria
entry for the report. If I understand you correctly, my preferred
method would be to put it directly into the query that the report is
based on, i.e. in the criteria of the text-date field in the query,
put the equivalent of...
Format([Forms]![NameOfForm]![DateCriteriaTextbox],"yyyymmdd")
Otherwise, if you are indeed running the export and optional report
from an event on the form, which one would presume, once again you
could refer once again to the date criteria entered on the form, for
example...
DoCmd.OpenReport "ReportName",,,"[TextDate]='" &
Format(Me.CriteriaBox,'yyyymmdd') & "'"

I can't see at the moment why you want to manipulate the data within
the report itself at print time.

- Steve Schapel, Microsoft Access MVP