Refer to database variables in an Access query
It can be useful to save various database program variables in a table so that they
can be referred to by queries, reports and user defined (VBA) functions. These
variables could include the name of the organisation; the current financial year;
the time at which a table was last updated; or the folder on your PC where certain files are to be found.
In this example a table called KeyData is created to store these values.
| KeyData
: table (design) |
| |
Field Name |
Data type |
Description |
|
| |
RecordNo |
Number |
unique number reference for each record |
|
| |
FieldOne |
Text |
a piece of program data |
|
| |
FieldTwo |
Text |
description of data item |
|
| |
| KeyData
: table |
| RecordNo |
FieldOne |
FieldTwo |
| 1 |
d:\fin\download\ |
Default directory for import / export |
| 2 |
G*.txt |
Type of file to be loaded |
| 3 |
2004/2005 |
Current financial year |
| 4 |
31/03/05 |
End of current financial year |
| |
These values can be read or updated using a VBA user defined function. In a
query however they can be directly referred to. The following query uses the 4th
KeyData record which contains the last date in the current financial year. The
KeyData!FieldOne value is used as a criteria against the Date_Return field in
order to exclude dates after 31 March 2005. The CVDate() function is required in
order to convert the data type because the FieldOne value was stored as a text
string.
| Bookings_In_Current_Year
: Select query (design) |
| |
 |
| Field: |
USERGROUP_NAME |
DATE_RETURN |
INVOICE_AMOUNT |
RecordNo |
| Table: |
Bookings |
Bookings |
Bookings |
KeyData |
| Sort: |
|
Ascending |
|
|
| Show: |
Y |
Y |
Y |
N |
| Criteria: |
|
<=CVDate([fieldone]) |
|
4 |
| Or: |
|
|
|
|
| |
| Bookings_In_Current_Year
: Select query |
| User Group |
Date Return |
Inv Raised |
Invoice £ |
| CAPED CRUSADERS CLUB |
18 Mar 05 |
23 Mar 05 |
69.75 |
| CHELSFIELD GIRL GUIDES |
21 Mar 05 |
23 Mar 05 |
26.55 |
| HIGH FLYERS ATHLETICS |
25 Mar 05 |
04 Apr 05 |
23.25 |
| ST MARTINS CHURCH CHOIR |
29 Mar 05 |
04 Apr 05 |
49.50 |
| ST MARTINS CHURCH CHOIR |
30 Mar 05 |
04 Apr 05 |
40.95 |
| |
Alternatively the KeyData table values can be used to add a new field to the
query. In the following example,
record number 3 is used to add an extra field containing the current financial
year (2004/2005). The same field value will be added to every record.
| Bookings_In_Current_Year
: Select query (design) |
| |
|
| Field: |
DATE_RETURN |
FinYear: FieldOne |
RecordNo |
|
| Table: |
Bookings |
KeyData_1 |
KeyData |
|
| Sort: |
Ascending |
|
|
|
| Show: |
Y |
Y |
N |
|
| Criteria: |
|
|
3 |
|
| Or: |
|
|
|
|
| |
