Modules & VBA :: Function Not Available In Expression Error
Sep 10, 2013
I have got a problem on one of our computers.
We use Microsoft Access databases to enter test data and eventually generate reports.
On 1 computers i get the following error when opening the report:
"Function is not available in expressions in query expression 'Left(.....)"
I figured this is a reference problem so the next thing i did was to pinpoint what reference was causing this error.
It turns out it is the Microsoft DAO 3.6 Object Library. Simply removing the reference and adding it again fixes it and allows you to open reports just fine.
The problem is though, this message comes back every now and then. Which is getting annoying and the person who is making the reports is about to throw his computer out of the window.
View Replies
ADVERTISEMENT
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
Apr 13, 2015
I have the following code with dcount
If DCount("Username", "[tbl_userinformation]", "[Username] = " & Me![Text146] & " AND [actualdate]=" & Me![Text148] & " ") > 0 Then
but i have the following error ...
runtime erro 3075: syntax error in number in query expression '[username]=f15691b and [actualdate]=13.04.2015'
f15691b and 13.04.2015 are the values those i entered.
View 13 Replies
View Related
Aug 11, 2014
I am getting Run-time error '3464': Data type mismatch in criteria expression when i execute the code:
Option Compare Database
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Public verificacoes As Boolean
[code]....
View 5 Replies
View Related
Jul 2, 2013
I'm trying to delete a file in a network location. Sometimes the file is there, sometimes it's not, so I'm using the following code:
Code:
strPath = "etwork driveMy Folder"
strFilename = "MyFile.doc"
If Not Dir(strPath & strFilename) = "" Then Kill strPath & strFilename
Occasionally, I get a runtime 52 error when the file doesn't exist. I don't understand why I'm getting this when, if the file doesn't exist, the Dir function should return "".I searched the forums for Runtime 52, and didn't find anything pertaining to this.
View 11 Replies
View Related
Sep 18, 2014
I have been struggling with getting the syntax right for the ConcatRelate function. I have looked at other peoples examples and mine seems to have exactly the same syntax but it is giving me an error.
My Sql is
SELECT qr_RiverGroup.River, ConcatRelated("Site_ID","qr_RiverGroup","[River] = '" & [River] & "'") AS Expr1
FROM qr_RiverGroup;
View 7 Replies
View Related
Dec 3, 2013
I ahve declared custCount as an integer and id as a string that comes from the user form (id = me.CustID) and trying to count the number of matching records in the recordset (rst) using the following:
custCount = DCount("[customerId]", rst, "[customerId] like '" & id & "'")
I'm getting a type mismatch error on the DCount statement?
View 3 Replies
View Related
Jun 10, 2014
I haven't created classes in a while, and I don't see what is the problem
I'm modifying a function to get fractions from double. It worked well, but it returned a string.
I need it to be in parts, so I created this class
Code:
Option Compare Database
Public Entier As Integer
Public nominateur As Integer
Public denominateur As Integer
Public Function getText()
getText = Entier & " " & nominateur & "/" & denominateur
End Function
I changed the function type and the assignation:
Code:
Function GetFraction(ByVal Num As Double) As nombreEnFraction
Set GetFraction = New nombreEnFraction
If Num = 0# Then
GetFraction = "None"
Else
Dim WholeNumber As Integer
Dim DecimalNumber As Double
[Code] ....
For some reasons, when I get to the end of the function, I get a 91 error, like if it was nothing
But when I use a spy, I see values in the object until the end.
View 1 Replies
View Related
Oct 17, 2013
Anyway I've got a type mismatch that I can't figure out.
Code:
dMaxLstReq = DMax("reqNumb", "FlightLog", "Month([txtDate])='" & frmMonth & "'" And "Year([txtDate])='" & frmYear & "'")
In plain english: Select the highest value in the field named reqNumb from FlightLog where the Month of txtDate is equal to the variable frmMonth and the year of txtDate is equal to the variable frmYear.
txtDate is a Date/Time field in the table FlightLog
frmMonth and frmYear are both integer variables that take the system time (sysTime) and determine the month and year: I.E.
Code:
frmMonth = Month(sysTime) & frmYear = Year(sysTime)
Am I correct in thinking that the fact that my variables are integers and not times, that this is the cause of the mismatch?
View 2 Replies
View Related
Nov 18, 2014
Code:
Private Sub Form_Open(Cancel As Integer)
cboDateStart.Value = Format(Now() - 7, "short date")
...
End Sub
When I run procedure the Format is highlighted and popup error message:
Compile error: Can't find project or library
View 1 Replies
View Related
Nov 18, 2013
I am struggling trying to execute a function inside a Form_current event to display some stats.
The Function is this:
Code:
Function FlightsByAircraft(Aircraft As Long) As Long
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim str As String
str = "SELECT * FROM tblFlights WHERE AircraftID = " & Aircraft
[Code] ....
The code for the Form_Current event is this:
Private Sub Form_Current()
txtStats1 = FlightsByAircraft(Me.AircraftID)
Very simple. Well, the problem is when I move to a new record, a error message comes up: "Run-time error '94' - Invalid use of Null". It is because the AircraftID is not populated at that time. I tried to insert in the function code something like that:
Code:
If IsNull(Aircraft) then
exit function
else
.... (the DAO.Recordset code)
but it doesn't work.
View 8 Replies
View Related
Jan 30, 2014
I 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
View 2 Replies
View Related
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
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
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
Oct 9, 2004
Access 2002
I am building a form. In the form I have created a series of fields that successfully calculate the profit of a job. From [Profit] I am trying to configure a commission schedule which would be simple for me if the commissions were a set percentage. However there is a minimum involved and I am trying to use the IIf or Switch functions to distinguish between the values. I cannot seem to get it.
I am looking for the expression that would do this:
If the [Profit] is greater than 150, my field should calculate the amount x30% (.30). If less than 150 my field should show a value of $50.00.
I have tried many different variables in trying to write the expression but to no avail. Any advise/help would be greatly appreciated.
View 1 Replies
View Related
Aug 12, 2015
I keeping getting the error "Invalid use of Null" when i call my email function using optional parameters.
Code:
Email Me.ProjectID.Column(1), _
Me.ProjectAddress, _
Me.TaskDescID.Column(1), _
Me.TaskDescExt, _
strStatus, _
Me.TaskStatusID.Column(1), _
[Code] .....
View 4 Replies
View Related
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
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
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 12 Replies
View Related
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
May 23, 2006
I have a database functioning pretty well.
All I did was move the files from one computer to another via USB stick and now I get this alarm when I try to run this particular macro.
It is a macro to set a value to specific field.
The Macro uses a condition [Forms]![tblCustomerCall]![Alarm Number]="000".
If the condition is met then it is supposed to set a value to a field in the same form.
[Forms]![tblCustomerCall]![Alarm Descrption] is the item to set.
Left("TV Parity Alarm",50) is the expression.
So if 000 is entered in then the text TV Parity Alarm is filled into the Alarm description field.
It was working perfect until I moved the files to another computer.
I have Office SP2 installed and have updated office with everything available.
Please help.
Thanks
View 10 Replies
View Related
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
Jul 13, 2012
I want an example of choose function to write in expression builder in access 2007 ..
I have a size as 1/4,2/3,4/9 etc to be converted to ABC,DEF,GHI etc . i have nearly 40 entries so i think choose statement would do it .. moreover i cant find switch statement in expression builder .......
For example one could be to replace the text ..... or any other method to change the size to alphabetical grade.
View 4 Replies
View Related
Nov 8, 2012
I'm relavtively new to databases but after a steep learning curve (being left with many databases created by my old manager with no instructions on use) I'm getting there.What I am trying to do is pull out the date from records in a field called "Model_ID". The Model_ID field contains this kind of information:
Endscopy20120726JSmith
GISurgery20120521JDoe
I want to bring back "20120726" or "20120521". In excel I can do this with the mid and find functions but find doesn't work in access. I've tried combining the mid function with instr function but it comes back as too complex!
View 10 Replies
View Related
Oct 10, 2013
I have a query that will be assisting me find pricing based upon quantity ranges of specific equipment for a given FY.
I'm no access expert, and I keep getting "You tried to execute a query that does not include the specified expression...as part of an aggregate function".
I have tried several things, but cannot seem to figure this one out.
The SQL of my query is as follows:
SELECT IIf(Nz(Sum([Current Orders]![Quantity]))+Nz([forms]![04c Test Query for ROM Support]![Quantity])
Between 1 And 4,[04b Pricing Products]![01-04],IIf(Nz(Sum([Current Orders]![Quantity]))+
Nz([forms]![04c Test Query for ROM Support]![Quantity])
Between 5 And 10,[04b Pricing Products]![05-10],
IIf(Nz(Sum([Current Orders]![Quantity]))+Nz([forms]![04c Test Query for ROM Support]![Quantity])
[Code] .....
View 14 Replies
View Related
