Search results

That sounds like a good piece of work. Not that I'm surprised, I've been greatly impressed by your posts in the month since I joined the forum. One of the worst sets of imported data I've come across was when I did some work for a bunch of nuns somewhere in America. Designing the database was...

Normalization by decomposition will pick up inconsistencies in the spreadsheet data, like the file I came across with three versions of my own name as author of technical articles in my own field of work. If this had been imported without being normalized the database would have seen me as...

Importing a spreadsheet’s data into an Access table, or linking to a spreadsheet is simple enough. However, a spreadsheet will rarely be structured in the correct way for representation of the data in a relational database, which normally requires a set of related tables, each of which is...

My TimeArithmetic demo contains the following function to return the 'week starting' date for any date: Public Function WeekStart(intStartDay As Integer, Optional varDate As Variant) ' Returns 'week starting' date for any date ' Arguments: ' 1. intStartDay - weekday on which week...

In many cases the range will be defined by user entered parameter values, so: WHERE TransactionDate >= [Enter start date:] AND TransactionDate < [Enter end date:]+1 is better as the user will normally enter the two dates. Generally the parameters will be controls in a form, so you can...

We nowadays tend to use the 24 hour clock for most official purposes here in the UK, and I always do in databases, but in everyday use people still use AM and PM. To Access a value at midnight is an integer, but as an integer date/time value is mostly used to record what we think of as a date...

A value of Date/Time data type with a time of day of midnight is an integer, so by default Access formats it as a date. To format it with a zero time of day include the hours, minutes and seconds in the format mask. The ISO standard for date/time notation allows midnight to be formatted in a...

If the Hours Worked column is returning a value of Date/Time data type then you can sum the two values with an expression like this as the ControlSource property of a text box in the form's Detail section: =DSum("[Hours Worked]","[NameOfYourQuery]", "EmployeeID = " & [EmployeeID] & " And...

To create a relationship firstly add both the Outcomes table and the referencing table to which your form is bound to the Relationships window. Then drag from the OutcomeID primary key of Outcomes to the Outcome foreign key column in the referencing table. Then right click on the line which...

Make sure that the fields are returned by the form's RecordSource, and that the control's you've added to the form have their ControlSource property each set to the correct field name.

Pat Hartman makes a valid point with regard to the need for the form to be open at the correct record when the report is opened. This is fine if, in the application, the only way to generate the invoice is from the form, and only one invoice need be generated at any one time. That's rather...

The values LEE and Withheld are data. 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 column positions in rows in tables, and in no other way. I would therefore recommend that you...

Has the form by any chance been opened with the acFormAdd constant as the DataMode arrgument? e.g. DoCmd.OpenForm "frmEmployeeList", DataMode:=acFormAdd

The functions in my TimeArithmetic demo attached to an earlier reply cater for this as below: Public Function TimeDuration(dtmFrom As Date, dtmTo As Date, _ Optional blnShowdays As Boolean = False) As String ' Returns duration between two date/time values ' in...

Taking the form in my TimeArithmetic demo as an example, a text box with the following expression as its ControlSource property could be added to the form's Detail section to return the total time worked by each employee per day: =DLookUp("DailyTime","qryDailyTimeWorked","EmployeeID = " &...

The attached file illustrates the use of a number of time arithmetic functions, whose return values are in conventional time format. If you open the form for the summation of date/time values you'll see that it returns the time worked in each row. At present there are two rows per day as in...

When building a date literal in code it prudent to format the date either in US short date format, as in MajP's reply above, or in an otherwise internationally unambiguous format such as the ISO standard for date notation of YYYY-MM-DD, as in cheekybuddha's reply. Otherwise, on a system like...

If you take a look at the DecomposerDemo file that I attached to an earlier reply in this thread, you'll see that, rather than linking to the Excel file, it imports the data from it into a predefined MasterTable, which is then decomposed into a set of predefined normalised tables. If, when...

The attached file illustrates how inactive entities, employees in this case, can be retained in a database, but not shown as an option for new records, when selecting an employee in a combo box. This is achieved by using the following as the combo box's RowSource property: SELECT...

So, you have 25 possible invoices per what? I'm guessing its per project or similar. In a relational database there would be a one-to-many relationship type between Projects and Invoices, to model which you'd have Projects table and an Invoices table. The latter would include a ProjectID or...