problem query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lynn atkinson - 17 Nov 2005 11:10 GMT

I am having a problem with the following query

SELECT progress.[progress id], progress.[award id], progress.status,
progress.[candidate ID], progress.[start date], progress.assessor,
progress.IV, progress.comments, progress.award, progress.[units completed],
IIf([award]="CARE 2",DateAdd("m",12,[start date]),IIf([award]="care 3" Or
[award]="management 3",DateAdd("m",18,[start date]),IIf([award]="care 4" Or
[award]="management 4",DateAdd("m",24,[start date]),""))) AS [expected
completion], employeeinfo.surname, employeeinfo.forename,
employeeinfo.Project, progress.[completed date]
FROM employeeinfo INNER JOIN progress ON employeeinfo.[employee ID] =
progress.[employee id]
WHERE (((progress.[start date]) Between [date of last EV visit] And [date of
next EV visit]))
ORDER BY IIf([award]="CARE 2",DateAdd("m",12,[start date]),IIf([award]="care
3" Or [award]="management 3",DateAdd("m",18,[start date]),IIf([award]="care
4" Or [award]="management 4",DateAdd("m",24,[start date]),"")));

Firstly, when I try to run it as a query, I get the message that it is typed
incorrectly or may be too complex. However, if I open the report based on
this query, I get no such message.
Next, It is now not doing the expected completion calculation (it did before
I started playing with the query!)
The reason I was changing the query in the first place was so I could change
the parameters to filter the expected date field instead of the start date. I
put CVdate in front of the calculated field, but then lost the plot and
cannot return to a working query!
Can anyone help.

Reply to this Message

Allen Browne - 17 Nov 2005 11:42 GMT

The "too complex" message often means that Access is having a problem with
figuring out the data types of the fields or parameters or criteria values.

You could help it like this:

1. Declare any parameters. Choose Parameters on the Query menu, and enter a
row for each parameter. For example, if [date of last EV visit] and [date of
next EV] visit are parameters, you would enter these 2 rows in the dialog:
[date of last EV visit] Date/Time
[date of next EV visit] Date/Time

2. Use null instead of a zero-length string (zls).
A Date/Time type field (or a Number field for that matter) can be null, but
cannot be a zls. Your calculated field - [expected completion] - returns a
ZLS as the last option in the embedded IIf()s. Replace it with the word
Null. It also helps to typecast the calculation. Try:

CVDate(IIf([award]="CARE 2", DateAdd("m",12,[start date]),
IIf([award]="care 3" Or [award]="management 3",
DateAdd("m",18,[start date]),
IIf([award]="care 4" Or [award]="management 4",
DateAdd("m",24,[start date]), Null)))) AS [expected completion],

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I am having a problem with the following query
>
[quoted text clipped - 33 lines]
> cannot return to a working query!
> Can anyone help.

Reply to this Message

lynn atkinson - 17 Nov 2005 11:57 GMT

Brilliant - thanks for your help. I had plodded on and got it working but I
will put in your recommendations re the null etc.

cheers

> The "too complex" message often means that Access is having a problem with
> figuring out the data types of the fields or parameters or criteria values.
[quoted text clipped - 56 lines]
> > cannot return to a working query!
> > Can anyone help.

Reply to this Message