Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305

    Need blank cell to pull info from another field

    Essentially I have a table of phone numbers and two columns: primary phone and secondary. Don't ask me why, but for maybe 15-20% of the entries, the primary phone field is empty and their number is in the secondary field.



    Is it possible to have a query pull the secondary number into a the primary field if the primary field is blank? Or maybe create a whole new field which pulls in the primary and then pulls in the secondary if the primary is blank? Any suggestions?

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by templeowls View Post
    Essentially I have a table of phone numbers and two columns: primary phone and secondary. Don't ask me why, but for maybe 15-20% of the entries, the primary phone field is empty and their number is in the secondary field.

    Is it possible to have a query pull the secondary number into a the primary field if the primary field is blank? Or maybe create a whole new field which pulls in the primary and then pulls in the secondary if the primary is blank? Any suggestions?
    You could create a query with a calculated field, something like: Trim([FirstFieldName] & " " & [SeconFieldName])
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    So that combines them into two. Basically if there's numbers in both, it will look like (123-525-5698 123-525-5699). I'm looking for it to always show only the primary number UNLESS there is no primary and so then the secondary is shown.

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by templeowls View Post
    So that combines them into two. Basically if there's numbers in both, it will look like (123-525-5698 123-525-5699). I'm looking for it to always show only the primary number UNLESS there is no primary and so then the secondary is shown.
    Perhaps:
    N: IIf(Nz([FirstFieldName],0)=0,[SeconFieldName],[FirstFieldName])
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    So I inputted that with the code below but I'm getting a #Error result in each of the fields

    Code:
    IIf(Nz([PrimaryPhone],0)=0,[SecondaryPhone],[PrimaryPhone])

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I'd just use an Update query to copy the secondary to the primary if the primary is Null or a ZLS
    Then another update query to delete the secondary it it is the same as the primary. That is only if you need that.

    Can all be done with the Query designer?

    Create a Select query first to check the data, then change to an Update query. Even try on a COPY of your DB first?

    HTH
    Last edited by Welshgasman; 03-11-2021 at 04:59 PM.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    I'd just use an Update query to copy the secondary to the primary if the primary is Null or a ZLS
    Okay how would I go about coding that or inputting it into a new field?

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Okay how would I go about coding that or inputting it into a new field?
    Don't think you want a new field. What if primary and secondary both are present? What goes in the new field? In any case you would have 3 fields containing phone numbers with 2 of them duplicates in many cases. That could be more of a problem than the original.

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by templeowls View Post
    Okay how would I go about coding that or inputting it into a new field?
    I am not talking about creating a new field.?
    I am talking about cleaning the data once and for all. Then if you still get empty primary and completed secondary, you know your system is not that robust?
    However you could run that process again until you get to the bottom of the error.?

    Otherwise for a new field, or even to display a number, it would be Bobs code, Use Primary if it exists, else use Secondary. Hopefully you have at least one in each record?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Is this not a continuation of this issue?
    https://www.accessforums.net/showthread.php?t=83108
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Is this not a continuation of this issue?
    https://www.accessforums.net/showthread.php?t=83108
    Not really. Ironically, that's a different table with different phone numbers. The particular question there was regarding a table that stores our CRM customer phones and how to just populate both in one field. This one is pertaining to a table with employee numbers and needing the primary to always appear alone unless there's no primary. I feel like they're fairly different questions for entirely different projects

  12. #12
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    What if primary and secondary both are present? What goes in the new field?
    Just the primary. I only want the secondary to appear if there is no primary. I know its a weird premise....

    I am talking about cleaning the data once and for all.
    @Welshgasman This data is coming from our HR department's program. And they will not alter the structure of how they store their numbers in that system. So unfortunately it can't be cleaned once and for all. Every time I import the new employee list, the numbers will be screwy. That's why I just want an update query that pulls in the secondary if there is no primary, and if there is a primary, it doesn't alter that field. I honestly just don't know if that's even possible tho

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Please see attached sample.
    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #14
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by templeowls View Post
    @Welshgasman This data is coming from our HR department's program. And they will not alter the structure of how they store their numbers in that system. So unfortunately it can't be cleaned once and for all. Every time I import the new employee list, the numbers will be screwy. That's why I just want an update query that pulls in the secondary if there is no primary, and if there is a primary, it doesn't alter that field. I honestly just don't know if that's even possible tho
    Of course it is possible, just as it is to run that cleaning query every time you import the HR data. You do it once there.
    If you are going ro run a query to take secondary if no primary, and create another field to refer to, then I do not see much difference in updating primary where empty from secondary.?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  15. #15
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Please see attached sample.
    Cheers,
    Vlad
    @Gicu the first query is asking for a parameter for the Secondary field...I'm confused

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Leave one field blank in pull-down menu
    By snowboarder234 in forum Access
    Replies: 8
    Last Post: 03-12-2018, 01:32 PM
  2. Replies: 2
    Last Post: 01-03-2017, 03:27 PM
  3. Replies: 6
    Last Post: 09-10-2013, 08:37 AM
  4. Replies: 1
    Last Post: 07-12-2013, 01:48 PM
  5. Replies: 2
    Last Post: 02-22-2012, 02:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums