Open a form with Multiple Where Clauses (1 Viewer)

[pjawynn]

Good Morning

I am having trouble opening a datasheet form with mulitple where clauses here the VB that I have so far
-------------------

Private Sub Command192_Click()
If IsNull(Me.Startdate) = True Or IsNull(Me.Enddate) = True Or Startdate = "" Or Enddate = "" Then
MsgBox "Start AND End dates are required" '
Exit Sub '
ElseIf Startdate > Enddate Then
MsgBox "Start Date cannot be later than End Date"
Exit Sub '
Else

DoCmd.Openform "Summarys", acNormal, , ("[Incident Date] Between #" & Format(Me.Startdate, "MM/DD/YYYY") & "# And #" & Format(Me.Enddate, "MM/DD/YYYY") & "#" & "'" & " " And " & ([Location] = '" & "Avon Hall" & "'" And " & ([Location] = '" & "Regency Hall" & "')"), acFormReadOnly
DoCmd.Close acForm, "Search Reports"
End If
End Sub

-----------

At the park for the openform I recieve a Mismatch error 13 have trie serveral different ways by all fail any suggestions would be greatfull

Thanks

 

[namliam]

Keep things readable and it becomes easy(ier) to spot the mistakes:

WhereClause = ""
WhereClause = WhereClause & ("[Incident Date] Between #" & Format(Me.Startdate, "MM/DD/YYYY") & "# "
WhereClause = WhereClause & "                     And #" & Format(Me.Enddate  , "MM/DD/YYYY") & "# " 
WhereClause = WhereClause & "'" & " " And " & ([Location] = '" & "Avon Hall" 
WhereClause = WhereClause & "'" And " & ([Location] = '" & "Regency Hall" & "')"


DoCmd.Openform "Summarys", acNormal, , WhereClause, acFormReadOnly

Let me know if you cant see them

 

[Galaxiom]

Keep things readable and it becomes easy(ier) to spot the mistakes:

Definitely, but it could be even more readable by avoiding the repeating concatenation and simply using continuation instead.

Moving the trailing spaces in the SQL to the beginning of the line makes them easier to see too.

Another improvement is to include the date delimiters in the Format string as literals. It is much clearer and more concise than concatenating them.

Format([Incident Date],"\#MM\/DD\/YYYY\#")

 

[namliam]

Definitely, but it could be even more readable by avoiding the repeating concatenation and simply using continuation instead.

I find the dicussion of concatting vs continueing one of personal taste...
Continuing is limited in number of lines and has some other drawbacks (imho) vs that of concatting but either will work much better than one huge long off-screen scrolling string...

Same goes for the ## around dates, since you will need the '' around text ....
By seperating out the # from the format you actually keep things the same and thus readable.... (to me)

...#" & Format(Me.Startdate, "MM/DD/YYYY") & "#...
...'" & Format(Me.Startdate, "MM/DD/YYYY") & "'...

With formats offcourse you can always include it but if you are passing strings into your code..... you cannot.

On the other hand it is mostly what you are used to and what you personally prefer I think...

 

[Galaxiom]

Continuing is limited in number of lines and has some other drawbacks (imho)

Yes continuation is limited to 22 lines. However it is very rare that more than this number of lines are required. When required I separate (in the worst case) the Select, From, Where and Order By into different continued string variables and concatenate them.

If you want your argument to be taken seriously you will have to do better than claiming unspecified "other drawbacks".

The drawbacks of recursive concatenation are clear. FOr one, it makes the lines wider which works against the whole point of the exercise. A large proportion of the typing is redundant and the repetitive clutter gets in the way of reading what would otherwise be virtually identical to the actual SQL statement.

It is pretty obvious that those who use the recursive concatenation started out that way and are reluctant to consider the obviously superior alternative. I don't really have a problem with that but I do feel the need to comment when I see such obviously clumsy syntax promoted.

 

[namliam]

well to have continuation "work" in readable you end up with something like

strSQL = "select bla bla bla, bla bla bla" & _ 
              ", Bla bla " & _
         "From yourtable " & _
         "where ...." 

strSQL = ""
strSQL = strSQL & "select bla bla bla, bla bla bla"
strSQL = strSQL & ", Bla bla "
strSQL = strSQL & "From yourtable " 
strSQL = strSQL & where ...."

Some drawbacks that come to mind:
- it is still all one line
Thus if you make some errors in your code, it is only detected at the end of the line making it "harder" to analyze (i.e. you forgot to put a & in there someplace.)
- The limit of continuation isnt clear
People dont realize the limit is there or where it exactly is, the concat will always work

 

[Galaxiom]

well to have continuation "work" in readable you end up with something like

strSQL = "select bla bla bla, bla bla bla" & _ 
              ", Bla bla " & _
         "From yourtable " & _
         "where ...." 

strSQL = ""
strSQL = strSQL & "select bla bla bla, bla bla bla"
strSQL = strSQL & ", Bla bla "
strSQL = strSQL & "From yourtable " 
strSQL = strSQL & where ...."

Some drawbacks that come to mind:
- it is still all one line

Not if you do it like this:

strSQL = "SELECT blah, blag, blog" _
       & " FROM sometable" _
       & " WHERE thingimy='" &  Me.whatsit  & "'" _
       & " ORDER BY SomeSequence"

Thus if you make some errors in your code, it is only detected at the end of the line making it "harder" to analyze (i.e. you forgot to put a & in there someplace.)

I use the & to start every line. Likewise I include a space. Very hard to miss because it all lines up (ampersands, open quotes and spaces) and indents naturally without the clutter of the double repetition of the variable name on every line. Far more readable.

With recursive concatenation the dominant vision is the variable name and it is ridiculously distracting from what actually matters. Just take an honest look at your code and mine and it is impossible to deny.

If the continuation mark is missed the editor objects immediately when you leave the line making it impossible to leave it out.

Each line stands alone. It simply isn't worth the bother of putting a closing SQL quote mark on the next line as you have in your first example. That really does confuse things.

The case is surely closed when you add the fact that continuation uses less memory and runs faster.

- The limit of continuation isnt clear
People dont realize the limit is there or where it exactly is, the concat will always work

Not clear? 22 lines every time and rarely reached. If the string includes lengthy subqueries I will concatenate them.

There are many things that a novice might not realise but we would be incredibly limited if we eschewed them all for that reason.

 

[namliam]

Guess I should mend my ways perhaps I am stuck in old solutions

 

[pjawynn]

Good evening

namliam I seem to be getting a Compiler error expected on the second line any ideas ?

Thanks

 

[Galaxiom]

namliam only reformatted your code without changing it so you would be able see what was wrong and fix it yourself.

Post what you think is right now.

 

[pjawynn]

If I ' out the 3rd and 4th line for Location the date between range works so their must be problem with the Location, bu for the life of me I cannot figer it out

whereclause = ""
whereclause = whereclause & "[Incident Date] Between #" & Format(Me.Startdate, "MM/DD/YYYY") & "# And #" & Format(Me.Enddate, "MM/DD/YYYY") & "#"
whereclause = whereclause & [Location] = "Regency Hall" & "'"
whereclause = whereclause & [Location] = "Avon Hall" & "'"

Any sugestions

 

[Galaxiom]

whereclause = whereclause & " AND ([Location] = 'Regency Hall'"
whereclause = whereclause & " OR [Location] = 'Avon Hall')"