Solved Simple Query Design

[Marshall Brooks]

I think this is a simple question, but I'm pretty weak in query design.

I have a table called tblChapterNames, it has two fields "ChapterNumber" and "Subject". Some subjects have a value of "N/A" so I want to omit those.

I figured out how to make a two-column query, but I would like to use the query with a combobox drop-down list, so I would like the first field to combine the items with a dash between them - i.e. "ChapterNumber-Subject", but I don't know how to make the combined field.

Thanks in advance!!!

 

[Minty]

Something like

SELECT [ChapterNumber] & "-" & [Subject] as ChapSubj
FROM tblChapterNames
Where [Subject] Is not Null AND [Subject]  NOT ="N/A"

 

[Marshall Brooks]

Yes - that looks correct. Let me test and report back!!!

 

[ebs17]

Notice

Where [Subject]  NOT ="N/A"

Since a comparison with NULLl cannot result in True, NULL contents are eliminated with just one expression.

 

[cheekybuddha]

Where [Subject] NOT ="N/A"

... NOT = ... is not valid SQL syntax

Perhaps you want:

Where [Subject] <> "N/A"

 

[Marshall Brooks]

SELECT [ChapNumber] & "-" & [Subject] as ChapSubj
FROM tblChapterNames
Where [Subject] Is not Null AND [Subject]  <> "N/A"

Works perfectly for me. I'm not sure if it would work if there were Null fields in the table. I don't think there are any.

Thank you all!!!

 

[Pat Hartman]

Since a comparison with NULLl cannot result in True, NULL contents are eliminated with just one expression.

The point is moot
Neither
Where [Subject] <> "N/A"
Nor
Where [Subject] Not = "N/A"
can return True when Subject is Null. It is very unlikely that the user wants Nulls to be returned if subject contains any.

 

[ebs17]

My statement would be that the extra check for "Is Not Null" is unnecessary in conjunction with the other expression because NULL content is omitted anyway. What is moot?

 

[Marshall Brooks]

Got it!

Null Content is omitted so it could be:

SELECT [ChapNumber] & "-" & [Subject] as ChapSubj
FROM tblChapterNames
Where [Subject]  <> "N/A"

@ebs17 has a very valid point - not in a query context, but in general:

I've had code elsewhere where I tried to use:
If subject isNotNull and subject <>"N/A" Then
and as @ebs17 says, Null will return true for the second condition of the clause so you have to use:
If subject IsNotNull Then
If subject <> "N/A" Then

Followup - or I think you could also use (for strings):
If (Subject & "") <> "" and (Subject & "") <> "N/A" Then

 

[cheekybuddha]

The point is moot
Neither
Where [Subject] <> "N/A"
Nor
Where [Subject] Not = "N/A"
can return True when Subject is Null. It is very unlikely that the user wants Nulls to be returned if subject contains any.

At least Where [Subject] <> "N/A" will not return an error

 

[Pat Hartman]

Null will return true for the second condition of the clause so you have to use

A comparison to Null does not return true or false, it returns null So whether the compare is
Null = "a"
Or
Null <> "a"
The result is Null, NOT True, NOT False, but Null. And since Null is not true, the assumption is false so BOTH statements are false which most people have trouble getting their heads around and so that's what trips people up. It is always easier for most people to understand the result of a positive condition than negative a one. For your own sanity, I recommend casting conditions as positive statements rather than negative if possible whenever one of the operands might be null. You are much more likely to get the result you are looking for.

When one of your operands might be null, it is always best to explicitly check for null so everyone knows how nulls should be treated. Go with clear, understandable conditions where the reader doesn't have to worry if you have considered nulls.

 

[Marshall Brooks]

Additional things that have tripped me up in the past:

For a string, varString = Subject.value will result in "Invalid use of Null" if Subject.value is Null. varString = Subject.value & "" will return "" which is likely desired.

Conversaitionally, we tend to use OR with either negative or positive statements. I want the correct answer to be 3 OR 5 works fine. (If Answer = 3 or Answer =5 Then). I'm fine with any answer as long as it is not 3 OR 5 is not an OR statement, but an AND statement (If Answer <> 3 AND Answer <> 5 Then)

If Answer <> 3 Or Answer <> 5 Then ... will ALWAYS resolve to positive b/c the Answer can't be BOTH 3 and 5.

 

[Pat Hartman]

Subject.value & "" will return "" which is likely desired.

Not by me. Null is nothing. "" is something. It is far less confusing in the long run to set the Allow ZLS property of text fields to false than to leave it as the default of true. That way, if you have to check for null, it doesn't matter whether the field is numeric or a string, you can check both for null rather than having to differentiate the data types and check one for "" and the other for null.

If Answer <> 3 Or Answer <> 5 Then ... will ALWAYS resolve to positive b/c the Answer can't be BOTH 3 and 5.

That happens because too many people sleep through math class and completely miss the segment on boolean logic and order of precedence.

 

[Marshall Brooks]

Have a related question ...

Now I would like to display the table results.

From https://www.access-programmers.co.uk/forums/threads/how-to-pop-up-query-window.207239/ - I gathered that I shouldn't pop-up either the table or a query based off the table, but I should pop-up a form based on the query based on the table.

From https://www.access-programmers.co.uk/forums/threads/sorting-text-as-a-number-in-access.295896/ - I figured out how to sort the query by length (1, 2, 5, 100), not (1, 100, 2, 5).

It somewhat works, but I am having the following issues:

• The form is not sorting the numbers per the query - i.e. it still shows 1, 100, 2, 5. I set the Order By property to nothing, but that didn't change anything.
• I had previous problems with Datasheet view and records being copied to other tables, but I'm not sure that is a concern here?
• I would like the first column to be "Chapter Number" not "ChapNum". I changed the caption, but it didn't change.
• I would like the form title to be "Chapter Names", not "frmAllChapterNames". Again, I changed the form caption, but it didn't change.
• The form is a couple of extra columns wide. The width is 2.4583". I tried to reduce that, but it always changes back to 2.4583.
• The form is about 50 rows longer than the data. I don't see a height property to change it.

I'm open to other suggestions, if there is a better way to show this.

 

[Josef P.]

till shows 1, 100, 2, 5.

=> "1", "100", "2", "5" ... looks like a text sort and not like a numerical sort.
Can you provide an example in which the form is sorted differently in the form despite the Order By clause being set appropriately in the form data source, even though form sorting is not activated?

I would like the first column to be "Chapter Number" not "ChapNum". I changed the caption, but it didn't change.

You mean the property 'datasheet caption'?

 

[Marshall Brooks]

Correct - text sort, not numerical sort. I can probably solve this myself. I set all the table fields to "short text". If I change the ChapNum Field to Integer or numeric, it probably sorts correctly, but I'm not sure if Integer will allow leading zeros.

"Datasheet Caption" is what I wanted to change, but I think I changed the caption of the field. (Actually, I just changed the name of the field.)

I'm more concerned with the width and height of the pop-up form being incorrect.

Slightly obfuscated version attached - Odd ...

• Sort order is correct now. Maybe because I saved and closed and re-opened the database. It is sorting correctly in the query and the form now. (I changed the query order yesterday as I was building the form.)
• Datasheet caption is what I needed to change, and I didn't have that changed. Oddly, if I change that property to "Chapter Number", the new caption doesn't fit the field, but the pop-up form width is reduced correctly.
• Height is still off, but this seems to be the only truly remaining issue.

 

[Josef P.]

I'm more concerned with the width and height of the pop-up form being incorrect.

Is it possible to use the data sheet form as a subform and create an unbound main form with the appropriate size as a cover?

Alternatively, you can also set the size using VBA.
FormRef.Move ...

Note:
ORDER BY Len(tblChapterNames.ChapNum) & tblChapterNames.ChapNum;
vs.
ORDER BY Len(tblChapterNames.ChapNum), tblChapterNames.ChapNum;
... this saves a string concatenation.
or
ORDER BY Val(tblChapterNames.ChapNum)

 

[mike60smart]

Correct - text sort, not numerical sort. I can probably solve this myself. I set all the table fields to "short text". If I change the ChapNum Field to Integer or numeric, it probably sorts correctly, but I'm not sure if Integer will allow leading zeros.

"Datasheet Caption" is what I wanted to change, but I think I changed the caption of the field. (Actually, I just changed the name of the field.)

I'm more concerned with the width and height of the pop-up form being incorrect.

Slightly obfuscated version attached - Odd ...

• Sort order is correct now. Maybe because I saved and closed and re-opened the database. It is sorting correctly in the query and the form now. (I changed the query order yesterday as I was building the form.)
• Datasheet caption is what I needed to change, and I didn't have that changed. Oddly, if I change that property to "Chapter Number", the new caption doesn't fit the field, but the pop-up form width is reduced correctly.
• Height is still off, but this seems to be the only truly remaining issue.

Hi
You just need to change the Popup Form's Default View to be Continuous Form and NOT Datasheet
Put the Labels in the Form Header and line them up Horizontally
Line up the Controls to suit.

 

[mike60smart]

Correct - text sort, not numerical sort. I can probably solve this myself. I set all the table fields to "short text". If I change the ChapNum Field to Integer or numeric, it probably sorts correctly, but I'm not sure if Integer will allow leading zeros.

"Datasheet Caption" is what I wanted to change, but I think I changed the caption of the field. (Actually, I just changed the name of the field.)

I'm more concerned with the width and height of the pop-up form being incorrect.

Slightly obfuscated version attached - Odd ...

• Sort order is correct now. Maybe because I saved and closed and re-opened the database. It is sorting correctly in the query and the form now. (I changed the query order yesterday as I was building the form.)
• Datasheet caption is what I needed to change, and I didn't have that changed. Oddly, if I change that property to "Chapter Number", the new caption doesn't fit the field, but the pop-up form width is reduced correctly.
• Height is still off, but this seems to be the only truly remaining issue.

Here is your database modified

 

[Marshall Brooks]

A bit closer:

Form is too long

I have an 'Alert' form (continuous form) set to pop up in front of the navigation form when the database opens with two hyperlinks set at the bottom edge of the form. Currently the length (vertical height) of the form is full height of the window which means that the hyperlinks are not visible...

www.access-programmers.co.uk

A way to accurately size a pop-up form

Some time ago when I was programming in Access 2000, I used to get frustrated because I could never by sure what size a pop-up form would be and where it would appear. As a result, I used the code shown below to precisely size and position my form, then set PopUp = True; Modal = True. It did...

www.access-programmers.co.uk

HOw to open a POP UP form to a specified size

On start up I disable all toolbars and commandbars. Thus my "base forms" open (Maximised) with a simple border and nothing else (other than the minimise, restore and close buttons in the top right corner). (everything else is disabled) I have a "new form" which opens on top of these "base...

www.access-programmers.co.uk

I think I can set the height of the detail and get the form to display as I want.

Initially, I didn't like this, but the form will be longer than the window anyway and if I add a record, it will still need to be scrolled to.

@mike60smart - Different approach, but I think I can make that work - much appreciated!!!