Help needed join fields from different tables

[fibonaccistu]

I'm having trouble with a query in Access. I have two columns for the description for inventory items. Standard and Extended. The two fields come from different tables. The standard description has a character limit of 29 characters. If a description is longer than this, it gets truncated at 29 characters, and the full description is written to the extended description field, otherwise the extended description is blank.

I'm trying to create a report based on inventory items ordered for each customer. I need to merge the two columns, but only if the extended description is blank. How can I accomplish this?

 

[Joe4]

Welcome to the Board!

How about something like this:

Description: IIF([Extended]="",[Standard],[Extended])

So, if the Extended field is blank, it will only return the Standard field. Otherwise, it will return the Extended field.

 

[fibonaccistu]

I guess the field would be considered null not empty. would the code be:

iif([CI_ExtendedDescriptionText] is null, [CI_Item.ItemCodeDesc],[CI_ExtendedDescriptionText])

?

 

[Joe4]

If it is null, you could simply use the NZ function and make it real simple, i.e.

NZ([COLOR=#333333][CI_ExtendedDescriptionText],[/COLOR][COLOR=#333333][CI_Item.ItemCodeDesc])[/COLOR]

See here for the description on NZ: MS Access: Nz Function