How Do I Perform A "not Like '*xxx*' Scenario
Apr 20, 2006I know how to do a "Like '*xyz*' " type condition in a query. How do I set up a "Not like '*xyz*' " type condition in a query?
Thanks for your help. This forum is wonderful!
ADVERTISEMENT
Scenario-help (posted In Macros Too)
Aug 10, 2006Hope I am posting my question in the correct place. First of all I am entry level user in access, but knowledgeable in excel. As I am working with data containing over 65,000 rows I decided to use access however I don’t know how create a function that will work give the same result as (vlookup) in excel. Scenario case: first column “Serial Number” contain a list of serial numbers, 2nd column will be fed manually or by a bar-scan, the third column should show the value of “1” if the value in the 2nd column exists in the first column else a value of “0” should appear
The validation formula in excel looks like: =IF(ISERROR(VLOOKUP(B2,$A$2:$A$65000,1,FALSE))=TRU E,0,1)
Serial Number Confirmed Serial Validation
141614661
141614662
141614663
141614664
141614665
141614666
Thanks in advance,
Tarek
tarek_habal@yahoo.com
Another Update Criteria Scenario
Jan 28, 2008All -
I have another update query that is basically looking at all of the Enrollment ID's that are associated with a ClassID. If each EnrollmendID has a Graduation date and the Graduated check = yes, then I want to place the current date in the Closed Date field for the Class.
I can get a select Query that shows all of the classes and all of the EnrollmentID's but I'm not sure how to tell the query, "For Each ClassID, If All EnrollmentID's have a Graduated Date and Graduated = Yes, Then Class Closed Date = Date()"
Reports :: Results For A Particular Scenario Not Shown
Aug 29, 2014In the test db included the report works fine if every Rep (representative or User/Admin) has records assigned. But, for example, if I go to the table and re-assign the first record to another user/admin (pick list in the table field [Rep]), then `User01’ will have no record assigned at all. Then an error message appears: The Microsoft Office Access database engine does not recognize 'User01' as a valid field name or expression. And no report is produced.
I wanted to try solving it with the info on thread searched by: "cross tab query reports", (13th result) but to be honest I really don’t know how to implement.
Client & Server Scenario Access Databases
Oct 20, 2006This post is really aimed at someone that has experience of network databases and the problems they can pose but if you can help at all that would be great!
I work for an insurance company and we have created a database for registering complaints on. The one we currently use is a single .mdb file which can be shared by up to 60 people at any one time. This is creating a lot of problems when the queries and some VBA code are run. I therefore made a server file which is just an mdb file with data tables in and NO forms or queries. This file is stored on a shared drive on the network server. We then created a client file which contains all of the forms and queries and code. This file contains linked tables to the server so when data is entered into the form, it "Sends" it to the server mdb file. The client is installed on each of the local machines c:. The problem with this was that if we made a change to the database, we would need to reinstall the client on every single user pc. This would of took ages. I therefore made an auto-update function that checked the version number on the server and if the server number was greater than the client number, a simple .bat fiile was run which copied the updated client file from the network to the relevant users local disk.
The problem we have now is that our IT department are concered that if we make a change to the client and all the staff log in at 9am for example, it will start doing multiple copying of a file around 8mb in size to around 60 machines. They are only running on a 2meg pipe so this could cause some problems. We are not looking to change the client & server idea but does anyone know if this will have a big impact and infact if the updates for 60 machine is the equivalent or lesser of 60 people sharing the single file i mentioned earlier. If the IT dept are happy with 60 users accessing the same file at the same time, which they currently are, why are they unhappy with it updating these machine using the new method.
Would really apopreciate any thoughts anyone has..
Thanks a lot.
Gaz
Forms :: ADO Bound Recordsets In A Form / Subform Scenario
May 22, 2013I have a form and subform. The main form shows some customer details, and the continuous sub form shows that customer's charity donations.The code below runs when the form opens, and binds ADO recordsets to the two forms. The binding appears to be successful.
However whatever I do I can't make the subform update correctly to show the relevant customer donations. For example, when I use the **'d lines to update the link child/master fields, I get a "Data Provider Could Not Be Initialized" error.
Code:
Private Sub Form_Load()
Dim cn As New ADODB.Connection
Dim rsCust As New ADODB.Recordset
Dim rsDons As New ADODB.Recordset
[code]...
How Do Perform Lookup
Mar 10, 2006Hi i really need help!
i need to be able to use "lookup", i have a set of postcodes from 3000 customers, i need to extract these and compare them to lists i have of postcodes, which make up a region. And run queries from these
e.g a customer on my database has the postcode BD21 7KK
On my list of postcodes i have on paper - WEST YORKSHIRE - BD21 7
therefore The BD21 7KK Customer falls in the West Yorkshire region.
I need to do this four 4 regions with lists i have on paper then run queries.
Can some one please provide the neccessary steps?
Or even give me an example made?
Im really stuck on this at the moment and me job depends on it ! :(
Thanks
Field To Perform This Calculation
Feb 23, 2007How do I get a field to calculate this formula?
I want a script to find the precise current date and from another field find a number and add it to the current date day to make current date + number = date in the future.
For example
Field: Total Nights Hired = 2
Field: Date due back = 23/02/07 + 2 = 25/02/07
I am sorry if I did not explain this well enough. I am totally new to databases but need help in my corsework. I am designing a video rental system.
Any help will be greatly appreciated.
Andy
Perform Function Before Next Record
Feb 27, 2006Hi,
Is there a way i can perfrom some functions before or after the next record in the recordset is loaded. (form is set to continuous)
For instance:
If i have 5 records in my forms record source after the 1st it will msgbox "sumin" after the second msgbox "sumin" and so on?
This is the record source in the properties of the form by the way not a recordset within vba?
Thanks
k0r54
Help Needed To Perform Calulation
Nov 3, 2004Hello Everyone, (where would i be without ya)
Here is what i would like to do if anyone can help
I have a table with the following columns which are currency values, along with aload of other stuff
High
Med
Low
These have been put into a form as bound text boxes, i would like to allow the user to
chose their rate, say by a button or maybe option group "i'm open to ideas"
Dependent on the choice made the rate chosen will then go into a independent text box.
After there i'm fine, the reason for this is,.. the chosen rate/independent text box will form part of a calculation
Best Regards AM
Not Enough Memory To Perform Operation
Apr 10, 2014I'm getting an error when trying to run a report. "There isn't enough memory to perform this operation. Close unneeded programs and try the operation again."
1.) I tried to install both the V-Tools and Rick Fisher add-ins, and neither one could be added. I got errors from both.
2.) I'm working with a small test database to create a report. Db is tiny, 1.8Mb. But I've also created a report which has several embedded subreports. Everything is based off of queries which certainly have to sort through some data.
3.) I have compacted and repaired.
Perform Lookups From Access Tables
Jan 27, 2006Can someone please tell me if there is a way to perform lookups in Excel from an Access table without first exporting the data into Excel? I have a form that is being exported into Excel from a pricing tool our company uses from an outside vendor. It is important the users have the ability to perform complicated calculations and are not at all experienced in Access. There are several data sources that are well over the 65,000+ rows limited by Excel. I need to either prove or disprove whether it is possible to perform lookups between Office applications. If it is possible to lookup between applications, I would appreciate any sources known to learn how to do this. If not possible, I would like to be able to confirm that fact to my manager.
Thanks for any help that can be offered.
tschultz
How Do Perform An Update Query From One Table To Another?
Apr 4, 2006Hi there.
Does anyone know how to use an update query to copy some records from one table into another?
I have table1 which has all the contact details and what I would like to do is work out a process for importing records from an excel spreadsheet.
What I have so far is as follows:
1. In access go into the queries tab and select update query
2. Open both table1 and table2
3. Drag the fields to be updated (table1)
4. In Update to select [table2].[table2 column name]
When i run this nothing appears to happen. 0 records are appended.
Does anyone now how to go around this with another solution or should I be using other SQL methods?
All comments much appreciated
Thanks
Macro - To Perform Function Outside Of Access
Dec 20, 2004Is it possible to create a macro in Access that opens up a word document from a shared drive? Within Access, I don't see a macro available to do this.
View 1 Replies View RelatedAcc2007 W/ 2003 DB - There Is Not Enough Memory To Perform This Operation. Please...
Mar 3, 2008Environment: Front-end Access application developed in 2003, with back-end database in 2003. Being deployed in a mixed 2003/2007 environment. Users with Office 2007 will have to open and use the 2003 Access application without converting it to Access 2007.
Problem: When I take the Access 2003 application and open it with 2007, some forms give the following error message (whether selecting Open, Layout View, or Design View):
"There isn't enough memory to preform this operation, Please close unneeded programs and try the operation again."
The real problem is some of the most complex forms open in design view, and some of the simplest do not. There appears to be no ryhme or reason to it. Some of the forms are bound and some aren't. Some of the bound forms are bound at run-time with embedded SQL, some aren't. All query objects seems to be fine, when they are used on bound forms. Some forms have significant amounts of code behind them, some do not.
All report objects execute fine with no problems opening any view.
I can open the same file in Access 2003 and everything is fine. I've compacted and repaired the DB in Access 2003, no help when I go back to 2007.
Wondering if some references are off? But I have some really simple forms that can't be opened. I find nothing on MSDN on this.
Perform An Action On Each Record In A Table In Turn
May 30, 2006OK, usually I can figure these things out for myself, but I'm realy stuck on this one and any help would be appreciated
background: I work in a small scale pilot production line, and we take a series of measurements for every part that we make, each of which has a unique ID. Sometimes we need to take the measurements more than once, so that the data output from the measurement system (which it can append straight to an access table) has a part ID and a measurement number, the combination of which is unique (for reasons that I won't go into it is not possible to combine them into a single field, otherwise we would do this)
problem: The measurement system can be temperamental and sometimes sends the same record more than once, and It is horribly time consuming to go through and check manually
It occured to me that we could send the measurements to table1 (where they would be stored temporarily), and write a sub to go through each record in table1 in turn, compare the part ID and measurement number to the records stored in table2 (where they would be stored permanently), and append the record to table2 only if the part ID and measurement number combo has not already been taken, before moving onto the next record in table1 and doing the same thing
The part I just can't figure out is how to go through the records in turn and compare them, I can do the append and deletion using SQL, that's not a problem.
if anyone can point in the right direction I'd be realy grateful (or tell me if I'm barking up the wrong tree completely)
regards
Bogzla
Command Button To Perform Popup Action
Aug 29, 2005Hello,
I am having trouble making a command button do what I want. I have a subform within my form, but I only want it to appear when I click the button. I tried using the wizard but there is no option to open subform, only open form. Any thoughts would be greatly appreciated!
Is There A Way Perform CTRL+' With Vba Code In MsAccess 2000
May 28, 2006Is there a way perform CTRL+' with vba code in MsAccess 2000
Carbo
CTRL+' copies data from a field in the previous record to that field in the current record.
Resolution:
http://support.microsoft.com/?id=210236
If you receive a compile error related to DAO Recordset go to:
http://www.mvps.org/access/bugs/bugs0031.htm
Forms :: How To Perform Search From Navigation Form
Mar 1, 2015I have my setup all complete and everything works great. Now, I have a subNavigation form within the Navigation form. Thing is, I am not sure how to go about the code for the query to pick up the Beginning and End Date to generate my report. Here is my Form setup:
frmNavigationForm (Main form) - Tabs on top.
NavigationSubform (first Navigation Form, where I have a tab called Admin)
frmAdminNav (where I have embedded under the Admin tab) - Tabs on left side.
NavigationSubform (Second Navigation form where I have a tab called "Search") I don't recall what the name. How do I find it, stupid question, I know...
frmSearch (where I am trying to pull a detailed report from date search criteria)
Here is one of the codes I have been trying to use in my Query and nothing seems to work.
Code:
Between [Forms]![frmNavigationForm]![NavigationSubform].[Form]![frmAdminNav].[Form]![frmSearch]![BeginDate] And [Forms]![frmNavigationForm]![NavigationSubform].[Form]![frmAdminNav].[Form]![frmSearch]![EndDate]
Forms :: Functional Keys To Perform Commands?
May 2, 2014tell me that can we use functional keys F1, F2,F3,,,,,,,,,,,,,,F12 in ms access to perform commands like saving a form data , for closing form refreshing form, clear form.Or is there any way to make shortcut keys combination like[(ctrl+s)(ctrl+c) (ctrl+A)] in access to perform action like saving,closing,clearing, current form.
View 1 Replies View RelatedEdit A Query In SQL View To Perform Multiple Actions
Aug 11, 2005Hi,
How can I edit a query in SQL view to run multiple lines of SQL?
For example I can use the GUI to create a query, then in SQL view enter the following:
DELETE
FROM Employees_copy;
which clears the Employees_copy table. Perfect. But what if I want to do this for mulitple tables in the same query eg for a second table Employees_copy_2 as well? I've tried
DELETE
FROM Employees_copy
DELETE
FROM Employees_copy_2;
which gives a syntax error, as does
DELETE
FROM Employees_copy,
DELETE
FROM Employees_copy_2;
any suggestions?
Queries :: Get A Query To Perform A Calculation And Round The Results?
Feb 20, 2015I'm trying to get a query to perform a calculation and round the results.
The fields that I am running the calculation and am trying to round are Data Type Number, properties Field size Single and decimal places Auto.
I have tried the built in function described in Allen Browne's site to no avail.
[URL]
I have tried rounding the individual fields and then adding result.
I have also tried rounding the result (as attached).
Forms :: Textbox To Perform Dlookup If Checkbox Is Checked?
Sep 9, 2013I have a text box on a form, currently it performs a dlookup for me. I want to change it so that it only performs the Dlookup if a checkbox is checked, but otherwise will display the text that's in an unbound text box.
Not 100% this is even possible, but hoping so. So far I have in the textbook:
=IIf([CHECKBOX]=0,DLookUp("TABLE","FIELD","ID = " & [Text57] & ""),[TEXTBOX])
The dlookup works fine by itself, it's just making it conditional that's causing me problems. I know it could just be a small problem with the way I've written it, or it could be a bigger thing with me not understanding the way IIF works.
Forms :: Perform Calculations After Text Boxes Updated
Mar 14, 2013I have a subform based on a query. That query contains a couple of calculations that end up in textboxes on the subform. The problem is that the calculations require me to enter 2 dates that are on the subform. If these are not entered before the calculation is performed I get a "Cannot update record" error message and then #error in both Lease cost and Discount Cost textboxes.
View 6 Replies View RelatedThere Isn't Enought Memory To Perform This Operation. Close Uneeded Programs...
Nov 22, 2007Hi
Has anyone ever got this error message before? I’ve never came across it and I don’t know how to fix it. The error message displayed when I added three extra labels to a form then tried running it. At the beginning, it took more time than usual to think about it then the error message was displayed. Now I can’t open my form in design view nor can I run it! It’s totally inaccessible.
The error msg is attached.
Any help will be very much appreciated,
B
Modules & VBA :: Word Document Load When User Click Button And Perform Email Merge
Oct 7, 2013I have a Word 2010 document linked to an Access 2010 data source. When a user clicks a button in Access, the Word document loads and performs a email merge using the below VBA code:
Private Sub Document_Open()
With ActiveDocument.MailMerge
.Destination = wdSendToEmail
.SuppressBlankLines = True
[Code] ....
However, as the .mailsubject part is not in the loop it is only retrieving the first Return Code. I have tried to integrate in the loop to no avail. Also, how do I add static text to the Subject, I need something like "Your Return Code" + "Return Code"..