Closing spaces between concatenated text

[drisconsult]

Hello All

I have a form that allows the user to enter the following data about a teacher. These are the fields:

CTitle
Cfname
Clname

Some users wish to leave out the title, but when they do so, there is a gap where the title would reside. How do you close up that gap? Also applies to the first name as well. I have included a zipped file of the program.

Thanking all who take the trouble in resolving this problem for me.

Regards
Drisconsult

 

[Mile-O]

The Nz() function or the Trim() function. They are detailed in the Help Files.

 

[drisconsult]

The Nz() function or the Trim() function. They are detailed in the Help Files.

Many thanks for taking the trouble over my problem. I forgot to mention that the text is populated into a List Box.

I had already tried the Nz() and Trin() functions. None of them work. Were you able to see the zipped file I sent. This has been extracted from the main db because of Data Protection Laws prevailing in the UK at the moment.

Even in the Query, neither of the suggestions work.

 

[Mile-O]

In your query's calculated field, you have three Trim statements around the field:

All you have to change this to is:

CNAME: Trim([CTITLE] & " " & [CFNAME] & " " & [CLNAME])

May I suggest that you use naming conventions for your tables, queries, forms, etc. as all three in that example had the same name which can prove problematic as Access is forced to guess, sometimes, at which you may be referring - especially with tables and queries. Spaces in names are frowned upon too.

So, as examples:

Table: tblTeachersData
Query: qryTeachersData
Form: frmTeachersData
Report: rptTeachersData

 

[drisconsult]

In your query's calculated field, you have three Trim statements around the field:

All you have to change this to is:

CNAME: Trim([CTITLE] & " " & [CFNAME] & " " & [CLNAME])

May I suggest that you use naming conventions for your tables, queries, forms, etc. as all three in that example had the same name which can prove problematic as Access is forced to guess, sometimes, at which you may be referring - especially with tables and queries. Spaces in names are frowned upon too.

So, as examples:

Table: tblTeachersData
Query: qryTeachersData
Form: frmTeachersData
Report: rptTeachersData

You sir, are a gentleman. I have added the following code so that the same rationale applies when a user leaves out the first name.

trim([ctitle] & " " & trim([cfame] & " " & [clname]))

Can't thank you enough for your help, and may I wish you a peaceful and Access free Easter!!!