I have a database which is used by operators to input manufacturing data i.e. LotNo, Customer, SalesOrderNo, PartNo, LineNo, ActualQty, Length, ScrapMat, (MatStdCost), StdLineSpeed, SetupTime, RunTime, LostTime and weightperpiece.
We then use this data to produce information like
Standard Run Time: (([ActualQty]*[Lenght])/[StdLineSpeed])/60
Machine Efficiency: ([Standard Run time]/[Runtime])
these work fine if values are inserted. The problem is that when a value is zero it returns a #error value in the field. This is probably caused by dividing by zero which can happen.
A test may occur where there is zero StdLineSpeed etc.
Is there any way I can bet access to return zero in these cases instead of the #error because I wish to do further calculations on totals and averages and I cannot if even one #error occurs.
I have a DB and I have a button that triggers the find and replace. this will search over 100K entries and filter a specific client. The clients record can be 100's of entries.
This all works fine, but its always ascending and I have to scroll to the bottom for the latest record.
I can't set it to show Descending permanently as some employees can't cope with change. so...
I have a checkbox on my form for either Ascending or descending results. Is there any way I can get the find / replace to reference it, Or can I re-sort them after the search.
I've tried putting re-sort code on the 'find' button as lostfocus and mouseup but it doesn't work.
I have a query where I make a calculation. In some cases this calculation results in a division by zero. The field will then show #Fout (#Error in english?). I want to replace this message with something else, like a dash for example.
Having set a table short text field to 'Indexed (No Duplicates)', I have a form which produces a '2105 runtime error' when the user attempts to submit a duplicate value. I would like to replace this default error message with a more user-friendly MsgBox.
My code for the SaveRecord button is:
Code:
Private Sub SaveRecord_Click() DoCmd.GoToRecord , , acNewRec MsgBox "Record successfully saved", vbOKOnly + vbInformation, "Record Saved" End Sub
My code to capture the 2105 runtime error is:
Code:
Private Sub Form_Error(DataErr As Integer, Response As Integer) If DataErr = 2105 Then MsgBox ("This villa booking has already been logged.") Response = 0 End If End Sub
Unfortunately when the save button is clicked (when attempting to save a duplicate value), the 2105 error still runs. What are I doing wrong?
Code: INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA ) SELECT Left([dbo_BACKUP_ACESSOS.LOGIN],255) AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA FROM dbo_BACKUP_ACESSOS WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>"ACTIVE DIRECTORY") AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));
But Iwant to be able to use a set of data to be used in the Replace Statement, so I create a table to add each string I would like to have replaced by "nothing", and trying to make the replace query to look there in order to find what to replace.I also created a table where I will list the systems that I dont want in the select, so I removed the "ACTIVE DIRECTORY" and replaced by the colum that have the list of system I dont want listed.This is the result:
Code: INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA ) SELECT Replace((Left([dbo_BACKUP_ACESSOS.LOGIN],255)),[PREFIXOS_E_SUFIXOS]![Valor],"") AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA FROM dbo_BACKUP_ACESSOS WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>[SISTEMAS_EXCLUIDOS]![Sistema]) AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));
The thin is that this keeps asking me to enter the parameter value for "PREFIXOS_E_SUFIXOS!Valor" and for "SISTEMAS_EXCLUIDOS!Sistema"
What I wan't to do seems very hard, and am wondering if anyone would be able to help out.
Here is what I am trying to do: First of all, so you somewhat understand my field headers and table, I work for an oil company. I have a form that is based off a table and some of the fields include "Date", "Well Name", "Stock", "Sales", and "Oil". In my table, data is entered for each well every day.
Here is an example of my table:
Date Well Name Stock Sales Oil 08/15/2005 Well1 140 44 25 08/16/2005 Well1 120 35 15 08/15/2005 Well2 150 49 22 08/16/2005 Well2 115 40 30
What I want to do is create a text box on my form and I want it to do a calculation to see if the data is correctly entered. Here is the formula that I want to use in this text box:
If ([Stock] - [StockFromPreviousDay] + [Sales]) = ([Oil]) { Print "OK" } Else { Print "Error" } I just can't figure out how to get the Stock from the previous day to work with. I have been stuck on this problem for about a week now, so any help would be gratefully accepted.
Having a problem with results on a form. Three unbound text boxes. One called "BillableDays" second called "DailyAmount" and third "PartialInvoice" Now "PartialInvoice" is a result of [BillableDays]*[DailyAmount] But........ The figures are not correct. For example.... [BillableDays] = 7 ... [DailyAmount] = $135.42 and the result in [PartialInvoice] is $947.91 ........... Any thoughts on why this is happening and how to correct??
I've encountered another stumbling block. My form is almost done except for some minor details. However, one calculation does not work even when I use the correct terms.
This calculates the tuition balance per customer based on data from 2 subforms on the main form: sbfTuitionAccount and sbfTuitionPayment.
My formula is =Nz(sbfTuitionAccount.Form![txttotaltuitionpayable])-Nz(sbfTuitionPayment.Form![txttotaltuitionpaymenttotal])
I've counterchecked all form and control names, however, when I test it, I only get a '#Error ' in form view. A similar formula has proven functional in another form. Do you have any ideas as to what the problem is?
The AfterUpdate for txtQuantity = Me!txtExtended_Price = Me!txtItem_Cost*Me!txtQuantity, which puts the extended price in the control.
In the FOOTER of the form, I have txtSub_Total. It's Control Source is: =SUM([txtExtended_Price]).
As far as I can tell from reading, when placed in the form footer and used in 'continuous form' layout, this ought to give me the total cost of ordered items.
Instead, I am getting #error. Can anyone help me with this?
I have a query that add up the numeric values in a value list assinged in a combo box in response to each question, the row source for question 15 for exmaple is as follows:
ROW SOURCE: 0;"I have no idea";5;"I indicated that I wouldn’t have time today";0;"Was mentioned early on and then not offered again";5;"The salesperson said the vehicle wasn’t available"
I then run a query that adds together the responses to 3 questions, including question 15, the field in the query appearing as follows:
LS5: ([q14]+[q15]+[q16])
It was working fine but has stopped working, the fault lies with q15, if I take it out it works again. So I looked at the table as I am sure it must be the way it is set up, why it worked before I don't know and I attach a screen shot of how the field is set up in the table, which is no different to q14 and q16.
Anyone got any ideas?
One last thing is that it makes no difference if the fileds contain a number (including zero) or are blank
I have form that uses a query as its datasource. In this query, there is a column for Body_Height. From this value for Body_Height, there is a calculation made inside the query, e.g. Body_Height / Body_Mass = Body_Calculation Then, there is a textbox on the form, which uses the calculated value Body_Calculation from the query. However when Body_Height is empty, the term "#Error" appears in the textbox. (Actually "#Fehler" - German for error - appears there as I am using the German version of Access.) I know it is an error, as the data for Body_Height is not yet available and has not been entered into the db yet.
My question: Is it possible to replace the term "#Error" with something user-defined, like "N/A" or "Not available" ? It will just help make the form look a bit better in the end I think.
Please help me as I am still a bit unsure how to do this. :confused:
This may be a really dumb question, but those are the easiest to answer right? ;)
Anyway, I have a parameter query where the user enters in certain criteria that it wants to look up. If there are no results that pertain to what a user enters in, is there a way that I can put in an error message that tells them that there are no results? Right now it just goes to a blank form/report/table (depending on what they are searching). I'd like something to pop up telling the user that there is no information cooresponding with what they've typed in.
I'm getting a Run-time error 2295: Unknown Message Recepients.
I've got a DB of about 2000 clients. In testing I did a test DB with 50 random clients. Using any search criteria, it would grab those clients and open a new email with their email addresses no problem. However when I do this same thing with my complete DB of 2000 clients, I get this run time error. I'm pretty confused here, and anxious. It looks like the program is working, I just need it to run with all my clients. This is the last thing I must conquer to be finished with this project.
I'm creating an item look-up form with 2 criteria that I wan't to auto-filter as the user types. I'm placing my code in the Change event of the textbox, and only calling the .text for the current textbox (.value or no property at all for the other). The filter works smoothly and as intended until it returns no results in my table, and then it pops me with the error. I've tried forcing focus to the textbox in different places throughout the code, but to no avail. If I use the .value or no property for the current textbox, I'm not getting the CURRENT text, but the old text before it was changed, so the filter doesn't work properly.
Private Sub txtVendor_Change() Me.FilterOn = True Me.Filter = _ "((strSearchVendor Like '*" & Me.txtVendor.Text & "*' AND strSearchItem LIKE '*" & Me.txtPartNum & "*'))" Me.txtVendor.SelStart = Len(Me.txtVendor.Text) End Sub
It usually pops the error on the last line "Len(Me.txtVendor.Text)", but I've had it do it on the Me.Filter line as well (only when debugging and messing with next statement placement).
Now when you select time frame, it works fine. However I also have "Closed Sale" and "Lost Sale" in that list as well and when chosen i get this "#Error" because its text.
How can I replace the #Error with a N/A for not applicable.
I tried:
=DateAdd("m",[Time Frame],Now()) & IsError("NA")
Better yet:
If "Closed Sale" is chosen is there a way to put that in the "Estimated Closing" area along with "Lost Sale" if that is chosen as well.
I have a form and when it's opened you get 3 different search questions to answer or leave blank. My question is.... If a search parameter is entered but no results are found to match it, how do I create an error message telling the requestor "No Data Found"?
I am trying to filter a form to show the entire weekend's activity on Monday but only yesterday's activity Tuesday through Friday. Using this code I can return Friday's results on Monday and yesterday's for the rest. How do I get the range Friday to Sunday?
I have built a custom search form in a MS Access 2010 database so that users can find specific records to edit. After entering the search criteria and hitting a Search button, another form opens up that shows the search results. This second form includes a command button for generating a report of the search results.
Right now, the custom search form and the search results form are both working properly, but the search results report is showing every record in the database instead of just the search results. This is true whether I access the report via the command button in the form or the navigation pane. I'm not sure if I need to correct my VBA code or the report's properties.
I have tried putting vbcrlf and "NN" as parameters into this but it doesn't work (maybe it can't see vbcrlf as a separate word!)
Function ReplaceWord(strText As String, _ strFind As String, _ strReplace As String) As String
' This function searches a string for a word and replaces it. ' You can use a wildcard mask to specify the search string.
Dim astrText() As String Dim lngCount As Long
' Split the string at specified delimiter. astrText = Split(strText)
' Loop through array, performing comparison ' against wildcard mask. For lngCount = LBound(astrText) To UBound(astrText) If astrText(lngCount) Like strFind Then ' If array element satisfies wildcard search, ' replace it. astrText(lngCount) = strReplace End If Next ' Join string, using same delimiter. ReplaceWord = Join(astrText) End Function
i have a table that has a column containing a letter reference a / and then another reference ( EG. AAA/A56457A, ght/6577654ghf, etc ) and i want to replace the / with a _
whats the easest and quickest way to do this as an expression
Need a little bit of help, I have a report I pull out of an inhouse system with sales, shortages etc. I want to use a query to alter the formatting slightly and append it in to a different table. Most of this i can do fine, except for one thing.
Two of the sales fields in my report I'm uploading come out in the following text format: 19.0PC 27.0PC 45.0PC etc...
I need to change this before appending as its going in to a number field, all i need to do is lose the PC, e.g. 19.0 27.0 45.0 etc...
I can do this manually, it's a simple find and replace in excel before uploading, gowever I want to automate this as much as possible. Looking around I'm pretty sure it can be done in a query using the REPLACE function but I'm unsure on the formats to use.
The only other thing is once its converted even though it will visually be a numbe (e.g. 45.0) it will still technically be a text field, will this prove a problem on the appending?
I am currently working on a project where i need to try and compare a list of organisation names held by my organisation and another organisation (around 20000 records each).
To take into account differences in how the organisation names may have been entered I'm wanting to strip out things like ltd, limited, company, co, &, and , and so on. I've tried using IIf and replace which works ok if the organisation name only contains one of the substrings I want to replace but not if contains more than one.
How do i go about doing multiple replaces in the same string?
