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.
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 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 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 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 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'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" ?
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],''))))
So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies View RelatedI have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total
-----------------------
2 | 1234 | 80.00
------------------------
1 | 43526 | 20.00
------------------------
> | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer)
Dim SPSelect As String
SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE"
SPSelect = SPSelect & " ItemID = '" & ItemID
SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
=SUM(CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit]))
#Error
We have a date function that converts a text date format. Can someone help me with time function to do the same thing? We want military time. The field is like this now: txt fields.
160037
213137
224356
235716
235800
12341
21708
22732
Here is the date function we use:
Function f2Date(strDateOld As String)
Dim strDate As String, strMonth As String, strYear As String
strMonth = Mid(strDateOld, 5, 2)
strDate = Right(strDateOld, 2)
strYear = Left(strDateOld, 4)
f2Date = strMonth & "-" & strDate & "-" & strYear
f2Date = CDate(f2Date)
f2Date = Format(f2Date, "mmmm d yyyy")
End Function
Hi all,
I need a little help. In my DB, I have a command button set up (I was tired of typing in dates) for date, but I used the Now function, which also gives me the time.
Now I have over 3000 subrecords of the main ones. I now need to queries transaction for that specific date, but it also retrieves the time.
I tried to go back and change the NOW to DATE in VB, but the code does not run.
How do I change all records that have date and time (using NOW function) and only click that command button to show only the date (mm/dd/yyyy)?
Thanks in advance.
Is there any function that can convert either a True, False, "Yes", "No" or Null value.... to a yes/no (check box) data type?
What I want to do is create a make table query. I would like to add a checkbox field from the query. So if there was a function that could convert a data type to a Yes/No checkbox, this would be very helpful...
To give you and idea of what I'm talking about...and how I want to use this... If I am making a table from a query and I want to add a number field, but leave it empty for the time being, what I can do is make a new field in the query maybe call it "Number1". What I would do is Add this as a field in my make table query:
Number1: Cint("")
This will add a blank field that is formatted as a number field when I run the make table query.
So using the same logic, I'd like to make a blank (or unchecked) Yes/No check box field when I run my make table query.
Anyway...that's what I'm hoping for.... I hope someone can help!
Gary
I'm having a problem getting a form to open. I'm using access 97 on on XP machine and whan I open my database I get Function isn't available in expressions in query expression '[Case Types]![theName]&"-"&Format([SourceDocs]![CaseNumber]&"","00")&"-"&Format([SourceDocs]![CaseYear]&"","00")'.
This database works fine on a Windows 2000 machine running Access 97 but none of the XP machines will open it.
Any suggestions?
Thanks,
Rick