MeadInKent
| Feedback | A function to lookup variables | Using Access Queries |

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
  query design 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 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

 
UK more ...   USA 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
  query design table in query design  
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
  Query design
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
 



file: accprogvars.htm Page last updated Mar14 MeadInKent.co.uk 2014