Search results

But what if some rows wrap to two or more lines?

You don't need to return any blank rows. You can simply force a page break in the detail section after each 20th row. Firstly add a page break control to the bottom of the Detail section. Next declare a module level variable in the report's module's Declarations area. The following is from...

To identify tables which are available for the whole of any given date/time range you firstly need to identify the intersections of two sets. You can then exclude those results from the available tables/times. This requires firstly an auxiliary calendar table, which is simply a table of all...

You can return the full address as a concatenated string expression in one query, and then base another query on that query rather than the base table to reduce the complexity. When concatenating values you might find the following function useful. It's an amended version of the CanShrinkLines...

I should have made it clear that this only applies to times under 24 hours. A value such as 31:05 returned in the example in my last post cannot be recognized directly as a time either in Excel or Access. When undertaking time arithmetic every step in a routine should be undertaken on the...

Excel will still treat the expression as a time, and it will be possible to do time arithmetic on it, as with your example, subject to the same proviso that applies in Access, that the result must be less then 24 hours to be intelligible. Otherwise the result, formatted as hh:nn will be the...

The DateTime data type in Access is implemented as a 64 bit floating point number, with zero representing 30 December 1899 00:00:00. There is really no such thing as a time value in Access, so if you enter a time without a date the numeric value by which this is implemented will actually...

The attached little demo file illustrates the basics of the sort of reservations database which The_Doc_Man describes. Its model is: The central functionality of the database is that it detects intersecting date ranges (date/time ranges in your case) to determine when a room ( table in your...

Storing each member's full address table in a single table implies that the table is not normalized to Third Normal Form (3NF), as this would involve a number of transitive dependencies on the key. The table is consequently open to to the risk of update anomalies. The address data is...

Like Duane, I'd suggest that you open a dialogue form containing a multi select list box, whose RowSource restricts the rows returned to those which reference the parent form's key used as the subforms LinkMasterFields property. The following is an example of the sort of code in the dialogue...

This implies that there is a relationship between Companies and Opportunities, modelled by a table CompanyOpportunities with columns CompanyID and OpportunityID for example. I'd assume that the query is correlated with a form's current record by referencing a CompanyID control or similar. The...

These tables each model a many to many relationship type between tblEmployeeList and the other referenced table by resolving the relationship type into two one to many relationship types, so, as Peter has explained you do not need the foreign keys in tblEmployeeList. One important point abput...

The key to that is correct indexing of the columns in the Access tables, along with correctly structured INSERT INTO statements. In my demo for instance the Country column in the Countries table is indexed uniquely, whereas the City table in the Cities table is not, because city names can...

The attached little demo file illustrates how to import data from Excel and then decompose it into a set of normalized related tables. It would equally apply to importing data from a .CSV file. The zip archive includes two versions, one in which the each cell in the Excel file contains single...

That should work. Try it and see.

There's no need to worry about birthdays on 29 February. The DateSerial function handles that, returning a date on 1 March in non-leap years, e.g. ? DateSerial(2025,2,29) 01/03/2025 There is no international consensus as to what is the anniversary of 29 February in a non-leap year. In some...

You could just loop through all possible birthdays for a given date, and see if one falls within the date range: Public Function GetBirthDay(dtmDoB As Date, dtmFrom As Date, dtmTo As Date) As Boolean Dim n As Integer Dim dtmBirthday As Date For n = 0 To 120 dtmBirthday...

Is it necessary to store the line numbers in the table, rather than just computing them in a query, from which the json can be generated? That way you can guarantee that they will always be sequential. The following is a simple query which returns subsets of row numbers per customer: SELECT...

Which is one good reason the correct methodology in a relational database in a context like the OP's is summation over a set of rows rather than addition of a fixed number of values.

While we are on the subject I might as well throw in my six pennyworth. The following Module was developed years ago by Stuart McCall and myself when we were both sysops in the old CompuServe Access forum. Stuart built the original module to pass a comma delimited argument list. Later, in...