Selecting field in a table using a form

[astro3ron]

Yes, I was missing something! Format and typos and understanding. It's been a while since I've done this. Thanks for the help! It worked.

[astro3ron]

OK, one other refinement. The not null takes care of empty values in the year field, but suppose I want to to select only those records that have an S in the field and not another value. Like I said, I'm a little rusty and it's taking a while to catch up...

[MMcCarthy]

Try this and see if it gives you the results you are looking for ...

Private Sub cmdReport_Click()
    Dim strWhere As String
 
    strWhere = "[" & Me.lstYear & "] Like '*s*'
    Call DoCmd.OpenReport(ReportName:="rptMailings",
                          WhereCondition:=strWhere)
End Sub

[NeoPa]

For line #4 I'd use :

strWhere = "[" & Me.lstYear & "] = 'S'

[astro3ron]

strWhere = "[" & LabelYrSel & "] like '*S*'" works

strWhere = "[" & LabelYrSel & "] = '*S*'" doesn't work

subtlties of the = and Like always seem to get me in queries and filters.
Also, there are some fields where there's a S and others where there's S/R, so I need the wild card on either side. Previously I was combining a query and the filter in the report and they were stomping on each other. That was my problem.

[astro3ron]

In the "old" days, pre 1990s, I would have just written a little BV (not Victorian Bitters, although would taste good about now!) apps to read in the records to an array that was indexed, look in the cell of the "year,recor d" and checked for a S in the cell, then gone on to the other records doing the same. Finally producing a printable report. Access and VBA has a lot of power and complexity, sometimes to complex, too much flexibility. Oh well, thanks for the help and tutorage, folks. Think I got to where I want to be. Time for a VB or a pint of Ol Peculiar...

[NeoPa]

Ahh Theakston's My mouth is watering just thinking about it :-)

As for the SQL, no-one (least of all I) ever suggested your second version. It seems clear however (from post #20 but not before) that you are looking for records where the record contains an "S" within the field, as opposed to records within the data set whose value is "S". In this case the former would be correct anyway. My version (X = 'S' without wildcards) was for finding the latter case.

I'm pleased you have your solution anyway. Less pleased that you're the one with more immediate access to the Ol' Peculiar, but I'll have to live with that :-D

[astro3ron]

Sorry for the confusion on the selection criteria, but wasn't sure if too much info at the start would get me rambling. Actually, my access to Ol P is further away that yours, as I'm in the States now. But I can still dream of the tour through the Theakston brewery in Masham and relaxing moments in the pubs when I was up in North Yorkshire for work years back. Latched on to that brand and never looked back. Still miss it!! Cheers!

[NeoPa]

Are you a Brit abroad then? Or one of that extremely rare breed of Yanks who appreciate British beer? Ol P is certainly one of my favourites, if a tad on the strong side for too regular imbibement.

[astro3ron]

I be a Yank, who thoroughly enjoys the British beers and food! I came over there, over there... back in 1995 and fell in love with the country side, people, food and imbibement. Loved driving the open roads out in the countryside and could have stayed had I not any attachments back in the States. I liked the verbal banter that could take place and my "dry wit and sarcasm" allowed me to fit right in. Sense of humour and willingness to verbally engage in various topics made me feel as if I were back home. Actually, ancestors were from south England (Wood), so I guess I did sort of come home.

I'd tried a number of dark ales but settled on the taste of Ol P. One or two pints was all I could do at lunch or dinner, WITH food, or I'd fall asleep in the pub, nestled up in the corner booth in front of a warm fireplace. The American beers are more like flavoured water and never cared for them. Newcastle is as close as I could come over here to the English beers. But Theakston was the tops. Cheers and thanks for the help!

[NeoPa]

Astro3ron:
my "dry wit and sarcasm" allowed me to fit right in.

It would. You sound like a natural born Englishman, and from my perspective at least, that's a compliment.

It's my privilege to have helped :-)

[astro3ron]

Thanks for the tip of the hat and the tips for Access as well.