Concatinating Field Names By Not Null Values

[mikebaldam]

Is there an easy way of pulling fieldnames into a concatinated query field?

If For Example...

FieldNames are Option1, Option2, Option3, Option4

So that Concatined Field States...

Option1 Option2 Option4


but only where these vaules are not Null...

Thanks

Mike
________
iolite portable vaporizer

 

[SikhSioux]

Reply

Mike,
i am new to Access, but this idea may be useful.
Create a new 'calcualted' field in your query. With the following code:

If(IsNull(Option1) Or IsNull(Option2) Or IsNull(Option4), "",Concatenate(Option1,Option2,Option4))


Then in the criteria row for this field type:

Not Null


This will bring up all rows in your underlying table where either Option1, Option2, or Option4 is not null and will also concatneate them.

Let me know if it works

Chao

 

[Rich]

NewField:Nz([Field1]+Nz([Field2]) etc

 

[mikebaldam]

Ok... I cant get the If Statement working from SikhSioux
but I've got Rich's one working.

I still cant pull the info out in a text string...

I want to create a concated field list the names of the fields that are displayed null...

at the moment I'm pulling a list of the correct values but the concatenated field is comming up 0-1, -1-1, etc..

AllDetails: Nz([Tbl1]![Option1] & Nz([Tbl1]![Option2]))

How do I convert the -1's to the text I want ???

Cheers


Mike
________
Honda VF700C

 

[EMP]

Try this;-

AllDetails: IIf(Not IsNull(Option1),"Option1 ","") & IIf(Not IsNull(Option2),"Option2 ","") & IIf(Not IsNull(Option3),"Option3 ","") & IIf(Not IsNull(Option4),"Option4 ","")

 

[mikebaldam]

Cheers... gor it working a treat..

Thanks

Mike
________
Deauville

 

[mikebaldam]

Err.. Still having some trouble, At the moment I'm pulling both 0 & -1 values and converting them to the text... I only need the -1 values...

Is there an easy way to drop 0 Value text strings from the concatination?

Thanks Again

Mike

 

[EMP]

Are Option1, Option2, etc Yes/No fields?

Yes/No fields contain values of -1 for True or 0 for False.

If they are Yes/No fields, try this:-

AllDetails: IIf([Option1],"Option1 ","") & IIf([Option2],"Option2 ","") & IIf([Option3],"Option3 ","") & IIf([Option4],"Option4 ","")

 

[mikebaldam]

Cheers.... Got it workng ... Fully now.. Thanks everyone..

Mike