Search results

  1. E

    Access won't "let go" of a linked text file.

    A link to a text file should not be a problem if you want to delete the text file. However, you must not have ACTIVE access to the link/file via DLookup / Recordset / bound form. I use a link via query for temporary access. When the query is no longer used, the text file is free. SELECT T.*...
  2. E

    "Search key was not found in any record" error while importing large .txt

    I would first ask what significance a search key has in the context of an import. There is nothing to search for in a pure data transfer. If you carry out specific actions, these should be described in more detail.
  3. E

    Search in any part of a list box

    You can SEARCH anywhere. The initial questions you should ask yourself are - What do you want to search for and how do you provide the search parameters? - What do you want to do when you FIND some / all hits? In our latitudes, people also like to go mushroom hunting for the experience itself...
  4. E

    When Update Query update No rows in a field (Because Condition not met) Error Message not Appear

    Use the code from @The_Doc_Man, with the small correction mentioned. Dim CurDB As DAO.Database ' => this is a DAO object DoCmd => this is an Access object Two objects that know nothing about each other. If you let one object work, the other one doesn't know what's happening. So DoCmd.OpenQuery...
  5. E

    When Update Query update No rows in a field (Because Condition not met) Error Message not Appear

    RecAff = CurDB.RecordsAffected A value other than 0 can only be generated here if the database object (CurrDb) is also used in the query. Using OpenQuery is something completely different and particularly counterproductive here. Not just copy a little bit, copy everything. Small correction...
  6. E

    Update multiple filtered invoices as "paid" and add payment notes without opening each invoice individually

    - This would also be the same for recordset.edit - Saving edits beforehand is now no longer a challenge.
  7. E

    Update multiple filtered invoices as "paid" and add payment notes without opening each invoice individually

    Dim sSQL As String sSQL = "UPDATE YourTable SET PaidField = True, PaymentNote = 'hello'" & _ " WHERE " & Me.subFormNeme.Form.Filter CurrentDb.Execute sSQL, dbFailOnError The first question that comes to mind is always a query.
  8. E

    Mail merge query with multiple records

    @AlliCarr Thinking about a modified mail merge and how to fill it out only makes sense if you have an idea of what it should look like in the end. It's nice when @Gasman shows something visual. But something like that should actually come from you, since it's about your task. I derive methods...
  9. E

    Use form filter text in SQL

    I said that if you make things easy at the beginning, you'll pay more later with more effort. I would of course offer my own interface for selecting filters and could therefore evaluate them because I know them. If you want to use the built-in helpers in Access, you have to know their name and...
  10. E

    Use form filter text in SQL

    But that doesn't sound simple or efficient when it comes to updating something. I would replace the hard-coded values with parameters in a statement as shown, the parameters would be taken directly from the ComboBoxes, and a clear query would be ready.
  11. E

    Use form filter text in SQL

    UPDATE MainTable SET FieldX = 123 WHERE ForeignID1 IN ( SELECT ID FROM LookupTable1 WHERE FieldY = "hello" ) AND ForeignID2 = ( SELECT ID FROM LookupTable2...
  12. E

    Use form filter text in SQL

    First of all, a filter is SQL, namely the content of a WHERE clause without the keyword WHERE. So nothing needs to be converted. For a better overview, you can remove the many unnecessary brackets. Therefore, the developer will not rely on superficial help from Access to put something together...
  13. E

    Convert the query contents

    If I use it, then yes. TRANSFORM MIN(X.SHID) AS xy SELECT X.m_box_id FROM ( SELECT T.m_box_id, T.SHID, "F" & DCOUNT("*", "tblNaobao", "m_box_id = '" & T.m_box_id & "' AND SHID < '" & T.SHID & "'") + 1 AS PivotColumn FROM...
  14. E

    Convert the query contents

    Or use a cross tab query. This allows you to convert rows into columns.
  15. E

    Mail merge query with multiple records

    Can you use a cross tab query directly as a source for a mail merge? Probably not. But you could wrap this query in a select query. Hopefully the PIVOT columns have consistent names. For higher demands, as already mentioned, automation would be used. - You use a Word template with bookmarks for...
  16. E

    Optimizing complex queries and reports - Emmanuel Katto

    Before using temporary tables, you should first look at the queries. A blanket statement "complex" is meaningless. For some, complexity occurs when more than two tables are linked or the total length of the SQL statement exceeds 35 characters.
  17. E

    VBA RecordSet if EOF

    About the technology: with rs .movefirst if .EOF = False Then The order is wrong. If the recordset is empty, a MoveLast/MoveFirst will generate an error.
  18. E

    Splitting text box text into sentences

    Insert code into a standard module and start with the F5 key. The output ends up in the immediate window (Ctrl+G).
  19. E

    Splitting text box text into sentences

    Sub test_datatypes() Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset( _ "SELECT AnyText AS F1, Replace(AnyText, '.', '.' & Chr(13) & Chr(10)) AS F2," & _ " Left(Replace(AnyText, '.', '.' & Chr(13) & Chr(10)), 65335) As F3 FROM YourTable") With rs...
  20. E

    ENGLAND

    The Germans say the same thing, and they had Spain on the verge of defeat. Unfortunately, they met Spain in the quarter-finals.
Back
Top Bottom