I am trying to do a simple division. But I am getting an #Error when Access query tries to divide 0 by 0. Here is my example:
fldOne = 0
fldTwo = 0
fldCalc: IIf(([fldOne]/[fldTwo])=0,0,([fldOne]/[fldTwo]))
result = #Error
However:
fldCalc: IIf(([fldOne]+[fldTwo])=0,0,([fldOne]/[fldTwo]))
result = 0
If I change the IIF to [0]+[0] (plus) then it works. So I think I am trying to find out how to divide two fields that both contain zeros? (...I think!)
I am attempting to create a customized task manager. I have created a form that has a combo box that list a series of categories. This list is pulled from a query. I also have a sub-category list that is pulled from a separate query. The relevant section of the subcategory query looks like this:
The query has a criteria that sources the combo box on the task creator form. This filters out all other primary categories. I have a macro that auto refreshes the page after the primary category combo box is updated. The sub category combo box then displays the related sub categories.This works great as a stand alone form. However, when I attempt to use a navigation form or use the tab navigation window I get the error message "Enter Parameter Value." I know am getting this message, because the related categories query is looking for a category in the combo box on the form, which at this point in the process is missing. It also does not update once it is moved to a navigation form or tabbed window.
I have to write a SQL statement which can divide Field1 and put the result sets in another field.In plain English I want to say, In Field1 if there is a Comma, put the letters between 2 commas, in field 2, If there is another comma put it in Field3 Field1 is an adress field and has values like 19 Stoney Gardens, Livingston, West Lothian I want this address to be divided. I am new and am stuck now. What is the best way to write a SQL query for this. Many thanks
Not sure whether this would be easier in a query than in VBA.. but I'm new to this:9
I've got a guest list for a big party, and one of the fields "Arrival" is a DATE/TIME field where it says when the guest entered the premises. What I want for reporting purposes is to divide guests into nine "arrivalgroups", a number field I've added to the table in casu.
The groups look like: 117-18(or <16:00 AND >=18:00) 218-19 319-20 etc. all the way up to 9, last group being 01:00-02:00
That is, if the guest arrived between 20 and 21, the table field "arrivalgroup" would be set to 4.
I am trying to get my report to divided numbers that are there. I get this data from subreports, then want it divided by the appropriate number. Example:
23 0 10 15 0 0 this would equal 48, but I want it to be divided by 3, not by 6. Or say the next report would have:
10 10 5 0 10 10
which would equal 45 and this would be divided by 5, not by 6.I was wondering is there a way by creating a hidden box that would check each value to see what it would be divided by. Example: iff(report.hasdata, control +=1)
I currently have a query that pulls selected data from a table. There can be multiple rows of data, and two columns include dollar amounts and quantities. I have a total line going at the bottom so I can see the Grant Total of all the rows (for dollar amounts and quantity).
Is it possible to add a column to this query that will calculate the expression:..?
=Grand Total of Dollar Amts for selected data/Grand Total of Quantity for selected data
I tried to use a query in a query, but must have done something wrong because it just said circular reference.
We use a 3D program that uses access for it's data base. For reasons I have yet to figure out when the data base is accessed tables are creted called "at_(random number)". For example at_31789. These tables sometimes contain no information or duplicate information of other tables. How can I prevent this from happening and what causes this?
Hi, I have several forms with subforms attached for data entry purposes. In a nutshell - this program is for an interview - where we need the respondent to give us a listing (say as to what intices them to join a study, etc.), but for some cognitive research method we need to be able to know the order they gave us the list in then we have them rank their list, thus the order may not be in the order they gave us the list. I hope that made sense. Anyway, when a form is called and loaded the subform always adds an extra record. Below is a screen shot of what I am talking about:
So, not only do I get an extra record, but when I am entering data and get down to the new record line, the cursor will jump up to the top of the column. What can I do to prevent the extra record and also the cursor skip? I am thinking if I resolve one issue the other will fall into place.
When I launch my modal, I want the user to be able to 'cancel' without creating a new record. It's not doing that and creating extra 'junk' in my table. How do I prevent that?
Here's my code: Private Sub Btn_Exception_SubModal_Click() DoCmd.OpenForm "Frm_Exception_UpdateModal", acNormal, , acFormAdd Forms! [Frm_Exception_UpdateModal]![clientnmbr].value = Me![clientnmbr].value End Sub
I have a form with a number of fields and buttons on it. The form is opened in "add new record" mode. I have noticed that when I move from the last field in the tab order to the first field in the tab order it saves an entry in the database.
This is causing me problems because when users move from the last field in the tab order to the field in the tab order an entry is saved to the database but it by passes all the data checks ensuring that certain fields have been filled-in etc.
I don't know why the movement between these two fields is causing an entry to be saved. I don't have code associated with either of these objects. I aslo changed the last field in the tab order to a different field and it still happens.
Any ideas or suggestions would be greatly appreciated.
I have a form that uses the following join as its control source:SELECT * FROM [Purchase Order] LEFT JOIN [Work Orders] ON [Purchase Order].WorkOrderID = [Work Orders].WorkOrderID; I also enclosed a small picture of the relationship between the two tables. This is the problem I'm getting when using my purchase order form.
What I Want: When I type in the "Work Order #" it will lookup the most recent work order with the same number and put its ID into the purchase order record.
What I'm Getting: When I type in the "Work Order #" a brand new work order is created and the "Work Order ID" field gets the next number in the sequence.
Make note that I'm not using code for any of this. This is happening all on its own. I think the join is messing things up here. Any ideas?
I am upgrading a .mdb to MSSQL. The .mdb is 17MB, but the resulting MSSQL is 72MB. Tried using both the Access Upsizing Wizard and Enterprise Manager DTS. I have done this a number of times before, but never ran into this problem. Any ideas what coule be going on, and how to fix it?
I have a form that has a main for, a subform, and then another subform inside the 1st subform. The form works like this:
Conact info --->Call info and notes ---> Orders worked
Each of those being nested as they appear. The problem is, the user usually jumps strait to putting the order number in "Orders worked" before entering in any notes or info... So no record exists in it's parent form to tie to. How can I have the database create a new record in its parent form when the user types in the subform?
Just been upsizing an Access DB. It seems to create views and functions for normal select statements and stored procedures are only created for insert or update statements. As fas as a recent DBA told me, he said that views should be used sparingly as they are not very efficient. Is this the case?
I also don't see why you would create a function when you could use a stored procedure to so the same SELECT statement(s)?
hello! I'm wondering how would I, in Access, make a query that would let me do a UNION and then insert that data into a table? Is there a way that I could achieve that in one(1) query?
I have a form that uses Table A as a record source and has a subform that uses Table B as a record source. There is a one to many relationship between Table A (one) and Table B (many).The user opens the form to a set record, then uses the subform to create a new record in Table B.
This works perfectly and I can do everything I want. But sometimes the subform creates a blank entry in Table B before I enter any data. Then, once I start entering data, it creates another record in which it stores the data that I am entering.The result is two records: One that only has the date of the record's creation (I set that to be automatic upon creating a new record) and the primary key that links Table B to Table A. A second one is the "true" record, the one which stores all of the data that I am entering.
It won't happen for 10 records or something and there is no apparent pattern. In case it makes a difference, the form is set to open as the subform as the focus.
I have a form that uses a text box to search for a specific record in a table.(Cotton12) This works perfectly. What I would like to implement however is if the user so wishes he can edit the data he is shown, whithout editing the actual data in cotton12 and then with this new info create a new record in cotton13.
I have a frontend and a backend database. As usual i keep the tables in the backend. Now I have some tables which need to be emptied and then refilled. I empty the table using the code
Code: DoCmd.RunSQL "DELETE * FROM TransNMTbl;"
then I fill in the table using
Code: DoCmd.RunSQL "INSERT INTO TransNMTbl SELECT * FROM TransTbl;"
The code runs fine. The problem is that the linked table is not used and the sql statement creates another TransNMTbl table in the front end and puts the data there. Bu I need the table to be in backend.
I have a database that was created by a colleague. They have created a form for adding client records onto the database.This is done via a button that, when pressed, checks mandatory fields for correct entries and then saves the record.However, when we do this, it is creating two identical records on the database.
She cannot see what she has done wrong in the code, and I know next to nothing about coding,
Code: Private Sub CmdAdd_Click() Dim dbAddClient As DAO.Database Dim rstAddClient As DAO.Recordset Set dbAddClient = CurrentDb Set rstAddClient = dbAddClient.OpenRecordset("ClientInfo")
I have several different sub-forms that have a button that opens a new form which creates a new record. Each of the different sub-forms have a field value that needs to be passed to the new record when the other form is opened. I've tried a few solutions, but to no avail. Right now I'm using the macro functionality as follows for one of the subforms:
The problem I think is that I'm creating a new record so the value doesn't get passed. The new record is only created after the user begins to enter data in the new form that was opened.
I have a form which saves to a database correctly. It has a key field name called "code".
I can recall the data into a copy of the first form to edit by using the key field "code".
All works well, however, when I click save data from the edit form it does not alter the original data but creates another form but with the same key field called code.
I created a basic database in Access 2013 to keep up with jobs, and each job has a job number assigned to make each job unique (Primary Key).
At first I had it where I had to enter the new job number manually for each job. I have updated it and now it takes the last job number and adds 1 to it and then fills in the Job Number field automatically.
The problem is when I clicked the Last Record button, it will go to the last existing record, but if I click the Next Record button while on the last record, it will create a new record. It creates a new job instead of stopping at the last record. I assume it has to do with the auto numbering I have setup for the job numbers, since it didn't do this before, but I am not sure why. I ONLY want to be able to click the New Record (Blank) button to create a new job, NOT by also clicking the Next Record button.
Is there a way to force only the New Record button to add a new record? I still need the Previous and Next Record buttons to maneuver through the existing records. I just don't want to create new jobs accidentally by clicking the Next Record button when I'm at the end of the existing records. It should just stop, or say last record or something similar.
I have a main form with several tabs. One of the tabs is a subform with just one number control, driven by a select query. The control shows a number, and you can type in a new number, which then updates the field in the table.
BUT, the control then shows a zero, it has moved to a blank record. If you enter a number again, it will attempt to write it to a new record.
So how do I display the number, and allow a new number to be entered that will update the SAME record, and not moved to a new record?
I am trying to create a form on Infopath 2007 based on a table in access 2007. I want my users to be able to fill in this form and submit it back to me in such a way that it will directly populate the table in the database. I know I can do this. However, I am unable to modify the infopath that access 2007 creates from the table. I want to change the date format. By default it takes up the date as "date and time" and I want it to be the date only.
I have the following code that creates a long string that works most of the time but sometimes gets cut short. I can't understand why it does this. When it cuts the string short it cuts it short in the same place. Everything gets in the string up to/or about the following code '</Practice Name>'This string is needed to upload info to a server.
