Using "Date" as a field name 3

[alvechurchdata]

I want to call the Date function to put today's date in a field:

[tt]Me.txtDate.Value = Date[/tt]

Easy enough except that there's a field named "Date" and this line of code gets that value rather than calling the Date() function. If I type Date() then Access autocorrects it and removes the brackets.

In an ideal world I would change the database structure and revoke the author's programming privileges but neither of these is possible. Is there any way of telling Access to call a function rather than read a field?

Geoff

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Andrzejek]

One of many ways: [tt]Split(Now, " ")(0)[/tt]

Now you know why it is a bad idea to use reserved words as field names... :-(

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Andrzejek]

...or, where you set the data source, you may use an alias for your Date field:
[tt]Select ..., [blue][Date] As MyDate[/blue], ... From ...[/tt]
and your field now is [tt]MyDate[/tt], so you can use [tt]Date[/tt] as was designed

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MajP]

you can fully qualify it
Me.txtDate.Value = vba.date

There are a lot of naming conventions out there. Use one. This one is OK

Some Basics. 

Normalisation is a topic that is widely discussed. If you are not aware or at least have a basic understanding of the subject then you should make yourself familiar. There are many discussions available on the WWW. Normalisation is the foundation stone of any Access database.

Reserved Words are words that are reserved for use by the built in Access functionality or SQL functionality. Examples of the most common reserved words that new developers use when naming database objects are: Date, Day, Month, Year, Now, Print. A developer may have a field in the Sales table called ‘Date’. This reserved word ‘Date’ should be replaced with something like ‘SaleDate’. 
A full list of reserved words can be found by searching the WWW. 

Spaces and Special Characters. You may be tempted to use spaces or special characters when naming database objects. These special characters can produce errors when referring to them in queries or VBA if not handled correctly. Avoid them when naming objects in your database. The underscore “_” is a widely accepted character by many programmers. It is not recommended here, but simply acknowledged to be acceptable.
Special characters / \ | @ £ ^ ( ) [ ] { } ; : ! # & = * + - ? " ' $ %

Keep Names Short yet Informative. A table, query, form, report etc. needs to have a name that is informative. Not too long and not too short. For example a query named ‘qryListOfSalesForEachDepotGroupedByMonth’ should be called something like ‘qryMonthlySalesByDepot’ and should definitely not be called ‘MSD’. 

Title Case also known as CamelHump. A query named ‘qryMonthlySalesByDepot’ is easier to read than a name written in all UPPER CASE or all lower case i.e. ‘QRYMONTHLYSALESBYDEPOT’ or ‘qrymonthlysalesbydepot’

Name Prefixes. The following is a list of prefixes and example names to use when naming objects. By using a prefix you can more easily distinguish between different object types that have the same name. I.e. if you have a table named ‘Staff’ and a report named ‘Staff’ it is difficult to tell which is which, so name them ‘tblStaff’ and ‘rptStaff’. 
Another reason we name our tables and queries with a prefix is that when you are working with these two object types in the query window, Microsoft Access simply lists all tables and queries together without differentiating them. By using the tbl and qry prefixes on these two objects, it ensures that the tables are shown separately from queries. 

Other objects that need to be named include the bound and unbound controls found on forms and reports. When you create a form or report using the wizard or AutoForm or AutoReport each of the controls are named the same as the field names. Now whilst this is by MS Access default, it is not really an acceptable way of naming controls. One of the key reasons we do not want to do this is that sometimes when we are working with forms and reports we want to refer to the control rather than the field. By having the field names the same as the object names, you can have a situation where the wrong object is referred. 

The way we overcome this, is by naming our controls based on the control type. For example if our field was called Postcode and the control we are using for this field is a text box, then we would name the text box txtPostcode and the label lblPostcode. Note how we refer to text boxes as a control. It is not a field as many think. There are no fields in Reports and Forms. They are controls.

HIERARCHY OBJECTS
Tables tbl
Queries qry
Forms frm
Reports rpt
Macros mcr
Modules mod, mdl or bas

TABLES.
Tables are usually plural and are preceded with tbl
e.g. tblClients
tblSales
tblCities

QUERIES
Queries are preceded with qry
e.g. qryClients
qrySales
qryCities

For greater clarity one can describe what a query is used for. This will also sort the queries into like groups and distinguish a query from a table. Notice the use of CamelHump. 

E.g. qryFrmClients Main Form
qryFrmSubClients Sub Form
qryRptClients Report
qryRptSubClients Sub Report

FORMS and REPORTS
Forms follow the same convention and so do Reports.

E.g. frmClients Main Form
frmSubClients Sub Form
rptClients Report
rptSubClients Sub Report

MACROS are preceded with mcr
MODULES are preceded with mdl or mod. Some programmers of old use bas.

Naming of Fields in Tables. Usually singular
City
SaleDate
FirstName
LastName

Adding prefixes to field names is a practice of old and not often adopted in MS Access.

Primary Keys and Foreign Keys. 

Primary Keys should share the same name with its Foreign Keys.
The Primary Key name should be unique within the Database.
The Primary Key should be tagged as the Primary Key and the Foreign Key should be tagged as the Foreign Key.
E.g. ClientPK as Primary Key
ClientFK as Foreign Key.

The commonly used ‘ID’ for both Primary and Secondary Keys does not create a distinguishing identifier.

Naming Fields in Queries.

Queries inherit the table’s field name.
Calculated Fields within queries require their own naming and should observe the basic convention of short, simple and descriptive. 

Form and Report Control Objects.

Text Boxes txt
Labels lbl
Command Buttons cmd
Combo Boxes cbo
List Boxes lst
Images img
Sub Forms sbf
Sub Reports sbr

 

[MajP]

https://support.office.com/en-in/ar...-symbols-e33eb3a9-8baa-4335-9f57-da237c63eabe

Reserved words in Access

 

[SkipVought]

Nice summary Major!

 

[alvechurchdata]

Thanks both. I was sure there had to be something like vba.Date and I do like the Now technique. For the moment though I've fixed it with a call to a new MyDate() function in a separate file.

Hateful project. It was written by a user (long since left) using switchboards and wizards. Any field names that aren't reserved words have spaces in them and I would dearly love to refactor it. I daren't change it though because there's a whole mess of reports and spreadsheets that will come crashing down about my ears.

Geoff



Geoff Franklin

http://www.alvechurchdata.co.uk