Search results

The attached little demo file illustrates how line items from multiple orders per customer can be consolidated into a single invoice. If you take a look at the orders form you'll see that each line item in the order details subform includes an Add to Invoice check box. This adds that item to a...

For best results you would probably use subreports, but combine these with grouping the parent report. This gives you flexibilty over the layout of the invoice which grouping alone does not. You'll find an example in the attached little demo file. The Open Current Invoice button on the invoice...

PS: Don't be tempted to wrap the value in the # date delimiter characters. This would change 4th July to 7th April for those of us not using US short date format.

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...