If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update - If statement
My data is Y or N, I need to Update the data to: If "Y" then -1 Else If "N"
then 0 Else NA.. Can I add an experssion to do this, if so, what would it look like.. Does the expression go in the Update To: Field???? |
#2
|
|||
|
|||
Are you changing the data in the same field that has the Y or N in it?
Is that field a boolean or text field? Please, a bit more info so that we can suggest how to do what you want. -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... My data is Y or N, I need to Update the data to: If "Y" then -1 Else If "N" then 0 Else NA.. Can I add an experssion to do this, if so, what would it look like.. Does the expression go in the Update To: Field???? |
#3
|
|||
|
|||
Yes - I want to change the same field (Y to -1) and (N to 0) and (D to N) - N
because N/A will not work. Yes - it is Text. Once I change the field I want to merge the data into another table, that is why I am changing the data. Thanks "Ken Snell [MVP]" wrote: Are you changing the data in the same field that has the Y or N in it? Is that field a boolean or text field? Please, a bit more info so that we can suggest how to do what you want. -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... My data is Y or N, I need to Update the data to: If "Y" then -1 Else If "N" then 0 Else NA.. Can I add an experssion to do this, if so, what would it look like.. Does the expression go in the Update To: Field???? |
#4
|
|||
|
|||
OK:
UPDATE TableName SET FieldName = IIf([FieldName]="Y", "-1", IIf([FieldName]="N", "0", IIf([FieldName]="D", "N", [FieldName]))); -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... Yes - I want to change the same field (Y to -1) and (N to 0) and (D to N) - N because N/A will not work. Yes - it is Text. Once I change the field I want to merge the data into another table, that is why I am changing the data. Thanks "Ken Snell [MVP]" wrote: Are you changing the data in the same field that has the Y or N in it? Is that field a boolean or text field? Please, a bit more info so that we can suggest how to do what you want. -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... My data is Y or N, I need to Update the data to: If "Y" then -1 Else If "N" then 0 Else NA.. Can I add an experssion to do this, if so, what would it look like.. Does the expression go in the Update To: Field???? |
#5
|
|||
|
|||
OK, this is the expresion I put into the Criteria.
But, when I run this I get an error requesting the destination field, obviously I cannot put Y - N or D... "Ken Snell [MVP]" wrote: OK: UPDATE TableName SET FieldName = IIf([FieldName]="Y", "-1", IIf([FieldName]="N", "0", IIf([FieldName]="D", "N", [FieldName]))); -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... Yes - I want to change the same field (Y to -1) and (N to 0) and (D to N) - N because N/A will not work. Yes - it is Text. Once I change the field I want to merge the data into another table, that is why I am changing the data. Thanks "Ken Snell [MVP]" wrote: Are you changing the data in the same field that has the Y or N in it? Is that field a boolean or text field? Please, a bit more info so that we can suggest how to do what you want. -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... My data is Y or N, I need to Update the data to: If "Y" then -1 Else If "N" then 0 Else NA.. Can I add an experssion to do this, if so, what would it look like.. Does the expression go in the Update To: Field???? |
#6
|
|||
|
|||
No, what I posted is the entire query's SQL statement. The Criteria: box
should be empty on your grid after you create this query. To set it up in design view of the query, select the table. Put this expression (after changing FieldName to the real name of the field being updated) in the UpdateTo: box: IIf([FieldName]="Y", "-1", IIf([FieldName]="N", "0", IIf([FieldName]="D", "N", [FieldName]))) -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... OK, this is the expresion I put into the Criteria. But, when I run this I get an error requesting the destination field, obviously I cannot put Y - N or D... "Ken Snell [MVP]" wrote: OK: UPDATE TableName SET FieldName = IIf([FieldName]="Y", "-1", IIf([FieldName]="N", "0", IIf([FieldName]="D", "N", [FieldName]))); -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... Yes - I want to change the same field (Y to -1) and (N to 0) and (D to N) - N because N/A will not work. Yes - it is Text. Once I change the field I want to merge the data into another table, that is why I am changing the data. Thanks "Ken Snell [MVP]" wrote: Are you changing the data in the same field that has the Y or N in it? Is that field a boolean or text field? Please, a bit more info so that we can suggest how to do what you want. -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... My data is Y or N, I need to Update the data to: If "Y" then -1 Else If "N" then 0 Else NA.. Can I add an experssion to do this, if so, what would it look like.. Does the expression go in the Update To: Field???? |
#7
|
|||
|
|||
With your help, I'm so closer..
Let me explain my situation: Table 1 - Type=Number and Size=Byte Table 2 - Type=Text and Size=1 I need to merge Table 2 into 1. Is this what you suggest: Step 1- Update Query - Table 2 (Y=-1, N=0, D=N). Step 2- Rename Table 1 Step 3- Create new Table 1 (because the forms/reports already exist). Step 4- New Update Query with all 3 tables ??? This is where I get stuck, how do I get table 2 data into table 1.???? "Ken Snell [MVP]" wrote: No, what I posted is the entire query's SQL statement. The Criteria: box should be empty on your grid after you create this query. To set it up in design view of the query, select the table. Put this expression (after changing FieldName to the real name of the field being updated) in the UpdateTo: box: IIf([FieldName]="Y", "-1", IIf([FieldName]="N", "0", IIf([FieldName]="D", "N", [FieldName]))) -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... OK, this is the expresion I put into the Criteria. But, when I run this I get an error requesting the destination field, obviously I cannot put Y - N or D... "Ken Snell [MVP]" wrote: OK: UPDATE TableName SET FieldName = IIf([FieldName]="Y", "-1", IIf([FieldName]="N", "0", IIf([FieldName]="D", "N", [FieldName]))); -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... Yes - I want to change the same field (Y to -1) and (N to 0) and (D to N) - N because N/A will not work. Yes - it is Text. Once I change the field I want to merge the data into another table, that is why I am changing the data. Thanks "Ken Snell [MVP]" wrote: Are you changing the data in the same field that has the Y or N in it? Is that field a boolean or text field? Please, a bit more info so that we can suggest how to do what you want. -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... My data is Y or N, I need to Update the data to: If "Y" then -1 Else If "N" then 0 Else NA.. Can I add an experssion to do this, if so, what would it look like.. Does the expression go in the Update To: Field???? |
#8
|
|||
|
|||
Let's back up. You're now talking about two tables; but in an earlier post,
you answered my question about this by saying that you were changing the data in the same field -- nothing was said about two tables. Let's go back to the beginning. State clearly what you want to achieve -- such as I want to set the value of a field field name in a table table name based on the current value in a field field name in another table table name , etc. Give examples of the data in the first table, and what the data should be in the second table, etc. -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... With your help, I'm so closer.. Let me explain my situation: Table 1 - Type=Number and Size=Byte Table 2 - Type=Text and Size=1 I need to merge Table 2 into 1. Is this what you suggest: Step 1- Update Query - Table 2 (Y=-1, N=0, D=N). Step 2- Rename Table 1 Step 3- Create new Table 1 (because the forms/reports already exist). Step 4- New Update Query with all 3 tables ??? This is where I get stuck, how do I get table 2 data into table 1.???? "Ken Snell [MVP]" wrote: No, what I posted is the entire query's SQL statement. The Criteria: box should be empty on your grid after you create this query. To set it up in design view of the query, select the table. Put this expression (after changing FieldName to the real name of the field being updated) in the UpdateTo: box: IIf([FieldName]="Y", "-1", IIf([FieldName]="N", "0", IIf([FieldName]="D", "N", [FieldName]))) -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... OK, this is the expresion I put into the Criteria. But, when I run this I get an error requesting the destination field, obviously I cannot put Y - N or D... "Ken Snell [MVP]" wrote: OK: UPDATE TableName SET FieldName = IIf([FieldName]="Y", "-1", IIf([FieldName]="N", "0", IIf([FieldName]="D", "N", [FieldName]))); -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... Yes - I want to change the same field (Y to -1) and (N to 0) and (D to N) - N because N/A will not work. Yes - it is Text. Once I change the field I want to merge the data into another table, that is why I am changing the data. Thanks "Ken Snell [MVP]" wrote: Are you changing the data in the same field that has the Y or N in it? Is that field a boolean or text field? Please, a bit more info so that we can suggest how to do what you want. -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... My data is Y or N, I need to Update the data to: If "Y" then -1 Else If "N" then 0 Else NA.. Can I add an experssion to do this, if so, what would it look like.. Does the expression go in the Update To: Field???? |
#9
|
|||
|
|||
I have two databases.
I need to add the data from Database 2 into Database 1, (just one table). The fields in both tables are Y/N/D, the problem is: Table 1 - Type=Number (Y/N/D's were entered) Table 2 - Type=Text (Y/N/D's were entered) And I cannot change any of the data in Table 1. I ran the update Query to change Table 2 from Y/N/D to 1/0/N. Done. Was this a waste of time, I thought if I update Table 2, I could merge the data. But did I update it to something that can be merged together? I want to set the value of a field ADVDIR in a table NGBU name based on the current value in a field ADVRDIR in table SGBU SGBU Table Example = field ADVDIR format Number. NGBU Table Example = field ADVDIR format Text. I just want to add the results of NGBU into SGBU. I hope I explained it better, my desired outcome is very simple. Thanks for your patience. "Ken Snell [MVP]" wrote: Let's back up. You're now talking about two tables; but in an earlier post, you answered my question about this by saying that you were changing the data in the same field -- nothing was said about two tables. Let's go back to the beginning. State clearly what you want to achieve -- such as I want to set the value of a field field name in a table table name based on the current value in a field field name in another table table name , etc. Give examples of the data in the first table, and what the data should be in the second table, etc. -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... With your help, I'm so closer.. Let me explain my situation: Table 1 - Type=Number and Size=Byte Table 2 - Type=Text and Size=1 I need to merge Table 2 into 1. Is this what you suggest: Step 1- Update Query - Table 2 (Y=-1, N=0, D=N). Step 2- Rename Table 1 Step 3- Create new Table 1 (because the forms/reports already exist). Step 4- New Update Query with all 3 tables ??? This is where I get stuck, how do I get table 2 data into table 1.???? "Ken Snell [MVP]" wrote: No, what I posted is the entire query's SQL statement. The Criteria: box should be empty on your grid after you create this query. To set it up in design view of the query, select the table. Put this expression (after changing FieldName to the real name of the field being updated) in the UpdateTo: box: IIf([FieldName]="Y", "-1", IIf([FieldName]="N", "0", IIf([FieldName]="D", "N", [FieldName]))) -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... OK, this is the expresion I put into the Criteria. But, when I run this I get an error requesting the destination field, obviously I cannot put Y - N or D... "Ken Snell [MVP]" wrote: OK: UPDATE TableName SET FieldName = IIf([FieldName]="Y", "-1", IIf([FieldName]="N", "0", IIf([FieldName]="D", "N", [FieldName]))); -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... Yes - I want to change the same field (Y to -1) and (N to 0) and (D to N) - N because N/A will not work. Yes - it is Text. Once I change the field I want to merge the data into another table, that is why I am changing the data. Thanks "Ken Snell [MVP]" wrote: Are you changing the data in the same field that has the Y or N in it? Is that field a boolean or text field? Please, a bit more info so that we can suggest how to do what you want. -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... My data is Y or N, I need to Update the data to: If "Y" then -1 Else If "N" then 0 Else NA.. Can I add an experssion to do this, if so, what would it look like.. Does the expression go in the Update To: Field???? |
#10
|
|||
|
|||
ACCESS is not designed to allow the storage of letters in a number field in
a table. How did you get such results in Table 1? If indeed you have letters in the field in Table 1, and indeed that field has a data type of Number, then the table probably is corrupted and the data will need to be put into a new table. -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... I have two databases. I need to add the data from Database 2 into Database 1, (just one table). The fields in both tables are Y/N/D, the problem is: Table 1 - Type=Number (Y/N/D's were entered) Table 2 - Type=Text (Y/N/D's were entered) And I cannot change any of the data in Table 1. I ran the update Query to change Table 2 from Y/N/D to 1/0/N. Done. Was this a waste of time, I thought if I update Table 2, I could merge the data. But did I update it to something that can be merged together? I want to set the value of a field ADVDIR in a table NGBU name based on the current value in a field ADVRDIR in table SGBU SGBU Table Example = field ADVDIR format Number. NGBU Table Example = field ADVDIR format Text. I just want to add the results of NGBU into SGBU. I hope I explained it better, my desired outcome is very simple. Thanks for your patience. "Ken Snell [MVP]" wrote: Let's back up. You're now talking about two tables; but in an earlier post, you answered my question about this by saying that you were changing the data in the same field -- nothing was said about two tables. Let's go back to the beginning. State clearly what you want to achieve -- such as I want to set the value of a field field name in a table table name based on the current value in a field field name in another table table name , etc. Give examples of the data in the first table, and w hat the data should be in the second table, etc. -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... With your help, I'm so closer.. Let me explain my situation: Table 1 - Type=Number and Size=Byte Table 2 - Type=Text and Size=1 I need to merge Table 2 into 1. Is this what you suggest: Step 1- Update Query - Table 2 (Y=-1, N=0, D=N). Step 2- Rename Table 1 Step 3- Create new Table 1 (because the forms/reports already exist). Step 4- New Update Query with all 3 tables ??? This is where I get stuck, how do I get table 2 data into table 1.???? "Ken Snell [MVP]" wrote: No, what I posted is the entire query's SQL statement. The Criteria: box should be empty on your grid after you create this query. To set it up in design view of the query, select the table. Put this expression (after changing FieldName to the real name of the field being updated) in the UpdateTo: box: IIf([FieldName]="Y", "-1", IIf([FieldName]="N", "0", IIf([FieldName]="D", "N", [FieldName]))) -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... OK, this is the expresion I put into the Criteria. But, when I run this I get an error requesting the destination field, obviously I cannot put Y - N or D... "Ken Snell [MVP]" wrote: OK: UPDATE TableName SET FieldName = IIf([FieldName]="Y", "-1", IIf([FieldName]="N", "0", IIf([FieldName]="D", "N", [FieldName]))); -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... Yes - I want to change the same field (Y to -1) and (N to 0) and (D to N) - N because N/A will not work. Yes - it is Text. Once I change the field I want to merge the data into another table, that is why I am changing the data. Thanks "Ken Snell [MVP]" wrote: Are you changing the data in the same field that has the Y or N in it? Is that field a boolean or text field? Please, a bit more info so that we can suggest how to do what you want. -- Ken Snell MS ACCESS MVP "Dan @BCBS" wrote in message ... My data is Y or N, I need to Update the data to: If "Y" then -1 Else If "N" then 0 Else NA.. Can I add an experssion to do this, if so, what would it look like.. Does the expression go in the Update To: Field???? |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Calculated Mean | Charles Deng | Running & Setting Up Queries | 21 | October 15th, 2004 08:37 PM |
how come I can't update office 2003 | pghsteelersfan | Powerpoint | 2 | September 9th, 2004 02:11 AM |
Can't update table from Form | sara | Using Forms | 3 | June 11th, 2004 02:12 PM |
Access 2000 query SQL statement into VBA code | Clint | Running & Setting Up Queries | 1 | June 10th, 2004 01:33 PM |
Why no update? Help! | Brian | New Users | 1 | May 6th, 2004 11:15 AM |