error with the Eval() function

I have a string that has only a function name in it. I want to call the function name that is in the string. my function is called goodtime. I have the following code.

	Public Function goodtime() As String
    goodtime = "testing"
End Function

	       strTemp = strAnswer & "()"
        strAnswer = Eval(strTemp)

When I run this at the eval statement I get the following error.

The expression you entered has a function name that microsoft office access can't find

I cannot figure out why, if I just run
then it works fine. Does anyone have a clue why this is not working?

Post your answer or comment

comments powered by Disqus
Could somebody help me with the Eval function?
I have a string that I store in a variable. I would like this string to be evaluated to true or false, but I always get an error message so now I am totally lost how Eval function might work.


When I write: ? Eval(TestValue="ReportRequest") I get it evaluated to -1. So far so good.

But if I have the string stored in a variable like StringToEval="TestValue='ReportRequest'"

? Eval (StringToEval) gives me the following error message:
Can't find the name 'TestValue' you entered into the expression.

Could somebody tell me how to evaluate this variable (like StringtoEval) that contains a string with a variable?

Many thanks.

I have a field on a report called date open and a field called date sent.

I need to create as field that will show the diffrence betweent hese two. i know how to do that with the now() function. but sometimes the date sent field is not filled in this case i need to compare the date open to todays date. is there a way to do this in one field or will there need to be two. and how would i compare todays date with the date open

thanks in advance

I am working on a Access Web application and I am running into a problem with the REPLACE Function. I am trying to replace a " * " in the second position of a model number:

I am using the following expression in my query table:
Access 2010 gives a message indicating that this function is not supported in Web-Compatible queries.
ny help would be GREATLY appreciated!!!


ok... i have a problem with the count function because the zero value, i've search this forum and i read a lot of threads about it, but i just cant make it work ... i'm a little newbie in access so if any of you can spare a little of your time with this subject that as been talk to death, that would be just great.

Heres the situation i want to resolve:

I have 2 tables ( Emp1 and Emp2 )



The tables have the relationsship of: 1 to many (Emp1 --> Emp2)

The field CONT_EMP2 on table Emp2 can have the values "Yes" or "No"

I want to be able to show for a given curse on table Emp1, the number of "Yes" or The number Of "No"


Emp1 data:

123 : Excel
124 : Winword

Emp2 data:

11 : 123 : Yes
12 : 123 : No
13 : 124 : Yes
14 : 124 : Yes

I want a query that ask for the curse name and then shows the following result:

The users chooses the curse Winword:

Curse ----------------- Number of No's
Winword -------------------- 0

The above result never shows because the there isnt any "No"'s for the curse Winword.

I saw an example with a LEFT JOIN but i just cant make it to work, brrr

Any help would be very appreciated

Thank you.


Hi there

I'm hoping someone can help me out with the use of the Eval function. I am using Access2003 under WinXP Pro. I can successfully use the Eval function and get it to call any function with or without parms. I know that any function that is passed to Eval() must be declared Public. It can be a Sub or Function, as long as it's Public. I even have it where the "function" evaluated by Eval can be in a form (class) module or in a standard code module (and if in a form module, as long as the call is fully qualified, I can call it). That's all good.That's not the problem. The problem is with the # of times my routine that houses my Eval function call is run by Access.

I have done some research on this, but it appears that those out there having the same problem have never really gotten a good answer to/reason for this problem (just some work arounds I imagine)

Research Links:
Here problem did get a resolution, but Dirk's post shows that he was having problems with the function being called twice too:

Same posting:

And there was another older posting I've seemed to have lost the link to where the poster was having the same recursive call problem, but there were no responses ever posted ;-(


I have a Main Form (frmMain) containing 2 subforms (fsubChild1 and fsubChild2).

The SourceObject value for fsubChild2 is constantly being changed to show whatever form I am working on at the time (think of frmMain as simply a container form for my 2 subforms and really does nothing more and has no other controls on it, other than an Exit Application button)

fsubChild1 contains labels, that, when clicked on, excutes a standard function that must exist in the code-behind-form for any valid form that can be plugged in as the SourceObject for fsubChild2. Let's call this standard function mtdRunTheCode. It exists as a PUBLIC function in every form that could potentially be the SourceObject for fsubChild2 and is passed the name of a function to execute, depending on the label clicked in fsubChild1 (each label in fsubChild1 passes mtdRunTheCode a different function. Think of mtdRunTheCode() as a wrapper function. It always contains the Eval(strFunction) code line, but may also contain code or calls to other routines specific to the form in which it is executing. I wanted to do this so that fsubChild1 could call generic functions (located in a standard code module) that must be run when a label is clicked (no matter what form is contained in fsubChild2), but, depending on the form in fsubChild2, can run code specific to fsubChild2 first (such as validating the current record, saving, etc etc)

Let's imagine that I had 2 forms: frmCust and frmProd.

Both of these forms at any given time, could be the SourceObject for fsubChild2
Both of these forms contain their own version of mtdRunTheCode - declared Public, named the same and located in the subform's form module (ie the function becomes a "method" of the form)..

This is how the function looks in frmCust

	Public Function mtdRunTheCode(strFunction)
   'This function is called by the OnClick event of a given label in fsubChild1
   'The value of strFunction changes, depending on the label that was clicked
   'and contains the name of a unique, public function to execute. All of these
  ' public functions are contained in a single Code Module.

   'Code that is specific to this form
   Application.RunCommand acCmdSaveRecord

   'Execute the general function passed in by clicking a label in fsubChild1

End Function

And this is how that same function it looks in frmProd

	Public Function mtdRunTheCode(strFunction)
   'This function is called by the OnClick event of a given label in fsubChild1
   'The value of strFunction changes, depending on the label that was clicked
   'and contains the name of a unique, public function to execute. All of these
  ' public functions are contained in a single Code Module.

   'Code that is specific to this form
   DoCmd.OpenReport "rptSomeName"

   'Execute the function passed in by clicking a label in fsubChild1

End Function

The Calling Code in fsubChild1

	Private Sub lblTask1_Click()

  Call Forms!frmMain!fsubChild2.mtdRunTheCode("udfGeneralFunc()")

End Sub

Private Sub lblTask2_Click()

  Call Forms!frmMain!fsubChild2.mtdRunTheCode("udfAnotherFunc()")

End Sub

And finally, the generic function located in it's own standard code module

	Public Function udfGeneralFunc() As Boolean

  MsgBox "This is nifty stuff"

End Function

By doing the above, I can utilize the same general functions for an OnClick of a lable, no matter what subform is in fsubChild2, yet execute form specific code first, if needed.

I don't get errors if I try to run Eval routine in any of the the mtdRunTheCode() routines. What happens is that the mtdRunTheCode routine runs TWICE. I tracked it and when I click the label in fsubChild1, the following events occur:

- Calls mtdRunTheCode
- Executes the Eval function (which in turns calls the passed in function located in a code module)
- Recursively calls mtdRunTheCode again
- Re-executes the Eval function again.

So for example, if the strFunction arg passed to mtdRunTheCode was a call to the public function udfOpenTheForm('frmTest'), and udfOpenTheForm simply ran the openform command to open the frmTest form (in Normal Window mode), this is what happens

- Calls mtdRunTheCode
- Executes the Eval function (calls udfOpenTheForm which opens the frmTest form)
- Recursively calls mtdRunTheCodeagain
- Re-executes the Eval function again (recalls udfOpenTheForm)

If udfOpenTheForm opens the form in dialog mode, since that halts execution until the form is closed, it causes different problems, making the user click twice to close that form ->

- Calls mtdRunTheCode
- Executes the Eval function (calls udfOpenTheForm which opens the frmTest form in acDialog mode). Waits until user closes form.
- Recursively calls mtdRunTheCode again
- Re-executes the Eval function again (recalls udfOpenTheForm)
- waits until user closes the form, then finally leaves the mtdRunTheCode routine

I don't really want to put the mtdRunTheCode() in a standard code module, because I really want to be able to execute form specific things before executing the code resulting from a label's onclick event (and I may not want to do that for a different form)

Does anyone know the reason for the recursive call?? I've been struggling with this for a bit now and it's driving me nuts.


I am developing two database, one that is linked to anothers table. In the table that has the table that is non-linked I have a simple function in a query that parses out the phone number of an individual so that it will display the way I want it to on a report etc, this works fine, in the other data base with the linked table to the same data I have another query that has the exact same syntax, however when I run this query I get an error stating that this function is not available in expressions. I did some further testing with the Trim function and it creates the same error. I created this function from a different PC and then zipped it and ported it to another PC where I unzipped it, at the other PC it works fine, but here it is a different story. Is there a database setting that needs to be set, or possibly a table property? Any help would be greatly appreciated, wasting too much time on this one.

Hi, is it possible to use the eval function with variables e.g. Eval("foo + bar") or Eval("""The value of foo is "" & foo")?

This is because I have my database settings in a table (one field having the name of the setting and the other containing the value) and I wish to be able to reference variables in the settings value. E.g., for the "MsgOnExit" setting, I would have the string

"Are you sure you would like to exit " & AppTitle

Where AppTitle is the name of the database. I would then plan to use Eval at runtime to evaluate the expression to resolve AppTitle to a value. Of course, this may not be the best example as many would argue AppTitle should be a constant, but there are other settings which rely more heavily on variables being able to be resolved at runtime.

(I'm using a settings table because it allows for a settings GUI and also the ability to variables across sessions.)


Hello, I have a macro which outputs the results of a query to excel using the outputto function. I have made some changes to my data such that the output of this query is now over 17,000 lines.. my macro hangs for a few minutes and now fails with "no current record"

Any ideas? If I copy/paste the output of the query into excel it works (albeit after 3-4 minutes)..

This macro worked perfectly when the output of the query was around 14,000 lines...

I really appreciate the assistance, I hope to be an active member here as my new job requires quite a bit of access development!

HI everybody,
I need some help with the following function related to the table attached.
I am trying to define in the column [prove2] a true value for all the [type_risk] = 1 but not sub-sequential 1 like specified in the attachment.

here is my function.

Function r2(type_risk As String, periudha As Integer)
Dim i As Integer
For i = 1 To periudha
If type_risk = "1" And periudha = i Then
r2 = "true"
End If

End Function

can somebody help me??
thanks in advance

I'm trying to update a form using the eval function in a program.
However there is a part that I can't get to work
It will not change the back ground color to white.
I use the eval function since I will do this many times and the form automatically add textboxes when more data is available

Function test()
a = "Forms![INFORM]!LabelIt7.backcolor= 16777215"
Eval (a)
Debug.Print a
End Function

Also tried "Forms![INFORM]!LabelIt7.backcolor= " & 16777215

It doesn't change the color and it doesn't give an error message.
If I change the a string into an command that is not possible it will give an error message.

If I give an enter in the immediate window at the place where it printed the 'a' string the function works perfectley.

Can anybody tell me where I go wrong?

When I use the EVAL function to execute a procedure in a form it executes the procedure twice. Does anyone know of a workaround or a fix for this?

For example:

On Click: =eval(Form_AfterUpdate)

where Form_AfterUpdate is a public procedure.

I tried to use the EVAL function in a popup menu to execute a procedure in a form but had the same result. Instead I had to use the SendKeys function but the problem with that is it only works for control with shortcuts defined.

I need to set a control to a given field on a form.
But, the form could either be a form, or a subform.

Is it possible to set a control using the eval function as follows:

	Dim ctl as Control
Dim strForm as string
dim strControl as string
strForm = "frmItem!sfrmItemSupplier.Form"
strControl = "UnitPrice"
set ctl = eval("Forms!" & strForm & "!" & strControl)

If this can't work, is there another command similar to eval that lets you execute a line of code from a string?
I know I could use Forms(strForm).Controls(strControl) - but this doesn't work for a subform.

Thank you,


How do you get only the date with use of the NOW function and not with the time also? thanks

Hi there,
I am working on the query were I have multiple calculations which sometimes gives me #error when I run the query. I am trying my hand with the iif function. I tried to search on google about getting iif and sum togeather but none of them work.

Here's my expression

I want to display 0 if Total online time is 0 or carry on with the expression.

I have more then one items similar to this due to which I cannot use where statement.

Subqueries is other option. But i have read some bad stories about subqueries breaking.

Please suggest were am I going wrong .Thanks.

I'm trying to set a DAO recordset to the following query:

	SELECT DISTINCT Temp.ID, Temp.DateUpdated FROM (SELECT DISTINCTROW B.ID,[P].[DateUpdated] FROM (tblBuyers AS B tblApartments
AS P)as Temp WHERE (([Temp].[DateUpdated]) Between ([Forms]![frmDateRange]![txtFrom]) And ([Forms]![frmDateRange]![txtTo]))

But I get the too few parameters Error.

The form is open (and visible), the controls are named properly.

I tried wrapping the form references in the Eval() function:

	WHERE (([Temp].[DateUpdated]) Between Eval('[Forms]![frmDateRange]![txtFrom]') And Eval('[Forms]![frmDateRange]![txtTo])')

But it didn't make a difference.

Help sorting this out will be greatly appreciated!


I'm having trouble with a DateSerial function. Actually, the function itself is working fine. The problem is when I use a second query with the "between and" function that i run into problems.

So, I'm using query 1 with the DateSerial function to adjust dates saved as text (example "20110401") to short date format ("4/1/2011"). That is working fine. However, in query 2, i use that adjusted date field with a "between and" criteria ("Between #1/1/2011# and Date()). This appears to work at first as the results load and look normal. However, if I try to work with the results at all (click the arrow for the last field, try to auto fit all fields etc), the "Data type mismatch in criteria expression" error appears and all fields change to "#Name?"

I'm sure this is due to an issue with the DateSerial adjusted dates but I don't know what to do to fix.

I appreciate any help that can be provided.

i have just managed to include the time in my database for my it project. iam having problems with the date function. i would like to find out how to make the date function work. iam doing a video renatl so it would be ideal to have a booking form so i would be able to calculate when the bookings expire. any help would be muchly appreaciated

My backend is an SQL server and the front end uses linked tables in access. everything works fine except the following situation.

If a user makes a change on a form, and then changes it back, then tries to move to a different record (or anything that causes an update I think) I get a write conflict error with the options to copy or drop. You can choose 'drop' and nothing is lost - because nothing was done really.

Think of checking a check box, then unchecking it and moving away.

I've error trapped this in the OnError event, but suppose the user clicks a "goto next record" button. Even if I trap the write conflict error it's followed by a "Can't go to the specified record" error.

My nav buttons are coded so that if you're on the first record the "previous" button is disabled so that normally handles the type of error. But that is moot when the write conflict messes it up.

Any ideas?

(Thus far I've just told my users not to do that lol, but I'd like a better solution)

EDIT: In addition, I've tried adding timestamp fields as recommended by several people to fix the write conflict. This results in a 7776 error which as far as I can tell is unfixable

Hi all,

does someone know if it were possible to use the Eval function to do following, and if so - how to do it. Cause I am failed.

Thing is I store strings in 7 different variables. They are named


And for a specific output I want to join this values in one string. I know this is all very easy, but I thought it could be accomplished with the Eval function in a for - next - loop.

	For i=1 to 7
strBuffer = Eval("strValue" & i)    'strBuffer should so have the value of strValue1 - if i = 1 
Endstring = Endstring & ", " & strBuffer 

But if I do it with this Eval function, it says it doesent find strValue1. If I call Eval(strValue1) and strValue1 = "Hello", than it says there is no value "Hello".

Can anyone help. It's just that I love to learn.
Kind regards,

Hi All,

I made a function to calculate the difference in hours between two times.

	Function TimeDiff(Time1 As String, Time2 As String) As Double
'This function is used to calculate the difference in hours between two times.
'It is capable of handling a finish time that is after midnight.
    TimeDiff = 0
    If Not IsNull(Time1) And Not IsNull(Time2) Then
        If CDate(Time1) > CDate(Time2) Then
            TimeDiff = ((CDate(Time2) - CDate(Time1)) + 1) * 24
            TimeDiff = (CDate(Time2) - CDate(Time1)) * 24
        End If
    End If

End Function

I have a query where there are 2 sets of times in fields (Start1, Finish1, Start2, Finish2).
When I add a calculated column with the following expression:

	Expr1: TimeDiff(Start1,Finish1) + TimeDiff(Start2,Finish2)

it calculates in the query correctly.

However, when I made a new function that incorporated this function, it gave me the #error result in the calculated field.

	Function OrdinaryHours(Status As String, Start1 As String, Finish1 As String, Optional Start2 As String, Optional Finish2 As
String) As Double
    OrdinaryHours = 0
    'If employee is NOT a casual
    If Not Status = "CT" Then
        If Not IsMissing(Start2) And Not IsMissing(Finish2) Then
            OrdinaryHours = TimeDiff(Start1, Finish1) + TimeDiff(Start2, Finish2)
            OrdinaryHours = TimeDiff(Start1, Finish1)
        End If
    End If
End Function

Does anyone know why this would #Error. I have the TimeDiff function initialising to = 0 before doing anything...I am stumped why it returns #Error when it should return 0 in the case of something going wrong.


I need to trap an error if the user enters a item number that does not exist in the table.

I created a macro for the OnNoData event and it works great when I test the macro by running the report from the Reports Objects list.

However, when I run the report from a command button on my Switchboard, after my custom error message appears warning that an invalid item number was entered and the I click OK, I receive the error "The OpenReport action was canceled."

Obviously, I'm missing something in the command button properties, but I haven't got a clue. My error code is as follows:

Private Sub PrintOneSpec_Click()
On Error GoTo Err_PrintOneSpec_Click

Dim stDocName As String

stDocName = "rpt_Print 1 Packaging Spec with Revision History"
DoCmd.OpenReport stDocName, acPreview

Exit Sub

MsgBox Err.Description
Resume Exit_PrintOneSpec_Click

End Sub

I want the user to be returned to the Switchboard if there are no records found.

Any and all help will be appreciated.

I have been trying to get my crosstab working and have run into a spot of bother. I have followed the process detailed in SOLUTIONS.mdb to the letter but keep getting thrown out with the rejection Run-Time Error 2448 You can't assign a value to this object" at the line highlighted in the code below. What seems strange is that the object causing the problem is just being passed a standard variable which has been declared at 3 other instances in the code with no problems occuring.
If anyone has any ideas then they would be greatly appreciated.



	Option Compare Database
Option Explicit

Const conTotalColumns = 12

Dim dbsReport As Database
Dim rstReport As Recordset

Dim intColumnCount As Integer
Dim lngRgColumnTotal(1 To conTotalColumns) As Long
Dim lngReportTotal As Long

Private Sub InitVars()
    Dim intX As Integer
    lngReportTotal = 0
For intX = 1 To conTotalColumns
     lngRgColumnTotal(intX) = 0
Next intX
End Sub

Private Function xtabCnulls(varX As Variant)
    If IsNull(varX) Then
       xtabCnulls = 0
       xtabCnulls = varX
    End If
End Function

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intX As Integer
    If Not rstReport.EOF Then
       If Me.FormatCount = 1 Then
          For intX = 1 To intColumnCount
              Me("Col" + Format$(intX)) = xtabCnulls(rstReport(intX - 1))
          Next intX
          For intX = intColumnCount + 2 To conTotalColumns
              Me("Col" + Format$(intX)).Visible = False
          Next intX
       End If
    End If
End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim intX As Integer
Dim lngRowTotal As Long
   If Me.PrintCount = 1 Then
      lngRowTotal = 0
      For intX = 2 To intColumnCount
          lngRowTotal = lngRowTotal + Me("Col" + Format$(intX))
          lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" + Format$(intX))
      Next intX
      Me("Col" + Format$(intColumnCount + 1)) = lngRowTotal
      lngReportTotal = lngReportTotal + lngRowTotal
   End If
End Sub

Private Sub Detail_Retreat()
End Sub

Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim intX As Integer
For intX = 1 To intColumnCount
Me("Head" + Format$(intX)) = rstReport(intX - 1).name
Next intX
Me("Head" + Format$(intColumnCount + 1)) = "Totals"
For intX = (intColumnCount + 2) To conTotalColumns
Me("Head" + Format$(intX)).Visible = False
Next intX
End Sub

Private Sub Report_Open(Cancel As Integer)
Dim intX As Integer
Dim qdf As QueryDef
If Not (IsLoaded("SetDates")) Then
Cancel = True
MsgBox "To preview or print this report, you must open Setdates in form view", vbExclamation, "Mustopen dialog box"
Exit Sub
End If
Set dbsReport = CurrentDb
Set qdf = dbsReport.QueryDefs("xtabqry")
qdf.Parameters("forms!setdates!begdate") = forms!setdates!begdate
qdf.Parameters("forms!setdates!enddate") = forms!setdates!enddate

Set rstReport = qdf.OpenRecordset()
intColumnCount = rstReport.Fields.Count

End Sub

Private Sub Report_close()
On Error Resume Next
End Sub

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No records match the criteria", vbExclamation, "No Records Found"
Cancel = True
End Sub

Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)
Dim intX As Integer
For intX = 2 To intColumnCount
Me("Tot" + Format$(intX)) = lngRgColumnTotal(intX)
Next intX
Me("Tot" + Format$(intColumnCount + 1)) = lngReportTotal
For intX = intColumnCount + 2 To conTotalColumns
Me("Tot" + Format$(intX)).Visible = False
Next intX
End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
End Sub

Thank you in advance for your help.

I have a filter that uses multiple criteria to produce a report. It works great with one exception. When the Between function is followed by another criteria (such as "Action" required) I get an error message: Run-time error '3075': Syntax error (missing operator) in query expression 'SaleDate Between [txtSaleFrom] And [txtSaleTo][Action] =-1'. Any other combination of criteria works great. Am I missing an "AND"?

The first part of the code for the filter is:

Private Function BuildFilter() As Variant
Dim varWhere As Variant

varWhere = Null ' Main filter

' Check for Sale Dates within the range.
If Me.txtSaleFrom > "" Then
varWhere = varWhere & "[SaleDate] Between [txtSaleFrom] And [txtSaleTo]"
End If

' Check for LIKE Product Type
If Me.cboProductType > "" Then
varWhere = varWhere & "[ProductType] LIKE """ & Me.cboProductType & "*"" AND "
End If

' Check for LIKE Product Number
If Me.txtProductNo > "" Then
varWhere = varWhere & "[ProductNo] LIKE """ & Me.txtProductNo & "*"" AND "
End If

' Check for Action Required
If Me.chkAction < 0 Then
varWhere = varWhere & "[Action] = " & Me.chkAction & " AND "
End If

I create a report and I try to use the DlookUp function;
I have a table (Values_T) [ID], [Accepted], [Reject], [VL100], [Vl600], [E-Date] in my reports I have field txtCalc I try to brinf the value of [VL100] to my report and make some calculations, and it is the function

=DLookUp("[VL100]","Value_t”, “[E-Date] = & #04/06/2012#")

but I got a #Error in the field what is in my report, What is wrong n with this function

Thank you for your help.


Not finding an answer? Try a Google search.