Is it possible to have a IFF statement on a field in a form?
If there is a field number in a form, can an IFF statement be used to check that value. IFF "field" <= 10, Then it the field highlights red.
I am trying to created an IIF statement in a field I have in a form.
I have entered the following statement but it keeps coming up with the following error and I cannot see where I am going wrong.
The expression you entered contains invalid syntax, or you need to enclose your text data in quotes
=IIf([RenewalInstallment]=0,(12-[MFAccepted])*[CurrentAnnualPremium]/12)*(1-[DiscountAccepted]/100),IIf RenewalInstallment]<>0,(12-[MFAccepted])*[RenewalAnnualPremium]/12)*(1-[DiscountAccepted]/100)
If I enter the first part of the If statement i.e.
=IIf([RenewalInstallment]=0,(12-[MFAccepted])*[CurrentAnnualPremium]/12)*(1-[DiscountAccepted]/100)
The field calculates correctly however there seems to be something wrong with the nested if.
I need a pop-up form that is based on an if then statement.
For example if there is a certain customer selected on the form and the detailed bill is more than 10 lines long (I have a count field) then I need a box to pop-up that says....
REMINDER: You will need to manually reformat this 500byte file.
I also need another one that pops-up when the record loads that warns if a purchase order field is blank.
I have a continuous form sfrmAddress which is based on my table tblAddressList. What I am trying to achieve concerns the date fields DateFrom and DateTo.
DateFrom is a bound text box txtDateFrom
I am trying to put an IIf function in the Control Source of unbound text box txtDateTo
=IIf([txtDateFrom]=[qryAddressHistory1]![LatestFromDate],Date(),[tblAddressList]![DateTo])
qryAddressHistory1 returns the LatestFromDate , so the most recent address.
The idea being if it is their most recent address, they have lived there until today, if it is not their most recent address it will display the date contained in the table (when they moved out or DateTo).
I have tried this a few different ways, double and triple checked my references and I'm usually OK with IIf so I don't understand where it's going wrong!
I should add I'm getting the #Name? error all the way down the column when I open the form.
I want to create a button that executes an SQL statement in a form. The reason I can't just save a query and run the query from the form, is because the SQL statement needs to use a combobox value from the form. I've learned that docmd.runsql will not work for a simple select query.
View 5 Replies View RelatedI have a form in my Access database that has 3 input boxes by which a user can locate a record by. The problem I am running into is that I can not get a nested IF statement to work properly to first check which of the search boxes are filled in and second search by whichever is filled in. I have come up with the following code that seems to be only searching by the first input box. If the first input box is blank it does not move to the second or third numbers so I imagine there may be something wrong with the order of my IF statement or the syntax is off.
View 14 Replies View RelatedI have a form based on a query in datasheet view. What i would like is to show only those records that fulfill the truepart of iif statment, and do not show the ones that fulfill the false part.
I have the following fileds in the query
month
currency 1 checkbox
currency 2 checkbox
sum
In the form, the control named month contains the following code:
control source: =IIf([checkbox1]=no;[month];0)
So this gives the appropriate months, but also shows the all other records with a 0 in the month field. I would like to get only the records shown that are correct with the truepart of the statement.
I'm trying to calculate a value for a field based on the value of another field, [Field1] has a value list of 28 choices I want [Field3] to take that value and multiply it by the value of [Field2]. I'm using an Iif statement and it sort of works. I looks like this:
IIf([Field1]=1 Or 2 Or 3 Or 4 Or 11 Or 12 Or 13 Or 21 Or 22 Or 28,[Field2]*0.06,IIf([Field1]=5 Or 6 Or 14 Or 15 Or 23 Or 24 Or 29,[Field2]*1.1,[Field2]*2.1))
I receive nor errors but it will only return the value of [Field2]*.06 no matter what is selected in [Field1].
I'm sure I'm missing something and there is probably an easier syntax to use, but I'm at a loss at the moment.
How can I get the value from a field in one table (in the sub form) to copy/insert into a field in another table (in the main form) when adding a new record?The main form and sub form are linked using parent/child linking, and the sub form is in a tab.I have table A (Visit Dates) in the main form which is used to record the date of a visit to a church. Table B (Quarters and Peals) is used to record an event that took place at that church during that visit. Note that not all visits in table A require a record to be created in table B - but half or more do.
In tables A and B I have a field called "QuarterOrPealID" and these are both primary keys, though the field in table B is set to 'no duplicates' and in table A it's set to 'duplicates allowed', as table A has its own auto number/pk. They are both linked in the relationships.
So, when I add a new record to table A using the main form, I might then need to click on the tab in the sub form to create a new record in table B, which has to be linked to the same record in table A. When the "QuarterOrPealID" auto number/pk is generated in the sub form (table B), I need that value to update to the "QuarterOrPealID" field of the main form (table A), so that when I'm viewing these records the form pulls all the information nicely together.
I am currently stuck on set focus property. I have a main from with nested subform. I am trying to move the focus from last field of the subform to another field on the main form.
Customers(mfrm)....>Addresses(sfrm)...>Orders(sfrm Add)......>OrdDetails(sfrmOrders)
Now I have a field name [Securedesign] in frmOrderdetails and I want the tab order to navigate to field [CustomerID] in frmAddresses which is a subform to frmCustomers.
I have my Assets form and the primary key is the ChargerID, in this form I have an "Add New Job For This Asset" button, which opens up the Jobs form at a new record.
How do I make it so that the ChargerID field is automatically filled with whatever the previous record was instead of being blank.
For example if I have Charger12345 open in the Asset form, I'd like to click the Add New Job button and it automatically have Charger12345 in the ChargerID field of the Jobs form.
I have a form where I would like to validate (restrict) choices in subsequent fields.
Example
Combobox choices are Air, Fire, Water, Earth, Space
In the form I have 4 fields = Material1, Material2, Material3, Material4. (all in same record on my table)
Basically I need the validation to not allow duplicates across the 4 fields.
something like....
[Material1] <> [Material2] or [Material3]or [Material4]
I am not sure of the proper syntax for the validation field in the Form Properties. Or if I am even putting it in the right place.
I am creating a training database and first I have tables in relationship to the courses:
tbl_Courses
Course ID (Primary Key)
Course Title
Objectives
Instructors (lookup field allowing multiple instructors to be selected, meaning they are skilled to teach the course)
tbl_Instructors
Instructor ID (Primary Key
Last Name
First Name
Full Name (Caluclated to put Last Name, First Name)
[code]....
What I am trying to do on a form to create a new event is once the user selects the Course from the Course ID combo box, then I need the Instructor combo box to only display the instructors who are skilled to teach the course which are selected in the tbl_Courses.
I can get all Instructor ID's, but not the names and the class that has multiple instructors show on one line.Should I have not put the Instructors field in the tbl_Courses? Do I need another table for instructor skills or something?
What is the best way to move from the last field on the last page of a form to a new record field on the first page?
View 5 Replies View RelatedI am making a very simple 'registration' database for a children's event in a couple of weeks.I the table/form there is a checkbox field called 'consent' which, if checked, indicates that a child can leave the event without parental consent.
There is a report printed on each child (a registration page which the leaders get a copy of). I would like on this report a 'red box' to appear if the child cannot leave without permission (i.e. the consent box is not checked). I would also like this 'red box' to appear on the form. I had thought of doing it this way - but I'm not sure if it's the best, or if it's possible:
Have a field in my table called 'consentindicator'. When the 'consent' box is checked, there is a period ('.') placed into the 'consentindicator' field. It is set to turn red when a period is present. That way, when the consent box is checked, a get a red 'box'.
I am creating a table which is called a New Connection Analysis from there I have created a form that is linked to the table. I the form I have created a combo box to show the different division e.g. Central, Western, Northern. Now the combo box is created but when I enter the data in the form once I select the division e.g. Central when I save it the data doesn't go to the Division list on the table.....How can that be fixed????
View 4 Replies View RelatedI have a main form with many subforms. Each subform has couple of ComboBoxes or/and TextBoxes. I want to make sure that end users dont miss completing any of required values. For example, in the subform (Clients), an end user is supposed to put either "Male" or "Female" in the (Gender) ComboBox. In another subform (Job Info), the end user will put info of the job like Wage, Employer, Date of Job Start... ete.
What I want is to get VB IF statement which tests if the textboxes "Wage", "Employer", and "Date of Job Start" in the subform (Job Info) are not null, then the CobmoBox "Gender" in the subform (Clients) should not be null too, with a warning message to complete the missing value.
How do I populate fieldA in formB with FieldB from FormA..
So Field is the last numeric field in Form A..so I need to the take the calculation value of that field and be the starting value in formB.
I am trying to write a statement and it returns: #Type!. I know what this means but it should not give this error.I have button that copies a certain number of fields to the clipboard. The one below is one of them. Here is the coding I am using that works:
Code:
=Trim(IIf([CallingContactRel] Is Not Null,[CallingContactRel] & ": " & [CallingContact],[CallingContact]))
I would like to add the following condition to this code. I have a yes/no field called BookingNoteContactExcl. If this is ticked, it should exclude [CallingContact] from being copied, in other words, return "" in the code above.
I have one table containing name of restaurant with its address etc. Then i created another table to list out the restaurant workers names and details. Just as an example,
Table:Restaurant
Restaurant name
Address line 1
Address line 2
Restaurant #
Website
Table:StaffContact
Staff Role
Name
speciality
email
phone
I have the main form that has all the restaurant details only. And i have another form containing the Staff information. Please note the two table have a relation and it works well.
Now to make it user friendly(basically easier for the lazy ones), I dragged the staff contact form on to my main form and displayed it as a datasheet(basically a sub form).
Now, my boss does not want users to add/delete on this sub form(datasheet). So,he wants me to create buttons to open new record of staff for each restaurant(new form)
My issue is with opening a new record to enter a new person to the staff list and give them a role as well in form view.The new form has
So i ran a Macro, with open form with Where condition
Code:
[Staffcontact]![Rest Name]=[Forms]![MainForm]![RestaurantName]
But, it does not work .
Hi!
I have a table called myTable and it contains two fields Year and Month. Both are type of integer.
Now in my select statement, I like to combine these two fields in to one and name it as period and it will have the format, for example 200501, 200502, ..., 200512. How can I do that in Access query statement?
I know how to do this in SQL Server 2000. That is:
Select Convert(varchar(4), [Year]) + right('00' + cast([Month] as varchar(2)), 2) as Period from myTable
How can I do that in Access Query statement?
Please advise.
Aijun.
i have a crosstab query that contains employees and their sassocaited assignements he/she is working on. each assisgnment is associated with a start and end date. there are monthly columns that forecast until 2007.
i need to create a headcount report that provided a count "1" for each employee in a monthly column.
currently i there is a Vaule Field that Counts the number of Employee......yeilding the desired "1" BUT i want it to only populate a "1" if the Montly Column is Between the Start and End Date for the assocaited assignment.
is it possible to add a If Statement in the Employee Column to only Count if the Month from the column is between the assignments start and end?
if so, can you please provide?
gracias
I am working on a hired plant database and want to limit the combo box on the hired form to only list items not on hire.I thought I could do this using the IssueDate and ReturnDate fields within a query with an IIf statement forcing the Yes/No field to choose Yes when there is a date in the IssueDate field and No when the ReturnDate field has a date entered.
View 14 Replies View RelatedI am having some trouble with an iif statement as selecting the field..I have two columns, one called [sdCounterpartyprice] and one called [bid]I want to select the [bid] if it's not equal to Null or 0, if it is equal to that then pick [sdCounterpartyprice]this doesn't work
Code:
CptyPrice: IIf ([bid] is null or = 0; [sdCounterpartyprice]; [bid])
We have a database with one main data entry which stores records in a seperate table. There are several thousand records keyed in using this form. Therefore, alongside the entry we have a search form which allow the user to search for a specific record and display it in the main if he/she needs to edit one.
We have received a request from users for a functionality which allows users to browse all records, using the main form layout, that they have keyed in. I guess what I am asking is: Is there a way to limit the records visible in a form using field on that same form as a limitation (ie. the users name - which is a required key-in)?
I have form named home page in this homepage form in the form header i have inserted some fields from some tables like province, district, community etc. these fields are unbound and are used for filtering purposes, when i select a province all districts related to that province displayed and when i select a a district all related communities displayed, there is a one to many relationship between district table and community table,
What I want is, when i select a district on the form header(it is unbound as i explained before) of this homepage form, the DistrictID should appear in community subform under DistrictID(as there is a one to many relationship between district and community) .
For more clarification find the snap shot attached...