Open form with OR And using expressions to filter

[gg80]

Using A2007. This question hopefully a little simpler than the other recent post with similar title.



I am opening a form and filtering it as follows:
DoCmd.OpenForm "F_ErrorsMisc_Popup", , , "(ScoringBasis=""tot"" and (isnull(A) or isnull(B))) OR (ScoringBasis=""tod"" and (isnull(Distance) or Distance=0))"

It works, but I hate having long expressions like this-(I’m a little dyslexic and I will be adding conditions) I would like to simplify by making Dim statements for the expressions on each side of OR so that I can have smaller expressions to modify if needed. Then, open the form with:

Docmd.openform,”F_ErrorsMisc_Popup”, , , sWhere1 OR sWhere2

I try it and just can’t seem to find the right sequence of quotation marks or whatever else is needed.. Always get errors like type mismatch etc.
Any help much appreciated

[June7]

Have to concatenate variables with literal text.

sWhere1 & " OR " & sWhere2

[gg80]

I don't understand "literal text", but it works great -just wish I had posted sooner.. I just can't seem to grasp the logic of some of these things. I will save it in my large list of How-To's. I guess I do the same when I add Swhere3.

Thanks so much for the quick and accurate reply, much much appreciated.

[June7]

The OR is not a variable - it is just a string of characters - it is not a variable that holds a value - you want it to be included in the compiled statement exactly as it is typed - literal text. Literal text must be delimited within quote (or apostrophe in some cases) marks.

[gg80]

Thanks again. When saving this in my "how to" files, I tried same expression in an already opened Form: me.filter= swhere1 "or" swhere2. Doesn't work there. I did find an Allen Browne post that included "and/or" in the dim statement that does work. Curious as to why different.

[June7]

Don't know what you mean be included "and/or". Want to post code or provide link?

me.filter= swhere1 "or" swhere2

doesn't work because missing & concatenation characters.

[gg80]

I probably didn't make it clear, but this is what I meant by including "or/and". I know don't need it, but saving this as an example in case I get a some long expressions later.

Form.FilterOn = True
Dim Filt3 As String
Filt3 = "Boatname=""Bewitched"" or "
Filt3 = Filt3 & "boatname=""Silkye"""
Me.Filter = Filt3

I can't find the Allen Browne link but did download the example long time ago and will send if you like. He had quite a few statements, strings, dates etc., followed with AND, any one of which may not have been used. So, had code to chop of the AND when final applying final filter.

[June7]

I think I know the link (http://www.allenbrowne.com/ser-62code.html) but not seeing the Dim statement you describe. The VBA approach does not mix OR operator with AND. If you want to expand on the code and use OR operator it will get complicated.

[gg80]

I didn't mean to imply that I was trying to use both and & or in same Dim statement.. I just didn't know the proper word in VBA for a conjunction so put "and/or" down. It is interesting that the filter statement for opening is a different format for filtering while form is open.

The link you show is the one that I used. I appreciate how much attention you giving this this.