While the query should return Null based on the join type, as Allen Browne states in his article, Bug: Outer join expressions retrieved wrongly, “Instead, it behaves as if [the JET query optimizer] is evaluating the expression after it has returned the results from the lower-level query.” Consequently, you must select the calculated field using … Read more
Date & Time input in access use #, since access can’t do auto conversion from char/text into date or time in SQL Query (or access call it query), and you better use international standard for inputting date time which was YYYY-MM-DD HH:NN:SS (4-digit year, 2-digit month, 2-digit day, 2-digit hour, 2-digit minute, 2-digit second) so … Read more
Well, it looks like Access can’t do aggregates in UPDATE queries. But it can do aggregates in SELECT queries. So create a query with a definition like: SELECT func_id, min(tax_code) as MinOfTax_Code FROM Functions INNER JOIN Tax ON (Functions.Func_Year = Tax.Tax_Year) AND (Functions.Func_Pure <= Tax.Tax_ToPrice) GROUP BY Func_Id And save it as YourQuery. Now we … Read more
[For the record, the official name for what once was ‘Jet’ is now the ‘Access database engine’.] For ACE (the Access2007 engine .accdb format) features it has to be ACEDAO. For Jet 4.0 features it has to be ADO classic. For Jet 3.51 features and earlier, pick either ADO or DAO. There are advantages and … Read more
You can try this query: Select A.*, (select count(*) from Table1 where A.ID>=ID) as RowNo from Table1 as A order by A.ID
In the cmdUpdate button’s on click event, you need to place a procedure that will generate a sql command, then execute it. Private Sub cmdUpdate_Click() ‘Create a string variable to hold your sql command string Dim strSQL As String ‘Fill the variable with your sql command, plucking values out of your ‘form as shown using … Read more