Conditional Moves

[Guest]

I have two inputs that I want to join using queries. Normally, no problem
but I would like to have a conditional move.

ie. I want to move field1 to field2 if fielda = fieldb.

How do I attack this?

Thanks again.

 

[Guest]

Dear Bunky,

In your query have a field that is a expression.The field gets it's value
through a IIF statement that determines which input field to use as the value
for that expression. Below is a example of a expression that I used:

Expression2: IIf([fielda]=[fieldb],[field1],[field2])
If the fielda=fieldb, then output field1 else output field2 as the value of
Expression2

Expression2 could also be the new name of field2

Of course the source for this query must have field1 and field2 and fielda
and fieldb available.

Hope that helps jump start your project,
Steve

 

[Guest]

Thank you, Steven.
That works.

 

[Guest]

One more thing, Steven.

How would I say

If fieldA = fieldB, move field1 to field2 else move field3 to field2.

Sorry to trouble you again, but the syntax is giving me fits.

Thanks

Thank you, Steven.
That works.

Dear Bunky,

In your query have a field that is a expression.The field gets it's value
through a IIF statement that determines which input field to use as the value
for that expression. Below is a example of a expression that I used:

Expression2: IIf([fielda]=[fieldb],[field1],[field2])
If the fielda=fieldb, then output field1 else output field2 as the value of
Expression2

Expression2 could also be the new name of field2

Of course the source for this query must have field1 and field2 and fielda
and fieldb available.

Hope that helps jump start your project,
Steve

 

[Guest]

Hi

Are you working on a form or a query ? It sound like you're on a form

 

[Guest]

Hi Wayne!

Nope, Working on a query in design form.

 

[Guest]

Am a little confused by this

Field2:IIf ( [TableName]![FieldA] = [TableName]![FieldB] , [TableName
]![Field1] , [TableName]![Field3] )

Do you already have a field called Field2 if so - what is in it. You may
need to UpdateQuery this if there is something in the field that you want to
replace. In this case the above would not work - you need to set the
criteria and then Update. But as I can't your query I can't give other
advice without more info

You should use the full TableName and FieldName in calculations - sooner or
later you will want to find out where the results come from and if you use
the full details it will be much simpler.

Good luck

 

[Guest]

Wayne,

I am writing an expression in a query in design view.

GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &
Year],[startpoints],[sumofpoints])

This works fine but I do not have the else condition just the true condition
I want it to say something like this

GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &
Year],[startpoints],[sumofpoints] else [points],[sumofpoints)

I know the syntax is wrong but I do not know what the right syntax would be.
Sorry I was not clear and thank you for your assistance.

Am a little confused by this

Field2:IIf ( [TableName]![FieldA] = [TableName]![FieldB] , [TableName
]![Field1] , [TableName]![Field3] )

Do you already have a field called Field2 if so - what is in it. You may
need to UpdateQuery this if there is something in the field that you want to
replace. In this case the above would not work - you need to set the
criteria and then Update. But as I can't your query I can't give other
advice without more info

You should use the full TableName and FieldName in calculations - sooner or
later you will want to find out where the results come from and if you use
the full details it will be much simpler.

Good luck


--
Wayne
Manchester, England.

Hi Wayne!

Nope, Working on a query in design form.

 

[Guest]

GoodPts: If ( ([ StartAttWeek] = [ AttendWeek] And [ StartAttYear] = [
AttendYear] ) , [ startpoints] , [ sumofpoints] )

I have taken out the table names for clarity. BUT
I don't think this will work?
Where are you getting the SumOfPoints (is this a subquery). Do you have a
year field for the Start and another for year Attend ??
You may be better not to use a year but simply use a time/date field in the
table. I think you may need t look at how the tables and querys are built.


--
Wayne
Manchester, England.

Wayne,

I am writing an expression in a query in design view.

GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &
Year],[startpoints],[sumofpoints])

This works fine but I do not have the else condition just the true condition
I want it to say something like this

GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &
Year],[startpoints],[sumofpoints] else [points],[sumofpoints)

I know the syntax is wrong but I do not know what the right syntax would be.
Sorry I was not clear and thank you for your assistance.

Am a little confused by this

Field2:IIf ( [TableName]![FieldA] = [TableName]![FieldB] , [TableName
]![Field1] , [TableName]![Field3] )

Do you already have a field called Field2 if so - what is in it. You may
need to UpdateQuery this if there is something in the field that you want to
replace. In this case the above would not work - you need to set the
criteria and then Update. But as I can't your query I can't give other
advice without more info

You should use the full TableName and FieldName in calculations - sooner or
later you will want to find out where the results come from and if you use
the full details it will be much simpler.

Good luck


--
Wayne
Manchester, England.

Hi Wayne!

Nope, Working on a query in design form.

:

Hi

Are you working on a form or a query ? It sound like you're on a form


--
Wayne
Manchester, England.



:

I have two inputs that I want to join using queries. Normally, no problem
but I would like to have a conditional move.

ie. I want to move field1 to field2 if fielda = fieldb.

How do I attack this?

Thanks again.

 

[Guest]

can you post the table and field names


--
Wayne
Manchester, England.

Good Morning Wayne,

GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &
Year],[startpoints],[sumofpoints])

This is a query that is using data from several subqueries. The names
StartAttWeek & Year / AttendWeek & Yr represents an interval week and yr
formated like '0722' for a week ending date of 06/02/07. But all this is not
relevant to my problem.

I want a query expression to say
GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &

Year],[startpoints],[sumofpoints] ELSE do something else like the field points to the field sumofpoints)

Sorry if I am not being specific enough.
Thanks,

GoodPts: If ( ([ StartAttWeek] = [ AttendWeek] And [ StartAttYear] = [
AttendYear] ) , [ startpoints] , [ sumofpoints] )

I have taken out the table names for clarity. BUT
I don't think this will work?
Where are you getting the SumOfPoints (is this a subquery). Do you have a
year field for the Start and another for year Attend ??
You may be better not to use a year but simply use a time/date field in the
table. I think you may need t look at how the tables and querys are built.


--
Wayne
Manchester, England.

Wayne,

I am writing an expression in a query in design view.

GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &
Year],[startpoints],[sumofpoints])

This works fine but I do not have the else condition just the true condition
I want it to say something like this

GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &
Year],[startpoints],[sumofpoints] else [points],[sumofpoints)

I know the syntax is wrong but I do not know what the right syntax would be.
Sorry I was not clear and thank you for your assistance.



:

Am a little confused by this

Field2:IIf ( [TableName]![FieldA] = [TableName]![FieldB] , [TableName
]![Field1] , [TableName]![Field3] )

Do you already have a field called Field2 if so - what is in it. You may
need to UpdateQuery this if there is something in the field that you want to
replace. In this case the above would not work - you need to set the
criteria and then Update. But as I can't your query I can't give other
advice without more info

You should use the full TableName and FieldName in calculations - sooner or
later you will want to find out where the results come from and if you use
the full details it will be much simpler.

Good luck


--
Wayne
Manchester, England.



:

Hi Wayne!

Nope, Working on a query in design form.

:

Hi

Are you working on a form or a query ? It sound like you're on a form


--
Wayne
Manchester, England.



:

I have two inputs that I want to join using queries. Normally, no problem
but I would like to have a conditional move.

ie. I want to move field1 to field2 if fielda = fieldb.

How do I attack this?

Thanks again.

 

[Guest]

I think you are looking for an if called a "nested if". You can nest up to 7
but this would mean (if you needed all 7) that you maybe should look at
another way of doing things.

Anyway

Nested If

Say you have a field [FieldA] and you want something to happen if this is
equal to another field [FieldB]

An if calculation will only give you 2 answers – true or false. If
something is true you can do something if it’s false you can do something
else - or ask another question.

Like this

Name:IIf (The expression goes here, The action if true, The action if false)

So in your case it would look like this

Field2:IIf ([FieldA] =[FieldB], [Field1], [Field3])

This will insert the value of [Field1] into the calculated field2 ( [Field2]
) if [FieldA] is equal to [FieldB]. If they are not equal it will insert the
value of [Field3]

Of course you can add to this and “nest†the if’s like this


IIf ([FieldA] =[FieldB], [Field1], IIf ([FieldA]=[FieldC}, [Field4,
[Field5]) )

This means that if [FieldA] is not equal to [FieldB] then ask is [FieldA]
equal to [FieldC] if it is then insert the value of [Field4]. If non of it
is true then insert the value of [Field5].

You can “nest†up to 7 if’s.

Good luck

--
Wayne
Manchester, England.

Wayne,

I cannot do that since everything comes about 5 deep from subqueries.
I have found a work around but still do not know how to do a conditional If.
I just wanted to know the syntax for
If [FieldA] = [FieldB],[Field1],[Field2] ELSE [Field3], [Field2]. This was
to represent if a = b, move field1 to field2
else
move field3 to field2
End IF

Thanks for your time.

can you post the table and field names


--
Wayne
Manchester, England.

Good Morning Wayne,

GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &
Year],[startpoints],[sumofpoints])

This is a query that is using data from several subqueries. The names
StartAttWeek & Year / AttendWeek & Yr represents an interval week and yr
formated like '0722' for a week ending date of 06/02/07. But all this is not
relevant to my problem.

I want a query expression to say
GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &
Year],[startpoints],[sumofpoints] ELSE do something else like the field points to the field sumofpoints)

Sorry if I am not being specific enough.
Thanks,


:

GoodPts: If ( ([ StartAttWeek] = [ AttendWeek] And [ StartAttYear] = [
AttendYear] ) , [ startpoints] , [ sumofpoints] )

I have taken out the table names for clarity. BUT
I don't think this will work?
Where are you getting the SumOfPoints (is this a subquery). Do you have a
year field for the Start and another for year Attend ??
You may be better not to use a year but simply use a time/date field in the
table. I think you may need t look at how the tables and querys are built.


--
Wayne
Manchester, England.



:

Wayne,

I am writing an expression in a query in design view.

GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &
Year],[startpoints],[sumofpoints])

This works fine but I do not have the else condition just the true condition
I want it to say something like this

GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &
Year],[startpoints],[sumofpoints] else [points],[sumofpoints)

I know the syntax is wrong but I do not know what the right syntax would be.
Sorry I was not clear and thank you for your assistance.



:

Am a little confused by this

Field2:IIf ( [TableName]![FieldA] = [TableName]![FieldB] , [TableName
]![Field1] , [TableName]![Field3] )

Do you already have a field called Field2 if so - what is in it. You may
need to UpdateQuery this if there is something in the field that you want to
replace. In this case the above would not work - you need to set the
criteria and then Update. But as I can't your query I can't give other
advice without more info

You should use the full TableName and FieldName in calculations - sooner or
later you will want to find out where the results come from and if you use
the full details it will be much simpler.

Good luck


--
Wayne
Manchester, England.



:

Hi Wayne!

Nope, Working on a query in design form.

:

Hi

Are you working on a form or a query ? It sound like you're on a form


--
Wayne
Manchester, England.



:

I have two inputs that I want to join using queries. Normally, no problem
but I would like to have a conditional move.

ie. I want to move field1 to field2 if fielda = fieldb.

How do I attack this?

Thanks again.

 

[Guest]

Good Morning Wayne,

GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &
Year],[startpoints],[sumofpoints])

This is a query that is using data from several subqueries. The names
StartAttWeek & Year / AttendWeek & Yr represents an interval week and yr
formated like '0722' for a week ending date of 06/02/07. But all this is not
relevant to my problem.

I want a query expression to say
GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &

Year],[startpoints],[sumofpoints] ELSE do something else like the field points to the field sumofpoints)

Sorry if I am not being specific enough.
Thanks,

GoodPts: If ( ([ StartAttWeek] = [ AttendWeek] And [ StartAttYear] = [
AttendYear] ) , [ startpoints] , [ sumofpoints] )

I have taken out the table names for clarity. BUT
I don't think this will work?
Where are you getting the SumOfPoints (is this a subquery). Do you have a
year field for the Start and another for year Attend ??
You may be better not to use a year but simply use a time/date field in the
table. I think you may need t look at how the tables and querys are built.


--
Wayne
Manchester, England.

Wayne,

I am writing an expression in a query in design view.

GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &
Year],[startpoints],[sumofpoints])

This works fine but I do not have the else condition just the true condition
I want it to say something like this

GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &
Year],[startpoints],[sumofpoints] else [points],[sumofpoints)

I know the syntax is wrong but I do not know what the right syntax would be.
Sorry I was not clear and thank you for your assistance.

Am a little confused by this

Field2:IIf ( [TableName]![FieldA] = [TableName]![FieldB] , [TableName
]![Field1] , [TableName]![Field3] )

Do you already have a field called Field2 if so - what is in it. You may
need to UpdateQuery this if there is something in the field that you want to
replace. In this case the above would not work - you need to set the
criteria and then Update. But as I can't your query I can't give other
advice without more info

You should use the full TableName and FieldName in calculations - sooner or
later you will want to find out where the results come from and if you use
the full details it will be much simpler.

Good luck


--
Wayne
Manchester, England.



:

Hi Wayne!

Nope, Working on a query in design form.

:

Hi

Are you working on a form or a query ? It sound like you're on a form


--
Wayne
Manchester, England.



:

I have two inputs that I want to join using queries. Normally, no problem
but I would like to have a conditional move.

ie. I want to move field1 to field2 if fielda = fieldb.

How do I attack this?

Thanks again.

 

[Guest]

Dear Bunky,

Sorry I didn't get back on till now to see your 2nd question,

But I couldn't have answered that one. That was a level above my access
skills. So it was great that Wayne came in with the answer. He is a excellent
Access problem solver and way above me. If that was in COBOL I could have
done it, but that doesn't matter these days.

Good Luck with your project.
Steve

 

[Guest]

Wayne,

I cannot do that since everything comes about 5 deep from subqueries.
I have found a work around but still do not know how to do a conditional If.
I just wanted to know the syntax for
If [FieldA] = [FieldB],[Field1],[Field2] ELSE [Field3], [Field2]. This was
to represent if a = b, move field1 to field2
else
move field3 to field2
End IF

Thanks for your time.

can you post the table and field names


--
Wayne
Manchester, England.

Good Morning Wayne,

GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &
Year],[startpoints],[sumofpoints])

This is a query that is using data from several subqueries. The names
StartAttWeek & Year / AttendWeek & Yr represents an interval week and yr
formated like '0722' for a week ending date of 06/02/07. But all this is not
relevant to my problem.

I want a query expression to say
GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &

Year],[startpoints],[sumofpoints] ELSE do something else like the field points to the field sumofpoints)

Sorry if I am not being specific enough.
Thanks,

GoodPts: If ( ([ StartAttWeek] = [ AttendWeek] And [ StartAttYear] = [
AttendYear] ) , [ startpoints] , [ sumofpoints] )

I have taken out the table names for clarity. BUT
I don't think this will work?
Where are you getting the SumOfPoints (is this a subquery). Do you have a
year field for the Start and another for year Attend ??
You may be better not to use a year but simply use a time/date field in the
table. I think you may need t look at how the tables and querys are built.


--
Wayne
Manchester, England.



:

Wayne,

I am writing an expression in a query in design view.

GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &
Year],[startpoints],[sumofpoints])

This works fine but I do not have the else condition just the true condition
I want it to say something like this

GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &
Year],[startpoints],[sumofpoints] else [points],[sumofpoints)

I know the syntax is wrong but I do not know what the right syntax would be.
Sorry I was not clear and thank you for your assistance.



:

Am a little confused by this

Field2:IIf ( [TableName]![FieldA] = [TableName]![FieldB] , [TableName
]![Field1] , [TableName]![Field3] )

Do you already have a field called Field2 if so - what is in it. You may
need to UpdateQuery this if there is something in the field that you want to
replace. In this case the above would not work - you need to set the
criteria and then Update. But as I can't your query I can't give other
advice without more info

You should use the full TableName and FieldName in calculations - sooner or
later you will want to find out where the results come from and if you use
the full details it will be much simpler.

Good luck


--
Wayne
Manchester, England.



:

Hi Wayne!

Nope, Working on a query in design form.

:

Hi

Are you working on a form or a query ? It sound like you're on a form


--
Wayne
Manchester, England.



:

I have two inputs that I want to join using queries. Normally, no problem
but I would like to have a conditional move.

ie. I want to move field1 to field2 if fielda = fieldb.

How do I attack this?

Thanks again.

 

[Guest]

Wayne,

Thanks Wayne, that was exactly what I was asking.
Have a great day!

I think you are looking for an if called a "nested if". You can nest up to 7
but this would mean (if you needed all 7) that you maybe should look at
another way of doing things.

Anyway

Nested If

Say you have a field [FieldA] and you want something to happen if this is
equal to another field [FieldB]

An if calculation will only give you 2 answers – true or false. If
something is true you can do something if it’s false you can do something
else - or ask another question.

Like this

Name:IIf (The expression goes here, The action if true, The action if false)

So in your case it would look like this

Field2:IIf ([FieldA] =[FieldB], [Field1], [Field3])

This will insert the value of [Field1] into the calculated field2 ( [Field2]
) if [FieldA] is equal to [FieldB]. If they are not equal it will insert the
value of [Field3]

Of course you can add to this and “nest†the if’s like this


IIf ([FieldA] =[FieldB], [Field1], IIf ([FieldA]=[FieldC}, [Field4,
[Field5]) )

This means that if [FieldA] is not equal to [FieldB] then ask is [FieldA]
equal to [FieldC] if it is then insert the value of [Field4]. If non of it
is true then insert the value of [Field5].

You can “nest†up to 7 if’s.

Good luck

--
Wayne
Manchester, England.

Wayne,

I cannot do that since everything comes about 5 deep from subqueries.
I have found a work around but still do not know how to do a conditional If.
I just wanted to know the syntax for
If [FieldA] = [FieldB],[Field1],[Field2] ELSE [Field3], [Field2]. This was
to represent if a = b, move field1 to field2
else
move field3 to field2
End IF

Thanks for your time.

can you post the table and field names


--
Wayne
Manchester, England.



:

Good Morning Wayne,

GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &
Year],[startpoints],[sumofpoints])

This is a query that is using data from several subqueries. The names
StartAttWeek & Year / AttendWeek & Yr represents an interval week and yr
formated like '0722' for a week ending date of 06/02/07. But all this is not
relevant to my problem.

I want a query expression to say
GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &
Year],[startpoints],[sumofpoints] ELSE do something else like the field points to the field sumofpoints)

Sorry if I am not being specific enough.
Thanks,


:

GoodPts: If ( ([ StartAttWeek] = [ AttendWeek] And [ StartAttYear] = [
AttendYear] ) , [ startpoints] , [ sumofpoints] )

I have taken out the table names for clarity. BUT
I don't think this will work?
Where are you getting the SumOfPoints (is this a subquery). Do you have a
year field for the Start and another for year Attend ??
You may be better not to use a year but simply use a time/date field in the
table. I think you may need t look at how the tables and querys are built.


--
Wayne
Manchester, England.



:

Wayne,

I am writing an expression in a query in design view.

GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &
Year],[startpoints],[sumofpoints])

This works fine but I do not have the else condition just the true condition
I want it to say something like this

GoodPts: IIf([StartAttWeek & Year]=[AttendWeek &
Year],[startpoints],[sumofpoints] else [points],[sumofpoints)

I know the syntax is wrong but I do not know what the right syntax would be.
Sorry I was not clear and thank you for your assistance.



:

Am a little confused by this

Field2:IIf ( [TableName]![FieldA] = [TableName]![FieldB] , [TableName
]![Field1] , [TableName]![Field3] )

Do you already have a field called Field2 if so - what is in it. You may
need to UpdateQuery this if there is something in the field that you want to
replace. In this case the above would not work - you need to set the
criteria and then Update. But as I can't your query I can't give other
advice without more info

You should use the full TableName and FieldName in calculations - sooner or
later you will want to find out where the results come from and if you use
the full details it will be much simpler.

Good luck


--
Wayne
Manchester, England.



:

Hi Wayne!

Nope, Working on a query in design form.

:

Hi

Are you working on a form or a query ? It sound like you're on a form


--
Wayne
Manchester, England.



:

I have two inputs that I want to join using queries. Normally, no problem
but I would like to have a conditional move.

ie. I want to move field1 to field2 if fielda = fieldb.

How do I attack this?

Thanks again.