Use of updated field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to update a field and then use it in an "IIF" statement to update a
second field. However, when I access the updated field in the IIF statement
it does not seem to have the new value. Any ideas?
 
Stukmeister said:
I want to update a field and then use it in an "IIF" statement to update a
second field. However, when I access the updated field in the IIF statement
it does not seem to have the new value. Any ideas?
Click to expand...

Stukmeister,

Can you post a copy of the VBA or SQL, please.

But from the description, it sounds like you are attempting to do
something . . . odd. Each Update statement is entirely
self-contained. You can't access an "updated" field in the same
Update, because no Update happens until the entire statement is over.


Sincerely,

Chris O.
 
I want "Address Key" to have the combination of the fields that has just been
updated.

SET D.[Address 1] = IIf(([address 1] Is Null or [address 1] like "addrs" or
[address 1] like "address")
And [country]="UN1","xxx",[address 1]),
D.City = IIf([city] Is Null And [country]="UN1","xxx",[city]),
D.State = IIf([state] Is Null And [country]="UN1","xxx",[state]),
D.ZIP = IIf([zip] Is Null And [country]="UN1","xxx",[zip]),
D.[Address Key] = ([state] & "-" & [city] & "-" & [address 1])
 
In old ISAM databases, compound keys were created by concatenating two or
more fields into a single field - there was no other way. In RDBMS' such as
Access that technique violates several important normalization "rules". The
preferred technique is to simply select each field - hold the cntl key as you
select the fields and when all the fields are highlighted, press the key
icon. This creates a multi-field primary key without duplicating data.

Stukmeister said:
I want "Address Key" to have the combination of the fields that has just been
updated.

SET D.[Address 1] = IIf(([address 1] Is Null or [address 1] like "addrs" or
[address 1] like "address")
And [country]="UN1","xxx",[address 1]),
D.City = IIf([city] Is Null And [country]="UN1","xxx",[city]),
D.State = IIf([state] Is Null And [country]="UN1","xxx",[state]),
D.ZIP = IIf([zip] Is Null And [country]="UN1","xxx",[zip]),
D.[Address Key] = ([state] & "-" & [city] & "-" & [address 1])


Chris2 said:
Stukmeister,

Can you post a copy of the VBA or SQL, please.

But from the description, it sounds like you are attempting to do
something . . . odd. Each Update statement is entirely
self-contained. You can't access an "updated" field in the same
Update, because no Update happens until the entire statement is over.


Sincerely,

Chris O.
Click to expand...
Click to expand...
 
Stukmeister said:
I want "Address Key" to have the combination of the fields that has just been
updated.
Click to expand...

Stukmeister,

UPDATE ??
SET D.[Address 1] =
IIf(( [address 1] Is Null
or [address 1] like "addrs"
or [address 1] like "address")
And [country] = "UN1"
,"xxx"
,[address 1])
,D.City = IIf( [city] Is Null
And [country] = "UN1"
,"xxx"
,[city])
,D.State = IIf( [state] Is Null
And [country] = "UN1"
,"xxx"
,[state])
,D.ZIP = IIf( [zip] Is Null
And [country] = "UN1"
,"xxx"
,[zip])
,D.[Address Key] = ([state] & "-" & [city] & "-" & [address 1])

I think I see, now.

The only way to load [Address Key] with the same information that was
loaded into the previous three fields to make *exactly* the same
decisions while loading it.


,D.[Address Key] =
(IIf( [state] Is Null
And [country] = "UN1"
,"xxx"
,[state])
& "-" &
IIf( [city] Is Null
And [country] = "UN1"
,"xxx"
,[city])
& "-" &
IIf(( [address 1] Is Null
or [address 1] like "addrs"
or [address 1] like "address")
And [country] = "UN1"
,"xxx"
,[address 1]))

Basically, I took the IIf statments that were used to load the
previous fields, and dropped them into the appropriate slots.

It's untested, of course. Try it out.


Sincerely,

Chris O.
 
I am new to Access sql, so this may be a dumb question ... but is there a way
to load "work" or "temp" fields? Can I load say "waddr1" with the info once
and then use waddr1 for "Address 1" and "Address Key".

Chris2 said:
Stukmeister said:
I want "Address Key" to have the combination of the fields that has just been
updated.
Click to expand...

Stukmeister,

UPDATE ??
SET D.[Address 1] =
IIf(( [address 1] Is Null
or [address 1] like "addrs"
or [address 1] like "address")
And [country] = "UN1"
,"xxx"
,[address 1])
,D.City = IIf( [city] Is Null
And [country] = "UN1"
,"xxx"
,[city])
,D.State = IIf( [state] Is Null
And [country] = "UN1"
,"xxx"
,[state])
,D.ZIP = IIf( [zip] Is Null
And [country] = "UN1"
,"xxx"
,[zip])
,D.[Address Key] = ([state] & "-" & [city] & "-" & [address 1])

I think I see, now.

The only way to load [Address Key] with the same information that was
loaded into the previous three fields to make *exactly* the same
decisions while loading it.


,D.[Address Key] =
(IIf( [state] Is Null
And [country] = "UN1"
,"xxx"
,[state])
& "-" &
IIf( [city] Is Null
And [country] = "UN1"
,"xxx"
,[city])
& "-" &
IIf(( [address 1] Is Null
or [address 1] like "addrs"
or [address 1] like "address")
And [country] = "UN1"
,"xxx"
,[address 1]))

Basically, I took the IIf statments that were used to load the
previous fields, and dropped them into the appropriate slots.

It's untested, of course. Try it out.


Sincerely,

Chris O.
Click to expand...
 
Is there some reason you don't want to do this the right way?

Stukmeister said:
I am new to Access sql, so this may be a dumb question ... but is there a way
to load "work" or "temp" fields? Can I load say "waddr1" with the info once
and then use waddr1 for "Address 1" and "Address Key".

Chris2 said:
Stukmeister said:
I want "Address Key" to have the combination of the fields that has just been
updated.
Click to expand...

Stukmeister,

UPDATE ??
SET D.[Address 1] =
IIf(( [address 1] Is Null
or [address 1] like "addrs"
or [address 1] like "address")
And [country] = "UN1"
,"xxx"
,[address 1])
,D.City = IIf( [city] Is Null
And [country] = "UN1"
,"xxx"
,[city])
,D.State = IIf( [state] Is Null
And [country] = "UN1"
,"xxx"
,[state])
,D.ZIP = IIf( [zip] Is Null
And [country] = "UN1"
,"xxx"
,[zip])
,D.[Address Key] = ([state] & "-" & [city] & "-" & [address 1])

I think I see, now.

The only way to load [Address Key] with the same information that was
loaded into the previous three fields to make *exactly* the same
decisions while loading it.


,D.[Address Key] =
(IIf( [state] Is Null
And [country] = "UN1"
,"xxx"
,[state])
& "-" &
IIf( [city] Is Null
And [country] = "UN1"
,"xxx"
,[city])
& "-" &
IIf(( [address 1] Is Null
or [address 1] like "addrs"
or [address 1] like "address")
And [country] = "UN1"
,"xxx"
,[address 1]))

Basically, I took the IIf statments that were used to load the
previous fields, and dropped them into the appropriate slots.

It's untested, of course. Try it out.


Sincerely,

Chris O.
Click to expand...
Click to expand...
 
Stukmeister said:
I am new to Access sql, so this may be a dumb question ... but is there a way
to load "work" or "temp" fields?
Click to expand...

Stukmeister,

Not in an SQL statement. No.
Can I load say "waddr1" with the info once
and then use waddr1 for "Address 1" and "Address Key".
Click to expand...

It sounds to me like you'd like to run some VBA code to handle
intermediate data, but that'll require some DAO know-how.

A simpler course would be to take those gigantic IIF statements, and
hide them behind VBA.


UPDATE ??
SET D.[Address 1] =
IIf(( [address 1] Is Null
or [address 1] like "addrs"
or [address 1] like "address")
And [country] = "UN1"
,"xxx"
,[address 1])
,D.City = IIf( [city] Is Null
And [country] = "UN1"
,"xxx"
,[city])
,D.State = IIf( [state] Is Null
And [country] = "UN1"
,"xxx"
,[state])
,D.ZIP = IIf( [zip] Is Null
And [country] = "UN1"
,"xxx"
,[zip])
,D.[Address Key] = ([state] & "-" & [city] & "-" & [address 1])


(Note: I'm using *really* trumped up function and variable names,
here.)

Air Code:

Public Function LoadCity(strCity as String _
,strCountry as String) AS String

dim strLoadCity AS String

strLoadCity = IIf(IsNull(strCity) AND _
strCountry = "UN1"
,"xxx"
,strCity)

LoadCity = strLaod

End Function

And etc. for State, Zip, and [Address 1].


UPDATE ??
SET [Address1] = LoadAddress1([address 1], [country])
,City = LoadCity([city], [country])
,State = LoadState([state], [country])
,ZIP = LoadZip([zip], [country])
,[Address Key] = LoadState([state], [country]) & "-" &
LoadCity([city], [country]) & "-" &
LoadAddress1([address 1], [country])

There, that looks much better, should do the same thing (it's
untested, of course, so please check it out). This will be slower,
though.


Sincerely,

Chris O.
 
Back
Top