I recently came across vba update statements and I have been using Recordset.Edit and Recordset.Update to not only edit my existing data but to update it. I want to know the difference between the two: recordset.update and Update sql Vba statement. I think they all do the same but I can’t figure which o…
Tag: ms-access
Data type mismatch when using a Select Query to feed a combo box
I am getting a ‘data type mismatch in criteria expression (error 3464) with the below routine when trying to activate drop down box ‘cboColleagues’. In short, there are two combo boxes of which the second one (‘cboColleagues’) is not visible until the user activates the first (&#…
Access Query – [col]/[col].[col]?
Just inherited an Access database and trying to figure some things out. For the life of me, I have no idea what this query means. Its like the [col1]/[col2] is the table name and [col3] is the column. But instead of a table, it is math function (dividing two columns) followed by the column name. SELECT table.…
Import Data into Excel from Access Table
I am trying to import data into Excel from an Access table. I am getting a syntax error This error comes up when I run the line: Select [Time], [Tank], FROM “UnitOneRouting”, WHERE [Date] = ” & RpDate & ORDER BY Tank, Time”, cn, adOpenStatic, adLockOptimistic, adCmdTable In the…
How to search for for strings/keywords in Access database using PHP?
I’m working on a school assignment and I’ve an Access database which has a memo field that stores lots of text. I wanna know how I can search for specific keywords in that memo field. For instance, …
Summing up the rows
I want to add up the profit of all the rows where 1.a=2.a && 1.b=2.b && 1.c=2.c ( The case in first and second row , also in third and fourth ). I wrote the following query but this isn’t working . db.Execute “UPDATE a,b,c,SUM(profit) FROM Sum ORDER BY a” Query execution give…
Calling Stored Procedure while passing parameters from Access Module in VBA
I am working in Access 2010 with a Microsoft SQL Server 2008 backend. I have a stored procedure that inserts new values(supplied by the parameters) into a table. The values assigned to the parameters are obtained from files stored in a folder. The Windows File System is used to scan a particular folder to mak…
How to SUM() for past 3 months for every field in a column
I have a table in MS Access that looks something like this And I want to count for every item in every month, what’s the sum of the past 3/6/12 months and it should look something like this Is there anyway this can be done in SQL queries? Answer Try a self-join on Item and t2.Date <= t1.Date and t2.D…
SQL query search by ID and get another attribute of the record
I’m working on a DB where I need to search MAX(ID), where ID is an autonumber, (basically the most recent record/last record) and then when found I want to get the receipt number. e.g. receipt table …
SQL query to get the Sum of all column values in the last row in access table
I have a table like below. I want to sum parts column and write the total after the last record. how to specify total and the result in SQL query. Answer Try this query