Help with alphabetizing movie titles that start with "the" and others

[kyuball]

I am building a small personal database to organize my DVD movies, but thought I would get this issue cleared in case I need to do this for either a work project or something for someone else.

One of the tables in the mdb file is, of course, the one that contains the titles of the movies. Eventually, the titles would show up on a list where I can go browse or search through them. To avoid having titles that start with articles such as "The" and "A" being mis-ordered (a hundred titles under "t"!), I set the fields in the table like so:

titleid (pk)
title (the name of the movie)
titlearticle (where I can insert articles like "the" separately. For those without an article in the title - e.g. 'Tron' - the field is null)

By ordering the title field in ascending order in a query of some sort, I would get the results I want.

My question is in regards how it will display on a form. If I do a concatenated text box (=[titlearticle]&" "&[title]), it would be fine for the movies titles that are headed by an article, but the movies that don't have an article would have an unnecessary space in front of the title and be misaligned in a continunous form list.

Can someone help come up with a string or a code that would help alleviate this problem (i.e. leave the [titlearticle] field out of the display if the field is null)?

 

[Scooterbug]

I would use an iif statement:

MovieTitle: iif(nz([titlearticle,"")="",[Title],[TitleArticle] & " " & [Title])

if you are using a query as the record source, you can put that directly into the query as a field.

 

[kyuball]

WOW! Fastest reply, ever!

Thanks, Scooter! That worked like a charm. If I can I ask a couple more questions if you (or anyone else with the knowledge) have the time:

1) Can you break down that string for me? I am having a hard time improvising my own strings because I can't seem to get a grasp on the proper "grammar" for strings such as iif statements (I can do REALLY basic ones like if some yes/ no box is true, then display some string of text). I am hoping that I can learn as I break down other people's statements and codes.

2) I tend to not pull lists from a query per se, but rather just fix the query behind the form that displays the list. Is this good practice or should I be making a separate query?

 

[Scooterbug]

1) Can you break down that string for me? I am having a hard time improvising my own strings because I can't seem to get a grasp on the proper "grammar" for strings such as iif statements (I can do REALLY basic ones like if some yes/ no box is true, then display some string of text). I am hoping that I can learn as I break down other people's statements and codes.

Sure, the syntax for the iif statement is as follows:

iif([Expression]=whatever,[WhatToDoIfTrue],[WhatToDoIfFalse])

the nz():
nz(Expression,[WhatYouWantTheValueIfExpressionIsNull])

(btw: more info for these can be found in the help files of the VBA editor)

2) I tend to not pull lists from a query per se, but rather just fix the query behind the form that displays the list. Is this good practice or should I be making a separate query?

Not sure what you mean. What I was referring to was to place the iif statement into the query that is being used as the record source for the form. You could also have put the iif statement into the Control Source for the Field on the form. I would imagine that it would be a bit quicker putting the iif statement as the control source as opposed to putting it in the query. By having it in the query, it has to evalute the statement as the whole recordset is being loaded. Is that what you meant?

 

[kyuball]

First off, thank you so much, Scooter, for your time and knowledge in helping me out. Your breakdown helped me a great deal to understand the syntax of strings.

In response to the second part: That is what I meant, but I was wondering if it is a better idea to pull records for a form like this one that lists records for display from a query that is built separately vs. just manipulating the underlying query in the form itself (which would likley just pull direclty from the tables where the records lie.)

 

[DCrake]

Here is a sample db that does what you were asking

Link

David

 

[Scooterbug]

In response to the second part: That is what I meant, but I was wondering if it is a better idea to pull records for a form like this one that lists records for display from a query that is built separately vs. just manipulating the underlying query in the form itself (which would likley just pull direclty from the tables where the records lie.)

I think I get what you are asking. When you use a query as the record source for a form, unless there is a preset criteria, all of the records for that query are loaded into memory. So if you load a form where the record source is:
Select * From tblMovies;
Then all of the records are loaded into memory. Any manipulation of the records from there (Searching for example) are done against the what is stored in memory. If you dont have a boatload of records, there shouldn't be a performance hit.

Now, if you filter what you are looking for before the form is loaded. Say you were looking for movies where the title contains the word Gun:

Select * From tblMovies WHERE tblMovies.Title = Like "*gun*";

The the records are searched and a record source is loaded into memory with just the results of your criteria. Any manipulation of the records after that will only be done against the records that have met your criteria.

I may not be 100% accurate on what I said...but I believe that's how it works in a nutshell. (My db experience is self-taught).

What I generally do here at work, is to either:
1. use a search form with list boxes to display search results. Then I make it so that double clicking on a result will open a form with all the data for that record. Or
2. Just put a list box on the form with minimal data and when a user clicks on a row, detailed information is displayed on the rest of the form.

 

[kyuball]

Muchas Gracias, Scooter, for your insight.