if statement in criteria

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

Guest

i have a field GenericDrugId from tbl dbo_DPhaDrugData

if this value is null,

i want to select field GenericName from tbl dbo_DPhaDrugData3

would i put an IIf statment in the criteria ?
 
i have a field GenericDrugId from tbl dbo_DPhaDrugData

if this value is null,

i want to select field GenericName from tbl dbo_DPhaDrugData3

would i put an IIf statment in the criteria ?
Click to expand...

Don't.

Instead, use a calculated field:

NZ([dbo_DPhaDrugData], dbo_DPhaDrugData3].[GenericName])


John W. Vinson[MVP]
 
when i use your code, i get a prompt box to enter a value for dbo_DPhaDrugData

John Vinson said:
i have a field GenericDrugId from tbl dbo_DPhaDrugData

if this value is null,

i want to select field GenericName from tbl dbo_DPhaDrugData3

would i put an IIf statment in the criteria ?
Click to expand...

Don't.

Instead, use a calculated field:

NZ([dbo_DPhaDrugData], dbo_DPhaDrugData3].[GenericName])


John W. Vinson[MVP]
Click to expand...
 
when i use your code, i get a prompt box to enter a value for dbo_DPhaDrugData
Click to expand...

I left out a square bracket: try

NZ([dbo_DPhaDrugData], [dbo_DPhaDrugData3].[GenericName])

This assumes that the Query (which I have not seen and do not know
anything about!) has the table dbo_DPhaDrugData3 included in it.

If this is an unrelated table, how would you know which *record* in
dbo_DPhaDrugData3 to use for the generic name?

John W. Vinson[MVP]
 
the query does contain the tbl dbo_DPhaDrugData3 in it. it is joined to the
tbl dbo_DPhaDrugData by a common field

i modified your suggestion to this:

NZ([dbo_DPhaDrugData].[GenericID],[dbo_DPhaDrugData3].[GenericName])

but the query always chooses dbo_DPhaDrugData3.GenericName

whether dbo_DPhaDrugData.GenericID is null or not.

what am i doing wrong ?

John Vinson said:
when i use your code, i get a prompt box to enter a value for dbo_DPhaDrugData
Click to expand...

I left out a square bracket: try

NZ([dbo_DPhaDrugData], [dbo_DPhaDrugData3].[GenericName])

This assumes that the Query (which I have not seen and do not know
anything about!) has the table dbo_DPhaDrugData3 included in it.

If this is an unrelated table, how would you know which *record* in
dbo_DPhaDrugData3 to use for the generic name?

John W. Vinson[MVP]
Click to expand...
 
the query does contain the tbl dbo_DPhaDrugData3 in it. it is joined to the
tbl dbo_DPhaDrugData by a common field

i modified your suggestion to this:

NZ([dbo_DPhaDrugData].[GenericID],[dbo_DPhaDrugData3].[GenericName])

but the query always chooses dbo_DPhaDrugData3.GenericName

whether dbo_DPhaDrugData.GenericID is null or not.
Click to expand...

Please post the actual SQL of the query. The NZ() function should
return GenericID if it is non-NULL.

Alternatively, try

IIF(IsNull([dbo_DPhaDrugData].[GenericID]),[dbo_DPhaDrugData3].[GenericName],[dbo_DPhaDrugData].[GenericID])

though I don't see why that should do anything different!

John W. Vinson[MVP]
 
your first suggestion was correct, i was calling the wrong field.

thank you for your time and efforts, please excuse my ignorance.

John Vinson said:
the query does contain the tbl dbo_DPhaDrugData3 in it. it is joined to the
tbl dbo_DPhaDrugData by a common field

i modified your suggestion to this:

NZ([dbo_DPhaDrugData].[GenericID],[dbo_DPhaDrugData3].[GenericName])

but the query always chooses dbo_DPhaDrugData3.GenericName

whether dbo_DPhaDrugData.GenericID is null or not.
Click to expand...

Please post the actual SQL of the query. The NZ() function should
return GenericID if it is non-NULL.

Alternatively, try

IIF(IsNull([dbo_DPhaDrugData].[GenericID]),[dbo_DPhaDrugData3].[GenericName],[dbo_DPhaDrugData].[GenericID])

though I don't see why that should do anything different!

John W. Vinson[MVP]
Click to expand...
 
Back
Top