IIF Statement Comparing Two Fields?

Sep 28, 2012

The two tables are joined together by a primary and foreign key in the query, . I have not added the actual tables below , as data is senitive - i'm using an example. This join is made using the graphic relationship between the tables.

Table_1 with below column
Mode
6
6
6
6
6
4
4

Primary KEY
0001
0002
0003

[code]....

Initially the pseudo code i'm trying to get to work is;

if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH"

Expr1: IIf(IsNumeric([TABLE_2]![TYPE])="06" And IsNumeric([TABLE_1]![MODE])=6,"MATCH","NO_MATCH") ,

However this calculated field returns incorrect results, i.e. the query returns MATCH for rows that do not match , e.g. mode=6 and type=GL , Previously , when it was returning #Error when trying to match mode=6 and type=GL when it should ideally return "NO_MATCH", which is why I added Isnumeric.

Once I have sorted this out, I would like to achieve this as the calculated field

if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH" or if table_1.mode = 4 and table_2.type = 04 , then "MATCH" , else "NO_MATCH

how I can do this?

View Replies


ADVERTISEMENT

General :: If Statement Comparing To Fields

Sep 27, 2012

query is refering to 2 tables

Table_1 with below column
Mode
6
6
6
6
6
4
4

Table_2 with below column

Type
06
GL
PL
04
16

Both tables are joined, and the pseudo code i'm trying to get to work is;

if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH"

I have tried below, but doesn't work.

Expr1: IIf(IsNumeric([TABLE_2]![TYPE])="06" And IsNumeric([TABLE_1]![MODE])=6,"MATCH","NO_MATCH") , returns incorrect results.

Ideally would like this

if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH"

or

if table_1.mode = 4 and table_2.type = 04 , then "MATCH" , else "NO_MATCH

View 6 Replies View Related

IIF Statement - Calculate Value Based On Comparing Current Date To Dates In Fields

Jun 27, 2013

In my table I have the following 4 fields with the associated date field:

Bronze: 11/1/2013
Silver: 5/1/2014
Gold: 11/1/2014
Platinum: 5/1/2015

I am trying to calculate a value based on comparing the current date to the dates in these fields. I am using the below formula. However, using 6/27/2013 as the current date, my formula keeps resulting in "Bronze" when it should result in "Standard" Am I doing something wrong?

=IIf(Date()<[Bronze],"Standard",IIf((Date()>=[Bronze]) And (Date()<[Silver]),"Bronze",IIf(Date()>=[Silver] And Date()<[Gold],"Silver",IIf(Date()>=[Gold] And Date()<[Platinum],"Gold","Platinum"))))

View 5 Replies View Related

Comparing Two Fields.

Dec 30, 2007

Hello,

I am a Beginner in MS Access and SQL and I need a query to compare two fields.

I am having two tables; T1 and T2, and I want to compare their
fields TF1 and TF2, and display the non matching fields in new
field.

Example:

Table T1
TF1

Location Louisville
Number 555345
Name Smith


Table T2
TF1

Location Louisville
Number 553450
Name Smythe


Thanks in Advance.:)

View 1 Replies View Related

Comparing Fields In Access

Apr 28, 2006

I have a database with a pre-surgery field and a post-surgery field. The use must enter the current procedure in the pre-field and then the actual surgery in the post field. I need to check to see if the fields match verbatim. The field is a memo field.

The users know how to copy and paste from the pre to the post fields. 90% of the time it is the same but not always.

Can anyone provide some help?

Thanks,
Deborah

View 2 Replies View Related

Comparing Two Fields In A Query?

Jun 18, 2007

I am trying to compare two different fields in the same query and return the most recent date in some another field. Would I be able to do this and if so how?

Any help would be greatly appreciated.

View 14 Replies View Related

Comparing 2 Fields--Looking For Differences

Jun 1, 2005

Hello,
I have 2 fields that are supposed to have the same information for each record. I got the information from different sources, which is the reason for having 2 fields. I wanted to make sure that the information was accurate. Turns out, that some of it isn't accurate--all the fields don't match.

Here's a little example of what I have:

|___TITLE___|___ISSN 1___|___ISSN 2___|
|__TITLE 1__|__12345678__|__12345678__|
|__Title 2__|__22224444__|__23224322__|
|__Title 3__|__98765432__|__98765432__|

As you can see, the ISSN numbers for Title 1 and 3 match. There's no problem there. I want a query that would list all those like Title 2, where the ISSN numbers don't match. Then I'd have a list of all of the problem Titles, and I could look up the real ISSN numbers.

Thanks

-Siena

View 2 Replies View Related

Comparing Fields Of Two Tables And Output To A Third

Jul 20, 2005

It has been a while since I last used Access, recently I've used mySQL and PHP.

First a little description of what I'm trying to accomplish:
I have three tables...

tblImport - has the fields: TestID(PK), i001, i002, i003, i004 ... i025
Note: i00# field contains the multiple choice answer (i.e. 1,2,3,4,5) as imported from a CSV file.

tblStudentAsr - has the fields TestID(PK), StudentID(PK), 001, 002, 003 ... 025.
Note: 00# containes the multiple choice answer (i.e. 1,2,3,4,5) as enterd by the student.

tblResult - TestID(PK), StudentID(PK), a001,a002, a003 ... a025.
Note: the fields a00# have the datatype set to "yes/no"

What I'm trying to do is compare the answers in tblImport to the answers in tblStudentAsr then output the result to tblResult .

Here is some dirty pseudocode:
if i001 = 001 then
INSERT 1 INTO tblResult
else
INSERT 0 INTO tblResult


The above example gets a little repetitive since I would have to do that for each question.
Is it possible to put the answer fields of each table into a recordset then compare them?
(I have heard that using rs's can be a little slow. Although there is only a max of 25 questions the number of students can be quite large)

Alternativly, can I accomplish this using only SQL statements?

What would be the best way to attack this?

Any suggestions (or alternate suggestions) would be greatly appreciated.

Thanks,

salmonman

View 6 Replies View Related

Comparing Two Fields In One Criteria Field

Aug 9, 2005

Hi,
I just started doing something in access and need your help.
I have created a database and need to create a query which will sort all important fields within a certain time period. I have a date field and in the criteria field I wrote an expression:">Forms![Insert_date_form]![starting_date_field] and < Forms![insert_date_form]![ending_date_field]"

Its purpose is to show all fields which date is between this to dates. User will have to enter this dates in a separate form(Insert_date_form) which will store it in a small database containing only these two fields, e.g. generate report of all employees that are started working within a period between starting_date and a ending date. When I try to create and open a report based on this query I've been prompted with a small dialog which says: Enter parameter value.... for starting_date_field and same for ending_date_field. I don't need that. These dates are allready entered by user in a insert_date form and stored in its databese.

Is there a beter way to do this?

Thanks!
P.S. Sorry for my poor english. It's not my native language.

View 8 Replies View Related

Queries :: Comparing 2 Fields Query?

Mar 11, 2014

Trying to Compare 2 fields Status with answers Yes or No, from 2 Tables PipeLine and Pipe, and get the fields that don't match, what am i doing wrong?

SELECT PipeLine.[Project Name], PipeLine.[Capacity DC], PipeLine.[Project Num], PipeLine.[Status]
FROM PipeLine LEFT JOIN Pipe ON PipeLine.[Project Name] = Pipe.[Project Name]
WHERE (((Pipe.[Status]) ="Yes");

View 1 Replies View Related

Forms :: Comparing Combo Box Value In Fields

Jun 26, 2013

I have form where some or more field exist. I want to compare two field as :

1. cboBatchID As Combo box
2. txtBillNum As Text Box

Private Sub cboBatchID_AfterUpdate()
If Me.cboBatchID.Column(4) <= 0 Then
Me.txtBillNum = 1
Else
Me.txtBillNum = CLng(Me.cboBatchID.Column(4)) + 1
End If
End Sub

I mean, if cboBatchID.Column(4) <=0 then txtBillNum start from 1 automatically or cboBatchID.Column(4) >=0 then txtBillNum = cboBatchID.Column(4)+1

I'm already trying with the code above. But does not work. Generate run time error.

View 14 Replies View Related

Comparing Two Fields With Identical Input Masks

Aug 31, 2007

I have a form control with the input mask on it for a phone number. I am using an append query to append the information entered in the form to a table. I need to pull two fields from a linked table from another database. What I need to do is be able to compare the entered phone number in the form with the phone number from the table. The problem I am running into is that it won't match the phone numbers. The same mask is used in the table I am pulling from. Why can I not just put =[Table].[Phone_No] in the Criteria of the field where the number from the form is placed so that I can get the right record from the linked table?

View 4 Replies View Related

Modules & VBA :: Comparing Two Multi Valued Fields

Jun 22, 2013

I have two fields in a table that have multiple values. Example:

Field A:

CT, CA, PA

Field B:

CT, CA

I want to compare the two fields and indicate that there is a match because in this example CT and CA are in both fields. I would like to create a function. I'm not sure if I would have to use something with like or create a loop.

View 5 Replies View Related

Delete Fields In Tables By Using Sql Statement

Nov 14, 2012

I need to delete data in a table that named Cap One Table. I can not use a delete query since database located on company server and it is being used by many people. So, I have created a form and put on there two text boxes - txtStartDate and txtEndDate and command button cmdDelete. An idea behind this is that a user types date in txtStartDate and txtEndDate and click command button and delition happend in table Cap One based on that criteria. I put the below code in command button click event , but it doesn't work.

Dim startDate As String
Dim EndDate As String
Dim strSql As String
startDate = txtStartDate.Value
EndDate = txtEndtDate.Value

[code]....

View 4 Replies View Related

Join Two Fields In Select Statement Results

Apr 12, 2006

I'm sure this is easy to do, but I'm not sure how to word my search criteria when trying to look for a solution.


Let's say that I have 2 fields in MyTable: FirstName, LastName.

I've been trying to use the following query, but obviously it does not work:
Select (FirstName + ' ' + LastName) as MyName from MyTable.

I would like to have the following data returned to me in the following format:
John Doe
Jane Doe
Jim Doe
Jill Doe

Thanks,
CRhodus

View 1 Replies View Related

Queries :: IIF Statement That Would Change Value To Multiple Fields

Oct 15, 2013

I have a problem that I can't seem to solve in SQL for my access 2010 query.

Let's say I have a the price of and for every year.

In the tables I have A building number, a building type, and electricity and water predictions for this year and many upcoming years (up to 40 years)

I need to apply a change to any building with the building type BRT to show only 10 percent of the electricity and water for ALL years (up to year 2052). So building 5 would show 8.5, 5.4, 9.5, 7.4, and so on.

View 2 Replies View Related

General :: IIF Statement - Calculated Fields Not Working

Apr 8, 2015

Access 2013

I've created a table for a college project and realised that one of my calculated fields isn't working. It doesn't produce an error message, it just doesn't calculate.

IIf([CycleSpeed]<10,[CycleTime]*236,IIf(10<=[CycleSpeed]<12,[CycleTime]*354,IIf(12<=[CycleSpeed]<14,[CycleTime]*472,IIf(14<=[CycleSpeed]<16,[CycleTime]*590,IIf(16<=[CycleSpeed]<=20,[CycleTime]*708,IIf([CycleSpeed]>20,[CycleTime]*944,0))))))

View 6 Replies View Related

Iif Statement To Read Fields From Form Not Returning Values

Nov 4, 2006

Hey there!
I've been searching about this forum and found a lot of threads on this sort of issue but haven't been able to figure it out from those alone, so feeling a bit useless :o long winded explanation by the way... I'm useless at explaining! :D

Well basically I have a query that is supposed to power a flexible search form, where you choose the fields you want to search by and enter your criteria. The form contains labelled fields with checkboxes next to them, which you tick if you want to search for something in that field. The results are displayed in a subform which reads from the query.

The problem is with the query! My solution seemed to work fine when I put my criteria in just one field when testing (that is the fields in query design builder, not the form), but when I applied the same to two fields it didn't. Instead, it would return nothing unless I specified exact and correct criteria for BOTH fields I had set up with my solution. So whereas before I could tick "Customer" and search for a customer account number and it would display ALL relevant records, now if i just ticked "Customer" and entered the same number, it would display nothing, unless I ALSO ticked "Booking" and entered a correct booking number that matched a record for that customer in the tables.

I've heard iif statements don't do too well in the criteria fields in the query design builder?? But I know nothing about using SQL really.

For example for the invoices field have used:
IIf([Forms]![frmFindInvoices]![ChkInvNo]=True,[Forms]![frmFindInvoices]![InvNo])

So:
IIf(form checkbox = true, form text box value)

However it seems to think that if the checkbox is not ticked, I want it to only display EMPTY invoicing fields. This is never the case and so no records are shown! If the tick box is not shown, there is not supposed to be criteria for this field, in other words it should allow ANYTHING through.

What I don't know how to do is tell the bloody thing that if the box is NOT ticked, it is NOT to check for anything in this field, because the user does not want to specify any criteria for this field! Things like "Is Null" produce the same results, probably because the query still thinks I want it to find records that have empty fields.

Any suggestions to specify to the query what I want? I really need to get this done! Thanks! :)

View 11 Replies View Related

Queries :: SQL Where Statement Linked To Fields When Field Is Empty

May 23, 2013

I'm having trouble with using a where statement linked to fields when the field is empty. I need a way to say if field is null then 'do nothing'/'select all' else use the text from the box.

I have a form (ServicesRCSSearch) which has 3 combo boxes (Location1, Location2 and Location3). These fields are linked to a query. The button on the form generates the query.

My SQL for the query is currently:

Select Services.Key, Services.Location, Services_1.Location, Services_2.Location
From Services, Services_1, Services_2 (copies of the same table all left joined)

Where
((IIf(forms!ServicesRCSSearch!Location1 Is Null,"",services.Location=forms!ServicesRCSSearch! Location1))<>False)

And ((Services_1.Location)=IIf(forms!ServicesRCSSearch !Location2 Is Null,forms!ServicesRCSSearch!Location1,forms!Servi cesRCSSearch!Location2))

And ((Services_2.Location)=IIf(forms!ServicesRCSSearch !Location3 Is Null,forms!ServicesRCSSearch!Location1,forms!Servi cesRCSSearch!Location3))

This works in that it uses the fields to filter the query but when Location1 is empty there are no results as you can see from the code.

View 6 Replies View Related

Queries :: IIF Statement Expression - Create Alias Column Based On Data From Two Fields

Jun 20, 2013

I am writing a very complex 'if statement' query expression. I need to create an alias column based on data from two data fields.

Here's the logic:

True: If [PP] = "WG" or "WS" or "WL" and If [GR] > 10 Then "Skilled" or
[GR] Between 6-9 Then "Semi - Skilled" or [GR] <6 Then "Unskilled"

False: "GS"

View 2 Replies View Related

Queries :: WHERE Statement - Enter Dates Into Date Reported Fields For Results To Show

Sep 11, 2013

I use this

'WHERE ((OperationalRiskEventTable.DateReported)>=Forms!U pdateForm!UDateBegin And (OperationalRiskEventTable.DateReported)<=Forms!Up dateForm!UDateEnd)'

in a query by form.

The problem is that you have to enter a date in the between values for results to show. If I don't enter information into a different field such as Full Name but I enter in 40 into Age then everyone that is 40 years old will show. On the other hand if I enter 40 into the Age field but I leave the Date Reported fields empty then no results will show.

How can I change it so that I don't have to enter dates into the date reported fields for results to show?

View 4 Replies View Related

Queries :: Update Statement For A OUTER JOIN Select Statement

Feb 12, 2014

I have the following Select Statement:

SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],

[code]....

Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?

View 2 Replies View Related

Queries :: IIF Statement With And In True Statement

Oct 31, 2014

I have a query with the following criteria in one of the fields:

>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())

fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.

>=DateAdd("m",-12,fom()) And <=fom()

Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.

Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())

I have also added the column name to each expression and it still doesnt produce any results.

View 4 Replies View Related

Creating IIF Statement In Between Statement For Dates?

Apr 28, 2015

I am creating a multi-search form for a student database, where after I enter my search criteria I hit a "Run Query" command button and then it opens a query form with all of my criteria.So far I can search using last name, first name, and middle name. When I try to search with a start date and end date I am have issues.The start date and end date is for the class date. In the query form under the field, class date, for criteria I wrote:

Between IIf([Forms]![Search Form]![Start Date]="",1/1/10,[Forms]![Search Form]![Start Date]) And IIf([Forms]![Search Form]![End Date]="",4/25/15,[Forms]![Search Form]![End Date])

I want it when I write a date in the start date and end date I want it to give me a list of all the students who took the course between those dates. Also, if I leave the dates blank I want it to search all dates. The dates 1/1/10 and 4/25/15 are just the dates I gave because that is far back as my database goes.

View 11 Replies View Related

Comparing Tables

Aug 17, 2006

I have two tables: -

1) Sales data
2) Claims data

I want to anaylse Premium (contained in the Sales Data Table) against Claims (contained in the Claims Data Table). The result I want to achieve is to show a loss ratio (Premium / Claims as a %). I need to show this on a per policy basis (each Table has a Policy Number Column).

Im new to Access and do not know how to go about this.

View 3 Replies View Related

Comparing Tables

Jul 12, 2005

Hi,

I have imported data from an ERP system into an Access table. This big table contains information about invoices processed last month. 2 of the columns are currency code and payment batch code.

Actually, there are rules that accountants should follow, but they can make errors. We would like to find those error using this Access database.

One rule is that only predefined payment batch codes should connect to a
currency code.

My idea was to create another table, where controllers are able to type in these rules like

EUR CIE
USD CIU
HUF BKH

This table has only 2 columns: currency code and payment batch code and no primary key has been defined.

The 2 tables were joined with the currency code and a query printed only those transactions where the payment batch code from the big table <> the payment batch code from the new table. This shows where accountants made an error.

This is the query:

SELECT [475 Master].*
FROM Tbl_Pmt_Btc INNER JOIN [475 Master] ON Tbl_Pmt_Btc.Cur_Code2 = [475 Master].Currency
WHERE ((([475 Master].Pmt)<>[Tbl_Pmt_Btc].[Pmt_Btc_Code]));

475 Master is the big table, Tbl_Pmt_Btc is the new table that can be modified by the controller.

This worked until more than one payment batch code were assigned to a currency code.

EUR CIE
EUR CIU

It seems that Access can use only one of them.

Can you please advise what to do in order that Access takes both rows into
account and the query prints those transactions booked in EUR where the payment batch code are neither CIE nor CIU.

Is there a simple solution without any programming?

Thanks,
Peter

View 2 Replies View Related







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