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
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
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.
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
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
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
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
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 RelatedI 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:
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!!!!!!!!
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 RelatedHi,
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
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.
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?
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?
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 ?
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].....
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.
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" ?
Hi,
I have a DLookup expression that is working in a Control on a form :
=DLookUp("[Class] ","Class","([Forms]![Orders_Crosstab]![Indcat] = [Clind]) AND ([Forms]![Orders_Crosstab]![2005] Between [From] AND [To])")
I simplified it for test purposes as an expression in a query:
Expr1: DLookUp("[Class] ","Class","([Indcat] = [Clind])")
This gave an error - MS Access cant find the name [Indcat] in the expression.
After some R&D on the web I found a solution that works:
Expr1: DLookUp("[Class] ","Class","([Clind]=" & [Indcat])
[Indcat] and [2005] are part of the recordset where [Clind], [From] and [To] are values in the Table 'Class'
MY CHALLENGE:
I cannot find the correct way to add the extra selection criteria to the query expression (as per the form expression above) that checks the value [2005] is between the [From] and [To] values in the table. Have tried a number of combinations without success.
Any help, suggestions and/or guidance very welcome.
Regards
Tony Randell
I have two forms...frm1 has a text box with an expression in it and I need frm2 to display the result of the expression. I'm using the DLookup expression and it either gives me #Name? or #Error? message in the text box frm2.
=Dlookup("[loan#]","tbl_loan","[Days] =" & Forms![frm1]!Days) <that gives me #Name? message
I've never used DLookup before and I can't get it to work for me so far.
I have 1 table which contains products and different properties of each product, such as the weight of the product.
I have created a query which sums the weight of all products, but only for those that have a value >0 in a certain field. This all works fine.
Now I simply want to display that calculated total weight in a text box on a form. So I thought DLookup could be used for that. But I can't get it to work, maybe because I'm not putting in any criteria? In the control source of the text box
I've put the following:
=dlookup("[TotalWeight]","qryTotals")
I don't have any criteria, I just want the value from my qry expression. The textbox on my form now displays #name?
1. I have a table called "CONTRACT NAMES AND NUMBERS" with a field called "REDUCED_USERS", this field is a checkbox (Yes/No in the table). This table houses all of the customers with their id numbers and basic info.
2. I have another table called "REQUESTS" which houses their orders. This also has a field called "REDUCED_USERS".
In my form "Amendment Request Tracking" I have tried to do a DLookUp in Expression Builder to check the box, per order, if the customer has reduced users in the "CONTRACT NAMES AND NUMBERS".
I have tried many variations and have just realised that this is probably because it is a yes/no field so may struggle with what to populate with (currenly nothing!).
My most recent variation of expression is (where NAD_NUMBER is the common field in both Tables and Form with relevant relationship):
=DLookUp("[REDUCED_USERS]","[CONTRACT NAMES AND NUMBERS]","[CONTRACT NAMES AND NUMBERS]![NAD_NUMBER]=[NAD_NUMBER]")
I have a field created in a query expression
BirthMon: Format([DOB],"mmmm")
I would like to create another field in the query using DLookup to return the value of the BrithMon.
BrithMonthID=DLookUp("MonID","tblListMons","BirthM on = Mon")
This works good if used with an unbound text box on a form, but when entered into a query expressions, an error is returned: cannot find the name 'BirthMon'
Can I used DLookUp in a Query expression to refer to another Query created field?
I have an asset database I am designing to manage our computer inventory and assets.
I am trying to get a DLookup to work with one of my forms that will auto-populate some of the fields depending on what is entered in to the ProductID field. For instance, Make, Model, Asset type...
My problem is that the string that returns contains special characters, specifically "#" and gives me the error message -
Run-time error '3075':
Syntax error in date in query expression 'productID=EN371UA#ABA'.
My expression is definitely working, it just looks like it things it has something to do with date/time which it does not. Unfortunately, most HP equipment contains a # in the Product ID number.
Here is my expression -
Private Sub ProductIDCombo_AfterUpdate()
Make = DLookup("Make", "productlist", "productID=" & [ProductIDCombo])
End Sub
Make is the field I am looking up from the ProductList table. The Product ID is the ID I'm looking up from the ProductList table to find the make. My problem is actually getting it to return the correct value of "HP or Dell or Lenovo". etc.
Hi guys! Would appreciate any suggestions that people have for my problem. How do you perform calculations in queries between tables? Do you use the DLookup function to update the values in the query, if not how so? I've heard from somewhere it may be useful to use the DLookup function, but i'm not really sure what this is!
Please help!