A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Update - If statement



 
 
Thread Tools Display Modes
  #1  
Old December 13th, 2004, 06:01 PM
Dan @BCBS
external usenet poster
 
Posts: n/a
Default 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  
Old December 13th, 2004, 06:10 PM
Ken Snell [MVP]
external usenet poster
 
Posts: n/a
Default

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  
Old December 13th, 2004, 06:37 PM
Dan @BCBS
external usenet poster
 
Posts: n/a
Default

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  
Old December 13th, 2004, 06:53 PM
Ken Snell [MVP]
external usenet poster
 
Posts: n/a
Default

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  
Old December 13th, 2004, 07:11 PM
Dan @BCBS
external usenet poster
 
Posts: n/a
Default

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  
Old December 13th, 2004, 07:29 PM
Ken Snell [MVP]
external usenet poster
 
Posts: n/a
Default

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  
Old December 13th, 2004, 08:23 PM
Dan @BCBS
external usenet poster
 
Posts: n/a
Default

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  
Old December 13th, 2004, 09:20 PM
Ken Snell [MVP]
external usenet poster
 
Posts: n/a
Default

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  
Old December 13th, 2004, 10:13 PM
Dan @BCBS
external usenet poster
 
Posts: n/a
Default

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  
Old December 14th, 2004, 04:28 AM
Ken Snell [MVP]
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 09:30 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.