Search results

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

That's a good approach, especially if the second form's recordset is restricted by parameters referencing the calling form. Provided that the referenced controls in each of the possible calling forms have the same names, the second form's RecordSource can easily be built in its Open event...

I would recommend using the OpenArgs mechanism as Gasman suggests. The following is code in an unbound form's module which builds the SQL statement for a query and passes it to a form as the OpenArgs argument of the OpenForm method: Dim strSQL As String Dim strWhere As String...

Because you provided no context to your original question. Consequently we were only able to deal with your issue at face value, which clearly pointed to an inappropriate course of action. If you'd taken the trouble to explain the background in the first place, then a lot of wasted time would...

The original question was answered in post #2.

The column names in the table you have posted differ from those in your code. If the Boxes table has the same columns as the table you've posted you'd have to change a query to the following, though I've had to guess what column names to use where they differ from those in your code: SELECT...

You could add the following line to the code: CurrentDb.QueryDefs.Delete "qryTemp" But, as Tom has said, it can be handy to have an object you can amend whenever you want a temporary query.