Writing a code in Expression builder in an query in Access

[dwayne94]

Column A
Fa Location Code(css) - Field name
10068082
10068651
10068760
10068788
10076550
10090280
10090940
10094492

Column B
DupFASiteNumber - Field name
100663-A
99965-A
103039-A
101289-A
80994-A
99952-A
93735-A
99173-A

Column C
Dup FA Action - Field name
In CSS Surviving Record
In CSS Surviving Record
In CSS Move FA To Site Number 50996-A
In CSS Move FA To Site Number 51791-A
In CSS Move FA To Site Number 9475-A
In CSS Surviving Record
In CSS Surviving Record
In CSS Move FA To Site Number 102286-A

Column D
DupUsidSiteNumber - Field name
51065-A
93004-A
50996-A
51791-A
9475-A
88640-A
85521-A
25740-A

Column E
Dup USID Action - Field name
Surviving Record | Append Documents
Surviving Record | Append Documents
Surviving Record | Append Documents
Surviving Record | Append Documents
Surviving Record
Surviving Record | Append Documents
Surviving Record | Append Documents
Surviving Record | Append Documents

Column F
Update Modification - Field name
Under Dup FA 100663-A, Under Dup USID 51065-A (first record)


Under Dup FA 102286-A, Under Dup USID , Under Dup USID 25740-A - (Last record)

From the data above please copy the columns accordingly in Excel. I need some assistance...I need to write an code in a query in Access (Expression builder). Look at column B and look at column D. If both columns have the words Surving Records then column F will say the following:
Under Dup FA (column B,) Under Dup USID (Column D)

How would I write this code?

 

[lameid]

IIF in Access is like the IF function in Excel.
Instr searches a string and returns the position in the string and it returns 0 if the text is not found...

I understood the test you wanted to perform but not what you wanted it to display when it does match and when it doesn't.
The below will get you started, you still need to complete the true part and the false part...

IIF(instr(1,DupFASiteNumber, "Surving Records") > 0 AND instr(1,DupUsidSiteNumber , "Surving Records") > 0,,)

Also there are multiple forums for Access. Next time please choose the one appropriate for your object, in this case, forum701

 

[dwayne94]

Thanks for your assistance...I wanted the end result to show in column F Under Dup FA 100663-A, Under Dup USID 51065-A. That means that they both matched. If it does not match (as the last entry show in coulmn B and D then the end result in column F will say Under Dup FA 102286-A, Under Dup USID , Under Dup USID 25740-A.

 

[lameid]

I think you are saying you want to cancatenate literal text in with the field value... You've already seen how to use fields in the piece I gave you...

The amperstand (&) concatenates or puts to strings togehter.
IF FieldName has a value of Bob then the below would evaluate to Some Text Bob

"Some Text " & FieldName

 

[dwayne94]

I have another question for you. In Access I have a query and I am trying to add a field. basically I am trying to write a code for the following scenario.

I have a field name called SITE STATUS and it may be ON- OFF- PENDING or NO STATUS. I am trying to write a code in a Access query

1) If Site Status = "ON" then the FA Status should say "Conflict w/ FA Master | Site Status ON"

 

[dhookom]

Try use IIf()

SELECT ...., IIf([SITE STATUS]="ON","Conflict w/ FA Master | Site Status ON",[FA Status]) as NewFAStatus, ....
FROM....

Duane
Hook'D on Access
MS Access MVP

 

[dwayne94]

Is this for the Expression Builder in the query?? This is what I have

FA STATUS: IIf([ZZ_DuplicatedFADeleteOutput(971)]![Usid(css)] In ([FA_Master_11i_FB]![SITE_CODE]) = "ON", "No Confllict w/ FA Master | Site Status ON", [FA Status] And [ZZ_DuplicateFAManualDeleteOutput]![Usid(css)] " "))

 

[lameid]

If Duane's code was in the expression builder it would be...

NewFAStatus: IIf([SITE STATUS]="ON","Conflict w/ FA Master | Site Status ON",[FA Status])

It says

IF [SITE STATUS]="ON"
Then display "Conflict w/ FA Master | Site Status ON"
Otherwise display [FA Status]

Your expression builder has an AND and an IN that I do not know what you are trying to do.

 

[dwayne94]

Thanks that worked fine. One more question in addition to IF [SITE STATUS]="ON"
Then display "Conflict w/ FA Master | Site Status ON"
Otherwise display [FA Status]
NewFAStatus: IIf([SITE STATUS]="ON","Conflict w/ FA Master | Site Status ON",[FA Status])

If want to add IF [SITE STATUS]="OFF"
Then display "Conflict w/ FA Master | Site Status OFF"
Otherwise display [FA Status]

IF [SITE STATUS]="PENDING OFF"
Then display "Conflict w/ FA Master | Site Status ON"
Otherwise display [FA Status]

how would I add to this expression NewFAStatus: IIf([SITE STATUS]="ON","Conflict w/ FA Master | Site Status ON",[FA Status])

 

[lameid]

3 options

Nest the IIF functions so that each next value is tested in the third or fals parameter.

Make a table that has a list of the Site Status and the value you want to show. Join to this table and display the second field.

Lastly write a function using select Case that returns the value you want.

I lean to the second option of creating a table but may not necessarily make sense.

 

[dhookom]

dwayne,
If you want to create "business rules" regarding the presentation of your data, I would stop trying to do this in an expression in the query. I would probably move this to a public function in a module "modBusinessCalcs".

Your function would accept the appropriate field values as arguments and return the appropriate output for display. Then if [red]when[/red] your rules change, you shouldn't have to find the queries and other places you have applied them to make changes. Just open your module and make your changes in one, easily maintainable place. You can also add comments to document your rules.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

Duane, Are you saying that a manager would suggest that business rules are immutable and then change his mind sometime down the road? That hasn't happened to me in at least fifteen minutes Good point though.

On a more serious note, tabularizing the values has the same kind of effect. You only have to change them in one place, the table. And of course Select Case is the way to go if writing the code in a central module.

 

[dhookom]

I was going to use the term pointed-haired boss but decided against it since I didn't want to offend any possible phb's. I generally prefer storing this type of logic in tables when possible. One of my favorite discussions on this is Data belongs in your tables -- not in your code

Duane
Hook'D on Access
MS Access MVP