Syntax error missing operator in query expression access Results


I am in the process of finishing off a database and was wondering whether I could seek some assistance to problems which are driving me mad.

I have has some very helpful responses from the forum and I am very grateful for them. I have 2 remaning problems and would be so appreciative of some help.

Database details:

It is an access 2003 contact database, where I have 3 tables: contact, company and address.

I have 3 forms, to match the above. The contact form asks for contact details and has an address subform. The company form asks for company details and has an address subform and contact subform.

My relationships go from addressID to companyID and from contactID to companyID (with both arrows going towards companyID at the centre).

The problems remaining are:

1. I am unable to enter new record information in the address subforms. They are set to allow deletions, additions etc. (and show the correct information) but when I try and type I get an error saying unable to enter data as 'control is read only, open in design view or value too large'. The source is the address table, so it should work okay.

2. I can't seem to get the links working correctly. When I enter a new contact, I type all the information in, including the company name (which is in a combo drop down box, getting information from the company table) and although it all saves fine, the new contact doesn't appear on the company screen (which has a contacts subform).

Occasionally I get a runtime error 3075 'syntax error (missing operator) in query expression 'CompanyID' =', am not sure how this relates.

If anyone could help, that would be so great. Am getting very depressed and frustrated by it all.

Let me know if you need any more information.

Thanks in advance.


DMax is returning an "#Error" as I try to use it to find the largest "Acqusition Number" in the table "ACQ FY08":

In the text box "Acquisition Number", on the data tab under Default Value I've entered --
=DMax("Acquistion Number","ACQ FY08")+1

I am also getting an Access error message: "Syntax error (missing operator) in query expression 'Max(Acqusition Number)'."

A past consultant set up this database with a "front end" ( gui file) and a "back end" ( .data file). Could that be causing a problem with the functioning of DMax? How do I get this to work?

I'm having problem saving a new record from a form without closing it and open a report. Please help.

When the main form and it's subform is opened, an auto-numbered field is populated. Once the main and sub form is completely filled, a command button needs to save the record and before closing it open a report preview using that auto-numbered field. I've tried several methods to save the record when the command button is pressed but none works. The error in all methods is Syntax error (missing operator) in query expression 'AutoGeneratedID='. But it works if I close the form and reopen the form filled with that record.

	    Me.Dirty = False

    DoCmd.RunCommand acCmdSaveRecord



    DoCmd.OpenReport MyReport, acPreview, , "AutoGeneratedID=" & AutoGeneratedID

Hi there
I am using Access 2010 and displaying the contents of a query in a form. When I click on one of the column headers (ie where you sort and filter) I get the above error. I can click Ok and then am able to sort the column but it doesn't show any of the values to filter on. Not sure where to start fixing as it is an in built function of access.

It is also strange that I have code in my form that applies a filter to the office and department fields, but when the form loads no filter is applied and I don't get an error on these fields. Hope this makes sense.

The query that the form is based on is below if that helps:

SELECT [FY2012 Source].[Staff Code], [FY2012 Source].[Surname], [FY2012 Source].[Preferred Name], [FY2012 Source].[Date Joined], [FY2012 Source].[Department], [FY2012 Source].[Office], [FY2012 Source].[Draft Grade], [FY2012 Source].[Proposed Grade], [FY2012 Source].[Participating in review process] FROM [FY2012 Source];

Any help would be appreciated.

Hello Forum,

I'm building a new "grades" program in Access 2007 for an English instructor at a local community college. The opening form has a combo box to select an existing course (I call it a "course" to avoid confusion with the programming term "class") or create a new course. If the user selects to create a new course, a new unbound form opens with three unbound text boxes to gather the needed data: the course code, the course description, and the start date. The code for the button to append a row to the tblCourses table is as follows:

Code: Private Sub cmdCreateCourse_Click() Dim rcc As Integer Dim description As String Dim start As Date rcc = Me!txtRCC_ID description = Me!txtCourseName start = Me!txtStartDate Dim StrSQL As String StrSQL = "INSERT INTO tblCourses ( RCC_ID, CourseDescription, StartDate) " StrSQL = StrSQL & "VALUES (" & rcc & ", " & description & ", " & start & ")" ' the message box below is for testing purposes and is commented out ' However, when it is enabled, the message box does properly display the ' query with the correct values in it. ' msgbox StrSQL DoCmd.RunSQL StrSQL End Sub This creates a Run-time error '3075': Syntax error (missing operator) in query expression.

I cannot see anything wrong with my query. I tried it both with and without the trailing semicolon, but that made no difference.

Any ideas? Thanks.

Hello all,

I have the following piece of a query that is causing a Run-time error'3075', Syntax error (missing operator) in query expression. The query works directly in Access as a Pass Through and it works in MySQL Query Browser, but it won't in VBA for some reason and I suspect that it is one of the MySQL Functions (LEFT, RIGHT, LENGTH, MAKETIME or TIMESTAMP) that is really causing the error. Can anybody else confirm or deny?

I'm new to Access. I'm trying to run this SQL query:

Code: SELECT Title.InnerText AS SystemID, RamSerial.InnerText AS RamSerial, RamTitle.InnerText AS RamTitle, RamModuleSize.InnerText AS RamModuleSize, RamPackage.InnerText AS RamPackage, RamFeature.InnerText AS RamFeature, RamSpecs.InnerText AS RamSpecs, Rating.InnerText AS Rating, Price.InnerText AS Price, RamSerial.Url FROM Title LEFT JOIN TitleInRam ON Title.InnerText = TitleInRam.InnerText LEFT JOIN RamSerial ON TitleInRam.Url = RamSerial.Url LEFT JOIN RamTitle ON TitleInRam.Url = RamTitle.Url LEFT JOIN RamModuleSize ON TitleInRam.Url = RamModuleSize.Url LEFT JOIN RamPackage ON TitleInRam.Url = RamPackage.Url LEFT JOIN RamFeature ON TitleInRam.Url = RamFeature.Url LEFT JOIN RamSpecs ON TitleInRam.Url = RamSpecs.Url LEFT JOIN Rating ON TitleInRam.Url = Rating.Url LEFT JOIN Price ON TitleInRam.Url = Price.Url But I get the error: "Syntax error (missing operator) in query expression 'Title.InnerText = TitleInRam.InnerText...'". The actual error message shows my query from "Title.InnerText = TitleInRam.InnerText" to the end.

I have a split form with a unbound combo box that queries based on the selection. That part queries fine, but I cannot get it to return all records if "All" is selected. I get the following error...

Run-time error 3075

Syntax error (missing operator) in query expression '[Assigned To] = All'

Row Source
SELECT Contacts.ID, Contacts.[Last Name] FROM Contacts; UNION SELECT "All","All" FROM Contacts;

Private Sub Combo81_AfterUpdate()
Me.Filter = "[Assigned To] = " & Me.Combo81
DoCmd.RunCommand acCmdApplyFilterSort
End Sub

I am fairly new to Access so please be patient. I am sure this is a easy fix for most of you.

Hello All,
I getting the following error from the code below. I have laid down data for hyperlinks before, in Access2003.
I am now working in Access 2010 for machines using both 2003 and 2007. For some reason, this error has only happened in code laid down in 2010.
Am I missing a change here? I can't find the problem, so any help would be appreciated. The debugger highlights the line
that starts "linkBack = "...
Best regards,
Run-time error '3075':
Syntax error (missing operator) in query expression
If IsNull(Forms!frmTrustIntermedResponse!Reasons) = False Then
DoCmd.SetWarnings False
Dim outSlip As String
Dim linkBack As String
outSlip = "RejectEntry#serverdirectory" & Forms!frmTrustReqDet!ReqID & "rejectentry.htm#"
linkBack = "INSERT INTO tblLinkBelt ( ReqID, LayDownDate, Flink )SELECT [Forms]![frmTrustReqDet]![ReqID] AS Expr1, Date() AS Expr2, " & outSlip & " AS Expr3;"
CurrentDb.Execute linkBack
DoCmd.OutputTo acOutputReport, "rptTrustCancellationWithFunds-html", acFormatHTML, outSlip, 0

DoCmd.SetWarnings True

MsgBox "No Reasons!", vbOKOnly, "Data Missing!"
Exit Sub
End If

Getting Syntax error using VB to insert into an Access table.

This code is in a loop.
The Division is set to a value pulled from a database
The Prior_Division stores the previous value for the Division varaible

Dim Division As String
Dim Prior_Division As String

SQL_Insert =
"Insert into TABLE_A (DateRun,Source,Period,Division)
(# " & Now & " #, ""SRC1"", ""2011"", & Prior_Division & );"

I am getting Syntax error (missing operator) in query expression '&Prior_Division &'
I've done debug statements and printed the values for the Division and Prior_Division variables and
they are simple text values like "Dept A" "Dept B"

Any help on why I get the error is appreciated

Dear, Access Master
I have 2 table tmpinduk and peminjaman:
tmpinduk :
induk | jdl_buku | pengarang | penerbit |jml_buku |status |
144 | Cerita Rakyat | Iskandar | Bondan | 2 | 0 |
155 | Naruto Komik | Masashi | Elexmedia | 0 | 0 |
156 | Bleach Komik | Masashi | Elexmedia | 1 | 0 |
177 | Teori Pembangunan | Mulyanto | UI-PRESS | 0 | 0 |
179 | Ensiklopedia Hewan | - | Gramedia | 2 | 0 |
180 | Ensiklopedia Tumbuhan | - | Gramedia | 1 | 0 |
181 | Teknologi | Zoomkobe | Ganeca | 2 | 0 |
182 | Baskeball Fundamental | Phil Jackson | Elexmedia | 1 | 0 |

peminjaman :
| mulai | kembali | nis |induk | jml_buku | denda | status |
16/8/2010 | 18/8/2010 | 6325 | 155 | 3 | 0 | 0 |
16/8/2010 | 18/8/2010 | 6325 | 156 | 2 | 0 | 0 |
16/8/2010 | 18/8/2010 | 6464 | 144 | 1 | 0 | 0 |
16/8/2010 | 18/8/2010 | 6464 | 177 | 1 | 0 | 0 |

The question is, how to update tables on a column tmpinduk jml_buku like this, (tmpinduk.jml_buku + peminjaman.jml_buku), and I imagine the result is :

tmpinduk :
induk | jdl_buku | pengarang | penerbit |jml_buku |status |
144 | Cerita Rakyat | Iskandar | Bondan | 3 | 0 |
155 | Naruto Komik | Masashi | Elexmedia | 3 | 0 |
156 | Bleach Komik | Masashi | Elexmedia | 3 | 0 |
177 | Teori Pembangunan | Mulyanto | UI-PRESS | 1 | 0 |
179 | Ensiklopedia Hewan | - | Gramedia | 2 | 0 |
180 | Ensiklopedia Tumbuhan | - | Gramedia | 1 | 0 |
181 | Teknologi | Zoomkobe | Ganeca | 2 | 0 |
182 | Baskeball Fundamental | Phil Jackson | Elexmedia | 1 | 0 |

I get an error message "syntax error (missing operator) in query expression, t.jml_buku + p.jml_buku from tmpinduk as t join peminjaman as p on p.induk = t.induk ",
if I using this query :

update t set t.jml_buku = t.jml_buku + p.jml_buku from tmpinduk as t join peminjaman as p on p.induk = t.induk in MsSQL Server this query have no problems...
thanks before, for all your help.....

I found a macro that was developed to automatically search a folder and import spreadsheets to a database, but it was formatted specifically for columns with numbers and such.

My column setup is as follows:

Code: Column Number Column Name Type 1 Cost Center Text 2 Cost Eleme Text 3 Posting Date 4 CO Doc # Text 5 Amount Text 6 User Name Text 7 FI Doc Text 8 Vendor # Text 9 Name Text 10 Info Field Text 11 Doc Header Text Text 12 Line item Text Text 13 Invoice # Text 14 Entry dt Date 15 Inv date Date 16 Invoice Month Date 17 Housekeeping Text I set up the code as follows, but it gives me an error in the INSERT INTO statement part, "Runtime Error 3075: Syntax Error (Missing Operator) in Query Expression 'Cost Eleme'. The error highlights at db.Execute sSQL.

Thanks for your help!

Code: Sub Import() Dim fs Dim fs2 Dim SourceFolder Dim sSourceDir As String Dim CurrFile Rem the Excel Application Dim objExcel Rem the path to the excel file 'Dim excelPath Rem how many worksheets are in the current excel file Dim worksheetCount Dim counter Rem the worksheet we are currently getting data from Dim currentWorkSheet Rem the number of columns in the current worksheet that have data in them Dim usedColumnsCount Rem the number of rows in the current worksheet that have data in them Dim usedRowsCount Dim row Dim column Rem the topmost row in the current worksheet that has data in it Dim top Rem the leftmost row in the current worksheet that has data in it Dim leftct Dim Cells Rem the current row and column of the current worksheet we are reading Dim curCol Dim curRow Rem the value of the current row and column of the current worksheet we are reading Dim word Dim sSQL As String Dim db As Database sSourceDir = "C:Documents and SettingsR356112DesktopHousekeeping2009" Set fs = CreateObject("scripting.filesystemobject") Set SourceFolder = fs.GetFolder("C:Documents and SettingsR356112DesktopHousekeeping2009") Set db = CurrentDb For Each CurrFile In SourceFolder.Files sFileName = CurrFile.Name sExcelFile = sSourceDir & "" & sFileName If InStr(sFileName, ".xlsx") Then Debug.Print "Reading Data from " & sExcelFile Rem Create an invisible version of Excel Set objExcel = CreateObject("Excel.Application") Rem don't display any messages about documents needing to be converted Rem from old Excel file formats objExcel.DisplayAlerts = 0 Rem open the excel document as read-only Rem open (path, confirmconversions, readonly) objExcel.Workbooks.Open sExcelFile, False, True Rem How many worksheets are in this Excel documents worksheetCount = objExcel.Worksheets.Count Debug.Print "We have " & worksheetCount & " worksheets" Rem Loop through each worksheet For counter = 1 To worksheetCount Debug.Print "-----------------------------------------------" Debug.Print "Reading data from worksheet " & counter & vbCrLf Set currentWorkSheet = objExcel.ActiveWorkbook.Worksheets(counter) Rem how many columns are used in the current worksheet usedColumnsCount = currentWorkSheet.UsedRange.Columns.Count Rem how many rows are used in the current worksheet usedRowsCount = currentWorkSheet.UsedRange.Rows.Count Rem What is the topmost row in the spreadsheet that has data in it top = currentWorkSheet.UsedRange.row Rem What is the leftmost column in the spreadsheet that has data in it leftct = currentWorkSheet.UsedRange.column Set Cells = currentWorkSheet.Cells Rem Loop through each row in the worksheet For row = 0 To (usedRowsCount - 1) Rem Loop through each column in the worksheet curRow = row + top curCol = column + leftct If Cells(curRow, 1) "" Then sSQL = "INSERT INTO Imports (A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q) VALUES (" For column = 0 To usedColumnsCount - 1 Rem only look at rows that are in the "used" range curRow = row + top Rem only look at columns that are in the "used" range curCol = column + leftct Rem get the value/word that is in the cell word = Cells(curRow, curCol).Value Rem display the column on the screen If curCol = 1 Then sSQL = sSQL & "#" & word & "#," Else sSQL = sSQL & word & ", " End If 'Debug.Print "CurRow " & curRow & " CurCol " & curCol & " " & (word) Next sSQL = Left(sSQL, Len(sSQL) - 2) & ")" Debug.Print sSQL db.Execute sSQL End If Next Rem We are done with the current worksheet, release the memory Set currentWorkSheet = Nothing Next objExcel.Workbooks(1).Close objExcel.Quit End If Next Set currentWorkSheet = Nothing Rem We are done with the Excel object, release it from memory Set objExcel = Nothing db.Close End Sub

Happy new year first.

I am just wondering if it is possible to use "if not exist" in Access, just like in SQL?

I want to insert record only when it is not there to prevent any duplicated records.

Here is what I have tried:

INSERT INTO PropertyForSale (Title) values('asdf') WHERE not exists (SELECT PropertyID FROM PropertyForSale where PropertyID = '12456085' )

I tested this query directly in Access DB, it throws me this error:
Query input must contain at least one table or query

If I change it a little bit to:
INSERT INTO PropertyForSale (Title) select 'asdf' WHERE not exists (SELECT PropertyID FROM PropertyForSale where PropertyID = '12456085' )

it throws error:
Syntax error (missing operator) in query expression 'asdf' wehre not exists (SELECT PropertyID FROM PropertyForSale where PropertyID = '12456085' )

If I put this query in my .NET, then it will throw a different error msg:
Missing semicolon ( at end of SQL statement.

My .NET code is as follow:

Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim sqlCmd As String
sqlCmd = "INSERT INTO PropertyForSale (Title, Area, PropertyName, PropertyID, Model, PropertyType, PropertySize, TotalPrice, Price, PubDate, Contact, Phone, Company, Branch, Detail) " + _
"Values(@Title, @Area, @PropertyName, @PropertyID, @Model, @PropertyType, @PropertySize, @TotalPrice, @Price, @PubDate, @Contact, @Phone, @Company, @Branch, @Detail) " + _
" WHERE NOT EXISTS (SELECT 1 FROM PropertyForSale WHERE PropertyID = @PropertyID) ;"

cn = New System.Data.OleDb.OleDbConnection(conStr)
cmd = New OleDbCommand(sqlCmd, cn)

cmd.Parameters.AddWithValue("@Title", Title)
cmd.Parameters.AddWithValue("@Area", Area)
cmd.Parameters.AddWithValue("@PropertyName", PropertyName)
cmd.Parameters.AddWithValue("@PropertyID", PropertyID)
cmd.Parameters.AddWithValue("@Model", Model)
cmd.Parameters.AddWithValue("@PropertyType", PropertyType)
cmd.Parameters.AddWithValue("@PropertySize", PropertySize)
cmd.Parameters.AddWithValue("@TotalPrice", TotalPrice)
cmd.Parameters.AddWithValue("@Price", Price)
cmd.Parameters.AddWithValue("@PubDate", PubDate)
cmd.Parameters.AddWithValue("@Contact", Contact)
cmd.Parameters.AddWithValue("@Phone", Phone)
cmd.Parameters.AddWithValue("@Company", Company)
cmd.Parameters.AddWithValue("@Branch", Branch)
cmd.Parameters.AddWithValue("@Detail", Detail)


Thanks to everyone who reply and happy holiday again.

I urgently need some help in regards to an Event Procedure that I need to include 'before updating' in the first field of my form.

Problem 1
What I am trying to do is to allow a user to type a location into the first field of a form and after typing it and pressing tab or enter the system will check to see whether it is a duplicate before the user proceeds in completing the rest of the form and if it is a duplicate not allow further input until the detail is changed or deleted. An example of what the user could type in is MC Donalds-Sydney or any free form text line.

I am only a novice when it comes to Access so I do apologise if this is a silly request.

On a different form I have a similar event procedure which runs and checks whether a particular ID no is a duplicate and if it is it returns a message box. I have tried pasting the same code in and just changing the relevant table names etc... however I am getting an error message.

The previous code I have included is as follows:

Private Sub LOI_BeforeUpdate(Cancel As Integer)
On Error GoTo LOI_BeforeUpdate_Err
If DCount(LOI, "Tble_Name", "LOI='" & Me.LOI & "'") > 0 Then
MsgBox "This LOI has already been used"
End If
Exit Sub
MsgBox Error$
Resume CNI_BeforeUpdate_Exit
End Sub

The error message I receive is
Syntax error (missing operator) in query expression 'Count(Smith Street)'.

Problem 2
Also in my original code for the ID number once the message box comes up saying "This CNI has already been used" the user is able to click on OK and then proceed with filling in the form without having to change the ID field. Does anyone know what extra line/s of code I would have to add to change it so that when the user click on OK the system will revert back to the ID field only and stay there until the info is changed or deleted.

Any help anyone could give me in regards to these two problems would be so greatly appreciated.

Eagerly and urgently awaiting a response.
Thanks & Regards, Michelle

Dear access forums,

I'm in a very high level of emergency. I have to finish a report system for tomorrow, and yet, just after beginning it, I've ran into a problem.

I have something a bit similar to:

Table 1: Shops /
- ID
- Name
- Type

Table 2: Trades /
- ID
- Date
- Shop_ID
- Amount (currency)

I want to make a query, that takes data from both tables, lists all Shops even ones without any trades in the given month.
And well, that's what doesn't work: I want to only sum trades from a given month. I'm using an outer join for that:

Code: SELECT Shops.Name FROM Shops LEFT JOIN Trades ON Shops.ID = Trades.Shop_ID AND ON Trades.Date=[When?] ORDER BY; But that doesn't seem to work. It keeps telling me:
Syntax error (missing operator) in query expression ".

Thank you very much for your help in advance!

Hi all, I'm by no means an expert in access but I'm trying to expand my knowledge by creating a call logging database. an exchange inbox is linked into the database. This will be where the support calls come from, i have setup a form with a button, the idea being to review the mails in the inbox and then to add them to a waiting list. I decided to it like this as sometimes there are unwanted mails in the inbox. So the idea being when you go through the most recent records you can click a button which will copy the record into another table which will open a "ticket".

Simple enough i know but stuck on the pull the record code for the button. I have tried an append query but to be honest, although i have used query builder in the past, it has only been for reporting so my funtion knowledge of querey append is not too bright so i decided to use code builder for the event and use an sql statement as follows:

	strSQL = "INSERT INTO Tickets " _
  & "([Importance], [Priority], [Message Size], [Has Attachments], [Received], [To], [From], [Sender Name], [Subject],
[body]) " _
  & "VALUES ('" & Me.Importance & "'" _
  & ", " & Me.Priority & "'" _
  & ", " & Me.Message_Size & "'" _
  & ", " & Me.Has_Attachments & "'" _
  & ", " & Me.Received & "'" _
  & ", " & Me.To & "'" _
  & ", " & Me.From & "'" _
  & ", " & Me.Sender_Name & "'" _
  & ", " & Me.Subject & "'" _
  & ", " & Me.Body & "'" _
  & ")"
DoCmd.RunSQL strSQL

unfortunatly im getting an error when running it: Run-time error '3075' Syntax error (missing operator) in query expression...

Could someone possibly shed some light on this? any help or pointers greatly appreciated

many thanks


Hi folks,

I'm a complete newbie with Access, but I have a great deal of experience in using VBA with Excel. However, I've realised that one of my Excel toys needs to be converted to Access owing to the volume of data it holds.

As such, yesterday I started to "play" with Access and built a couple of tables, queries and forms. On one of the forms I have a ComboBox (called startDate), I've added an after update event to this to trigger a macro to search for a record in a table called shiftTable, and return 3 pieces of data when it locates the record.

I built the query to do this using the Access query design which works correctly, and then grabbed the SQL to use in the macro, this is shown below.

	SELECT shiftTable.amWatch, shiftTable.pmWatch, shiftTable.nsWatch
FROM shiftTable
WHERE (((shiftTable.activityDate)=activitydetail.startDate));

This is the macro that I've built around that SQL....

Private Sub startDate_AfterUpdate()
Dim ssqlCom As String
Dim stDate As Date
Me.End_Date = Me.startDate
stDate = Me.startDate
ssqlCom = "SELECT shiftTable.amWatch, shiftTable.pmWatch, shiftTable.nsWatch" & _
          "FROM shiftTable " & _
          "WHERE shiftTable.activityDate=#" & stDate & "#;"
DoCmd.RunSQL (ssqlCom)
End Sub

However, every time I run it I get a 3075 missing operator error (in this example I selected 27/01/2011 in the startDate ComboBox);

Quote: Syntax error(missing operator) in query expression
'shiftTable.nsWatchFROM shiftTable WHERE
shiftTable.activityDate=#27/01/2011#'. I've tried various things to fix it, including converting the date to US format, putting a space in front of the FROM command, declaring stDate as string instead of date. I'm getting frustrated now, especially as I suspect I'm missing something very obvious. All help appreciated!


As an experiment, I just chucked a msgBox in before the DoCmd statement to show me the value of ssqlCom, which returns this:

Quote: SELECT shiftTable.amWatch, shiftTable.pmWatch, shiftTable.nsWatchFROM shiftTable WHERE shiftTable.activityDate=#27/01/2011#; Thanks,


Hi all
I am writing a VBA code, this is th first step of a lrage project
WHat I am trying to do is to put all the content of a word file (.doc) into a cell in access using this code:

Private Sub Command0_Click()

Dim app As Word.Application
Dim objDoc As Word.Document
Dim sVal As String

Set app = New Word.Application
Set objDoc = app.Documents.Open("C:Documents and SettingsAdministratorDesktopMagic Foldertest.doc")

sVal = objDoc.Content
sqls = "INSERT INTO Table1(TextContent) VALUES ('" & sVal & "');"
DoCmd.RunSQL sqls
Set objDoc = Nothing

Set app = Nothing
Debug.Print sVal

End Sub

SO when I try this with a small word file it works, if the file is too large i get an error
Run time error "3075" Syntax error missing operator in query expression, after this the error message displays part of the text in my word file

2 questions:
1-Is there a better way to do what I am trying ?
2-What is wrong with my code?


Really sorry if this is posted in the wrong place, but I a at my wits end.

Using Access 2003 I have a database that holds unique records for learners - Ethnicity, Learner_id (User) table and the courses that they have enroled on (Course_Information) table. These courses (Course) table are sperated into four Areas of Learning (Area of Learning) table, Vocational, Non-Vocational, Essential Skills and Employability.

I have to create a query that will count the number of unique learner codes that apper in a Course_Information Table, between two entered dates, looks up that learners Ethnicity Code from a User table and shows fo each Area of Learning how many learners of each ethnic code attend courses.

I am not an SQL programmer and I have managed to come up with the code below.

SELECT DISTINCTROW Ethnicity.Ethnic_Code, [Start] AS BeginDate, [End] AS EndingDate,

IIf(Area_Of_Learning.Area_Of_Learning='Vocational' ,Count(DISTINCT learner_id) AS Vocational FROM Course_Information,0),
IIf(Area_Of_Learning.Area_Of_Learning='Employabili ty',Count(DISTINCT learner_id) AS Employability FROM Course_Information,0),
IIf(Area_Of_Learning.Area_Of_Learning='Non-vocational',Count(DISTINCT learner_id) AS Non_vocational FROM Course_Information,0),
IIf(Area_Of_Learning.Area_Of_Learning='Essential Skills',Count(DISTINCT learner_id),0) AS Essential_Skills FROM Course_Information,0),

FROM Area_Of_Learning INNER JOIN (((Course_Information INNER JOIN Course ON Course_Information.Course_code = Course.Course_Code) INNER JOIN [User] ON Course_Information.Learner_id = User.Learner_ID) INNER JOIN Ethnicity ON User.Ethnicity = Ethnicity.Ethnic_Code) ON Area_Of_Learning.Area_Learning_ID = Course.Area_Learning_ID

WHERE (((Course_Information.Start_date) Between [Start] And [End]))

GROUP BY Ethnicity.Ethnic_Code, Course_Information.Establishment_ID, Area_Of_Learning.Area_Of_Learning

ORDER BY Ethnicity.Ethnic_Code;

When I run the query i get the Syntax error (missing operator) in query expression. and I do not know what to do to fix it.

I had the query running before , and the output seemed to count every learner entry in the Course_Information table.

Basically I might have a learner, Learner_id 123, Ethnic code W1 that might enrol on 5 Vocational and 5 Non Vocational courses, another Learner, Learner_id 456 Ethnic code A1 that might also enrol on 5 Vocational and 5 Employability Courses, I want the query to return result :

Between dates entered,

W1 Vocational = 1 Non Vocational =1
A1 Vocational = 1 Employability = 1

Please can anyone help me, I have been trying to get this working for a month or two.



I am trying to add code to a button click which will copy the selected record from its table (tblLocation) to another table (tblRemovedSamples - an archive table). I am using a slightly tweaked version of code found on the allenbrowne . com website (I can't post the link).
(I don't currently want the record deleting, which is why that section is commented out, and I don't want the checkbox to be visible to the user, just for it to be automatically checked when the button is clicked).

I'm using Access 2010 on Windows 7.

My code is:

	Private Sub btnRemoveSample_Click()

Me.AllowEdits = True

Me.MyYesNoField = True

On Error GoTo Err_DoArchive
  Dim ws As DAO.Workspace   'Current workspace (for transaction).
  Dim db As DAO.Database    'Inside the transaction.
  Dim bInTrans As Boolean   'Flag that transaction is active.
  Dim strSql As String      'Action query statements.
  Dim strMsg As String      'MsgBox message.

  'Step 1: Initialize database object inside a transaction.
  Set ws = DBEngine(0)
  bInTrans = True
  Set db = ws(0)

  'Step 2: Execute the append.
  strSql = "INSERT INTO tblRemovedSamples ( ID, Cryostat, Column_No, Drawer, Slot, Sample_ID, Cap_Colour, Date_Cryopreserved,
Cryopreserved_User, Storage_User, Notes ) " & _
    "SELECT tblLocation.ID, tblLocation.Cryostat, tblLocation.Column_No, tblLocation.Drawer, tblLocation.Slot,
tblLocation.Sample_ID, tblLocation.Cap_Colour, tblLocation.Date_Cryopreserved, tblLocation.Cryopreserved_User,
tblLocation.Storage_User, tblLocation.Notes " & _
    "WHERE (MyYesNoField = True);"
  db.Execute strSql, dbFailOnError

  'Step 3: Execute the delete.
  'strSql = "DELETE FROM MyTable WHERE (MyYesNoField = True);"
  'db.Execute strSql, dbFailOnError

  'Step 4: Get user confirmation to commit the change.
  strMsg = "Archive " & db.RecordsAffected & " record(s)?"
  If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then
    bInTrans = False
  End If

  'Step 5: Clean up
  On Error Resume Next
  Set db = Nothing
  If bInTrans Then   'Rollback if the transaction is active.
  End If
  Set ws = Nothing
Exit Sub

  MsgBox Err.Description, vbExclamation, "Archiving failed: Error " & Err.Number
  Resume Exit_btnRemoveSample_Click
End Sub

The error I am persistently getting is:

"Error 3075: Syntax error (missing operator) in query expression 'tblLocation.Notes WHERE (MyYesNoField = True)'."

I get the same error when I use a slightly different set of code I found elsewhere, leading me to think the problem is with my SQL statement??

This is driving me mad.

Any help gratefully appreciated.

Not finding an answer? Try a Google search.