Page 2 of 2 FirstFirst 12
Results 16 to 25 of 25
  1. #16
    bridgeo730 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Jan 2018
    Posts
    33

    I keep getting the message that I need to enter parameters for "c" and "r".

  2. #17
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    My shorthand for all the things you put in - post # 7

  3. #18
    bridgeo730 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Jan 2018
    Posts
    33
    Ah! That makes sense. Unfortunately that didn't fix the problem of too much white space. Each box is still three lines tall, whether they have data in them or not. It's almost as if the carriage returns need an if-clause too...

  4. #19
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I am assuming the height of the field in design view is 1mm high! Or .1 of an inch.

    The c/r is in an if clause.

  5. #20
    bridgeo730 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Jan 2018
    Posts
    33
    The text boxes are set to .1.

    I bet I messed something up when inserting the returns:

    Pax 1 Degrees: IIf(IsNull([Degree3]),IIf(IsNull([Degree2]),[Degree1],[Degree1] & Chr$(13)+Chr$(10)+[Degree2]),[Degree1] & Chr$(13)+Chr$(10)+[Degree2] & Chr$(13)+Chr$(10)+[Degree3])

  6. #21
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    That looks ok. I am sure it is ok about mixing + and &, not something I would ever do. I wonder if those fields are actually null, not blank or something. In the first query, add criteria for is null, one field at a time, see if it is giving the correct results.

  7. #22
    bridgeo730 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Jan 2018
    Posts
    33
    Ah yes, those fields are indeed blank! Not everyone has a 2nd or third degree, so they are just left blank. Only about 2 months in learning access, how does one make blank fields null? I've tried NZ in the first query:
    Degree1: Nz([Degree 1 Campus],Null) & " " & Nz([Degree 1 Year],Null) & " " & Nz([Degree 1 Type],Null) & " " & Nz([Degree 1 Major],Null)

    But that doesn't work. I've made the tables default to null if there is nothing in them (there is a profile table for each person where the fields are).

  8. #23
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The first thing to find out is what do those fields actually contain. Playing around with the criteria in a query will tell you. Null is null and blank is "". One can't visually see the difference but in Access there is a very real difference. Depending on the situation, sometimes one has to check to both - IIf(IsNull(Degree1) Or Degree1 = "",.......

    I wouldn't be concerned about the concatenating NZ stuff in the first query, just throw all the fields together like you had in post # 7. Then check what they contain as mentioned. The main reason I suggested this other query was to simplify the IIF statement and to remove any further problems with troubleshooting. Far easier to write the IIF statement using one field name instead of a whole bunch. And also now you can check them out as whole fields before going into the report.

  9. #24
    bridgeo730 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Jan 2018
    Posts
    33
    Yes, this was perfect! The blanks were "", and I had thought them Null the whole time. Everything is in order now, phew! Thank you!

  10. #25
    bridgeo730 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Jan 2018
    Posts
    33
    Back again! And this time it's been a while since I've been in access.... this thread was so helpful, but now I have a 4th degree to add to my field:

    Pax 1 Degrees: IIf(IsNull([Degree3]),IIf(IsNull([Degree2]),[Degree1],[Degree1] & Chr$(13)+Chr$(10)+[Degree2]),[Degree1] & Chr$(13)+Chr$(10)+[Degree2] & Chr$(13)+Chr$(10)+[Degree3])

    However, someone from this lovely forum wrote what is above. I would like to add the possibility of a 4th degree, but I am getting errors when I try to improvise... Mostly no one has this 4th degree so if it's blank, I'd like it to focus on the 3rd degree, if that's blank, and so on... Can someone help with the sentence above?

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

Similar Threads

  1. Expand entire row not just single cell in a Report
    By CityOfKalamazoo in forum Reports
    Replies: 2
    Last Post: 07-28-2016, 09:22 AM
  2. Can Grow in Report
    By gatsby in forum Reports
    Replies: 7
    Last Post: 03-22-2015, 10:55 AM
  3. Replies: 1
    Last Post: 09-25-2013, 10:33 PM
  4. Text Boxes Grow Horizontally
    By dvgef2 in forum Reports
    Replies: 4
    Last Post: 03-28-2013, 06:42 AM
  5. Border surrounding entire Report
    By yes sir in forum Access
    Replies: 1
    Last Post: 11-13-2010, 01:58 PM

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