select top 10 Name, Price from MyTable order by Price desc Updated: @Fionnuala pointed out that: “Access SQL selects matches, so it will select all items with the same highest prices, even if this includes more than 10 records. The work-around is to order by price and a unique field (column).” So, if you have … Read more
Here are some notes that may help you towards your goal, however life would be a lot easier and a lot safer with autonumbers. This is VBA as you mention MS Access. Function NextTranNumber(ByRef FirstTran As Long, _ ByRef LastTran As Long, Optional BlockSize = 1) Dim cn As New ADODB.Connection Dim rs As New … Read more
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
While the Access/JET TOP keyword does not directly provide an OFFSET capability, we can use a clever combination of TOP, a subquery, and a “derived table” to obtain the same result. Here is an example for getting the 10 rows starting from offset 20 in a Person table in ORDER BY Name and Id… SELECT … Read more
I used the technique similar to JohnFx To trap the Selection height before it disappears I used the Exit event of the subform control in the Main form. So in the Main form: Private Sub MySubForm_Exit(Cancel As Integer) With MySubForm.Form m_SelNumRecs = .SelHeight m_SelTopRec = .SelTop m_CurrentRec = .CurrentRecord End With End Sub Here is … 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
“Run-time error ‘3061’. Too few parameters. Expected 1.” I believe this happens when the field name(s) in your sql query do not match the table field name(s), i.e. a field name in the query is wrong or perhaps the table is missing the field altogether.
Consider: TRANSFORM First(Data.Cat) AS FirstOfCat SELECT Data.ID, Data.Name FROM Data GROUP BY Data.ID, Data.Name PIVOT “Cat” & DCount(“*”,”Data”,”ID=” & [ID] & ” AND Cat<‘” & [Cat] & “‘”)+1; Or if there is a unique record identifier field – autonumber should serve: TRANSFORM First(Data.Cat) AS FirstOfCat SELECT Data.ID, Data.Name FROM Data GROUP BY Data.ID, Data.Name PIVOT … Read more
My approach to security in Access is the following: Encrypt the main database using a complex password. Use a second database to log in to this encrypted database. The second database stores usernames, user salts, and an encrypted version of the main database password with the user password. This way, no user needs to have … Read more
Solved it now guys, works well. For other peoples convenience. TargetURL = “https://www.mysite.co.uk/app/api/v1/test” Set HTTPReq = CreateObject(“WinHttp.WinHttpRequest.5.1”) HTTPReq.Option(4) = 13056 ‘ HTTPReq.Open “PUT”, TargetURL, False HTTPReq.SetCredentials “user”, “password”, 0 HTTPReq.setRequestHeader “Content-Type”, “application/x-www-form-urlencoded” HTTPReq.send (“test[status]=” & Forms!curl!Text0.Value & “&test2[status]=” & Text2.Value) MsgBox (HTTPReq.responseText)