select criteria

[Guest]

i'm writing a query from a table TransactionData2005

the two fields i'm working with [LocationID] and [OldLocationID]

my criteria is based on [LocationID] containing "CO*" or "DO*"

if this criteria is not met, i want to select [OldLocationID]

how would i write this?

 

[OfficeDev18 via AccessMonster.com]

Samuel,

Assuming you're using Access' query designer, you would do the following:

LocID: IIf(([LocationID] Like "CO*") Or ([LocationID] Like "DO*"),[LocationID]
,[OldLocationID])

If you're building the SQL statement in VBA, it's slightly more complicated.

Hope this helps,

Sam

i'm writing a query from a table TransactionData2005

the two fields i'm working with [LocationID] and [OldLocationID]

my criteria is based on [LocationID] containing "CO*" or "DO*"

if this criteria is not met, i want to select [OldLocationID]

how would i write this?

 

[Guest]

is there a way to use this using a field as the criteria instead of "CO*"?

Samuel,

Assuming you're using Access' query designer, you would do the following:

LocID: IIf(([LocationID] Like "CO*") Or ([LocationID] Like "DO*"),[LocationID]
,[OldLocationID])

If you're building the SQL statement in VBA, it's slightly more complicated.

Hope this helps,

Sam

i'm writing a query from a table TransactionData2005

the two fields i'm working with [LocationID] and [OldLocationID]

my criteria is based on [LocationID] containing "CO*" or "DO*"

if this criteria is not met, i want to select [OldLocationID]

how would i write this?

 

[John Spencer]

Example:

LocID: IIf(([LocationID] Like [SomeFieldName] & "*" ,[LocationID],
[OldLocationID])

SomeFieldName must be in the same record as the other fields.

is there a way to use this using a field as the criteria instead of "CO*"?

Samuel,

Assuming you're using Access' query designer, you would do the following:

LocID: IIf(([LocationID] Like "CO*") Or ([LocationID] Like
"DO*"),[LocationID]
,[OldLocationID])

If you're building the SQL statement in VBA, it's slightly more
complicated.

Hope this helps,

Sam

i'm writing a query from a table TransactionData2005

the two fields i'm working with [LocationID] and [OldLocationID]

my criteria is based on [LocationID] containing "CO*" or "DO*"

if this criteria is not met, i want to select [OldLocationID]

how would i write this?

 

[Guest]

could you put in a reference to another table? [mytable]![somefield] for
example?

Example:

LocID: IIf(([LocationID] Like [SomeFieldName] & "*" ,[LocationID],
[OldLocationID])

SomeFieldName must be in the same record as the other fields.

is there a way to use this using a field as the criteria instead of "CO*"?

Samuel,

Assuming you're using Access' query designer, you would do the following:

LocID: IIf(([LocationID] Like "CO*") Or ([LocationID] Like
"DO*"),[LocationID]
,[OldLocationID])

If you're building the SQL statement in VBA, it's slightly more
complicated.

Hope this helps,

Sam

samuel wrote:
i'm writing a query from a table TransactionData2005

the two fields i'm working with [LocationID] and [OldLocationID]

my criteria is based on [LocationID] containing "CO*" or "DO*"

if this criteria is not met, i want to select [OldLocationID]

how would i write this?

 

[John Spencer]

Yes, but only if the table is included in the query (in the from clause).

I would suggest you try it and if it fails then post a question with the SQL
(Menu: View: SQL) text. And a specific informtion on what problem you are
trying to solve.

could you put in a reference to another table? [mytable]![somefield] for
example?

Example:

LocID: IIf(([LocationID] Like [SomeFieldName] & "*" ,[LocationID],
[OldLocationID])

SomeFieldName must be in the same record as the other fields.

is there a way to use this using a field as the criteria instead of
"CO*"?

:

Samuel,

Assuming you're using Access' query designer, you would do the
following:

LocID: IIf(([LocationID] Like "CO*") Or ([LocationID] Like
"DO*"),[LocationID]
,[OldLocationID])

If you're building the SQL statement in VBA, it's slightly more
complicated.

Hope this helps,

Sam

samuel wrote:
i'm writing a query from a table TransactionData2005

the two fields i'm working with [LocationID] and [OldLocationID]

my criteria is based on [LocationID] containing "CO*" or "DO*"

if this criteria is not met, i want to select [OldLocationID]

how would i write this?