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 Replies
ADVERTISEMENT
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 6 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
Nov 5, 2013
We have an "item" table that contains information specific to an item (the item number is unique); and multiple "production line" tables. There is a relationship between the Items table and the Production Line tables (all 8 of them). Each item can be entered on 1 or more production line tables because one item may be produced on multiple lines and the settings vary based on the line they're produced. When entering an item in the Production table we MUST make a selection from the Item table.
Now, what I want to do is identify which item number(s) from the Items table does not have a specific field entry in ANY of the Production Line tables.
View 9 Replies
View Related
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
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
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
Nov 16, 2012
I am trying to put a message box in an if statement and im struggling.
iif([Voucher]>0 and [VoucherNumber]>0,=MsgBox("Please Bla Bla Bla",vbOK))
I've searched google and 'Tech On The Net' and i still cant figure out why it has an error
the error is Complie Error: Expected Expression
The part hightlighted is the = sign. i remove this and it then doesnt like part of the Message. I don't know what is happening.
View 4 Replies
View Related
Nov 12, 2013
I am trying to add a value to a column in a query based on the value of another column.
I am using an iif statement for it but can't get it to work. I have a column called [Was Worker Born in UK] which has 3 options of "Yes, "No" and "Unknown". The next column is the [COUNTRYOFBIRTH] column.
I basically want [COUNTRYOFBIRTH] to say "866" if [Was Worker Born in UK] is "Yes".
View 14 Replies
View Related
Feb 25, 2014
Is there a way to use a variable in an SQL statement?
Something like this:
Code:
dim mysql1, newname
newname = inputbox("direction on what to do go here?",,)
mysql1 = "INSERT INTO FinalAll SELECT newname.* FROM newname;"
DoCmd.RunSQL mysql1
Where newname would be a variable assigned from an input box.
I want to populate a single table from several different tables but have the user specify which tables to use.
View 8 Replies
View Related
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
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
Feb 6, 2008
If I write a select case statement for a field X i.e. 6 to 7 for example will this:1. Select any fields X where the number is greater than or equal to 6 but less than (and not including) 7B. Select any fields X where the number is greater than or equal to 6 but includes decimels of 7 i.e. 7.5 ORC. Select any fields X where the number is greater than or equal to 6 but includes but literally only includes cases where the the whole number 7 appears>I only ask because in an example in a book I am looking at says:Case 10000 to 20000......Case 20001 to 30000I would have through the word "to" means until but not including but in this case the field that equals 20000 would never be selected?
View 14 Replies
View Related
Aug 23, 2012
using the Select statement on a form;I have it looking up 3 fields, Ordered by the first and bound by the first.the other 2 are being stored in different fields.What is weird is that I need to have the first field entered twice in order for it to show up, which means I'm looking up 4 fields.When I go to store the selection after, only 2 are available... know this confusing; here is the Select Statement;
SELECT [ITEM CODE1].[ItemCode],[ItemCode],[Customer],[Project Description] FROM [ITEM CODE1] ORDER BY [ITEM CODE1].[ItemCode] ;
If I don't type ItemCode twice, it shows Project Description in the drop down box. When I use the code above, only ItemCode and Customer are available to store in other fields;
Me.Customer = Me.ComID.Column(2) which actually stores Customer (should be column 3)
Column(3) and Column(4) won't do anything.
View 3 Replies
View Related
Mar 13, 2014
Payroll main form to post attendance record. Before I open the attendance form, I wanted to check if there is a record in the sql statement.
The code below produces error MS Acces object "Variable or With Block Variable Not Set"
Code:
Private Sub cmdPostAtt_Click()
Dim stSQL As String
Dim dbHBK As DAO.Database
[Code].....
View 3 Replies
View Related
