Compile Error - Expected Expression

I'm still trying to figure out this mail merge stuff. I have been trying the many samples from this board and listed in MS Knowledgebase.

I keep getting a compile error when I type the following code (listed right off Knowledgebase)

Dim objWord As Word.Document
Set objWord = GetObject("d:dataindependent contract.doc", "Word. Document")
'Make Word Visible
objWord.Application.Visible = True
'Set the mail merge data source
objWord.MailMerge.OpenDataSource Name:="C:Program FilesMicrosoft OfficeOfficeMSACCESS.EXE" & "d:dataaccesstemp.mdb"
LinkToSource:=True, Connection:="QUERY qryIndependent"

It hightlights the := next to LinkToSource and Connection and gives me the compile error.

I doubled check that I am using the right programs referenced in the document (Access 97 and Word 97) and that Microsoft Access 8.0 Object Library is checked.

What gives? I see the LinkToSource:= in many of the other samples posted out on the board.

Aargh. The trouble us entry level users get into.


Post your answer or comment

comments powered by Disqus
I wrote a function that I want to have 3 parameters for

Import_Reports(tblName as String, Specs as String, InputDir as String)

When i try to run it, i get:
Compile Error: Expected =

BUT
If i define 2 of the parameters within the function as vars, leaving only one as an argument, it works fine. I can use any one of the 3 alone as an argument and it works, but any more than 1 and i get that compile error...
i'm stumped!

Any help would be greatly appreciated!

Max

I have the following code

DoCmd.RunSQL ("INSERT INTO [tblDirect_Data_All_Utilities_(Apportioned)] ( Id, [Month], Apportioned, CO2 ) " _
& "SELECT [Points].[Id], DateAdd("d",-1,DateAdd(m,1,"01/" & Format(DateAdd("d",-15,[Date]),"mm/yyyy"))) AS [Month], " _
& "Int(([DataElectricity].[Units]/([Date]-(DateAdd("d",0,DMax("[Date]","DataElectricity","[Point_Id] = " " _
& "[Points].[Id] & " And [Direct] = 'D' And [Date] < #" & Format([Date],"mm/dd/yyyy") & "#")))))*Int(Format([Month],"d"))) " _
& "AS Apportioned, Int([Apportioned]*0.43) AS CO2 " _
& "FROM Points INNER JOIN DataElectricity ON [Points].[Id]=[DataElectricity].[Point_Id] " _
& "WHERE ((([DataElectricity].[Direct]) = "D")) " _
& "ORDER BY [Points].[Id], DateAdd("d",-1,DateAdd("m",1,"01/" & Format(DateAdd("d",-15,[Date]),"mm/yyyy")));")

I get a Compile Error Expected: List Seperator or ), at the section highlighted above.

Any idea, thanks in advance
Geoff

Hi Everyone,

I am having trouble in VBA putting two different derived fields into a string. The coding I am using is calling details from a subform, and I have enclosed what I am trying to do below.

strMessage = "Warning: The recorded stock holding of" & Me.Description "is" & Me.StockHolding

I keep getting a Compile Error Expected: end of statement with the "is" highlighted.

Description is a book title, and stockholding is the number of inventory Items.


If anyone can offer assistence, it would be greatly appreciated.



Thanks.

Please help me figure put why I am getting a compile error on the following:
DoCmd.FindRecord Me.UserName, , , , , ,
I am getting "Compile Error: expected:expression"

I have tried every variation I can think of. Thanks.

Hi everyone.

Can somebody tell me what I'm doing wrong here? I get an error message saying Compile Error Expected: expression.


	Code:
	If Me.cboRecentTypes.Value = "Recently Added" Then
    Me.lboRecent.RowSource = SELECT tblContacts.ContactID, [LastName] & IIf(IsNull([Lastname]),"",", ") & [NamePrefix] &
IIf(IsNull([NamePrefix]),""," ") & [FirstName] AS Name, [Title] & IIf(IsNull([Title]),"",", ") & [Business/Organization] AS
Business, [Address] & " " & [City] & IIf(IsNull([City]),"",", ") & State & " " & Zip AS Addrss, tblContacts.Phone,
tblContacts.DateAdded, tblContacts.DateModified FROM tblContacts WHERE (((tblContacts.DateAdded) Between
DateAdd("d",1,Date()) And DateAdd("d",-7,Date()))) ORDER BY DateModified DESC;
    End If
    
    Me.lboRecent.Requery



I’m receiving the following error message in one of my modules/procedures “Compile error - Expected variable or procedure, not module."

Here’s the background and code:

The module CheckForNulls checks for null values in form fields. The code is in a module because it should be available to all forms “on the fly”. The module is called by Private Sub cmdSend_Click() event, where it says
Cancel = CheckForNulls(me.form).

Due to the error message, I tried changing this to the following, but still get the error
Dim X As Boolean
X = CheckForNulls(Me.Form)

Back on the form, if CheckForNulls = False (no incomplete records were found), then some other routines take place.

PHP Code:
Public Function CheckForNulls(frm as form) as boolean

    Dim ctl As Control
    Dim intMsgResponse As Integer
    ' Loop through all the controls on the form
    For Each ctl In frm.Controls
        ' Check the tag property
        If ctl.Tag = "RequiredField" Then
            ' Check this control has a value
            If ctl = "" Or IsNull(ctl) Then
                ' No Value - Cancel the update event
                CheckForNulls = True

                'Exit loop when an incomplete required field is found
                Exit For
            End If
        End If
    Next
    ' Check to see if a required field was blank and inform the user
    If CheckForNulls = True Then
        MsgBox "Please fill in all required fields."
    End If
End Function 
Could someone look at this to help pinpoint what is wrong?

Christine

Hi,

I have a script to send Reports via e-mail. There are 2 different sets of reports, which are basically using the same module, modified to reflect the correct report names etc.

The first one is working perfectly.

The second one includes so many reports, that they do not fit in one line of VBA, therefore I have tried to break them down using space underscore, however I am getting "Compile Error: Expected: list separator or )". Any suggestions? here is the section of code that is throwing the error:

blnSuccessful = FnSafeSendEmail( _
"abc@xyz.com; def@xyz.com", _
"Statistical Reports", _
strHTML, _
"R:VRS1 Report 1 - YTD.snp; _
R:VRS2 Report 2 - Month.snp; _
R:VRS3 Report 3 - YTD.snp; _
R:VRS4 Report 4 - Month and YTD.snp;")
Thanks,

Kirsti

Good afternoon all, I'm running into this error and can't for the life of me figure out how to fix it.

Compile Error: Expected: line number or label or statement or end of statement

This is the code I am using (its in an if statement if that helps).

Code: "Update tblLogTemp set LogOffDate(Sql1) = LOD(sql2), LogOffTime = LOT(sql2)" &_ "where (SELECT top 1 dbo_Logoffs.Operdate as LOD, dbo_Logoffs.opertime as LOT" &_ "FROM dbo_Logoffs INNER JOIN tblLogTemp ON dbo_Logoffs.FullName = tblLogTemp.User" &_ "Where dbo_Logoffs.OperDate >= tblLogTemp.LogOnDate and dbo_LogOffs.OperTime >= tblLogTemp.LogOffTime);" Any ideas?

I have been researchin this issue all morning.

I am getting this error on some code I'm trying to run:

Compile error: expected sub, function or property

What does the error message mean?

It highlights a field I am refrencing but the field name is correct?

This is the code and I have typed in red the part the error refers to:

Sub DriversAvailable2()
Dim mydb As Database
Dim myset As Recordset
Dim strList As String
Set mydv = CurrentDb()
Set myset = mydb.OpenRecordset("qryDriversAvailable")
strList = "This is a list of available drivers: " & vbCrLf
myset.MoveFirst
Do While Not myset.EOF
strList -strList & myset![FullName] & vbCrLf
myset.MoveNext

Loop
MsgBox strList
End Sub

Thank you for any help and direction in advance!!!!

Hi,

I am trying to set a value (vor_id) by using a select-statement:

Form_22BeherenResidunormen.vor_id = select dbo_verontreiniging.vor_id FROM dbo_verontreiniging WHERE dbo_verontreiniging.sto_id = " & CurrentStof & "

I get the following message: "Compile error. Expected: expression".
I think it has something to do with " " or ( ) or ' ' I have to use for the select statement. But I can not find the correct notation.
Debugging highlights above statement and states "syntax error".

In case the remainder of the code is also necessary in this problem here it is:
---
Private Sub buttonToevoegenResidunorm_Click()

On Error GoTo Err_Command0_Click

Dim stDocName As String
Dim stLinkCriteria As String

If ((CurrentProd 0) And (CurrentStof 0)) Then
stDocName = "22BeherenResidunormen"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

Form_22BeherenResidunormen.pro_id = CurrentProd
Form_22BeherenResidunormen.sto_id = CurrentStof
Form_22BeherenResidunormen.vor_id = select dbo_verontreiniging.vor_id FROM dbo_verontreiniging WHERE dbo_verontreiniging.sto_id = " & CurrentStof & "
Form_22BeherenResidunormen.ProduktText.Value = DLookup("[pro_name]", "dbo_produkt", "[pro_id] = " & CurrentProd)
Form_22BeherenResidunormen.StofText.Value = DLookup("[sto_name]", "dbo_stof", "[sto_id] = " & CurrentStof)

Else
MsgBox ("Er moet eerst zowel een produkt als een stof geselecteerd worden.")
End If
Exit_Command0_Click:
Exit Sub
Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub
---

I hope someone can help me!
Thanks in advance.

Hi

Not sure if this has been covered elsewhere, I have a functuion which generates a query to filter a table down to a specific record. Works fine.

However when I test the code in the debug window, including the two arguements to the function I get a message saying "Compile Error, Expected: =". If I limit the call in the debug window to one argument, no problems. Syntax for the call in the debug window is correct, i.e.

Function Name ("Arguement1", "Arguement2")

Arguements 1 & 2 are strings past by the calling code.

As mentioned, when run from the calling code, works fine, when from debug window get error. i have tested other functions I have with two past arguements, same error, although code work fine at run-time.

Any ideas?

Many thanx

I have added an additional Combo box to my form so that user can select number of cost they wish to add to the bill.

From that another form will appear the amount of times via the number selected.

The code I am using is

If CostsDone = False And DisbursementsDone = False Then
varCosts = Val(cbCosts.Value)
ReDim CostsText(varCosts)
ReDim CostsAmount(varCosts)
ReDim CostsVAT(varCosts)
If varCosts 0 Then
frmBill.Hide
MsgBox ("You must now fill in the Costs")
For counter = 1 To varCosts
frmCosts.Show
Next counter
End If

End If

On the line of code "ReDim CostsText(varCosts)" I am getting a Compile Error Expected array. appearing.

Can anyone help with this??

Thanks,

Hi. I'm afraid i can't work out why this line of code won't compile. I know, it's probably obvious, but I've tried getting rid of the quotes and the brackets around HHSMreturn and all I ever seem to get is "Compile error: Expected expression". I've also tried parentheses around the arguments but that doesn't help. What am I doing wrong?

DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,"[HHSMreturn]","S:Test DatabaseExportsHHSM.xls",-1,,

Hello,

I have a list box that I want to be able to change the row source for using VB.
The row source will be SQL, and will filter depending on which button is pressed.

I have the below code but when I enter it into VB it goes red and says “Compile error: Expected end of statement”.

Can anyone see where I've gone wrong?

Thanks
Quote: Me.List.RowSource = SELECT SAI_QRY.FROM AS POL, SAI_ QRY.TO AS POD, SAI_ QRY.SIZE, SAI_ QRY.TEU, SAI_ QRY.USD_TEU AS [USD/TEU], SAI_ QRY.TOTAL_USD AS [TOTAL USD] FROM SAI_ QRY WHERE (((SAI_ QRY.TEU) Is Not Null) AND ((SAI_ QRY.TRADE)="Yes")) ORDER BY SAI_ QRY.FROM, SAI_ QRY.TO, SAI_ QRY.SIZE;


Me.List.RowSource = SELECT SAI_QRY.FROM AS POL, SAI_ QRY.TO AS POD, SAI_ QRY.SIZE, SAI_ QRY.TEU, SAI_ QRY.USD_TEU AS [USD/TEU], SAI_ QRY.TOTAL_USD AS [TOTAL USD] FROM SAI_ QRY WHERE (((SAI_ QRY.TEU) Is Not Null) AND ((SAI_ QRY.TRADE)="No")) ORDER BY SAI_ QRY.FROM, SAI_ QRY.TO, SAI_ QRY.SIZE;

All I want to do is run a make table query (or append) and show the user how many records were processed.
If I try to set recordset equal to the qdf.Execute I get the Compile error "Expected Function or variable".


I'm not sure what I'm missing...I've searched numerous threads and tried various combos of the execute method.

strquery = "qryEmailGenerate"
Set db = CurrentDb
Set qdf = db.QueryDefs(strquery)
Set rs = qdf.Execute
txtStatus = "Number of email recs: " & rs.RecordCount & vbCrLf

Hi,

I would like to lock my existing records, unless I press a button "unlock"
on the screen. From posts elsewhere on the net, I found the hint that I
should try the tips page of Allen Browne, found here:
http://allenbrowne.com/ser-56.html

Other people have used this tip with success, but when I follow the
walkthrough step by step, I receive a compile error at step four, after I try
to set the On Load property of my form to
=LockBoundControls([Form],True)

The error says in Dutch: "Compileerfout: verwacht regelnummer of naam of instructie of instructie-eind", which must mean something like "Compile error: Expecting line number or name or instruction or instruction end".

What does this mean? And how do I solve this problem? I'm quite a newbie
concerning VBA, so I would be pleased if someone could give me a hint.

Thanks in advance!

I am trying to adapt some code for a 64-bit system. It is for a browse button on a form. I get the error "Expected End of Statement" on the line

	Code:
	 
Declare PtrSafe Function aht_apiGetOpenFileName Lib "comdlg32.dll"
    Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

I don't know what I'm doing. Can anyone help?

Quote: Type tagOPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
strFilter As String
strCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
strFile As String
nMaxFile As Long
strFileTitle As String
nMaxFileTitle As Long
strInitialDir As String
strTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
strDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Declare PtrSafe Function aht_apiGetOpenFileName Lib "comdlg32.dll"
Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

Good Morning/Afternoon/Night (wherever you are)

I am getting this error when i attempt to run this code and i am not sure what is happening????? i dont see any errors with this.


	Code:
	Dim txtAcc As String

txtAcc = SELECT Employees.Access_Level FROM Employees WHERE Employee_ID = XXXXXXXX)

help please

Hi All,

Hope someone can help. I have a database with a form where a user inputs a customer number to update another database. What I want to do is put some validation on the customer number field where it checks if that customer number exists in the other database. If it doesn't I want a messsage to appear saying Invalid Customer Number and set the customer number field to blank.
To do this, I've put the following vba code in the after update event of the customer number field:
Private Sub CustNo_AfterUpdate()
If Me.CustNo Not In(Select[CustDetails].[CustomerNo] from [Cust Details])Then
MsgBox "This is an invalid Customer Number. Please input a correct Customer Number."
Me.CustNo = ""
End If
End Sub

However I think the syntax must be wrong as I get the following message with the In highlighted from If Me.CustNo Not In(:
Compile Error:
Expected: Expression

Can you do an If field not in (select... in vba and if so how?

Any help gratefully received
Thanks

Hello,

Can Someone Tell me whats wrong with that syntax? it pops up a Compile error Expected expression,

Any help is appreciated


	Code:
	CurrentDb.Execute tssql(" & intTripStatus & ",#" & OccupyDate1 & "#, " & val(rs![Dispatch ID]) &")



I am a rookie at this and do not have a background in VB. I am attempting to create a contact database and want to limit the contents of one list based on the value selected in another list. I have used the Access 97 Developer's Solutions sample db (DS) to get me started. In following the DS example I ran into trouble with creating the EnableControls function. I basically do not understand what the function says and therefore can't correct any errors I've made. would someone please explain to me in layman's terms what the arguments in the function mean?

For more background - the following describes what I did:

In step 3 iof DS example it says: Create the EnableControls function in the modSolutionsUtilities module.... I did that by copying the EnableControls function from an object box and pasting it into a newly created module. When I went back to the form and tried to pick a category from the first combo box I got an error message that said: "Compile error. Expected: Expression". When I clicked "okay", it showed me the module I had just created with the word "function" highlighted in the declaration "EnableControl function" found in the header portion of the window. From what I can gather I am supposed to add a name to this "named argument" but I don't know what that really means. Do you know what I need to do to fix this situation?

My function works except when either of two arguments, Frac_5_PARtbl or Frac_5, pass null values. If the nulls are changed to zeroes, the function works, so I've tried using the Nz() function to change the nulls to zero, but without success. The error in the immediate window is "compile error: expected: expression." Making the arguments optional didn't work, either.

Am I doing something wrong?

Here's the code:


	Code:
	Option Compare Database
Option Explicit

Public Function FigFract5(Distance_Feet As Single, Frac_5 As Variant, Frac_5_PARtbl As Variant) As Variant

Dim FigFract0 As Variant
Dim Frac_Distance As Single
Dim FracPar As Variant
Dim Frac As Variant
Frac = Nz(Frac_5, 0)
FracPar = Nz(Frac_5_PARtbl, 0)
FigFract0 = 95


If Frac > 0 Then

If Distance_Feet > 6600 Or Distance_Feet < 8580 Then
    Frac_Distance = 6600
ElseIf Distance_Feet = 8580 Or Distance_Feet = 9240 Then
    Frac_Distance = 7920
ElseIf Distance_Feet > 9240 Or Distance_Feet < 10770 Then
    Frac_Distance = 9240
ElseIf Distance_Feet > 10560 Or Distance_Feet < 11880 Then
    Frac_Distance = 10560
ElseIf Distance_Feet = 11880 Then
    Frac_Distance = 11220
End If

If Frac = FracPar And Frac > 0 Then
        FigFract0 = 95

ElseIf FracPar > Frac Then
    While FracPar >= Frac
        FracPar = FracPar - 0.01
        FigFract0 = (1 / FracPar * Frac_Distance / Distance_Feet) * 10 / 3 + FigFract0
    Wend

ElseIf FracPar < Frac Then
    While FracPar

I posted the below at the msdn discussions page..
Was hoping someone on this site could help also.

************************************************** ********

Morning all,

Im having a bit of a problem when it comes to setting some criteria for an
If statement.
Basically im trying to say if the words "Date" or "Time" DO NOT occur
anywhere inside this combo box then perform this code......
At the moment i've got this...
If Me.cboFields.Value Like "[!*Date]" Or Me.cboFields.Value Like "[!*Time]"
Then
If Me.cboFields2.Value Like "[!*Date]" Or Me.cboFields2.Value Like
"[!*Time]" Then

.....code goes here.....

It doesn't work.. no error or anything can just click button with no
reaction.

I'm not sure if thats right but i've got another the opposite way round
where the wildcard is like
If Me.cboFields.Value Like "*Date" Or Me.cboFields Like "*Time" Then
and this one works fine.
I read that it was the ! that excluded certain data. Is that right?
Any help is greatly appreciated

************************************************** ********

I got a response telling me...

RESPONSE: Jet doesn't recognise "!" in Like comparisons, especially inside Brackets,
which are intended to delimit object names containing spaces.

However, you can make use of the VBA Not operator, like this:

If Me.cboFields.Value Not Like "*Date*" Or Me.cboFields.Value Not Like
"*Time*" Then

Notice I've added extra asterisks. As you have it (in the working version),
the comparison is "return all entries ending in the word Date". Adding a *
to the end is saying "return all entries with the word Date anywhere in the
string".[/I]


This didn't work and gave me an error message
COMPILE ERROR: EXPECTED: EXPRESSION

The VBA help show this as an example
MyCheck = "F" Like "[!A-Z]" ' Returns False.

which leads me to believe that it would be along the lines of what i had at the start?

Like "[!*Date]"

Also date or time will always be at the end, there are no instances where it would be contained anywhere else. (eg. TotalTime, LoginDate)

Thanks for reading i know it's a lot of writing

Mike

Hi,

I have a form with STAFF and a subform with SKILLS. When I add a new STAFF record and update its [Job] field, I want to run an append query, to put required skills into a table and immediately update the display of the subform.

I tried two methods:

1. A macro that does "Save" and then "Open append query"

This does the first two parts as the tables are being updated correctly....but how would I then redisplay the subform? I tried Repaint on the subform, but it told me it wasn't open, then I tried Repaint on the main form but it appeared to have no effect.


2. Doing it via code.

I suspect this may me the better way as at least I know how to use the Requery statement (if almost no other code!!)

But what's the syntax for running an append query? The code I came up with (in my hamfisted way) is as follows:

Private Sub Job_AfterUpdate()
DoCmd.Save
DoCmd.OpenQuery(qryAppendRequiredSkills,acViewNorm al,,)
Me.frmStaffSkills.Requery
End Sub

But the OpenQuery line is giving me an error message. Compile error. Expected: expression

I don't know what it needs!


I don't mind whether I end up using method one or two, but as you can see I'm stuck on both.

Thanks for any help,

Pat


Not finding an answer? Try a Google search.