Search results

Hard to say which would be better. When joining tables on derivative values like this the real drawback is of course that the optimizer cannot make use of the indexes.

I'd suggest joining the tables on the formatted date/time values to ensure equality: SELECT tblFish.*, tblFlow.Fish_Count FROM tblFish LEFT JOIN tblFlow ON FORMAT(tblFish.Flow_DateTime, "yyyymmddhh") = FORMAT(tblFlow.Count_Date_Time, "yyyymmddhh");

Rather than calculating the values for the the computed controls in the form and subform, an alternative would be to return them in the parent form's RecordSource query as follows: SELECT *, DSUM ( "(UnitPrice*Quantity)", "SaleDetails", "SaleID = " & SaleID )...

I should have said all orders rather than open orders. To return only the latter the form's default RecordSource would be: SELECT * FROM Orders WHERE NOT Completed ORDER BY OrderDate

If those are the only allowed combinations of values you should enforce this constraint with a table-level Validation Rule: ([fldDiscNo] = 0 And IsNull([fldRecordingID]) Or [fldIgnore] = True PS: I see from the examples you posted earlier that other combinations seem to be allowed, so you'd...

When importing from Excel, the data will rarely be structured in a way correct for Access, in which data is stored in a set of correctly normalized related tables. The attached little demo file illustrates how data imported from Excel can be recast into a correct predefined structure by...

One way would be to amend the form's RecordSource property in code to join the form's default RecordSource to the query. As a simple example lets say you have a form based on the following query: SELECT * FROM Orders ORDER BY OrderDate; Rather than a button you could use a check box to toggle...

The attached StudentCourses file demonstrates a number of possible interfaces for a for a many-to-many relationship type. It includes a multi-select list box, or to save space in the form, a comma separated list. In the latter case the list is edited by means of small dialogue form opened...

If you split the database into separate front and backs ends, then you can have one back end file, distinct from your operational back end, with only the data you want others to have, e.g. referenced tables of cities for instance. You'd then distribute the front end with the 'safe' back end...

The following query references a query qryStockInHand which computes the current inventory per product and returns columns ProductID and StockInHand, and an OrderDetails table with columns OrderNumber, ProductID, Quantity, and UnitPrice. The query returns any orders for which there is not...

That's only a limitation of the built in formatting of values of DateTime data type. The underlying value is merely a 64 bit floating point number, so lends itself very easily to doing time arithmetic, and the result can easily be formatted to whatever you wish, e.g. Public Function...

Your question is a good example of where Boolean logic operators differ from our use of the same terms in day to day English. As the meaning of does not equal "#1" or "#2" is readily apparent in day to day English, you might think we could express this algebraically like this: x = 3 ? x <> 1...

In Control panel. In Category view, select Clock and Region | Change date, time or number formats. The ensuing Region dialogue allows the various default date and time formats to be set. In my case the default time format used by Access corresponds to the Long time format set in the dialogue.

In my case it shows as 14:00:00. I would normally set the Format property to whatever is the desired format, however, so the default format is immaterial. I'm guessing the default format set in Windows governs the behaviour in Access.

I would suggest that you also apply a ValidationRule property of Is Null Or (>-1 And <1). That will prevent a value other than a time only being inadvertently entered. I don't know whether a Sharepoint list allows this, but. if not, you should at least do it for the bound control in your data...

You can return a result table of all duplicated dates per machine per employee with a simple aggregating query: SELECT MachineID, EmployeeID, ShiftDate FROM tblShiftMachinesRan INNER JOIN tblShiftDay ON tblShiftMachinesRan.ShiftDayID = tblShiftDay.ShiftDayID GROUP BY MachineID, EmployeeID...

One way to restrict a form's recordset is to reference unbound controls as parameters in the form's RecordSource query. The following is a simple example: PARAMETERS Forms!frmEmployees!txtDateFrom DATETIME, Forms!frmEmployees!txtDateTo DATETIME; SELECT Employees.*, FirstName & " " & Lastname AS...

In the majority of cases i will reference controls in a form as parameters in the query's WHERE clause. The following is a simple example in which the parameters are three unbound correlated combo boxes in the bound form's header section: SELECT [FirstName] & " " & [LastName] AS FullName...

Is the Clients column of text data type, containing the client's name? If so the table should be decomposed so that it contains a ClientID foreign key column of long integer number data type, referencing the primary key of a Clients table in an enforced relationship. A query to return the...

Beware of names like mine in its original non-anglicized form: Cináed O'Siridean.