qry 2 complex - use of aliases in the criteria maybe?

[eti3nne]

I have a horrendous query with about 15 lines of criteria on 6 fields. OK, so I would be able to run it ok, but many of the criteria are of the form:

[forms].[frmMyFormName].[fldMyfieldName].

This is making the sql >>500 characters long (no I haven't actually counted!) Hence, I am getting the message "...Expression too complex to be evaluated...."

How can I use a short alias or similar to reduce the sql charaters to manageable propotions?

 

[dhookom]

Is the query the record source for a form or report? Can you provide some information about the WHERE clause in your query? Maybe paste the full SQL view in a response.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[eti3nne]

Yes, the query is the record source for a form. I have included a partial sql statement (1/4 of it!) below - this should give you the general idea....

As you can see, expressions such as [forms].[frmMailLogSearch].[framesearchchoice] recur frequently, and if I could replace [forms].[frmMailLogSearch].[framesearchchoice] etc. with say, xxx it would shorten the SQL considerably. ( I did want to attch a screen shot of the Access DESIGN view as this is more understandable, but I can't see how to attach it. Any ideas?)

SELECT tblMailLog.*, tblMailLog.PassedToLHBMember, tblMailLog.FromOrganisation, [forms].[frmMailLogSearch].[framesearchchoice] AS s, [forms].[frmMailLogSearch].[cboPassedToLHBMember] AS x, tblMailLog.MailItemIDNo, *
FROM tblMailLog
WHERE (((tblMailLog.PassedToLHBMember)=[Forms]![frmMailLogSEARCH]![cboPassedToLHBMember]) AND ((tblMailLog.FromOrganisation) Not Like "" And (tblMailLog.FromOrganisation) Is Not Null) AND (([forms].[frmMailLogSearch].[framesearchchoice])=2 Or ([forms].[frmMailLogSearch].[framesearchchoice]) Is Null Or ([forms].[frmMailLogSearch].[framesearchchoice])=0) AND ((tblMailLog.MailItemIDNo)=[forms].[frmMailLogSearch].[Text80]) AND (([Forms]![frmMailLogSEARCH]![FrameFilterIncomplete])=1) AND ((tblMailLog.ActionOrInfo)="A") AND ((tblMailLog.ActionComplete)="N")) OR (((tblMailLog.PassedToLHBMember)=[Forms]![frmMailLogSEARCH]![cboPassedToLHBMember]) AND ((tblMailLog.FromOrganisation) Like [Forms]![frmMailLogSEARCH]![cboFromOrganisation]) AND (([forms].[frmMailLogSearch].[framesearchchoice])=2 Or ([forms].[frmMailLogSearch].[framesearchchoice]) Is Null Or ([forms].[frmMailLogSearch].[framesearchchoice])=0) AND ((tblMailLog.MailItemIDNo)=[forms].[frmMailLogSearch].[Text80]) AND (([Forms]![frmMailLogSEARCH]![FrameFilterIncomplete])=1) AND ((tblMailLog.ActionOrInfo)="A") AND ((tblMailLog.ActionComplete)="N")) OR (((tblMailLog.FromOrganisation) Like [Forms]![frmMailLogSEARCH]![cboFromOrganisation]) AND (([forms].[frmMailLogSearch].[framesearchchoice])=1) AND ((tblMailLog.MailItemIDNo)=[forms].[frmMailLogSearch].[Text80]) AND (([Forms]![frmMailLogSEARCH]![FrameFilterIncomplete])=1) AND ((tblMailLog.ActionOrInfo)="A") AND ((tblMailLog.ActionComplete)="N")) OR (((tblMailLog.FromOrganisation) Like [Forms]![frmMailLogSEARCH]![cboFromOrganisation]) AND (([forms].[frmMailLogSearch].[framesearchchoice])=1) AND ((tblMailLog.MailItemIDNo)=[forms].[frmMailLogSearch].[Text80]) AND (([Forms]![frmMailLogSEARCH]![FrameFilterIncomplete])=1) AND ((tblMailLog.ActionOrInfo)="A") AND ((tblMailLog.ActionComplete) Is Null Or (tblMailLog.ActionComplete) Like "")) OR (((tblMailLog.FromOrganisation) Like [Forms]![frmMailLogSEARCH]![cboFromOrganisation]) AND (([forms].[frmMailLogSearch].[framesearchchoice])=1) AND ((tblMailLog.MailItemIDNo)=[forms].[frmMailLogSearch].[Text80]) AND (([Forms]![frmMailLogSEARCH]![FrameFilterIncomplete])=2) AND ((tblMailLog.ActionOrInfo) Like "*" Or (tblMailLog.ActionOrInfo) Is Null) AND ((tblMailLog.ActionComplete) Like "*" Or (tblMailLog.ActionComplete) Is Null Or (tblMailLog.ActionComplete) Like "")) OR ((([forms].[frmMailLogSearch].[cboPassedToLHBMember]) Like "Select LHB Member here"));

Thanks for helping.

 

[Crowley16]

the length of the query string isn't the problem...

I've written queries much longer than that which ran without problems, albeit slowly...

the complexity issue usually comes from trying to include too much stuff, including fields/joins/tables...

are you referencing another stored query in there somewhere? That's the most common way of suddenly getting very complicated...

--------------------
Procrastinate Now!

 

[Golom]

You have several filter conditions that don't seem to contribute anything. For example

Or (tblMailLog.ActionComplete) Like ""))

or

((tblMailLog.ActionComplete) Like "*"

or

Like "Select LHB Member here"));

The first one guarantees no matches. It may also be the reason for "too complex" because it translates to "LIKE NOTHING AT ALL" and SQL may be having a problem interpreting it. The second one matches everything and the third one will match only those records containing the quoted string exactly (i.e. it's not a parameter which is probably what you intend.)

LIKE clauses should include wild cards and mostly you don't have any (unless they are already in the form fields.) If it's your intention to look for an empty string then use
Len(Field) = 0 or Field = ""

 

[dhookom]

I try to avoid all references to form controls in Record Sources of forms and reports. I build a Where clause based on user entries on the form. For instance:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboPassedToLHBMember) Then
  strWhere = strWhere & " AND PassedToLHBMember = """ & _
      Me.cboPassedToLHBMember & """"
End If
If Not IsNull(Me.Text80) Then
  strWhere = strWhere & " AND MailItemIDNum = " & _
      Me.Text80
End If

'continue building the where clause based on user input
DoCmd.OpenForm "frmYourForm", , , strWhere

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]