problem query

G

Guest

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.
 
A

Allen Browne

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],

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

lynn atkinson said:
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.
Click to expand...
 
G

Guest

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

Allen Browne said:
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],

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

lynn atkinson said:
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.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

count unique - yet more info 4
whats wrong with this query? 4
complex querie - a follow up 2
confused query 2
Multi table query 1
"Query too complex" workaround 4
Query doesnt work after importing from 97 to 2000 4
Access shutting down when accessing saved query 1

Top