concatenation & iff based on number of fields with entered data

[theaybaras]

Hi,
I'm still getting a feel for some things in Access. What I have is a database of scientific articles. I have an unbound memo field in which I want to have the APA style citation concatenated from other fields. I have 12 fields for author names. lets say author1, author2, author3, ... author11, author12. I need a different text in the citation if 7 or more authors are entered into the above fields. If 1-6 authors are listed citation will look like

Last1, F1. M1. (2007).
Last1, F1. M1., Last2, F2. M2. (2007).

if 7 or more authors are filled in, I need it to return

Last1, F1. M1., Last2, F2. M2., Last3, F3. M3., Last4, F4. M4., Last5, F5. M5., Last6, F6. M6., et al. (2007).

so authors 7-12 aren't returned in the calculation but are returned as "et al."
but I don't want "et al." unless there are more than 6 authors filled in for the record.

I'm just not sure how to tell the field to calculate the citation one way if it is concatenating 6 or fewer authors and another if it is concatenating 7 or more. Also I'm not sure how to account for a blank entry for the authors.

I've given it a pretty good shot and I have no problem getting it to concatenate all 12 fields (with only the ones that are not null) using either formula (with or without the "et al." before the year. But I'm not sure how to count the number of non-null values across the 12 fields in a single record.

Anyone have any ideas? I'd really appreciate any guidance you can give me.

Thanks so much, let me know if you need more information or this wasn't clear.

:)

~me

[MMcCarthy]

Hi,
I'm still getting a feel for some things in Access. What I have is a database of scientific articles. I have an unbound memo field in which I want to have the APA style citation concatenated from other fields. I have 12 fields for author names. lets say author1, author2, author3, ... author11, author12. I need a different text in the citation if 7 or more authors are entered into the above fields. If 1-6 authors are listed citation will look like

Last1, F1. M1. (2007).
Last1, F1. M1., Last2, F2. M2. (2007).

if 7 or more authors are filled in, I need it to return

Last1, F1. M1., Last2, F2. M2., Last3, F3. M3., Last4, F4. M4., Last5, F5. M5., Last6, F6. M6., et al. (2007).

so authors 7-12 aren't returned in the calculation but are returned as "et al."
but I don't want "et al." unless there are more than 6 authors filled in for the record.

I'm just not sure how to tell the field to calculate the citation one way if it is concatenating 6 or fewer authors and another if it is concatenating 7 or more. Also I'm not sure how to account for a blank entry for the authors.

I've given it a pretty good shot and I have no problem getting it to concatenate all 12 fields (with only the ones that are not null) using either formula (with or without the "et al." before the year. But I'm not sure how to count the number of non-null values across the 12 fields in a single record.

Anyone have any ideas? I'd really appreciate any guidance you can give me.

Thanks so much, let me know if you need more information or this wasn't clear.

:)

~me

Post what you are currently using to concat the 12 values. It will make it easier to work out a way to do this.

[ADezii]

Hi,
I'm still getting a feel for some things in Access. What I have is a database of scientific articles. I have an unbound memo field in which I want to have the APA style citation concatenated from other fields. I have 12 fields for author names. lets say author1, author2, author3, ... author11, author12. I need a different text in the citation if 7 or more authors are entered into the above fields. If 1-6 authors are listed citation will look like

Last1, F1. M1. (2007).
Last1, F1. M1., Last2, F2. M2. (2007).

if 7 or more authors are filled in, I need it to return

Last1, F1. M1., Last2, F2. M2., Last3, F3. M3., Last4, F4. M4., Last5, F5. M5., Last6, F6. M6., et al. (2007).

so authors 7-12 aren't returned in the calculation but are returned as "et al."
but I don't want "et al." unless there are more than 6 authors filled in for the record.

I'm just not sure how to tell the field to calculate the citation one way if it is concatenating 6 or fewer authors and another if it is concatenating 7 or more. Also I'm not sure how to account for a blank entry for the authors.

I've given it a pretty good shot and I have no problem getting it to concatenate all 12 fields (with only the ones that are not null) using either formula (with or without the "et al." before the year. But I'm not sure how to count the number of non-null values across the 12 fields in a single record.

Anyone have any ideas? I'd really appreciate any guidance you can give me.

Thanks so much, let me know if you need more information or this wasn't clear.

:)

~me

I'm a little confused as to exactly what you want, but hopefully this should point you in the right direction.

1. Drop Author Fields 1 thru 7 (Author1...Author7) onto the Query Grid.
2. Create a Calculated Field which calls a Function and pass the Author1 thru Author7 Fields as Arguments.
   

   Citations: fConcatenateAuthors([Author1],[Author2],[Author3],[Author4],[Author5],[Author6],[Author7])

3. The Function should return an approximation of what you are looking for.
   

   Public Function fConcatenateAuthors(A1, A2, A3, A4, A5, A6, A7)
   If Not IsNull(A7) Then
     fConcatenateAuthors = Nz(A1) & Nz(A2) & Nz(A3) & Nz(A4) _
                           & Nz(A5) & Nz(A6) & " et al. (" & Year(Now()) & ")."
   Else
     fConcatenateAuthors = Nz(A1) & Nz(A2) & Nz(A3) & Nz(A4) _
                           & Nz(A5) & Nz(A6) & " (" & Year(Now()) & ")."
   End If
    End Function

[missinglinq]

You could enter author in the tag property of each textbox that holds an author's name, then use code like this to tally the number of authors entered:

Dim ctrl As Control
Dim AuthorCount as Int
 
AuthorCount = 0
 
For Each ctrl In Me.Controls
  If TypeOf ctrl Is TextBox Then
     If ctrl.Tag = "author" Then
         AuthorCount = AuthorCount + 1
     End If
  End If
Next
 
If AuthorCount > 6 Then
   Me.MemoField =  ConcatenatedString & "et al"
End If
 

[theaybaras]

Post what you are currently using to concat the 12 values. It will make it easier to work out a way to do this.

Thanks! Your reply was REALLY fast! True to my usual form, after posting in a forum I have at least come up with *A* way to do what I need to do. This may be a very inefficient way to do it. I realized that it really doesnt' matter if I have 12 authors listed. There will never be a null value in authors 1-6 and a non-null value in 7, authors are listed in incremental order. So I no longer needed to code for all 12. I figured out how to use the iif function. and now I have a LOT of them...
Once taking care of the authors then I append the Year from the a_year field, the title of the article from the a_title field, then the name of the journal from the a_journal field, then the volume and issue (if one is listed) from the a_volume and a_issue fields, and then the page range from a_pages. to take care of null values, I have put each of these in an iif statement. The record cannot be saved with a null value in the first author field.

My code:

 
=[a_author1] & (IIf([a_author2] Is Null,'',', ' & [a_author2])) & (IIf([a_author3] Is Null,'',', ' & [a_author3])) & (IIf([a_author4] Is Null,'',', ' & [a_author4])) & (IIf([a_author5] Is Null,'',', ' & [a_author5])) & (IIf([a_author6] Is Null,'',', ' & [a_author6])) & (IIf([a_author7] Is Null,'',', ' & 'et al.')) & (IIf([a_year] Is Null,'',' (' & [a_year] & '). ')) & (IIf([a_title] Is Null,'',[a_title] & '. ')) & (IIf([a_journal] Is Null,'',[a_journal] & ', ')) & (IIf([a_volume] Is Null,'',[a_volume])) & (IIf([a_issue] Is Null,',','(' & [a_issue] & ')')) & (IIf([a_pages] Is Null,'.',', ' & [a_pages] & '.')) 

from the data entered into my first record, this returns...

Averill, L. A., Creamer, M. C., Rothbaum, A. B., Gentry, C. R., Astin, M. H., Jones, A., et al. (2007). How to concatenate the author fields in the way described in my previous post without really trying. Journal of Traumatic Stress, 25(4), 325-331.

or when no more than 6 authors are entered it returns...

Averill, L. A., Creamer, M. C., Rothbaum, A. B., Gentry, C. R., Astin, M. H. (2007). How to concatenate the author fields in the way described in my previous post without really trying. Journal of Traumatic Stress, 25(4), 325-331.

Is there a more efficient way to do this? (i.e. without so many iif() statements?)are there any errors that may come up that I'm not aware of. I'm not still not very up on my SQL, or even all(most) of the functions in access.

Also, the field a_journal provides an ITALIC title of journal, but a roman format for title of journal in my memo field. I'm still learning a lot of things, and am not sure if rich text is available in a memo field. Is there a way I can bring the data from a_journal into my concatenated memo field while retaining the italic format for just that piece?

[ADezii]

Thanks! Your reply was REALLY fast! True to my usual form, after posting in a forum I have at least come up with *A* way to do what I need to do. This may be a very inefficient way to do it. I realized that it really doesnt' matter if I have 12 authors listed. There will never be a null value in authors 1-6 and a non-null value in 7, authors are listed in incremental order. So I no longer needed to code for all 12. I figured out how to use the iif function. and now I have a LOT of them...
Once taking care of the authors then I append the Year from the a_year field, the title of the article from the a_title field, then the name of the journal from the a_journal field, then the volume and issue (if one is listed) from the a_volume and a_issue fields, and then the page range from a_pages. to take care of null values, I have put each of these in an iif statement. The record cannot be saved with a null value in the first author field.

My code:

 
=[a_author1] & (IIf([a_author2] Is Null,'',', ' & [a_author2])) & (IIf([a_author3] Is Null,'',', ' & [a_author3])) & (IIf([a_author4] Is Null,'',', ' & [a_author4])) & (IIf([a_author5] Is Null,'',', ' & [a_author5])) & (IIf([a_author6] Is Null,'',', ' & [a_author6])) & (IIf([a_author7] Is Null,'',', ' & 'et al.')) & (IIf([a_year] Is Null,'',' (' & [a_year] & '). ')) & (IIf([a_title] Is Null,'',[a_title] & '. ')) & (IIf([a_journal] Is Null,'',[a_journal] & ', ')) & (IIf([a_volume] Is Null,'',[a_volume])) & (IIf([a_issue] Is Null,',','(' & [a_issue] & ')')) & (IIf([a_pages] Is Null,'.',', ' & [a_pages] & '.')) 

from the data entered into my first record, this returns...

Averill, L. A., Creamer, M. C., Rothbaum, A. B., Gentry, C. R., Astin, M. H., Jones, A., et al. (2007). How to concatenate the author fields in the way described in my previous post without really trying. Journal of Traumatic Stress, 25(4), 325-331.

or when no more than 6 authors are entered it returns...

Averill, L. A., Creamer, M. C., Rothbaum, A. B., Gentry, C. R., Astin, M. H. (2007). How to concatenate the author fields in the way described in my previous post without really trying. Journal of Traumatic Stress, 25(4), 325-331.

Is there a more efficient way to do this? (i.e. without so many iif() statements?)are there any errors that may come up that I'm not aware of. I'm not still not very up on my SQL, or even all(most) of the functions in access.

Also, the field a_journal provides an ITALIC title of journal, but a roman format for title of journal in my memo field. I'm still learning a lot of things, and am not sure if rich text is available in a memo field. Is there a way I can bring the data from a_journal into my concatenated memo field while retaining the italic format for just that piece?

You did mention the key point, any Authors entered into Fields 8 thru 12 are irrelevant, it only if Author Field #7 has an entry in it that is important. Beware of the IIF() Function! If any part of an expression containing this Function evaluates to NULL, the 'entire' expression will evaluate to NULL. This is a major drawback of using IIf() - NULLs will propagate through the entire expression.