By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,638 Members | 2,249 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,638 IT Pros & Developers. It's quick & easy.

concatenation & iff based on number of fields with entered data

theaybaras
P: 52
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
May 8 '07 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.
May 8 '07 #2

ADezii
Expert 5K+
P: 8,638
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.
    Expand|Select|Wrap|Line Numbers
    1. Citations: fConcatenateAuthors([Author1],[Author2],[Author3],[Author4],[Author5],[Author6],[Author7])
  3. The Function should return an approximation of what you are looking for.
    Expand|Select|Wrap|Line Numbers
    1. Public Function fConcatenateAuthors(A1, A2, A3, A4, A5, A6, A7)
    2. If Not IsNull(A7) Then
    3.   fConcatenateAuthors = Nz(A1) & Nz(A2) & Nz(A3) & Nz(A4) _
    4.                         & Nz(A5) & Nz(A6) & " et al. (" & Year(Now()) & ")."
    5. Else
    6.   fConcatenateAuthors = Nz(A1) & Nz(A2) & Nz(A3) & Nz(A4) _
    7.                         & Nz(A5) & Nz(A6) & " (" & Year(Now()) & ")."
    8. End If
    9. End Function
May 8 '07 #3

missinglinq
Expert 2.5K+
P: 3,532
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:
Expand|Select|Wrap|Line Numbers
  1. Dim ctrl As Control
  2. Dim AuthorCount as Int
  3.  
  4. AuthorCount = 0
  5.  
  6. For Each ctrl In Me.Controls
  7.   If TypeOf ctrl Is TextBox Then
  8.      If ctrl.Tag = "author" Then
  9.          AuthorCount = AuthorCount + 1
  10.      End If
  11.   End If
  12. Next
  13.  
  14. If AuthorCount > 6 Then
  15.    Me.MemoField =  ConcatenatedString & "et al"
  16. End If
  17.  
May 8 '07 #4

theaybaras
P: 52
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:
Expand|Select|Wrap|Line Numbers
  1.  
  2. =[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?
May 8 '07 #5

ADezii
Expert 5K+
P: 8,638
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:
Expand|Select|Wrap|Line Numbers
  1.  
  2. =[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.
May 8 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.