Forms :: Combining Values In Expression Using IIF?

Apr 30, 2014

I'm trying to create a text box on a form which displays a value based on numerous criteria.

Basically I manage a database of employees. Some are external employees, some are internal. Some have left the organisation, and some are still employees.

I have two fields which need to be pulled into this expression:

is_leaver - text box, with value either "Yes" or NULL

organisation - name of organisation (text)... lets say my organisation is called Happy People Ltd

The text box I have is called employee_status

I can see that there are 4 possible combinations:

1. Internal Employee - Is an internal employee and still works here
2. Internal Ex-Employee - Is an internal employee but has left
3. External Employee - Is an external employee who still works here
4. External Ex-Employee - Is an external employee who has left

So far I've managed this:

Code:
=IIf(IsNull([is_leaver]),"Employee","Ex-Employee")

This displays whether they're an employee or ex-employee. How would I form the expression to combine the two fields, and calcuate whether they're internal or external?

Due to the way the form is set up I don't want to have two separate text boxes and would prefer to combine it.

View Replies


ADVERTISEMENT

Forms :: Combining Date And Time Values Using DateDiff

Jul 8, 2014

I'm trying to create my own database dealing with hiring bikes on a daily basis.

I have created a form for the basic input, including StartDate, StartTime, EndDate and EndTime, followed by TotalDays. I added the time field so that if a client brings a bike back an hour or two after the pickup time on a later date (sounds complicated - e.g he takes it on Monday at 12pm until Thursday 14pm) then he would be charged for an extra day. It is similar to the car rental system used worldwide.

The TotalDays is currently using a simple expression =EndDate-StartDate.

Is there any way to make the form incorporate the time difference so that if EndTime-StartTime>1 then it would add an extra day to the "=EndDate-StartDate" calculation.

View 3 Replies View Related

Combining Values

Sep 9, 2005

I am pretty new to access and struggling with a query problem. I currently have a table containing data like:

Name_____________Color Chosen
Bill..............................Red
Bill..............................Blue
Tom............................Red
Mark...........................Green
Bill..............................Green
Tom............................Blue

And I would like to be able to get to

Name______________Colors Chosen
Bill..............................Red, Blue, Green
Tom............................Red, Blue
Mark...........................Green

Can anyone suggest a query to get me this second table. Theoretically the maximum number of Colors that one person can choose is 4.

Many thanks in advance

Chris

View 5 Replies View Related

Combining Values Of Comboboxes

Dec 20, 2006

Hi! I have a problem combining values of comboboxes, let me explain me better...

I have a text box and two comboboxes on a form
First, you introduce a data on the text box, then you select something of one of the comboboxes

the third combo box have to have the value of the text box and the value of the combo box separated by a comma or a space. Please help me with this. I need to finish this database soon:confused:

View 3 Replies View Related

Combining Two Boolean Values?

Feb 11, 2012

I basically have two yes/no fields in an access table. I want field one to have the value yes if field two has value no, and the other way around.

I had a look at the Boolean operators, but im not sure where to go from there.

View 6 Replies View Related

Combining Calculated Values From Two Queries.

Aug 24, 2006

I have created two queries which calculates a total. I want to add the values of the two queries in a third query to give me a grand total.

When I try to create this third query, it gives me an error saying that the fields cannot be used from two different queries. So I'm just wondering whether this is possible or is there any other way of doing this.

View 1 Replies View Related

Reports :: Combining Same Values From Two Fields

Aug 21, 2013

I would like to make a report to show how many employees and which employees are attending to which colleges/universities.

In my data (800 records), I have two fields which is "College 1" and "College 2" for each employee.

There are values that are enter in college 1 for some employees, other employees have the same value that is enter in in college 2. How do I get to show a report that has all the employees who attended the same college in either college 1 or 2?

For instance, this is my raw data:

Name - College 1 - College 2
Bob - University of HI - Honolulu CC
Sandy - Honolulu CC - University of HI
Clare - Kapiolani CC - University of HI
John - University of HI - Windardward CC

I want my report to show:

Colleges -
University of HI
Bob
Sandy
Clare
John

Honolulu CC
Bob
Sandy

Kapiolani CC
Clare

Windard CC
John

View 4 Replies View Related

Combining Rows With Duplicate Values

Jun 17, 2013

I am working with an Excel file of raw data aggregated from an annual customer relationship survey that has been sent out since 2010. The file has approximately 20,000 rows, meaning the survey has been taken around 20,000 times. Unfortunately, each time a customer takes the survey, it is included in the raw data as a separate entry. Therefore the file has numerous duplicate email addresses, corresponding with unique data for each time the survey was taken. Another issue regarding the data, is that in the first year the survey was sent out, the distribution mechanism "broke" and the survey was sent out multiple times (and completed multiple times) in the same year by the same customer, so the surveys are not necessarily uniformly distributed, if that makes sense.

I have been interested in isolating the common respondents (those who have taken the survey across multiple years, albeit not necessarily consecutive years). Up to this point, these respondents have been isolated manually using a pivot table, however I am now looking to enter the raw data into SPSS (a statistical analysis package), in order to view the drivers of these common respondents.

Therefore, I would like to be able to isolate these common respondents and the data corresponding with their surveys from the raw data in a separate worksheet or file. I have tried various formulas to do so in Excel to no avail. Is there anyway to accomplish this in Access or would a more complex database be needed?

Ideally the final product would have the common respondents' information from multiple surveys in one row and would be able to be filtered by feedback date (found in the raw data), so that hypothetically one could select a month and be able to tell how many customers considered to be common respondents completed a survey within that month.

View 8 Replies View Related

Combining Two Text Box Values Into One Label

Jun 12, 2013

I have two text boxes, and i want to make there value be the label for the form. Here is what i have in the Label on the form:

=Nz([Last Name] And [First Game],"Untitled")

Where am i going wrong?

View 5 Replies View Related

Queries :: Combining Fields - Spacing Between Values

Mar 6, 2014

I'm creating a query which pulls together the numerical values of 32 separate fields. Each field has the potential to have a value in it ranging between 1 and 9, but most fields will be blank.

I want to prefix the value (if the value is not null) with a 2 or 3 character-long code relating to the field name.I then want to combine them all together in a query so I can easily copy and paste the output to a spreadsheet.Here is what I have so far, with just 9 of the field names, and it's already looking a bit clunky.

Code:

IIf(IsNull([zam_adm]),"","ADM") & [zam_adm] & " " &
IIf(IsNull([zam_cts]),"","CTS") & [zam_cts] & " " &
IIf(IsNull([zam_crs]),"","CRS") & [zam_crs]& " " &
IIf(IsNull([zam_IPB]),"","IPB") & [zam_IPB]& " " &
IIf(IsNull([zam_opr]),"","OPR") & [zam_opr]& " " &
IIf(IsNull([zam_owl]),"","OWL") & [zam_owl]& " " &
IIf(IsNull([zam_pmi]),"","PMI") & [zam_pmi]& " " &
IIf(IsNull([zam_wl]),"","WL") & [zam_wl]& " " &
IIf(IsNull([zam_sgt]),"","SGT") & [zam_sgt]

The output, unfortunately, is putting spaces between empty fields and I'm not sure how to prevent this in the syntax?

View 3 Replies View Related

Queries :: Combining Columns And Replacing Certain Values

Sep 29, 2014

I need to create a query that combines two columns (lets say Column A and Column B for example) however the problem is that whatever non-null values that are in Column B must replace any value in Column A. If Column B has a value that is null then Column A's value is shown.

I have an example of what I'm working with (access file) and what result I want (excel file).

View 2 Replies View Related

Queries :: Drop Down - Query Not Combining And Filtering Values Based On A Form

Jul 22, 2014

I have a form (DropDown form) that has 3 drop down fields, you select your values from the drop downs and you would push a command button that runs an event procedure which runs a query (DropDown qry test). The user should have the option of picking any combination of fields to filter by. Or no combination, which would return all values in all fields. So I am basically using the form as parameter's for the query.

The problem I'm having is that my query is returning values for one field AND values for another field. Even if the other values selected are not in the same record. It's not combining the fields together to filter. For example: you pick a Project name and Supplier name, the query will return records that have the project name you selected but it will also return records with the supplier name you selected that have a different project name.

I've attached screen shots of the form and the design view of the query (the screen shot cut off the last column name. It is meant to say "Expr3: [Forms]![DropDown form]![Combo7]").

Using Windows 7,
Access 2010

Is there a way to select multiple values from the drop downs?

View 14 Replies View Related

Combining Multiple Columns Into One Longer Column (without Combining Fields)

Jul 17, 2013

I trying to combine three columns that I have into one column without combining fields.

Currently what I have:
(see image below)

What I want:
ID-----MOC
##----name1
##----name2
##----name3
##----name4
##----name5
etc

The list I have will be much longer and will be changing frequently, which is why I can't just go on excel and manually do this.

View 14 Replies View Related

Summing All Values In Textbox Which Has Expression

Mar 12, 2013

I have created a report and i am having trouble with the =Sum() function. I would like to sum all the values that are held in a text box which has the expression of '=[BookingSF_Qry subreport].[Report]![TotalSF]*[HowManyDays]'. No matter what i try i cant get a total value of all of these.

View 14 Replies View Related

Displaying Null Values With Expression Builder

Aug 16, 2006

Hi. I am using Expression Builder to total 3 columns for a query.

Total: [BaseTotal]+[ClickTotal]+[Adj Total]

The [Adj Total] is sometimes empty, and when this happens, Total is empty even though there are values for [BaseTotal] & [ClickTotal]. How can I make the Total show up regardless of whether any of the three items used to calculate it are Null?

Thanks

View 3 Replies View Related

Reports :: Expression To Count Values In A Range

Sep 3, 2013

I have been stuck trying to write an expression that will calculate the Yes values in a range of 16 Yes/No fields.

I have attached a screen print of the report I am working on.

View 5 Replies View Related

Queries :: Expression / Criteria To Return Alphanumeric Values Only

Jun 30, 2015

How to write an expression/criteria in a query that will return only ID numbers with alpha numeric values. Example: My Id field contains both numeric 479621, 680530 and alpha numeric 132NAV100, 174NAV26a values. I want to run the query to return only the alpha numeric values.

I do not know SQL nor VBA.

View 11 Replies View Related

General :: Handling Null Values In Expression Builder

Dec 19, 2014

In Expression Builder, I have the query(in the Criteria row) set to run based on values in different fields on a form. When one of the fields is empty however, the query will not return any records.

How can tell Access to ignore the fields when those fields on the form are empty and only look at fields that are not null.

View 2 Replies View Related

Queries :: Dealing With Null Values In Format Expression

Apr 4, 2014

I am trying to use the expression below to transpose strings of 4-6 numbers (140504) to a string of 8 numbers with "20" in front (20140504). Some of the [Receive_Date] fields are empty or are 0, and in those cases, I need the expression to return a null value.

New_Receive_Date: IIf([Receive_Date]=0,"",IIf([Receive_Date]="","",Format([Receive_Date_YMD],"000000")+20000000))

The Format(...) part of the expression works fine on its own, but I am new to dealing with null values in my expressions, and when I add the rest of the expression, all '#Error' values are returned.

View 2 Replies View Related

Forms :: Combining Split Database Or Easier Way To Update Forms?

Aug 5, 2015

I've split my database into a front end and back end which was relatively easy. I did this so that multiple users can have their own front end. But if I have to go in and change the back end and front end, does that mean I have to remake all the front ends? Right now I'm finding that if I delete my linked tables on the front end and then import them into the back end again and change everything on one screen and then split it again is the only option I know of.

View 6 Replies View Related

Tables :: How To Change Values Within Calculated Column Without Changing Expression

Jul 31, 2014

Currently I have a calculated field in my table; however, under certain circumstances I need to change the value to another value which is not related to the calculation. I understand that the calculated fields are read only (why is that?), so I was wondering if there was any way to change values within a calculated column without actually changing the expression itself.

View 1 Replies View Related

Queries :: Calculating Percentage - Exclude Null Values From Expression

Mar 10, 2014

I'm trying to create an expression to calculate percentage.

The fields, Value A and Value B might both have null values, but I do not want Access to treat nulls as zero (as in the Nz() fn). How do I get the expression to exclude any nulls and not calculate for those rows, but set the calculated value for a row with null A or B to zero.

I want it to set no calculated value to zero so I don't get overflow errors and can therefore order the data by the calculated field.

View 1 Replies View Related

Create Expression In Validation Section Prevent Duplicate Values In A Field?

Sep 11, 2012

create an expression in the Validation section to prevent duplicate entries in a field. Please view Powerpoint file on slide 1 on attachment for More detail instructions. Instructions are in simple Text and images which makes it easy to understand. I am using Access 2003.

View 14 Replies View Related

Queries :: Combining Queries Without Matching ID Values

Feb 2, 2015

I have two queries:

1. a sum of payments by individuals
2. a sum of charges of individuals.

I want to combine these queries to create a report that shows the sums for each person of the charges and payments and calculate the balance. However, it is only showing me those individuals that have both a charge sum and a payment sum, while some individuals will only have a charge sum. How do I get those individuals to show and show with a zero payment balance if they are in my charge sum query but not my payment sum query and vice versa (some may have made a prepayment even though they have not been charged yet).

View 5 Replies View Related

Combining Queries With Forms

Feb 18, 2006

I'm new to the forum as well as I am new in using MS Access, so please don't lough :) I wasn't even sure whether this is the right subforum to post to....

Anyway here it goes....I have this really big database (nearly 5000 records) and I was wondering how could I make a query, perhaps outside of the Design View, and making it appear like a form e.g. let's say I want the query to display in form style (or even report...) the rest of the fields concerning a certain record when I just give the ID number of a person.

Should I turn to code solution?

Help anyone?????

View 2 Replies View Related

Forms :: Default Values Disappear If Put Values In Text Boxes

Sep 17, 2013

In Access 2010 I have a Data Entry Form on which I have an unbound textbox in the header that the user can put a default date in. In the body of the form is a bound textbox that records the date and the default value is set as =defaultdatestat (obviously the name of the box in the header).

Problem: The default date shows up perfectly until a value is put in any of the other text boxes.

For further info : If you put values in text boxes default value disappears; if you then push escape the default value reappears when the values in the text boxes disappear.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved