FAQ Database Discussion Community


ACCESS: calculate timestamp difference between rows

sql,ms-access
Here is the data I am working with in MS Access from a system tracking when a agent makes system changes: |agentid|eventtype|reasoncode|eventdatetimelocal | |1830 |2 |32762 |01/01/2014 7:11:44 PM| |1830 |3 |0 |01/01/2014 7:13:46 PM| |1830 |2 |32762 |01/01/2014 7:14:55 PM| |1833 |2 |0 |01/01/2014 7:11:35 PM| |1833 |3 |32762...

How to Continuously Develop and Deploy an Access 2010 Database Application

ms-access,deployment,ms-access-2010
I've been developing an Access 2010 Database Application, let's call it Skill.accdb. I'm at the stage where I want to start deploying it so users can start using it and continue developing at the same time. I've been reading MSAccess-Deployment-Best-Practices, Deploy-an-Access-2007-application, and Ways-to-share-an-Access-database which talk about splitting, compiling, installing, etc.,...

Access, Update Query that does not overwrite data with blank cells

sql,database,ms-access
With this Update Query, how can I prevent it from overwriting data in existing rows with blank cells? UPDATE tbl1 INNER JOIN tbl2 ON tbl1.thing0 = tbl2.thing0 SET tbl2.[thing1] = tbl1.[thing1], tbl2.[thing2] = tbl1.[thing2], tbl2.[thing3] = tbl1.[thing3]; I have users on-site updating a table in real time and worry that...

Query in Access using NOT LIKE

sql,ms-access,sql-like
I am using a query as follows. SELECT * FROM TableName WHERE ([Material Description] Not Like "*LICENSE*" Or [Material Description] Not Like "*LICENCE*"); However, this fetches me results having records with LICENCE or LICENSE in the Material Description field. Please advise as to what is that I am missing so...

group by access seperate column

sql,ms-access,select,group-by
I'm very new to SQL and have the following (seemingly simple) database query. I have a table containing all timetracking information for a set of employees: date employeeId timeIn timeOut 01/01/20 1355 12:00 4:00 01/01/20 1355 4:30 6:00 01/01/15 1234 9:00 12:00 02/01/15 5555 5:00 6:00 02/01/15 1111 1:00 4:00...

Get the VBProject of a Database

vba,ms-access,access-vba,vbe
Given a database object in MS Access VBA, how can one get that database's VBProject? Function GetVBProject(ByVal db As Database) As VBProject Set GetVBProject = ??? End Function The only way I know how to get VBProjects in Access is through Application.VBE.VBProjects.Item(???). However, I won't know what order of the...

printing label at specific position

vba,ms-access,access-vba,ms-access-2007,ms-access-2010
I have 11 inch X 8.5 inch paper to print labels. The paper is divided into two columns i.e 4.25 width of each column containing 11 labels in each. So, Size of each label is 1 inch X 4.25 inch. Now my Question is: I'm using Northwind DB in MS...

VBA keeping excel files locked from myself after code

excel,vba,excel-vba,ms-access,access-vba
I've been running into issues with this code. It works fine if I restart the computer and run it, but once the code has been run once it starts to cause errors. I will either get either the "save error" or the "admin error" because the file (either the original...

Get work time in minutes based on shift schedule

sql,sql-server,sql-server-2008,ms-access,user-defined-functions
In production we have 3 shifts. Each Shift timing is described in table tbl_ShiftSched: WT - work time, PT - break time. ShiftTmID - schedule for 2 and for 3 shifts. I am looking for easy way to get work time in minutes having start and end time. For example,...

retrieve current path from file saved on server

vba,ms-access
How I can retrieve the current path of current db? I've one AC07 program, to distribute it I save one copy on the intranet server, how to copy this program into our PC and then use it? Always some people open the file directly on server. When the file is...

Is there a more efficient way to display multiple query results in different text boxes on a Form

vba,ms-access,access-vba,ms-access-2007
I have a form with several different text boxes. Each text box is populated with the result of a different query. I'm not very familiar with VBA / Access (I am teaching myself) and am not sure if what I'm doing is the most efficient. I was hoping someone could...

Microsoft Access VBA Run Time Error 2465

vba,ms-access,access-vba,runtime-error,ms-access-2010
I am currently working on a form to update fields in my database. The button (cmdFind) is meant to find the record for the part # (entered into text box txtFindPart), and then populate the data into In1-52 and out1-52. When I run it I get Run-time Error 2465 Microsoft...

SQL Syntax Error Missing Operator MS Access

sql,ms-access
I'm trying to run a query that will update a field in one table if a field in another table is equal to test. Here is the code: UPDATE Table1 AS t1 INNER JOIN Table2 AS t2 ON t1.Field1 = t2.F_Name SET t1.Field4 = (CASE WHEN t2.PlayField = 'test' THEN...

OleDbConnection to MS Access: SQL query like clause with escape characters and wildcards

c#,ms-access,like,oledbconnection
There must be some very obvious answer but i just can not see it nor find solution from web. I try to count from db table how many rows contains file path LIKE "path". In Access Settings-table I have rows where path field (short text type) containing string: \\server\dir\something\ I...

Access filter on time

sql-server,sql-server-2008,ms-access,access-vba,ms-access-2007
I have a table with a time in a datetime field on an MSSQL2k8R2 server. I have linked the table in Access 2007. The table was migrated with the migration assistant from Microsoft. i.e. the Managemantstudio will give on SELECT TOP 3 [AbZeit], [AbBrP] FROM [dbo].[tSollFahrten] the Result AbZeit AbBrP...

Don't understand why this query crashes Access

sql,vba,ms-access,access-vba,ms-access-2010
I have a query with a nested query in it like so: select table1.name, table1.address from table1 where table1.year=[forms]![form1]![year] and table1.name not in (select table2.name from table2 where table2.year=[forms]![form1]![year]) This crashes Access every time. Am a doing something that isn't allowed?...

Why do I have a decimal error when writing data to MS Access via Matlab?

matlab,ms-access,rounding,numeric
I write numeric data to MS Access tables. All the tables have the same numeric fields format (ie 'real simple', 'standard', 'auto'). I push numbers via Matlab in the tables and MS Access rounds numbers to the 1rst decimal and i can't modify it. Does someone know ? Screenshots there...

Data type mismatch when inserting record

sql,vb.net,ms-access,parameters,insert
I've this piece of code for inserting records into an Access table. LastUpdated field is defined as Date/Time at database level. It fails when inserting giving the error Data type mismatch in criteria expression I'm using parameterized query which avoid problems with formatting values and it's very weird because I've...

Populate Text box on report based on value on another form

ms-access,access-vba,ms-access-2007,ms-access-2013
Very novice question but I have a report that has a couple of columns including usernames. I also have a form where a user can select a user name in a combo box and the report will open up only showing records that pertain to that user. Is there a...

Group records by certain fields but only if they're “next” to each other

sql,ms-access
I need to group records in a table based on some odd requirements. I've gotten very close to what I need, but I can't get the last bit. ID Task Time 1 Task1 6:00 AM 1 Task1 6:05 AM 1 Task1 6:10 AM 1 Task2 6:15 AM 1 Task2 6:20...

Microsoft Access 2013 sql return which table information came from

sql,ms-access
I am re-making a data base to store an inventory of ID numbers, Locations, and Item type. In my older data base there are a number of tables (16) or so. I have written code to produce a list of all items in the data base (around 1500) and produce...

How do I resolve the “Enter Parameter value” error in MS-Access

sql,excel,vba,tsql,ms-access
This code and another is producing the logical error in Ms-Access where it asks for a parameter equal to an objects name. In this case it wants [DSRT_ERS].[ID]. INSERT INTO DSRT_ERS SELECT * FROM DSRT_TEMP WHERE [DSRT_ERS].[ID] <>[DSRT_TEMP].[ID]; If you look at the DB's documentation you notice it is spelled...

MS Access programmatically edit a datasheet cell value

ms-access,access-vba,ms-access-2013,datasheet
I'm validating my datasheet programmatically because it's not possible to do it the "normal" way (linked tables). The "normal" way to tell the user they entered a badness is to punish them with MsgBox popups - I want to display a message in a label on my form (this works)...

Adding a column to existing table in access without using any relationship

sql,ms-access,ms-access-2010
I am working on a project in ACCESS 2010 that requires me to give a rank to 30000 products based on their sales. I have tried using a query to do the ranking and it takes a long time. (Please find codes at Making the ranking query efficient) I figured...

Access Navigation form stop requerying forms

ms-access
in my MS Access 2013 application, I am using a Navigation Form as a top layer to connect all my subforms. How do I stop Access from requerying the subforms whenever I change tabs (forms) ?! (Basically I just want to use the navigation tabs as a way switch the...

How to set primary key when linking with CreateTableDef

sql-server,vba,ms-access,ms-access-2010
In a MS Access database, I'm connecting to views in a SQL Server database like this: Dim s As String s = "ODBC;DSN=mydb;Trusted_Connection=Yes;DATABASE=mydb;" Dim td As TableDef Set td = CurrentDb.CreateTableDef("vwMyView", 0, "MySchema.vwMyView", s) CurrentDb.TableDefs.Append td CurrentDb.TableDefs.Refresh This creates a linked table, which is linked to a view in SQL...

pass parameters to record source with query Access2007

ms-access,access-vba,ms-access-2007
I am relatively new to access and I have been tasked to improve the navigation of the app. I have a form which is used to open the report. The record source of the report is as follows, SELECT Projects.Project_Number, Projects.Customer, Projects.End_User, Projects.Engineering_Company, [Merged Ship and Delivery Dates].Sched_Next_Delivery_Date, [Merged Ship...

OleDb parameter query in Winforms (C#): no errors but no rows updated

c#,winforms,ms-access
I'm trying to update a table in MS Access, but the update operation doesn't work and I don't get an error. The rowsAffected are 0, so nothing was updated. Here is my code: public bool UpdateAddress(AddressModel address) { using (OleDbCommand dbCommand = new OleDbCommand()) { // Set the command object...

Iterate Databases and Delete Query

ms-access,access-vba
I am trying to iterate a table with database names in it, open the database, check if a query exists, if it does delete it, then import a query from the database this code is being executed from. I keep getting a compile time error on the line of Set...

MS Access validation rules not firing in subform

vba,validation,ms-access,access-vba,ms-access-2013
I have a datagrid that is a subform inside another form. I need to validate items inputted into this datagrid subform, but I'm having an excruciatingly hard time figuring out how. Whenever I read about this on forums and on Microsoft's help site, they recommend you use Required and Validation...

Adding Access totals cause report to not run

ms-access,access-vba,ms-access-2010
I am trying to add a few totals (sums and counts) using the wizard to an Access report. When I add them to the report and try to view the report, the report fails. Without the totals the report will display. The only thing I can figure that may be...

comparing two dates. Equal isn't working as well

sql,date,ms-access,compare,equals
I've got trouble with comparing two dates: There is short datetime field in database, recorded by Microsoft Access 2002-2003 data extension (#yyyy/mm/dd#). When I tried to compare via operators "greater than" and "less than" it works fine, but when I replaced with the operator "equal" it's giving me an empty...

Classic ASP Post to MS Access DB - Doesen't Work from Textarea Editor

ms-access,asp-classic,tinymce
I use a textarea modifyed with TinyMCE but when I send the form to New.asp which is my "New Post" page, it simply won't upload the content to the server. That is the error... Microsoft Office Access Database Engine error '80040e14' Syntax error (missing operator) in query expression ''&#60;p&#62;-: CLEANED...

Access VBA To Send Query Results to Outlook Email in Table Format

vba,ms-access,outlook
I would like to send an e-mail with outlook based on the query results from my table but with table formatting (in the body). For some reason the code is only outputting the last record in the table to the e-mail body, instead of looping and adding all 3 records....

ACCESS 2007 - Form / subform filtering between dates

vba,date,ms-access,filter
In ACCESS 2007 I have a main form with two unbound comboboxes named cmbStavOd and cmbStavK, both formatted as Short Date. I have a subform named frm_qryNaklady_subform, based on a query with a field named datNakladDatum (textbox name datNakladDatum), formatted as Short Date. I'd like to allow users to enter...

How to execute four queries once and then check success or failure?

vb.net,windows,visual-studio-2010,ms-access
I need to execute four queries and then if there is success must return true otherwise false. The queries affect the database but the function returns false Private Function save_to_data() Dim success As Boolean = False Dim conn As OleDbConnection = GetDbConnection() Dim total_due As Decimal = sanitize(txt_total_due.Text) Dim amount_paid...

I need help setting the RecordSource of a Report within a VBA Function

vba,ms-access,access-vba
Ok, so I've been looking around here for a few days (and a few other sites) and while I have modified my code a good bit to find a solution, it still doesn't work. I have four source queries, and I'd like to set the record source of my report...

LIKE operator in SQL for my Access Database returns no values in ASP CLASSIC while it does if the query gets copied directly in Access

sql,ms-access,asp-classic,sql-like
So as the title says, I'm trying to run a query in my ASP Classic page but for some reason it doesn't return a record set while it does returns a record set it if the query is copied directly in Access. There is one thing where it probably goes...

SQL for Aggregate 25 percentile value for subsets in MS Access?

sql,ms-access,ms-access-2013
This gets the 25th percentile value of the GM field when field GICS Sector = "Energy": SELECT tblFirst250.[GICS Sector] , 0.75*( SELECT Max(GM) FROM tblFirst250 WHERE tblFirst250.GM IN ( SELECT TOP 25 PERCENT GM FROM tblFirst250 WHERE tblFirst250.[GICS Sector ]= "Energy" AND GM Is Not Null ORDER BY GM)) +...

Attempted to load class “COM” from namespace -Symfony2

php,class,symfony2,ms-access
I am developing a web using symfony2 framework. In this web the users upload file(.mdb). The web read the file and if the information is correct it pass to database.(This is the idea) The problem is that i try to used "COM class of PHP". First i think that i...

what will be the select query for excluding empty values in ms access

ms-access,select-query
I am trying select * from table where Contact Is Not null but it is displaying values including empty values...

Access SQL: Not Equals on Null Text Field

sql,ms-access
I have this bit of code: UPDATE NewMaster SET NewMaster.[Risk Mitigation Status] = "Pending Audit/Assist" WHERE (NOT (NewMaster.[Risk Mitigation Status] = "Complete")); This updates all rows to "Complete", except "Pending Audit/Assist" (expected) and NULLs (for me, completely unexpected!) Same with <> operator. Do I just need to add OR IS...

Adding calendar in ms access and view the data of specific day by clicking the date

database,ms-access,calendar,ms-access-2010
I'm working on MS Access, using Northwind DB. I'm trying to to add a calendar in a form and link it with the dbo_orders table. such that i can retrieve how many orders made on particular day by clicking on the date in calendar. Ex. Consider the dbo_Orders in Northwind...

Using date in CreateQueryDef

vba,date,ms-access
I have a table on which I run a query that I export to Excel. Here is an example: Table: Food Item | Price | Limit_Date | ------------------------------- Carrot | 0.80 | 08/07/2015 | Salmon | 4.30 | 01/07/2015 | Biscuits | 2.40 | 15/12/2015 | Milk | 1.00 |...

Specified cast is not valid. c# mdb database

c#,ms-access,null
I'm using mdb database with my c# application, I have table products which has 3 columns (Id,Name,Note), note column contain NULL values. objConn.Open(); listView1.Items.Clear(); OleDbCommand cmd = new OleDbCommand("select prod_Id,prod_Name,prod_Note from products", objConn); OleDbDataReader dataReader = cmd.ExecuteReader(); int i = 0; while (dataReader.Read()) { listView1.Items.Add(dataReader.GetValue(0).ToString()); listView1.Items[i].SubItems.Add(dataReader.GetString(1).ToString());...

Syntax for opening a report

ms-access,access-vba,ms-access-2010
I am getting a Runtime 2465 error message trying to open a report using the following routine: DoCmd.OpenReport "rptSprechi", acViewPreview, , [qry_rptsprechi].[WasteType] = "UE", acIcon ...

Getting value of Date Time Picker and using BETWEEN for filtering date in Ms Access database

vb.net,ms-access,oledb
I have a From and To DateTimePicker. I converted them into a String and use it to filter a MsAccess database of a specific range but there is a problem with the filtering. For example, I would like to filter from January 1, 2015 to January 31, 2015. The results...

DMedian in access 2013, no values returned

sql,ms-access,access-vba
I have the following query in MS Access 2013 than does not return a Median value. The field IU is always NULL (blank) or 1. Column GM is a number formatted 0.0000 between -5 and 5 with NULL values in it occasionally. SELECT IU, DMedian("GM","tblFirst250","IU=1") AS MedianByIU FROM tblFirst250 WHERE...

Importing data from access to excel using vba

excel,vba,ms-access,import
I am trying to import data from the table "memory" in my access database called "Computer.accdb", which is located on the desktop to Excel. I was able to find a code online and it was able to run but it did not work properly. I added a reference to the...

Access 07 VBA Get Table by Path

vba,ms-access,access-vba,ms-access-2007
Is it possible to reference (in VBA) a non-linked Access table by its full path? For instance, say you're building a form that draws from a set of tables, but your user needs to add tables as time goes on; you might start with tableA, tableB, and tableC, but a...

SQL Update query on query result

sql,ms-access,ms-access-2010,inner-join
I have 2 tables like: Table 1 SID Sdefinition CValue 4057 s1 32 4058 s2 4059 s3 6 4060 s4 Mapping_tbl SID SINID ECFID SID-SINID 4057 1099 4027e 1099_4057 4058 1099 4027e 1099_4058 4059 1121 4003e 1121_4059 4060 1121 4003e 1121_4060 Query1 SELECT Mapping_tbl.SID, Table1.Sdefinition, Table1.CValue FROM Table1 INNER JOIN...

Can we create stored procedure in ms access 2007 [duplicate]

ms-access,stored-procedures
This question already has an answer here: can make stored procedure or function in Access 2003? 2 answers How can we create stored procedure in MS Access 2007. If we can then how should write procedure for insert statement....

Converting ADODB Loop into DAO

excel,vba,ms-access,ado,dao
Hi I've been developing a vba project with a lot of help from examples here. I'm trying to access a MS Access database from Excel VBA and import large data sets (500-100+ rows) per request. Currently, the following loop works using ADODB however, the Range("").Copyfromrecordset line is taking very long...

for-loop add columns using SQL in MS Access

sql,ms-access,table,for-loop,iteration
I am trying to add n columns to a table, like in this example of code where n = 10: Sub toto() Dim db As Database, i As Integer Set db = CurrentDb() For i = 1 To i = 10 db.Execute " ALTER TABLE time_series " _ & "ADD...

Get a Count of a Field Including Similar Entries MS Access

sql,ms-access,ms-access-2010
Hey all I'm trying to parse out any duplicates in an access database. I want the database to be usable for the access illiterate and therefore I am trying to set up queries that can be run without any understanding of the program. My database is setup where there are...

Add 1 to datediff result if time pass 14:30 or 2:30 PM

sql,ms-access,ms-access-2007
Here is my SQL query which returns number of days by subtracting current date from specified date and returns exact as i need, but in addition i want to add 1 to result if current time passes 14:30 or 2:30. My query SELECT reservations.customerid, DateDiff("d",reservations.checkin_date,Now()) AS Due_nights FROM reservations Am...

ms access query very slow

sql,ms-access,join
I have this ms access query: SELECT t1.sb, suchbegriff2, menge FROM (SELECT artnr & '/' & [lfdnr-kal] AS sb, left(suchbegriff,7) & val(right(suchbegriff,4)) AS suchbegriff2 FROM kvks WHERE suchbegriff like '*/*') AS t1 INNER JOIN (SELECT artnr & '/' & [lfdnr-kal] AS sb, [artnr-hz] & '/' & val(lfdnr) AS hz, menge...

Editing a query in another DB

vba,ms-access,access-vba,ms-access-2010
Is it possible to open a second Access database from within an Access database, and edit a query in that second DB? I know you can open one Access DB from another, but I'm just not sure whether or not you can edit a query that way. If it's possible,...

Quartile/Percentile in MS Access via SQL with a GROUP BY when some values can be NULL

sql,ms-access,ms-access-2013
I am looking to calculate a percentile of a subgroup for a field that can be NULL. Field IU is either 1 or Null. Specifically: *my table: tblFirst250 *group by: IU = 1 (which is Nullable) *percentile of: GM (which is Nullable) I am starting with the following (but I...

How To Use The Where & Like Operator Together in SQL?

sql,ms-access,where,like
I want to display a List of Instructors with only first name and last name, from Georgia and whose last name ends in ‘son’. I have written this so far: SELECT firstname, lastname, state FROM instructors WHERE state = 'ga' AND lastname LIKE '%son' But it is not returning the...

Calling MS Excel function from MS Access VBA

excel,vba,excel-vba,ms-access
I am working an MS Access application a part of which uses Beta Distribution function. Since MS Access does not have Beta Distribution function of its own I'm using calling BetaDist function from MS Excel. I've tested the code in MS Excel and it seems to run successfully. In MS...

Recordset.Edit or Update sql vba statement fastest way to update?

sql,vba,ms-access,access-vba,ms-access-2003
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...

Change Index Settings Access VBA

vba,ms-access,access-vba
I'm trying to automate a process in Access, and one of the steps I wish to automate is changing the Indexing settings of certain fields in my tables. I need to do this to increase the speed of subsequent queries (the query is about 100x faster with the indexing). In...

MS Access multi (INNER, LEFT & RIGHT) JOIN query

sql,ms-access,join,ms-access-2010,outer-join
Ok, here's the thing. I have the Following tables involved : > YEARS ------------------------------ ID YEAR ACTUAL ------------------------------ 1 2014-15 TRUE 2 2015-16 FALSE > SHOPS ------------------------------ ID NAME ... ------------------------------ 1 ThisShop ... > ITA ------------------------------ ID YEAR_ID SHOP_ID ------------------------------ 1 1 1 2 1 2 ... > INSPECTORS...

InvalidArgument=Value of '1' is not valid for 'index'

c#,ms-access
I have an error in my C# application that interacts with MDB access DB. The error is: InvalidArgument=Value of '1' is not valid for 'index'. My code: objConn.Open(); listView1.Items.Clear(); OleDbCommand cmd = new OleDbCommand("select a.bill_Id,a.bill_Number,a.bill_Date,c.sup_Name,Sum(b.de_NetPrice),a.bill_Note from (suppliers c right JOIN bills a on c.sup_Id = a.bill_From) LEFT JOIN bill_Details b...

MS Access, Combobox, select fields bases on query, How do I weight the result fields

sql,ms-access,ms-access-2010
I have a db where based on user input a series of comboboxes will populate. Private Sub txtUserInput_AfterUpdate() cbo1.RowSource = "Select txtValue from tbl1 where (txtValue=" & [Forms]![Form1]![txtValue] & ")" cbo1.Requery End Sub Easy enough. But I would like the order of the results to change based on weights I...

What are difference between referencing a DAO Recordset field?

ms-access,access-vba
I am curious what are the differences between the following and if there are advantages for one over the other. Assume MS Access and DAO Recordset rst![field] rst.Fields("field") ...

Solution to Find string from a list , to another string VBA

excel,excel-vba,ms-access
What I would like to do is, I have an excel where I load every day some football, tennis [Daily List] : https://www.dropbox.com/s/45ulku7val6q6lj/sample.jpg?dl=0 Additionally I have another excel file/sheet with some Name of Tennis Players, Football Teams Names, Football Leagues in this format : [RedList] : https://www.dropbox.com/s/h9nqx2zpq696o38/redlist.jpg Of course i...

How to calculate number of days by subtracting date_time field from current date_time?

sql,database,ms-access,ms-access-2007
I have reservations table to store hotel reservations, this table has reservation_id, customer_id, rommno, and checking date also there is another optional field, checkout field which will use when person checked out and also the status field will updated to checkout status wile firstly putted checking status. i need a...

Verify If New Record Access 2010

vba,ms-access
I have a table created to hold all my client data. The data is all entered in via form. When a new record is created (new client added) I also need to create an opening transaction balance on a separate table. So what is the best way to... 1) Identify...

Run various queries based on a combo box selection

vba,ms-access,drop-down-menu,access-vba
I have a form where a user will select a value from a dropdown. Based on this selection, I would like a series of queries to execute. For example, if a user selects 'A', then queries 1, 2 and 4 would execute. If user selects 'B', then queries 4, 2,...

MS Access If Function

sql,ms-access
I'm not using SQL at this point, I'm just trying to define a function using query design function in MS Access 2010. What i'm trying to do: So turns out that I have a 5 month spread (Jan,Feb..May) where each month is a column. Turns out that at times May...

Running Access from Command line but not loading it?

ms-access,ms-access-2010
I would like to launch a MS Access macro from the command line (it's being run from another tool) to generate an output. Thing is, I'd prefer it if it could supress access from loading it's interface. I just want it to run the macro and shut down. Any thoughts...

Count Distinct Number of Email Addresses in the Results of a Query in MS Access

ms-access,count,distinct
I have a query in MS Access to return multiple columns from multiple tables for a user-specified period of time. This is for a training registration database, and often times, people take multiple classes during a given time period. I need to get a count of the distinct number of...

How to create a date using strings in vba?

vba,ms-access,access-vba,ms-access-2003
I want to run a for-loop from sometime to some specific time. Let's say from the first day of the year to the last day: I am given the year and I need to add the month and the day to it: I am trying to concatenate into a full...

Dapper, MS Access, Integers and “no value given for one or more required parameters”

c#,ms-access,visual-studio-2012,oledb,dapper
I keep getting the error "no value given for one or more required parameters" on the third attempt to insert in the code below. The first two attempts work fine. I'm running in .NET 4.0, against MS Access in VS 2012. And this problem appears to be consistent whether I'm...

Connecting to ODBC using pyODBC

python,ms-access,odbc,pyodbc,dsn
I've read all the faq pages from the python odbc library as well as other examples and managed to connect to the DSN, using the following code: cnxn = pyodbc.connect("DSN=DSNNAME") cursor = cnxn.cursor() cursor.tables() rows = cursor.fetchall() for row in rows: print row.table_name but for everything else I keep getting...

Select Query Doesn't Show All Results

sql,ms-access,ms-access-2010
I have a combobox on a subform (ProgramSubform) in Access that's supposed to list report years for a project from a table (Program). Most projects have more than one report year, but the combobox always brings only 2014. For example, the dropdown for project 5278 should be: 2012 2013 2014...

Database only adds (x) amount of rows before error system resources exceeded

database,vb.net,ms-access
I am having a problem with my code where i am only able to add so many lines of text before i get an error "system resources exceeded". This is my code: Dim x As Integer = MsgBox("Update Record?", MsgBoxStyle.YesNo, "Are you sure?") If x = MsgBoxResult.Yes Then Dim accessconn...

Create Table - Time Statement

sql,datetime,ms-access,ddl,create-table
I am having trouble trying to create a table using MS Access. I know for sure my problem lies in the "datetime" field but I can not figure what I am missing or doing wrong. When I click "run" I get the "Syntax Error in Field Definition" Create Table Appointments...

copy formatted text into access using vba

ms-access,ms-word,access-vba,ms-access-2010,word-vba
I need to save formatted text from Word in an Access Database. So far I've managed to figure out how to store formatted text in an Access Field (Create a Memo Field in a Table and set the Text Format as Rich Text). Searching SO I have not yet come...

Access 2013 - Set a field value based on value of another field

vba,ms-access,ms-access-2013
I have a combo box (Status) which includes the following: Shortage Allocated Actioned Acknowledged Complete I also have 5 other date fields which are as follows: Shortage_date Allocated_date Actioned_date Acknowledged_date Complete_date However I want this status to be populated automatically based on what data has been entered in my previous...

Using “V” to represent vowels in user input in SQL

sql,ms-access,access-vba
I need to run a query, in which the user inputs a string, and then the query returns any records, in which any part of the data matches a string. For example, the parameter "w" would return "wa", "ew", etc. I can do this easily enough: SELECT getSourceID.Title, getSourceID.Author, word.form,...

Javascript ADO recordset open method not working. Parametrized queries

javascript,html,sql,ms-access,ado
I am creating a web page and have some javascript code to insert some information from the webpage to MS access database. I am using ADO connections and record set but I am having a problem with the open method. This is a snippet of the code I am having...

Im trying to populate a Sharepoint list with the most upcoming dates from certain colums of data from another Sharepoint list?

sql,ms-access,sharepoint,ms-access-2007
I have a list named Employee Dates, this list contains the columns: Employee | CPR Completed | CPR Required | ETC These columns keep going on for all of the training courses required for our employees with alternating columns for completed and required dates. I am using a workflow to...

Listing specific requirements on Access Database form

database,ms-access,access-vba,relational-database,entity-relationship
I am trying to create a database using Microsoft Access Database, but I have a problem. I want to create a database that holds information for computer components and a form to list certain components to build a computer. For example, I want to build computer A, and I have...

Populate Data on Access 2010 table using VBA

sql,vba,ms-access
I'm new to MS Access and am having a hard time trying to come up with a more efficient solution. I have a table with a lot more data which contains something similar to the table below. I want to be able to populate the data, which contains missing Dates...

Photos as Varbinary(MAX) in SQL Server 2012 causes error 502753 in Access 2010

sql,ms-access,sql-server-2012,ms-access-2010,varbinarymax
I have an Access 2010 odbc front end with a SQL Server 2012 back end. My images (.bmp) are stored as Varbinary(Max). When I insert an image using my bound object frame in an Access form, the image displays on the form and report perfectly. I had a large number...

Access query to email

database,windows,vba,ms-access,email-integration
I've got the attached query, which shows several rows (there is more data) but only 2 email address. I would like to send the corresponding rows to their relevant email address. All these details change every week, and there will be more (and sometimes less) email addresses, so I need...

Extracting data from Excel to Access Database

database,excel,ms-access,ms-word
There is a standard company form currently in Word format (could use a different technology) with fields. How do I extract the data into my Access DB from this fields?. I am also open to advice of which technology would suit my requirements better with the requirements being: Need document...

MS Access Text Field Special Characters

ms-access,special-characters
I have a search button that executes this applyfilter macro code: [ContractNumber] Like "*" & [Forms]![frmContractMatch&Revenue]![Text44] & "*" So it searches text box text44 for the appropriate ContractNumber. The trouble I am having is that if a contract number is ART#45 for example, it will not find it in the...

Edit fields in local table linked to PassThrough Query

sql-server,ms-access,odbc
I have been tasked to create a query that will pull records from Data Warehouse with a given criteria that will be used by data-entry level employees with read only access. Once they have used the query to identify a record that requires action in a different system, we would...

Access: Setting a source object to another source object path

vba,ms-access,access-vba
I'm trying something crazy again. I would like to use this line of code: Reports![PrntRep].SourceObject = Forms![Search Form]!Subform1.SourceObject The source object of subform1 is dynamically depending on which query you need to search. I want the source of the report to be set to the same query, so that I...

VBA - Using Typeof … Is ADODB.Recordset Results in Compile Error

ms-access,access-vba,ado,late-binding,adox
I am building a function with a set of supporting sub-functions to create ADOX.Catalog objects to help me build automation for Access database generation. I like to use late-binding for my applications because my user base doesn't always have the same version of office applications, so I can't always rely...

How do I get the sum of a column where another column's item value = a value?

c#,sql,ms-access
My code: string hweight = "Hunters"; string hsql = "SELECT SUM(Weight), Product From Opened WHERE Product =" + hweight; OleDbCommand hcmd = new OleDbCommand(hsql,conn); txtHunters.Text = hcmd.ExecuteScalar().ToString(); Then i receive the following error: You tried to execute a query that does not include the specified expression 'Product' as part of...

Is it possible to disable/enable controls in an Access Subform for a specific record? Access 2000 - VBA

vba,ms-access,access-vba,subform
As the title says, I am trying to disable some controls of a specific record based on the combobox value of that specific record. So when a User chooses A in their combobox field X will be enabled but if they press B in their combobox the field X will...

In SQL how do I update a table with a similar table?

sql,ms-access,access-vba
In my current Database I have a table whose data is manually entered or comes in an excel sheet every week. Before we had the "manual entry option", the table would be dropped and replaced by the excel version. Now because there is data that only exists in the original...

Update query when database in ms access

c#,ms-access
My update query update tbl_Clients set [email protected], [email protected] where [email protected] When I am updating this code in giving error Syntax error in update statement...

How to insert only time in ms access?

c#,ms-access,datetimepicker
This is the query I am having issues with: cmd = new OleDbCommand(insert into tbl_Customer(cReportingTime) values (@ReportingTime)", con); cmd.Parameters.Add("@ReportingTime", OleDbType.DBTime).Value = Time; cmd.ExecuteNonQuery(); When I try to run it I am getting this error: "Failed to convert parameter value from a DateTime to a TimeSpan" I want to insert only...

Using LIKE in Count(IIF()) in MS Access, get NA count

sql,ms-access,ms-access-2013,iif
I'm attempting to get a count of NAs in a field in a table in MS Access. I have multiple NAs: #N/A* (i.e. NAs that start with hash & "N/A" & some more text that varies.) NaN NA N/A (They all mean something different for us, so it i actually...