Setting Global Date(s) 1

[burcher]

I have a table called TimeTbl that has 3 fields: BegDate, EndDate, SelectDate. BegDate has the beginning date of the calendar year, EndDate has the Ending Date of the Calendar Year and The SelectDate is a Yes/No Field to specify which calendar year you are working in. I would like to make a global variable for BegDate and EndDate to use in different filters and queries, but this is something I've never done. Can someone get me on the right path please.

 

[scriverb]

Open a new database Module. Use the following code to create Global variables:

Global datBeginDate as Date
Global datEndDate as Date

Function BeginDate as Date
   BeginDate = datBeginDate
End Date

Function EndDate as Date
   EndDate = datEndDate
End Date

These variables can be loaded with values from your tables or through an assignment in VBA code anywhere in the database. They can referenced from anywhere in the database except from queries. You must create a Function to retrieve the Global variable values. In the above code you will also see two functions. They are setup to retrieve the date value of the global functions. You can now make Function calls to these functions to use your date values in a queries criteria row.

SQL for a WHERE clause in a query. Just put Between BeginDate() and EndDate() in the Criteria row of your datefield to achieve the following SQL in your Query:

WHERE tblname.[DateField1] Between BeginDate() and EndDate()

Post back if you have any further questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[burcher]

Bob,
I need for the query to return 0 if the voucher date is not within the current calendar year, The following Sql is executing that perfectly, so how do i subsitute the info you gave me above so that in my query I can remove the Between #date# And #date# and have it look it up? below is my current query:

SELECT [Voucher Data Table].*, IIf([Date Issued] Between #1/1/2003# And #12/31/2003#,[Amount Issued],CCur("0")) AS AmtIssued
FROM [Voucher Data Table];

I tried this:
SELECT [Voucher Data Table].*, IIf([Date Issued] Between DLOOKUP[BegDate] And DLOOKUP [EndDate],[Amount Issued],CCur("0")) AS AmtIssued
FROM [Voucher Data Table];

but it gave me a syntax error, so I'm missing something that your telling me. Can I even use DLOOKUP in this way, or am I on the wrong track?

 

[scriverb]

Try this after you have copied and pasted the global variables and functions into a database module and made the assignment of the table fields to Global variables.

SELECT [Voucher Data Table].*, IIf([Date Issued] Between BeginDate() And EndDate(),[Amount Issued],CCur("0")) AS AmtIssued
FROM [Voucher Data Table];

Post back with any questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[burcher]

I have copied and pasted the Global code in your first response under Module 2. I also modified the sql to what you have above, but it gives me a Complile Error:syntax error and takes me to the module for debugging. The "Function BeginDate() As Date" is highlighted in yellow and the "End Date" is highlighted in Blue. How does the code lookup in the Timetbl and see whichg BeginDate and EndDate selected is Yes? I think this is the piece I am missing.

 

[scriverb]

Yes, you have to assign a date value to the two Global variables. Because I don't know which records in the table you wish to use I thought you would be able to assign these values. If there is only one record then we can do this with a DLookUp function command. Here is an example:

datBegDate = DLookUp("[BegDate]", "TimeTbl")
datEndDate = DLookUp("[EndDate]", "TimeTbl")

With the above code the values in the first record of the table TimeTbl are assigned to the two Global variables. If you have more records in this table and must select them then the use of the third parameter for the DLookUp function must be added where you are indicating the Record Selection criteria.

Post back if you need more information.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[PHV]

burcher, in the 2 functions definitions, replace this:
End Date
By this:
End Function

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[scriverb]

PHV: Thanks for that catch. Must have been falling asleep when I typed that one up.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[burcher]

Bob,
Sorry it's taken so long for me to get back, other duties prevailed, but what you gave me works perfectly and I really appreciate your help.
And Thanks PHV for reviewing this msg as well. I did catch that and had already fixed it, but it's nice to know others are watching too.