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!
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.
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
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.
I am using VBA for MS Access 2003. I left this project since 4 years and I know back to it as mass needs forced me to use it again.
I use trim function. it raises the compile error: can't find project or library. I know that the solution is by adding the library in the References item from the Tools menu. Thus What are the minimum libraries that I should add so that these basic problems solved. By the way: I already added the Microsoft Visual Basic for Applications Extensibility 5.3.
I need to clean up data from text file which is huge.
I wonder if Access Expression Builder can mirroring "IF function" from Excel Here is what i am trying to do The data consist of multiple customer and multiple date. But the layout only specified customer ID once eg.
CustID: aaaa Date 01012013 02012013 03012013 CustID: bbbb 01012013 02012013
When exported the file I used Fixed Width command to separate Date Column and Cust ID column.
Below is the result that I am looking for CustID: aaaa : aaaa Date : aaaa 01012013 : aaaa 02012013 : aaaa 03012013 : aaaa CustID: bbbb : bbbb 01012013 : bbbb 02012013 : bbbb
1. Column A is Date
2. Column B is Customer ID
because Customer ID in column B only appear once, I need to create another column to populate that Customer ID whenever the transaction related with that Customer
3. Column C is the column where i tried to populate Customer ID to each date related to that Customer.
If I worked in excel the formula will be --> IF(AND(C5="",B6=""),"",IF(AND(C5="",B6<>""),B6,IF( B6<>"",B6,C5)))but in access i am stucked.
I have a continuous subform (BooksFullSubCC) that is located on a tab in the main form (BooksFull). In BooksFullSubCC, there is a text box in the footer that sums up all the amounts of credit card charges to make a total with =Sum([Amount]). For some reason, I cannot pass the value from the text box to VBA or another text box on the main form because Access "cannot find the form".Here's the code
Code: Private Sub TabCtl93_Change() Dim Amount As Currency Amount = Nz(CCur(Forms!BooksFullSubCC!CCchargesTotal)) Me!CCFees = Amount * 0.015 End Sub
Its really simple - Access should take the amount from the text box (CCchargesTotal) on the subform and then put it the result of some simple math into a text box on the other form.When I try and change tabs to initiate the code, i get "Run-time error '2450':Microsoft Access cannot find the referenced form 'BooksFullSubCC'."
I've checked the spelling, rewritten the code, everything. I even tried to make a text box on the main form with control source =[Forms]![Books FullSub CC]![CCchargesTotal] but that didnt work either...
I'm trying to find a complete Bible (any free version) in Access format.I found a few links but they point to a download from Simtel,which just shut down about 2 weeks ago.
I am trying to find an average of four fields in either a form or query. Basically I have figures for [Grade 1], [Grade 2] but [Grade 3] is an empty field. I need to include all three field because there are sometimes 3 grades, but a majority are 2 grades. How to do so?
I am working on recreating an excel file in access. My excel files uses the Vlook up function to get data from a table for calculations.I am calculating Fruit Solids.
(BRIX VALUE references a cell, and quantity references a cell just used the names instead of cell references).The Brix table has 751 entries. The headers for the following tables are "Brix, Gravity, Weight, GFL, LBS, FS/Gal".So the look up table takes the BRIX value, finds it in the first column then takes the value in the 6th column(FS/Gal) then multiples that by the Quantity inserted in the other table. I didnt create the Vlook up access form so I am trying to get an understanding and recreate it in access.
In access i made a table called BRIX to store the Brix table information like the one in excel.Then i have fields in a different table where you enter the quantity, and brix value of the product. I now need to make the Vlook Up function in excel work for my access data.
I created a Public Function called fNetWorkDays in my access database, however, when I try to use it in an expression I get "Undefined function 'fNetWorkDays' in expression". The Public Function is in a standard Module in my vba project. Why I cannot call it in my queries?
I've got a Table with a string of data delimited by an "*". The first part of the string before the "*" can be variable in length, so if I use a LEFT expression to break apart the 2 parts of the string on each side of the "*", my results are incorrect. In MS Excel, I can use the following formula to solve the problem:
=LEFT(B6,(FIND("*",B6)))
You are just substituting the number of positions with a Find command that looks for the "*", and returns anything left of the "*". Unfortunately, the brilliant programmers at Microsoft, once again, don't have an Excel function in Access. how to recreate that functionality in a table in Access?
I am trying to run a query to print invoices that are 30, 60, and 90 days past due. What expression do I use to search for records that were purchased 30, 60, 90 days prior? I was able to find the expression to add time for the due date but was unable to find the expression to subtract time for my search. Thanks for any help!! Beck
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
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???
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.
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?
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;
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.
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.
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.
Is there a function similar to the find function in excel which will return the starting postion of a charcter in a string and allow you to set the position for seaching IE I want to know at what postion the second ":" starts at in the following string
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])
