Refer to user defined 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 and used to store these values.
KeyData: table (design) |
ox |
|
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 |
ox |
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. They can
also be directly referred to using a Select Query. 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) |
ox |
|
|
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 |
ox |
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 |
|
Access 2010 VBA Programming |
|
|
|
|
more ... |
|
|
more ... |
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) |
ox |
|
|
Field: |
DATE_RETURN |
FinYear: FieldOne |
RecordNo |
|
Table: |
Bookings |
KeyData_1 |
KeyData |
|
Sort: |
Ascending |
|
|
|
Show: |
Y |
Y |
N |
|
Criteria: |
|
|
3 |
|
Or: |
|
|
|
|
|
If you need more than one of your KeyData values to be combined with another table
or query, it may be easiest to first of all create a query which extracts the
required set of values from KeyData.
KD_Dates: Select query (design) |
ox |
|
|
Field: |
FinYear: IIf([recordno]=3,[FieldOne],"") |
YrEndDate: IIf([recordno]=4, CVDate([FieldOne]),"") |
|
RecordNo |
Table: |
|
|
|
KeyData |
Totals: |
Max |
Max |
|
Where |
Show: |
Y |
Y |
|
N |
Criteria: |
|
|
|
3 or 4 |
Or: |
|
|
|
|
|
KD_Dates: Select Query |
FinYear |
YrEndDate |
2004/2005 |
31/03/05 |
|