ms-access

Querying a database or a datatable for duplicate records in one column

On MS access, how to get the "ID" of the records having duplicate content on the "myData" column ? something like : ---------------------- ------------------------ ID | myData | | ID | myData | ---------------------- ------------------------ ...

[MS Access 2003 Look up field and modules] Getting the value not the ID

I have a table tblInvestigators which contains a lookup field to display a list of names A grant may have more than 1 investigator. A requirement of my project is to list all investigators in a single cell next to the grant details, so i'd have: Grant A | Name A, Name B, Name C etc. I have a VBA module that concatenates the investiga...

error wrong number of arguments

this query is giving me error. I am looking for two similar letters in the 2 tables SELECT clients.ClinicName, clinics.clinicName, clinics.ClientID, clients.[Clinic ID] FROM clients, clinics WHERE Left(clients.ClinicName, Instr(Instr(clients.ClinicName," ") +1,clients.ClinicName," ")-1) = Left(Instr(clinics.clinicName," "...

Form error event not firing on DAO error

I have a save button on a form that does this (simplified): If Not Me.Dirty Then GoTo Exit_ DoCmd.RunCommand acCmdSaveRecord Exit_: Exit Sub Err_: ' ... handle runtime error ... Resume Exit_ This form has its Error event handled in a custom class FormDataError. Private WithEvents frm_ As Access.Form ... Public Sub frm...

select from access database file and insert to sql Database

Hi All, I have an access database file (test.mdb) and I need to write a stored procedure which will select some records from tblTest in test.mdb and insert them into tbsqlTest in my sql database . ==> I need a SP like this : BEGIN select * into tblTest from [test.mdb].[tblTest] where (my condition) END ...

Two check-box's in one check-box list

I am currently working on converting an application from access to C# and have come across an odd control I need to replicate. In access there is a list of check-box's, for the current month the user is looking at, which has everyday of that month with two check-box's beside it, D and N (for day and night). So my question is what would b...

running regsvr using VBA

Hi guys Is there a way I can run regsvr32.exe to register a dll using VBA? My software is in access (dont blame me, blame the company). Access for some reason always references the old version of the DAO library. After about 2 hours of searching the internet, someone suggested registering the library, and viola it worked. It cut down ...

When an Access 2007 Database is set to “Working online with Sharepoint” mode, my application cannot connect properly.

I have an ASP.NET application written in C# and hosted on IIS6 Win2k3. This web application connects through code to an Access 2007 Database which holds Sharepoint lists in tables. To get an idea of how this works you can go here: http://sharepoint.microsoft.com/blogs/GetThePoint/Lists/Posts/Post.aspx?ID=68 The issue is when the Acc...

access sql SELECT question

here is my code: select column1, column2 from table1; here is a preview of column1: 1.1 Specimen Mislabeled 1.9 QNS- Specimen Spilled in transit 1.3 QNS-Quantity Not Sufficient 1.6 Test Requisition Missing 1.11 Other - Validity only 1.11 Other-reject per practice 1.5 Patient Info. entered Incorrectly 1.11 Other-Validity 1.11 Other-va...

access: IIF statement and COUNT

here's my code that works thanks to Jim b: SELECT IIf([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)] Like '*1.11 Other*','1.11 Other',[Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]) AS [Occurrence Code], Count([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]) FROM [Lab Occurrence Form] WHERE ((([Lab Occ...

ms-access: how to comment sql code out?

is it possible to comment code out in the sql window in access? ...

Creating a SQL Query

I'm having trouble creating a query. I need to create a SQL query where ProductID = prodSelect (user choice) and EndTime = Null and StartTime < 3 hours from the current time. This is what I have so far. Could someone help me figure it out? Set rs = CurrentDb.OpenRecordset("SELECT TimeID " & _ "FROM tblLunchTime " & _ "WHERE Pr...

Invalid Argument Error: MSAccess and SQL

I am trying to access certain lines from my SQL database from MSAccess and I keep getting an Invalid Argument Error on this line: Set rs = CurrentDb.OpenRecordset("SELECT TimeID " & _ "FROM tblLunchTime " & _ "WHERE ProductionID = prodSelect AND EndTime is NULL AND StartTime < dateAdd('h', 3, NOW())", [dbSeeChanges]) Is someth...

converting SQL to ACCESS

here's a regular SQL statement: SELECT SUM(CASE WHEN [Column2] = 'Cylinder' THEN 1 ELSE 0 END) as 'Cylinder count', SUM(CASE WHEN [Column2] = 'Snap' THEN 1 ELSE 0 END) as 'Snap count', SUM(CASE WHEN [Column2] = 'Tip' THEN 1 ELSE 0 END) as 'Tip count', SUM(CASE WHEN [Column2] = 'Other' THEN 1 ELSE 0 END) as 'Other count' FROM [TableName]...

getting rid of zeros in the output of an access query

i have a query in access that is this: SELECT iif([Cup Type] like '*Cylinder*',count([Cup Type]),0) AS Cylinder, iif([Cup Type] like '*Snap*',count([Cup Type]),0) AS Snap, iif([Cup Type] like '*Tip*',count([Cup Type]),0) AS Tip, iif([Cup Type] like '*Other*',count([Cup Type]),0) AS Other FROM [Lab Occurrence Form] WHERE [1 0 Preanaly...

How to match an area code in MS access

I have 2 area codes I need to match in my database the area code is 214 and 927 I am using the LIKE clause select * test where phone like "*972*" I believe if 972 is anywhere in the phone number it will be matched. I need 972 to be only matched with the area code. The phone formats can be (###) ### - #### or ########## Is this pos...

SQL stored procedure where database to query is parameter

I am working with Microsoft Access (not SQL server) and have written a stored procedure (Query) as shown below in SQL. This procedure works but it relies on a hard coded path to the database from which to pull (SELECT) the data. To make the query more useful I want to pass the database to SELECT from as a parameter to the query - how d...

2 DBs, union ALL and JET error

Hi guys, info: dedicated server with iis 6, win 2003, several websites with no problems. i have an old website (i will call it "A" with his db "Adb") and now a new website for a part of the old one (call it "B" with a db name "Bdb"). in order to work with both DBs i used Union ALL like this: strSQL = "SELECT catid,name,1 as r FROM ca...

Queries in MS Access 2003, help using a NESTED SELECT

If I have three queries and rows from the queries are not all alike. How can I write an SQL statement that will combine all three queries and all their rows of data into one final query?? The query I'm using currently leaves some of the rows off, I'm assuming because the rows are not all equal, and from my understanding Access does not ...

Query a union query.

First of all I know this is probably bad sql but I just need to run a few queries of this and it will not go into official use anywhere. I need to find some records from the fcc uls database so I got the tables and they have one table with locations and one with frequencies for a number of categories. So what I've done is run queries to...