Search results

The problem with setting a control's DefaultValue property by means of an expression in its properties sheet is that, if an existing date in a record is edited, or a date is inserted in a new record, so that it is later than the default, the control's default value will not become this new later...

Agreed. Not only pointless, but more importantly, storing the computed values leaves the table open to the risk of update anomalies. The most efficient way to compute balances is by a join of two instances of the table, as in the example I posted earlier. Other examples, returning updatable...

The reason for the weird date is that the date is being interpreted as an arithmetical expression rather than a date. The result will be a small fractional number of course. As Access implements the date time data type as a 64 bit floating point number, this will be interpreted as sometime on...

In your original expression both operands are of short integer data type. Multiplying the two values tries to return another value of short integer type, but in this case the product of the two values is beyond the capability of a short integer data type, hence the overflow. Even though you...

You're not alone, many people make the same assumption in my experience. An autonumber is really a mechanism rather than a data type. It's a long integer data type with the added functionality of generating a distinct number when a row is inserted. An INSERT INTO statement can still be...

Why not just insert a row into ProductT with a ProductID value of 0, and a Description value of 'Hourly Billing'? That way there's no need to update OrderDetailT, and the relationship can be enforced. The DefaultValue property of ProductID in OrderDetailT can be left as 0 or made Null as the...

As has been explained, to filter the form to alternative subsets of rows from a single table you would do so on the basis of the value in a single column matching that selected in the unbound combo box. You might want to be a little more ambitious, however, and filter the form on values in a...

In most cases having multiple tables modelling subsets of a single entity type would mean that data is being encoded as table names. A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as single values at...

Do all rows have the leading character R in this column, or do subsets of rows have different leading characters? If the former then there is no need to store the leading character in each row, as you can concatenate it as a constant to the numeric characters whenever necessary. This begs the...

The attached file illustrates the use of some simple little functions which return the accounting year for any date in the format YYYY-YY, where the start date of the accounting year can be any date. By default the UK fiscal year, which starts on 5th April, is used, but all that's necessary to...

What you say is correct. I'd assumed from the remark line in the code ' Code to export and save data to MS Word Template that their intention was to save a document based on the template.

It's often not realised that the Windows API ShellExecute function can be called to print a file as well as opening it. The file can be any type which is capable of being printed: ' module basShellExecute Option Compare Database Option Explicit Public Const SW_HIDE = 0 Public Const...

The NotInList event procedure should not be used where the combo box returns a personal name as these can legitimately be duplicated. I was once present at a clinic when two patients arrived within minutes of each other, both female, both with the same first and last names and both with the...

You might find the attached little demo file useful. Note that where data is in a tree structured hierarchy, only the lowest level in the hierarchy should be stored in a referencing table, city in the demo. Otherwise redundancy is introduced and the table is not normalized to Third Normal Form...

It looks to me like you might be assigning a date of the first of the month to the column, but returning it in the control formatted as "dd/mm". If so, I'd suggest that you return three columns in the RowSource property: The actual value of the column as the first column, the formatted value...

I don't think that will work. Firstly you need tables to model the principal entity types. In broad outline: Employees ....EmployeeID (PK) ....FirstName ....LastName ....other attributes Buildings ....BuildingID (PK) ....BuildingName ....Address ....other attributes Floors ....FloorID...

If by that you mean that you want to number the rows in the subform as an unbroken sequence regardless of the insertion to or deletion from the table of any rows, then storing the values is inadvisable as it leaves the table open to the risk of update anomalies. The values can be computed at...

Further to Minty's post in which they suggested a simple model in which the many to many relationship is modelled by a third table which resolves the relationship into two one to many relationships, I would add one thing, viz that in each relationship referential integrity must be enforced. An...

Rather than setting the Value property of each control, which is the default property, so is usually omitted, I would suggest that you set its DefaultValue property, and that you do this conditionally in the form's Current event procedure, which will be executed however the form is opened. The...

The point is not the use of the Now() function or a custom function. The key thing is when the Now() function is called. It can certainly be called as the column's DefaultValue property in the table definition, to cater for a row being inserted other than manually in a bound form, but it...