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" ?
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 RelatedI 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 ?
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 am working on a query and currently I am not getting an error, but I am getting a parameter undefined pop-up when I try to run the query. The parameter it doesn't understand is really a field value that I am trying to interrogate.
NewKeyShip is one of 5 choices for the Key Order Type field. I am trying to have the system input a field value in SW Key when NewKeyShip is the selected value. I should mention that the rest of the code works perfectly starting with "If Order Form Details, part Number is like "H40." and through the end. It is only the first part that is not working well.
[code]
Sw key: IIf([tbl_OrderForm].[KeyOrderType]=[NEWKEYSHIP],[tbl_OrderForm].[NewKeyNumber],IIf([tbl_OrderFormDetailsNew].[PartNumber] Like "H40*",IIf([tbl_OrderForm].[AlohaKey]>0 And [tbl_OrderForm].[NewKeyNumber] Is Null,[tbl_OrderForm].[AlohaKey],IIf([tbl_OrderForm].[NewKeyNumber]>0,[tbl_OrderForm].[NewKeyNumber],''))))
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!!!!!!!!
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
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
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 have a query which includes a deposit field and a Transaction_Date field. I would like a cumulative deposit on each record (arranged according to date). The following is the expression I am using:
=DSum("[Deposit]","[myQuery]","[Transaction_Date]<=#" & [Transaction_Date] &"#")
I am getting completely nonsense values from the expression - some of the fields are blank (the first few fields) and then the values bear no relation to the deposits preceding them. I have tried all sorts of formats including using Format([Date], 'dd/mm/yyyy') on either side and then on both sides but with no effect.
I am having a query showing customer as client, paid amount as pamount, billingdate, payment date as pdate.
I want to get dsum of pamount specific for each client with date criteria for example if i open query through form mentioning start and end date then the sum must vary as per the date given. the date is "billingdate" as mentioned above.
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 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
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 RelatedDoes anyone know of a function that will change the date format from yyyymmdd to mmddyyyy? I have a linked table to a data repository, I'm using a date field in a query and would like to use the dateadd function to return a certain date range from 7 days prior. I can't get it to run, I thought this might be due to the date format.
here is what i have in the date field as the criteria: <DateAdd("d",-7,Now())
the date field is formated to yyyymmdd
Any suggestions?
I need to do a DSum with criteria that picks up a previous month based on todays date, I have got the following but I don't know how to format the last part of the criteria.
If I take out the Month(DateAdd("m",-13,(Date()))) and replace it with 6 it works fine.
Code:
DSum("[invquan]","[qrySalesByStockCode]","[stcode] = '" & [stockcode] & "' and [Month] = Month(DateAdd("m",-13,(Date())))'")
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:
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 have a query that I'm working on through Access 2010's design view. I'd like to add a criteria to the query where it only shows results with the employee name column matching a global variable I created that stores the name of the currently logged in employee.
Here's my vba code that declares the global variable and the public function i'm trying to pass as criteria in the query:
Global gbl_loginName As String
Public Function returnName() As String
If IsNull(gbl_loginName) Then
returnName = "test" ' dummy account created for development only
Else
returnName = gbl_loginName
End If
End Function
and here's the SQL code from Access's design view:
SELECT [Entry of Hours].WC, [Entry of Hours].[Employee Name],
[Entry of Hours].[Set Up Time], [Entry of Hours].[Run time],
[Entry of Hours].[Traveler Number], [Entry of Hours].[Entry Date],
[Entry of Hours].[Quantity Finished], [Entry of Hours].Notes, [Entry of Hours].WPS,
[Code] ....
when I try running the query, however, I get this error:
'returnName' is not a recognized built-in function name
Is there a problem with using public functions in Access' design view?
I have a report based on a crosstab query with a subreport based on a second crosstab query both the crosstab queries have the same 3 parameters (2 as string, 1 as boolean) which I pass through a dialog form that closes after the report is opened (in preview).looks like everything works fine at least for the first page of the report but when I try change page, it prompts me to re-enter the parameters. Of course, if a do not close the parameters form I will not have this problem but that is a popup dialog which I do not want to close manually after the operation is done
So I tried to pass the form input first to a Public variable then to a Public Function but I was not able to reference neither of them in the PARAMETER statement of the 2 Xtab queries..As a function, each of the following declarations prompts for input at runtime::
MyFunction, [MyFunction], [MyFunction()], [=MyFunction()]
while the following are not accepted (syntax error)
MyFunction(), =MyFunction()