Global values and queries

[Trudye]

Hi Everyone:

I believe I know the answer to this but just in case I missed something: Is it possible for a query to read a Global value?

In order to produce a report I must run 2 queries and 2 programs. Naturally they must use the same date, the ideal way would be to establish a Global date and have all components read it. The only other alternative would be to create a table to hold the date and have all components read the table.

Any Suggestions?


Any Suggestions?
Trudye

 

[cmmrfrds]

Yes, In the standard module.

Public pubBegDate As Date
Public pubEndDate As Date
Function ReturnBegDate() As Date
ReturnBegDate = pubBegDate
End Function

Function ReturnEndDate() As Date
ReturnEndDate = pubEndDate
End Function

'- In the vba code

dim sql1 as string
pubBegDate = #1/1/01#
sql1 = "select ID, startdate from employee "
sql1 = sql1 + "where startdate >= " & ReturnBegDate()

me.recordsource = sql1

 

[Trudye]

I am sorry I mislead you. I was referring to Access queries that you create from the Query tab in an Access database.

I apologize for not being clear
Trudye

 

[eljakim]

The answer remains the same.

1) create a module that contains the variable, plus
a function that returns the value of the variable

2) just use the result of this function in your SQL
code from the query designer... But you do have to
set the value of the variable first.

So the SQL would look like:

select ID, startdate from employee 
where startdate >= ReturnBegDate()

(make sure you have the module with the code
that cmmrfrds proposed)

Yours,

Kim

 

[Trudye]

I am sorry that I am unable to properly convey my problem. I am not writing SQL I am creating a Access Query that brings togeather 3 files and outputs anlarge number of fields. This is the reason I chose a query over SQL because I need to output so many fields. The common field within these files is a date feild.

Is'nt it true that if I can access a field that has been set by a function I could just as easily set a global value and access that field value? Which puts me right back at square one. Or did I miss your point?

Thanks
Trudye

 

[Trudye]

I am sorry that I am unable to properly convey my problem.

I am not writing SQL I am creating a Access Query that brings togeather 3 files and outputs anlarge number of fields. This is the reason I chose a query over SQL because I need to output so many fields. The common field within these files is a date feild.

Is'nt it true that if I can access a field that has been set by a function I could just as easily set a global value and access that field value? Which puts me right back at square one, how do I/can you, define a global date as input to an Access Query? Or did I miss your point?

Thanks
Trudye

 

[eljakim]

Trudye,

in Access you cannot access *global variables* from
your SQL query. You can access *functions*. The solution
presented above used this to 'simulate' global
variables.

If you really want to use variables, store them in
a seperate table and provide a link to this table.

Kim

 

[KyleS]

Trudye,
Go into the design view of your query and under the date column type this:
Between ReturnBegDate() and ReturnEndDate()

Just as if you were typing "Between #4/1/02# And #5/1/02#" so when the query is run, it will recognize those functions, go to the module suggested by cmmrfrds and get the data you were looking for. You just have to make sure you set the variables to something.

If that's not OK by you..Other than using the table, you could always use a form...Make the form hidden and set the values and reference them if you so desire... Kyle :

 

[Trudye]

Thanks a lot everyone for all your input and patience.

Since I am in a time crunch I think I'll go with Kim's suggestion and use a table. Not very code chic but effective.

Thanks again
Trudye

 

[Stickarm]

If you create a module like this

Option Compare Database
Option Explicit
Global gdate As Date

Public Function getdate() As Date
getdate = gdate
End Function

And set the gdate somewhere on from a form.. you can reference it in a query criteria using getdate()
Hope this helps.

Phil

 

[Trudye]

Thanks Phil once more you recue me with a great suggestion.

The only problem is, I think the form has to be open at the time. Aside from the fact I run an IsLoaded rtn constatntly, if a User happens to close the form before the query runs it blows.

But I like the way you think.

Thanks again
Trudye

 

[Stickarm]

Where are you feeding the Date value from, is it a field on a form?
If so on afterupdate on that field, set getdate to the value of the field, once the global variable is set it will stay that whether or not the form is open and will not change again until you trigger the Afterupdate on that field.