I currently have an Access query that returns data in two forms One form is 5 numbers, e.g., 12345 The other form is 2 letters, followed by 5 numbers, e.g., ab12345 This is coming in the same field, and I need to get only the numbers. I can't strip o
By default the following code sends the exported file to My Documents: Public Sub cmdExport_Click() Application.ExportXML ObjectType:=acExportQuery, _ DataSource:="export1", DataTarget:="whatever.xml" End Sub Is it possible to ask the
Could somebody tell me what I'm doing wrong in this query to get a calculated column? IIf(Left([VIN Number],2)="1F" OR "2F" OR "3F" OR "1L" OR "2L" OR "NM","Food","Comp") It's
I have to create some Number field-check of entered values in Textbox with formula in VBA, but don't know how to even start. Here is example: Textbox field value - 132549 you exclude 6, so you use 13254 then you do product and sum with other numbers
I'm trying to update an MS Access database in Excel VBA using an ADO Recordset object. When I fire the rs.Update method, I would like the following code to wait until the database has been updated before executing. It appears the database takes 3-5 s
I have done my research on this in Stack Overflow and am aware of offered solutions whereby the advice is that MSAccess reports override queries and the sort order is usually set in the report properties, or by VBA OnLoad, or something similar. I hav
I have a table of around 60 columns and 400,000 rows and increasing. Our company laptops and MS Excel cannot handle this much data in RAM. So I decided to store the data in MS Access and link it to Excel. However the pivot in Excel still downloads al
I've done a thorough search but can't find an answer to this question. I connect with Access ODBC and need to run a query which updates many tables at the same time based on one value. Here's what I'm doing at the moment: $DSN="accessodbc"; $DSN
Below is my query I want to fetch last entry of visitno ,of particular pid,this query works fine but selects all rows of pid ,visistNo is of Number Datatype public int GetPatientID_visitNo(int pid) { int data = 0; try { string sql = "Select VisitNo F
I have added the data as: public static void insert() { try { string connStr = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\test.accdb;Persist Security Info=False"); OleDbConnection conn1 = new OleDbConnection(); conn1.ConnectionString =
I'm in the process of converting an Access .ADP project to a .ACCDB with linked tables. I know that a big issue when working with a SQL Server backend is the use of nullable bit fields, since Access's Yes/No data type doesn't allow for nulls. So I co
Need to compare two access databases. Please guide on 1. how to use VBA to connect multiple database 2. how to dynamically choose which database to connect 3. how to find if match is unique / and if not insert result into different table 4. query to
I have a routine in my VBA MSAccess platform that has me baffled. All I am doing is summing up an amount field as I loop through a recordset. I have declared a variable called TempAmount (which holds the totals as we loop through). The field name in
I want to run a delete query in VBA to delete records containing a billing month that's greater than a precalculated current month. The source data from another system has the dates in text format, shown as (YYYY/MM). Here's the statement: mySQL = "D
I want to access my MS Access database through a SQL Server R2 instance. Is it possible? If yes than how?The short answer: OPENDATASOURCE http://msdn.microsoft.com/en-us/library/ms179856.aspx I have a few examples here My examples are not a "direct h
So, I once again seem to have an issue with MS Access being finicky, although it seems to also be an issue when trying similar queries in SSMS (SQL Server Management Studio). I have a collection of tables, loosely defined as follows: table widget_mfg
I'm using ASP.NET/VB and I'm trying to insert a date and time into an Access date/time field, but I'm getting an error (Data type mismatch in criteria expression). Here's a simplified version of my code: Dim myDate As Date = Now() Dim myIns1 As New O
I've searched StackOverflow and google for quite some time now and haven't been able to find an answer even remotely close to my desired solution using SQL. I have a CSV file (1 table) and it has data for numerous users. The problem is each user has
I'm working with MS Access 2010. I have a table of projects and each project has a STARTDATE, ENDDATE, and a True/False field for ACTIVE. Active is true if today's date is within Start and End dates - and I want it to be automatically checked or unch
This my code in VB.NET. My try catch says there is a syntax error in the instruction INSERT INTO. I don't know what happened to my INSERT. I searched for the error for over an hour... I'm not an expert in VB.NET, I'm better in C#, but I need to do th
I have an Access query that should pull the quantity purchased in a month for a set of items. My join is set to select all items from Analysis, and items from product that match analysis. SELECT AnalysisItems.Pmid, AnalysisItems.ProductName, Sum(Tran
I am running this query in Access 2007 and getting a syntax error on this query UPDATE INQuery SET Awords=(SELECT Coalsce("SELECT (parentdesc & '/' & keyword) From Awords LEFT JOIN INQuery ON Awords.id=INQuery.item_id",";")) WH
I have a problem in retrieve the difference between EntryTime and ExitTime. I have a table named IOData and I am working on IOTime column. The format of the table is: HolderName IODate IOTime IOGateName IOStatus Dinesh Kumar 2010/07/09 00:50:05 Basem
Update: Albert D. Kallal has kindly started the discussion off, and to get some more opinions I'm adding a bounty. This is a nontrivial question about maintenance of a legacy application myself and two other developers support. We are not the origina
Hey guys, I have the following table structured like this: So basically as you can see, the department goes through name changes every couple of years. Look at number 16 for example. I want a select query that will only get the name when the date is
i have a table in an access database this access database is used on a regular basis, basically from 9-5 someone else has a copy of this exact table. sometimes records are added, sometimes deleted, and sometimes data within the records is updated. i
i am a little confused in finding out what would be the best way to replace all occurances of 1. Blanks 2. - 3. NA from all collumns of TableA with question mark ? charachter. Sample Row in orignal tableA 444586 RAUR <blank> 8 570 NA - 13 - SCHS299
I using this in Access 2003. Pardon me if this is in the wrong spot, but I hoped someone could help. I have a table with multiple records that have a text represented date field. The date is in a format like: "06/01/2009" I need to select all th
I'm expecting to see a reference to VBscript Regular Expressions 5.5 by adding the path c:\windows\system32\vbscript.dll\3 to ms-access via Tools > References. However the directory tree only shows me the full path without the ending "\3" Wha
I have a function I've written that was initially supposed to take a string field and populate an excel spreadsheet with the values. Those values continually came up null. I started tracking it back to the recordset and found that despite the query b