Undefined Function 'replace' In Expression

Jul 12, 2007

I'm trying to help someone with some text functions in Access, and I have used the Replace function to strip out spaces in a postcode. I created a dummy database in Access 2003 but in 2000 format since she is still on Access 2000. However, she is getting the above message. Incidentally, I don't get the message when I run it in Access 2000.

She has checked her references and has no missing ones. She has:

Visual Basic for Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.5 Library


I have attached the DB - I'd be really grateful if someone could try opening it in Access 2000 to see if they get the same error.

Does anyone have any ideas?

Many thanks

View Replies


ADVERTISEMENT

Undefined Function Mid In Expression

Nov 30, 2005

I have written a statement within a select query that modified the format of a date value:

Date of Birth: IIf([Date_Of_Birth]="00000000","",DateValue(Mid([Date_Of_Birth],7,2) & "/" & Mid([Date_Of_Birth],5,2) & "/" & Mid([Date_Of_Birth],1,4)))

But receive the error:

Undefined Function Mid in Expression

Can anyone help?

thanks

View 4 Replies View Related

Undefined Function In Expression

Mar 14, 2008

I created a function and when I try to use it in my query I am getting the error message Undefined Function in Expression.

This is the function:

Option Compare Database
Option Explicit

Function Next_Weekday(DateField As Date) As Date

If Next_Weekday((DateField)) = 1 Then
Next_Weekday = (DateField) + 1
Else
If Next_Weekday((DateField)) = 7 Then
Next_Weekday = (DateField) + 2
Else
Next_Weekday
End If

End Function

Any suggestions as to why it won't recognize it?

Thanks.

View 2 Replies View Related

Undefined Function WHERE In Expression

Sep 20, 2004

Hi Guys

Im not very good with SQL so any help would be appreciated, I used:

SELECT
(SELECT Count(*) FROM [tbl_stock/equipment]) AS Bases,
WHERE ((([tbl_stock/equipment].InternalID) Like "D*" And ([tbl_stock/equipment].InternalID) Not Like "DM*") AND (([tbl_stock/equipment].Sold)=0)),

(SELECT Count(*)FROM [tbl_stock/equipment]) As Monitors,
WHERE ((([tbl_stock/equipment].InternalID) Like "DM**") AND (([tbl_stock/equipment].Sold)=0)),

Count(*) As Printers FROM [tbl_stock/equipment]
WHERE ((([tbl_stock/equipment].InternalID) Like "PR***") AND (([tbl_stock/equipment].Sold)=0));

and all Im getting is "Undefined function WHERE in Expression" and im stuck,

What im trying to do is get a count based upon the results of the Like query, and i dont know where im going wrong.

If i use:

SELECT

Count(*) AS Printers FROM [tbl_stock/equipment] WHERE ((([tbl_stock/equipment].[InternalID]) Like "PR***") And (([tbl_stock/equipment].[Sold])=0));

I get a count for that result, its just when i try and add multiple columns i get problems. Any Ideas???

Thanks
Mike

View 1 Replies View Related

HELP: Undefined Function 'DLookup' In Expression

Jul 6, 2005

i am trying to executed q query which has a Nz function. this works fine when exceuted from access. but when i try to executed the same from Vb i get an exception

"Undefined function 'Nz' in expression"

can anyone point out why this is happening? and wts the solution?

thanks in advance
ASMS

View 5 Replies View Related

Undefined Function In 'Conc' Expression

Mar 10, 2007

Please, Please tell me where I'm going wrong. I have read up and looked at examples of concatenating multiple rows into one, but I receive the error message " Undefined function in 'Conc' expression when I try to run the query.

Below is the sql:

SELECT testconc.InvoiceNo, Conc([InvoiceNo],"CostCentre") AS CostCentre
FROM testconc
GROUP BY testconc.InvoiceNo;

Please can someone put me out of my misery?:mad:

Many thanks
Melanie

View 14 Replies View Related

Undefined Function 'BuildCustomerName' In Expression

Feb 10, 2005

Hello first time poster :)

My boss recently moved a DB from one server location to another and now it is no longer working properly. Please bare with me, I am not very knowledgable of Access and am primarily an oopl coder. I am using Access 97 in an XP.

The database is comprised of 6 files: Service_Request.mde/mdb, Common_Code.mda/mdb/mde, and default.mde. Service_Request.mde/mdb both reference Common_Code.mde and default.mde.

Service_Request has a login screen when you first start it up caused (I assume) by a macro that makes the call: login("frm_requestlist"). A prompt appears where i can enter a user name and login but when I click ok it crashes about 1 minute later with the 'Undefined function 'BuildCustomerName' in expression' error. I can bypass the login with the Shift+F11 shortcut but all of the forms crash when I double click them.

The function 'BuildCustomerName' is located in Common_Code in the module 'Library'. However, I looked through the code in Service_Request and Common_Code and except for its definition, I cannot find a call to it anywhere. Service_Request does make calls to a few other functions in Common_Code, but I am unable to tell if they are working or not.

I have checked the references for Service_Request with a reference wizard and as far as I can tell they seem correct. It references both Common_Code and default in their current locations on the server. However, even if they were incorrect I cant figure out how to change them with my current resources.

Any help in this matter would be greatly appreciated as would tips on how to step code cause I havent been able to figure that out either.

Thank you

View 1 Replies View Related

General :: Undefined Function In Expression

Oct 28, 2014

I now try to rework on an old project but when I try to run some queries I get an error message: Undefined function 'Date' in expression . The same happen for the Format function.Both are built in Access functions

View 5 Replies View Related

Error 3085 Undefined Function Nz In Expression - Its Not The References

Apr 2, 2008

I have someone using my database as an mde, using the 2003 runtime, on a computer that has XP with Office 2007 (including Access 2007). Eveything works fine, except they get error 3085 on a line of code that is running an Insert SQL statement.

What is weird is that the SQL statement does not include the nz function. It does include a simple UDF for rounding, but that UDF doesn't return an error.

I also have code that checks the references on startup, and none of them are coming up as missing or broken. I have also double checked each reference and they have the exact same version number of every reference (dao360.dll, mscomctl.ocx, etc.). So if it is a "diambiguation" issue, I don't know how to fix it, or even identify it.

The code runs on several other computers with various combinations of XP, Vista, Office 2003, and Office 2007 with no problems.

I am stumped. What else could cause this error? Even if you don't really know, but you have a vague idea, please post it because I can't even think of anything else to investigate at this point.:confused:

View 14 Replies View Related

Undefined Function

Jan 27, 2007

Hello,

I have a make table query and at some field on criteria "[Forms]![Gest vz]![Det1]![Cod furnizor].[Column](1)" i have the following error:undefined function '[Forms]![Gest vz]![Det1]![Cod furnizor].[Column](1)' in expression.
Please help!!! :)

Thanks!!!!!!!!

View 4 Replies View Related

Undefined Function 'format'

Apr 9, 2006

Percentage: Format(CountOfRegular Booking/DCount("*","tblBooking"),"Percent")I get an error: 'Undefined function 'Format'' - how can I fix this? The above expression is supposed to convert values into percentages. I copied the expression from someone's sample database (to help me) and I just changed the appropriate parts around, but I get that error. I put a space between 'Regular Booking' because that's how it is presented as my field name, but I also put them together as one word to see if that would solve matters, but no luck. The sample database works fine, however.Sample query expression:Percentage: Format(CountOfbooleanFieldName/DCount("*","TableName"),"Percent")Thanks guys. :D

View 6 Replies View Related

Undefined Function Error

Mar 5, 2006

Hi,

I have used access to create a database for my web site which I use ASP to connect. I've written a function in Access which takes a product description and shortens it down...

Public Function GetShortDesc(strInput As String)

Dim x As Integer
Dim tempstr As String
Dim checkstr As String
x = 100

tempstr = Left(strInput, x)
If Right(tempstr, 1) = " " Then
GoTo stringfound
Else
Do Until checkstr = " "
x = x + 1
tempstr = Left(strInput, x)
checkstr = Right(tempstr, 1)
Loop
End If

stringfound:

GetShortDesc = Left(strInput, x - 1)

End Function

And I have used this function in a query which works fine in Access, but when I go to my ASP page and try to return the field to my page I am getting an error...

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'GetShortDesc' in expression.

It looks like the ASP page isn't accepting my function, Can anybody help???

Thanks in advance

View 4 Replies View Related

Undefined Function On Query

Jul 9, 2013

I am getting the message Undefined Function on this query.

SELECT tblTerminal.TerminalID, tblTerminal.TerminalName, tblTerminal.TerminalAddress, tblTerminal.TerminalTypeID, tblTerminal.TerminalLocation
FROM tblTerminal
WHERE (((tblTerminal.TerminalName)=Environ("ComputerName ")));

What could be causing this. I recently went from Access XP and XP to Access 2010 and Win 7 and Environ seems to be causing problems.

View 8 Replies View Related

ODBC Error 1 'Undefined Function'

Jun 8, 2006

I am trying to run a query in an Access Database from another program using ODBC.

The query contains a user defined function and works fine when I run it in Access.

When I try to run the same query externally via ODBC I get the following error:

"ERROR 1 - [Microsoft][ODBC Microsoft Access Driver] Undefined function 'replace_chr13' in expression"

If I replace my user defined function wit a standard Access function such as LCase, the query can be accessed externally fine!

Can anyone suggest a solution?

View 1 Replies View Related

ODBC Error 1 'Undefined Function'

Jun 8, 2006

I am trying to run a query in an Access Database from another program using ODBC.

The query contains a user defined function and works fine when I run it in Access.

When I try to run the same query externally via ODBC I get the following error:

"ERROR 1 - [Microsoft][ODBC Microsoft Access Driver] Undefined function 'replace_chr13' in expression"

If I replace my user defined function wit a standard Access function such as LCase, the query can be accessed externally fine!

Can anyone suggest a solution?

View 7 Replies View Related

General :: Undefined Function Format Number

May 25, 2013

I looked for missing references after I had received this message. List of available references had 4 selected items:

1) Visual Basic for Applications;
2) Microsoft Access 15.0 Object Library;
3) Microsoft Office 15.0 access database engine library
4) OLE Automation;

I tried to check off the 3rd and the 4th one, but it came to nought (error didn't dissapear). What else can I do ?

View 6 Replies View Related

Modules & VBA :: Undefined Function For A Module Called In A Query?

Sep 8, 2013

This module is giving me the "undefined function" error message when I try to run my query. I don't know why, but I have checked that there are no references with "missing" and there are not. I also added the word "Public" to the function becasue that was advised by another forum user. I thought it worked perfectly the first time I ran this query, but now it is not working and I do not recall making any changes. I have called the module basFunctions:

Option Compare Database
'************************************************* *********
'Declarations section of the module
'************************************************* *********
Option Explicit
'================================================= =========
' The DateAddW() function provides a workday substitute
' for DateAdd("w", number, date). This function performs
' error checking and ignores fractional Interval values.
'================================================= =========
Public Function DateAddW(ByVal TheDate, ByVal Interval)
Dim Weeks As Long, OddDays As Long, Temp As String

[code].....

View 3 Replies View Related

Queries :: Undefined Function In Query And Visual Basic

Apr 3, 2014

I'm doing some debugging over the phone.My friend has Access 2003.What does not work:When he codes the MID function in a query he gets an error like this:"Undefined function 'Mid' in expression."

In the Visual Basic editor, he gets the same error: MID is undefined.What DOES work:I walked him through the use of LEFT and RIGHT in the query and they work.In Excel, MID works.Might there be some odd ball Access configuration mess up, or maybe more likely, his Access installation is incomplete.

View 3 Replies View Related

Queries :: Format Date Parameter In Dsum - Undefined Function

Sep 25, 2014

I m using Access 2010.I m Facing a problem in the undermentioned expression,

OPB: 1*Nz(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<#" & Format([FromDt],"dd-mm-yy") & "#"),0)

when i run the query it gives "Undefined Function 'Format' in Expression" ?

View 3 Replies View Related

Expression To Replace / With _

Jun 23, 2005

i have a table that has a column containing a letter reference a / and then another reference ( EG. AAA/A56457A, ght/6577654ghf, etc ) and i want to replace the / with a _

whats the easest and quickest way to do this as an expression

best regards

View 1 Replies View Related

REPLACE Function

Oct 23, 2007

I am trying to use an update query to modify some text in a specific field using the REPLACE function without much luck!!!

I have a field called "Option" and I want to replace the word "Metallic" with "Paint" and the word "Electric" with "Windows".

Could someone please point me in the right direction.

Thanks in anticipation.

View 7 Replies View Related

Big Problem With Replace Function

Jun 23, 2006

Hi All,

I'm currently migrating a lot of Excel processing to Access and really enjoying the transition. I am, however, having big problems with the Replace function in Access.....


In Excel I use a macro to relace anything in a cell contents that follows a space with nothing......i.e. replace " *" with ""

The fields that i need to do this on do vary in length- they are product descriptors.....e.g


abcdefg 123456
xzy 987


The replace feature (ctrl + h) in table view will do this correctly if "any part of cell" is specified in "match" selection but I cannot seem to replicate this in a query.

I'd be grateful for any suggestions here. I thought about exporting the fields in the columns to Excel and doing the replace there, but I can't get it to work. Similarly I tried writing some VBA for this, but again no joy!

Thanks,

N

View 2 Replies View Related

Replace Function In Access

Aug 3, 2006

I'm trying to assign a numeric value to different existing text values in a field in Access. Does anyone know the syntax of this?? :) thanks

View 3 Replies View Related

Replace Function In A Query

Jan 19, 2008

Can the replace function be used in a query?

I have used this

=Replace(Format([SITELATSEC],"00.0"),".",",") in a report and it works fine.

I have the following expression in a query that is output to a report.

LAT: "Latitude: " & [SITELATDEG] & "º " & [SITELATMIN] & "' " & [SITELATSEC] & """ "

I need to be able to display [SITELATSEC] with a comma instead of a decimal point. I tried

LAT: "Latitude: " & [SITELATDEG] & "º " & [SITELATMIN] & "' " & =Replace(Format([SITELATSEC]00.0"),".",",") & """ "

and got an error message about too many commas.

On a more general note: Has anyone ever actually finished a database - ie got to the point where no changes needed to be made? 18 months after this started, and I'm still tryng to accomodate what my manager wants!

View 3 Replies View Related

Queries :: Trim And Replace Function?

May 31, 2014

I need to remove 1 final - from my string. Here is my sql.

Quote:

SELECT tblmaintenance.sernu, Right([sernu],10) AS m10digit, InStr([m10digit],"-") AS [Space]
FROM tblmaintenance;

View 3 Replies View Related

Query For Find And Replace Function?

May 16, 2012

How do you use a query for the find and replace function???

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved