Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Luvflt is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    26

    Why blank fields are not recognized.


    I am totally new to this but have been tasked with fixing an issue with our Access Data base (that was written by someone else) that is part of a SQL Server. We have a student table with First, Middle Initial and Last Names. These data types were all set up as nvarchar. When we go to print out a certificate the query takes FNAME+N' '+Initial+ n' '+LNAME and puts that info into FULLNAME. The issue is this: If a person does not have a middle initial and that field is left blank nothing prints out on the certificate (or any other query using that information). If you put a period in to the "INITIAL" field it will print out as "Jane . Doe" When I run across someone without a middle name I have to go into the query and remove reference to INITIAL and print out just that certificate. Is there a way I can give "INITIAL" another data type designation that will recognize a blank space? Also there are some 8,000 records in the student data base now. If I change the "INITIAL" field's data type will that mess up the existing table for any future info that may need to be taken from it. Desperately needing some guidance.

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need to use the isnull function to construct your string

    e.g. [Firstname] & iif(isnull([initial],""," " & [initial]) & " " & [Lastname]

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Ajax has posted an answer that will work, but I thought I'd expand a bit. You've run into a feature many people don't know about: + propagates Null, & does not. You can take advantage of that and do this type of thing:

    FNAME & (" " + Initial) & " " & LNAME
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Luvflt is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    26
    Well I tried it both ways [Firstname] & iif(isnull([initial],""," " & [initial]) & " " & [Lastname] and FNAME & (" " + Initial) & " " & LNAME and I come up with "Data type error in expression. In the case of the first suggestion there appears to be a missing Paren. I am also assuming that the brackets around the field names are not required in my instance. My other question is do I use ' ' or do I use " " to indicate a space? I get the concept of the & vs + but something is amiss in the expression. Thanks for any assistance.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Ah, Ajax missed the closing parentheses of the IsNull:

    [Firstname] & iif(isnull([initial]),""," " & [initial]) & " " & [Lastname]

    Brackets are generally only required if there are spaces or symbols in the field name (though sometimes Access will add them on its own). You can probably use either double or single quotes, though if you were within a double-quoted string it would have to be singles. Can you post the db where mine isn't working?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Ah, Ajax missed the closing parentheses of the IsNull:
    umm - sorry - so I did

    My other question is do I use ' ' or do I use " "
    depends on where you are using it. Directly in a query you can use either, within VBA, if it is a formula you would use " ", but if building it into a string, you would use ' '. e.g.

    SQLstr="SELECT * FROM myTable WHERE myStr='hello" & " " & me.txtfield & "'"
    SQLstr="SELECT * FROM myTable WHERE myStr='hello world'"

  7. #7
    Luvflt is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    26
    Ok, Maybe I only know enough to be dangerous. Let me explain further as I did cut and paste from the above suggestions and got the same error.

    I have a query Under "Column" I have Fname, Initial and Lname then across from each of them under "Table" is Certificate_Data which is the table this information comes from. Under the table itself each field name under "Data Type" is nvarchar with the size as 20,20 and 25 respectively. Allow Nulls is checked for each one.

    In the Query itself again under "Column" is FName + N' ' + Initial + N' ' + LName and next to that under "Alias" is FullName thus I assume it takes first name, a space, middle initital, a space and last name and puts it into Fullname which is later used in the report to print out the students full name.

    Also for further explanation in case it makes a difference I am using Access 2010 but it is running via a SQL server.

    Hopefully I shed some light on something that now makes sense but I appreciate any and all help so thank you all again.

  8. #8
    Luvflt is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    26
    More info on error --- when I have cut and pasted the info provided and I go to save the query I get "Data Type Error in Expression" when I hit ok I get MS Access can't find the object 'sp_certificate_PMT_mm1' I don't understand why it is looking for this when the actual query is called sp_certificate_PMT_mm Maybe this information will be helpful as well. Thanks again.

  9. #9
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    In the Query itself again under "Column" is FName + N' ' + Initial + N' ' + LName
    what is the N?

    Why not post the entire sql to your query

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Are you working in an Access query or directly in SQL Server? Both of the suggestions are intended for Access, though would work with linked tables. I didn't notice the forum.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    Luvflt is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    26
    I am working directly in access query. Apparently the {N} is placed by access. If I use ' ' to indicate a space access inserts the N.

    Here is a copy of the SQL:

    ALTER PROCEDURE dbo.sp_certificate_PMT_mm
    (@class_no nvarchar(5),
    @class_year nvarchar(5))
    AS SELECT program, class_no, class_year, soc_sec, LName, FName, Initial, Cert_No, enddate, Cert_Type, chours,
    FName + N' ' + Initial + N' ' + LName AS FullName, LEFT(CAST(enddate AS VARCHAR), 11) AS when_signed, Student_no
    FROM dbo.certificate_data
    WHERE (program = 'pmt') AND (class_no = @class_no) AND (class_year = @class_year) AND (Cert_Type = N'mm')
    ORDER BY LName

    Hope this helps to clarify even more.

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This must be an ADP? What you've posted is SQL Server syntax. Off the top of my head, try

    FName + ' ' + CASE WHEN Initial Is Null THEN '' ELSE Initial + ' ' END + LName AS FullName
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    Luvflt is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    26
    pbaldy,

    Thank you so much!!!!! This is what I got to work: FName + ' ' + CASE WHEN Initial IS NULL THEN ' ' ELSE Initial + ' ' END + LName

    Pretty much what you said. I just took out the AS Fullname. I ran it with nothing for Initial and it worked like a champ. This helps me out immensely. Now it is just a matter of me going through all the certificate queries we have to make the change. It is nice to know that I don't have to do anything that would affect the master table for any future reference.

    Again, my sincere thanks.... plus I learned something to boot :-)

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help! Sorry both of us assumed Access even though you posted to the SQL Server forum.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    Luvflt is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    26
    I guess my posting was again out of ignorance. Not sure how it all works. I just know I open access and work within access but it is all on the sql server so I assume it is a combination of both but generally I work within Access unless at times it is easier for me to change the SQL code. I am all new to this but was an old assembly language/FORTRAN programer from back in the early 70's so I do have some grasp of certain things. I am always thankful to the Internet and various Forums for getting me out of pickles. Thanks again.

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

Similar Threads

  1. Field Not Recognized
    By Rustin788 in forum Reports
    Replies: 1
    Last Post: 08-25-2014, 10:34 AM
  2. Combo box is recognized as digit format
    By Gealeks in forum Forms
    Replies: 8
    Last Post: 11-27-2013, 03:08 PM
  3. Loop or Array not recognized
    By dvgef2 in forum Forms
    Replies: 6
    Last Post: 05-31-2013, 08:37 AM
  4. Replies: 3
    Last Post: 01-08-2013, 02:41 PM
  5. New Record not recognized in my queries
    By Kimmcdt in forum Queries
    Replies: 6
    Last Post: 01-16-2010, 10:26 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