Parameter Query Error

[cRODEEkrank]

I'm trying to run a query that asks the user to enter a year. When I enter this data, I get the following error message:
"This expression is typed incorrectly, or is too
complex to be evaluated. For example, a numeric
expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the
expression to variables."

What baffles me is this query has been running OK with no errors up until today. No changes have been made to anything in the entire database. What can be causing this error?

 

[tlbroadbent]

Have you compacted the database lately? Try that.

That error message usually means the query is too large for the available memory. This doesn't refer to the total RAM, just the memory available for the query. If you can shorten the query by using aliases for table names, the query may run. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[cRODEEkrank]

Hi Terry,

Thanks for the suggestion. However, I compacted the database and used an alias for the table names and it still didn't work. Does anyone have any suggestions? I'm really at a loss here and need a solution!

Thanks!
Crod

 

[tlbroadbent]

Can you post the query here for review? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[combs]

I have recently had the same error message -- can't remember why right now, but I know I worked through it got it figured out. If you post the query that's not working it may jog my memory as to what the problem was....

 

[cRODEEkrank]

Here's the query:

SELECT tblAnnos.Status, [Publication List].Publication, Month([DateDue]) AS MONTHDUE, Switch([OutsideAuthor] Is Null And [InHouseAuthor] Is Not Null,[InHouseAuthor],[OutsideAuthor] Is Not Null And [First] Is Null And [InHouseAuthor] Is Null,[Last],[OutsideAuthor] Is Not Null And [First] Is Not Null And [InHouseAuthor] Is Null,[Last] & ", " & [First],[OutsideAuthor] Is Not Null And [First] Is Null And [InHouseAuthor] Is Not Null,[OutsideAuthor] & " And " & [Last],[OutsideAuthor] Is Not Null And [First] Is Not Null And [InHouseAuthor] Is Not Null,[OutsideAuthor] & " And " & [Last] & ", " & [First]) AS Author, tblAnnos.RegisterNum, tblAnnos.NumFinDate, tblAnnos.FinAnnoUnits, tblAnnos.EstAnnoUnits, IIf([EstAnnoUnits] Is Null,0,CDbl([EstAnnoUnits])) AS EstUnitNum, tblAnnos.TotalUnits, tblAnnos.DateDue, tblAnnos.DueDateExtension, Format(IIf([duedateextension]="ext" Or [duedateextension] Is Null,[datedue],[duedateextension]),"mm/dd/yyyy&quot AS DUEDATE, Year(IIf([duedateextension]="ext" Or [duedateextension] Is Null,[datedue],[duedateextension])) AS YEARDUE
FROM ([Publication List] INNER JOIN tblAnnos ON [Publication List].[Pub Code] = tblAnnos.Publication) LEFT JOIN [Contractor Data Sheet] ON tblAnnos.OutsideAuthor = [Contractor Data Sheet].CONTID
WHERE (((tblAnnos.Status)="assigned&quot AND (([Publication List].Publication) Like "*" & [Enter 5th or Fed] & "*&quot)
ORDER BY Month([DateDue]), Switch([OutsideAuthor] Is Null And [InHouseAuthor] Is Not Null,[InHouseAuthor],[OutsideAuthor] Is Not Null And [First] Is Null And [InHouseAuthor] Is Null,[Last],[OutsideAuthor] Is Not Null And [First] Is Not Null And [InHouseAuthor] Is Null,[Last] & ", " & [First],[OutsideAuthor] Is Not Null And [First] Is Null And [InHouseAuthor] Is Not Null,[OutsideAuthor] & " And " & [Last],[OutsideAuthor] Is Not Null And [First] Is Not Null And [InHouseAuthor] Is Not Null,[OutsideAuthor] & " And " & [Last] & ", " & [First]), Format(IIf([duedateextension]="ext" Or [duedateextension] Is Null,[datedue],[duedateextension]),"mm/dd/yyyy"


Sorry it looks nasty...I have taken this database over from someone who left the company.

Thanks!
Crod

 

[Trainingjason]

simple suggestion have you placed the parameters in the parameter box,

Query menu parameters

This normally overcomes most parameter problems

jason

 

[cRODEEkrank]

Thanks Jason. However, it's still not working.

Crod

 

[tlbroadbent]

I recommend simplifying the query by moving the SWITCH logic to a Public function and calling the function from the query.

Public Function fnAuthorName _
(sOutside, sInHouse, sLast, sFirst) As String

If IsNull(sOutside) And IsNull(sInHouse) Then
fnAuthorName = sInHouse
ElseIf IsNull(sOutside) And IsNull(sFirst) And IsNull(sInHouse) Then
fnAuthorName = sLast
ElseIf IsNull(sOutside) And IsNull(sFirst) And IsNull(sInHouse) Then
fnAuthorName = sLast & ", " & sFirst
ElseIf IsNull(sOutside) And IsNull(sFirst) And IsNull(sInHouse) Then
fnAuthorName = sOutside & " And " & sLast
ElseIf IsNull(sOutside) And IsNull(sFirst) And IsNull(sInHouse) Then
fnAuthorName = sOutside & " And " & sLast & ", " & sFirst)
Else
fnAuthorName = ""
End If

End Function


Query using the function: Not the aliases used for table names to shorten the query.

SELECT
ta.Status,
pl.Publication,
Month([DateDue]) AS MONTHDUE,
fnAuthorName([OutsideAuthor], [InHouseAuthor], [Last], [First]) AS Author,
ta.RegisterNum,
ta.NumFinDate,
ta.FinAnnoUnits,
ta.EstAnnoUnits,
IIf([EstAnnoUnits] Is Null, 0, CDbl([EstAnnoUnits])) AS EstUnitNum,
ta.TotalUnits,
ta.DateDue,
ta.DueDateExtension,
Format(IIf([duedateextension]="ext" Or [duedateextension] Is Null,[datedue],[duedateextension]),"mm/dd/yyyy&quot AS DUEDATE,
Year(IIf([duedateextension]="ext" Or [duedateextension] Is Null, [datedue], [duedateextension])) AS YEARDUE
FROM ([Publication List] pl
INNER JOIN tblAnnos As ta
ON pl.[Pub Code] = ta.Publication)
LEFT JOIN [Contractor Data Sheet] As cds
ON ta.OutsideAuthor = cds.CONTID
WHERE (((ta.Status)="assigned&quot
AND ((pl.Publication) Like "*" & [Enter 5th or Fed] & "*&quot)
ORDER BY Month([DateDue]),
fnAuthorName([OutsideAuthor], [InHouseAuthor], [Last], [First]),
Format(IIf([duedateextension]="ext" Or [duedateextension] Is Null, [datedue], [duedateextension]),"mm/dd/yyyy" Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[cRODEEkrank]

Thanks a lot Terry. I'll give this a try!

Crod

 

[Guest_imported]

Hi there,
Can someone tell me how to pass a parameter
from a Ms access form to a SQL query.
I've never used access b4 and had this project thrown at me.
What's even worse is that the @!#$@#% who did
this last didn't remember to make any documentation for it.
HELP!!!!