Search results

The following module was published by Microsoft many years to simulate the use of the IN operator with a value list as a parameter: Option Compare Database Option Explicit Function GetToken(stLn, stDelim) Dim iDelim As Integer, stToken As String iDelim = InStr(1, stLn...

If you are simply registering each product to each warehouse, rather then modelling inventory, i.e. there are no non-key attributes of the relationship type, then you do not need to model the relationship type by a 'junction' table. You can simply return the Cartesian product of the two tables...

Why are you storing the staff count per supervisor, rather than computing it with a simple query like this: SELECT tblPeopleStaffSupervisors.SuperVisorCode_I, tblPeopleStaffSupervisors.FirstName, tblPeopleStaffSupervisors.LastName, COUNT(*) AS StaffCount FROM...

If you set that property to 'No' you'll be able to call the IsNull() function in code, or the IS NULL operator in a query, and be confident that they will detect rows with empty column positions in the column in question. You might find that Access won't let you set the property to 'No'...

Disallowing Nulls is something Date would support. He argues that every column position in a table must be a legitimate value of the attribute type represented by the column. As Null is not a value, then it is not permissible. Codd, on the other hand, implicitly supports the use of Null by...

This illustrates the importance of defining each column (field) appropriately in table design view. In your case the appropriate properties would be: Required: No AllowZeroLength: No The first means that the column position can be Null, the second that a string expression...

That begs the question whether the reverse is also true, and a county can encompass more than one market areas in whole or in part. An analogy would be UK counties and postal areas, where postal area boundaries can overlap county boundaries. At my last address I lived in Staffordshire, and in...

If the County determines the Market #, storing the Market # in a column in addition to the County would introduce redundancy, and the table would not be normalized to Third Normal Form (3NF), which requires all non-key columns to be functionally determined solely by the primary key of the table...

The only advantage to be gained by including the Contact Types table in the form's query would be to order the rows returned by ContactType. Otherwise it would be omitted, and a combo box bound to the ContactTypeID foreign key column, set up in the usual way to show the referenced ContactType...

I have now added routines to select and print completed but unprinted invoices to the demo I attached earlier.

It will. The bound column of a combo box must contain distinct values, and is normally the key of the referenced table. In most cases this column is hidden. If the bound column does not contain distinct values, whichever of the duplicate values you select will map to the first instance of...

The following is a simple query for identifying gaps in a sequence: SELECT (P1.ProductID + 1) AS GapStart, (MIN(P2.ProductID) -1) AS GapEnd FROM Products AS P1, Products AS P2 WHERE P1.ProductID < P2.ProductID GROUP BY P1.ProductID HAVING (MIN(P2.ProductID) -...

In a case like this, where the report is opened only periodically, I would not use an object frame, or indeed reference the Excel data directly. I would import the Excel worksheet into a local Access table, which I'd empty first, and use a query on this table in the report, as the RecordSource...

Rounding is the conversion of a value from a higher precision to a lower precision. Prematurely rounding the return value of an expression is, in most contexts, inadvisable because, if the value is then used in further arithmetical operations, the lower precision is fed into those operations...

Rather than closing the login form once a user has successfully logged in, hide it by setting its Visible property to False in the form's module. You'll then be able to reference controls in the login form from other objects. In the attached little demo file the user logs in and enters a...

One correction to above. The accounting month needs to be formatted as 'yyyy mm' rather the calling the Month function, to cater for the accounting year spanning the calendar year: SELECT AcctYear (4, 1, T1.TransactionDate) AS AccountingYear, FORMAT(T1.TransactionDate, "yyyy mm") AS...

You can do all that with a single query, and you don't have to risk update anomalies by storing computed values in a column in a table. Using my AccountingYear.accdb demo again, and an accounting year starting 1st April: SELECT AcctYear (4, 1, T1.TransactionDate) AS AccountingYear...

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.