Search results

First of all, you should always use correct and complete syntax. DoCmd.TransferText (TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)

All of these facts should be stored in tables and thus be able to be evaluated. So show the database schema (representation of the tables with their relationships). Knowing this is always the starting point of a query. You also have to take into account that SQL performs set operations =>...

Things like object name autocorrection and display of sub-data sheets are performance brakes and should be turned off during operation. In my case, they are already turned off in a new, empty database.

TOP 1 of many equal delivers all, since there is no distinction.

This works, but is not a convincing solution. In the first subquery, "All" is duplicated according to the number of records in table1. UNION without ALL then performs grouping, which reduces the number again. In the second subquery, further grouping is performed using DISTINCT. This process is...

Access generally works locally, both with VBA and when using its own SQL dialect (Jet SQL). Compact & Repair - Are you talking about a frontend or a backend? When repairs are necessary, you should always be a little nervous. A frontend can simply be replaced with a clean copy. Compacting a...

? DateDiff("n", #0:00#, #2:45#), DateAdd("n", 165, #0:00#) 165 02:45:00 You can easily convert the minutes as a long into a DateTime value and vice versa. DateTime is also a number internally. Text as a data type is more complicated, you want to calculate with numbers. I would look at...

SELECT BuyerID, COUNT(*) AS NumberPayments FROM tblPayments GROUP BY BuyerID HAVING COUNT(*) <= 2

A calendar table only develops its real charm when the relevant fields are indexed (date value unique, additional fields with date formats simply indexed) so that these indexes can be used to improve performance in JOINs, filters and groupings. Providing the date values as in the query shown...

@The_Doc_Man describes this in his last paragraph: https://www.devhut.net/ms-access-persistent-connection-in-a-split-database/ Frontends should be installed LOCALLY, as should the necessary Access installation. This is of course different for a terminal server, but even then each client should...

ACCESS Dependency Checker by Thomas Koester However, in the case of the problem mentioned above, I do not believe that the presence of queries per se is the cause. You should broaden your view to include domain aggregate functions (DLookup, DMax, ...) that are used en masse in queries, forms...

You change the same field RaditPodle twice. Do you notice that? If you are serious about a temporary table, you will delete it and create a new one. When you delete it, all the legacy data disappears.

A DAO method such as Openrecordset passes the query directly to the database engine (Jet/ACE). This understands SQL, but cannot do anything with variables and objects such as form text fields, which is why the query occurs. In the simplest case, you make a replacement Forms.ClientAppt.Cname =>...

Q2 code modified SELECT Q1.drcname, Sum(Q1.PrizeConvert) AS SumOfPrize FROM (SELECT ...) AS Q1 GROUP BY Q1.drcname; detailed SELECT Q1.drcname, SUM(Q1.PrizeConvert) AS SumOfPrize FROM ( SELECT decrace.drdate, decrace.drid, decrace.drcname...

How does this work? Select queries are simply executed and produce a result. If you use such a query (stored query or subquery as an SQL statement) in the FROM part of another query, this "embedded" query is executed first, so the data is immediately and up-to-date available to the query.

Instead of creating a fireworks display with DSum, it is better to calculate everything in a database in one query. Approach: SELECT Year(DateCompleted) AS QYear, DatePart('q', DateCompleted) AS Quarter, SUM(QTY) AS SumQty FROM InspectionsT WHERE DateCompleted BETWEEN...

The variations are then no longer so slim. As requirements increase, the appeal of a RegEx solution increases.

To answer the question more literally: ? RegExReplace("1234 johndon street", "(\d+)(\d{3})", "$1,$2") 1,234 johndon street ' for examble in a query SELECT RegExReplace(D.Proadress, "(\d+)(\d{3})", "$1,$2") AS Proadress FROM tblData AS D Private pRegEx As Object Public Property Get...

That's right. The crucial question is not the dialogue, but the conclusion of the dialogue and the subsequent use of the result. If you do not save the import as such, the information for column data types and others will expire. Can you use an import specification for TransferSpreadsheet...

means (for me): - The import specification is stored in two system tables and can be viewed there. - The import specification stored under a can be used directly as an argument via standard import or linking in the same way as with TransferText. Also directly within queries. That won't happen...