Query crashes Access with no errors

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I'm still quite new to Access. I've built a query to pull course details
from the database. Unfortunately it seems to be causing problems and a part
of it is crashing access giving no errors.

I've identified that the following is the problem, but I can't see why. Can
you suggest any improvements to stop this happening?

This checks 2 fields and depending on the content assigns a duration, which
may or may not contain one of those fields.

Duration: IIf([Full Course Details]![Weeks_in_Qual]=1,([Full Course
Details]![Hours_per_Week] & " Hours"),IIf([Full Course
Details]![Weeks_in_Qual]=36,"1 Year",IIf([Full Course
Details]![No_of_Years]=2,"2 Years",IIf([Full Course
Details]![Hours_per_Week]=0,"TBC",IIf([Full Course Details]![No_of_Years]=1 &
[Full Course Details]![Weeks_in_Qual]<36,([Full Course
Details]![Weeks_in_Qual] & " Weeks"),IIf([Full Course
Details]![Hours_per_Week]=0,"TBC","Check"))))))
 
Not sure why this would crash Access, but there is probably an error in this
part of the expression:
[Full Course Details]![No_of_Years]=1 & [Full Course
Details]![Weeks_in_Qual]<36

The ampersand is the concatenation operator, so Access will evaluate the
first part as True (-1) or False (0) or Null, and likewise the second part.
It will then convert these values to text and concatenate them, so the
expression will yield something like "0-1" or "00" or "-1-1" or "-10" or
"-1" or "0" or Null. It will then try to evaluate those strings as a True or
False value, at which point it may spit the dummy.

There are lots of other things that could cause Access to crash, such as the
Name AutoCorrect problems:
http://allenbrowne.com/bug-03.html
or Yes/No fields:
http://allenbrowne.com/bug-14.html
 
Thanks Alan, I'll have look into those points.

Allen Browne said:
Not sure why this would crash Access, but there is probably an error in this
part of the expression:
[Full Course Details]![No_of_Years]=1 & [Full Course
Details]![Weeks_in_Qual]<36

The ampersand is the concatenation operator, so Access will evaluate the
first part as True (-1) or False (0) or Null, and likewise the second part.
It will then convert these values to text and concatenate them, so the
expression will yield something like "0-1" or "00" or "-1-1" or "-10" or
"-1" or "0" or Null. It will then try to evaluate those strings as a True or
False value, at which point it may spit the dummy.

There are lots of other things that could cause Access to crash, such as the
Name AutoCorrect problems:
http://allenbrowne.com/bug-03.html
or Yes/No fields:
http://allenbrowne.com/bug-14.html

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

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

Andrew Tan said:
Hi,
I'm still quite new to Access. I've built a query to pull course details
from the database. Unfortunately it seems to be causing problems and a
part
of it is crashing access giving no errors.

I've identified that the following is the problem, but I can't see why.
Can
you suggest any improvements to stop this happening?

This checks 2 fields and depending on the content assigns a duration,
which
may or may not contain one of those fields.

Duration: IIf([Full Course Details]![Weeks_in_Qual]=1,([Full Course
Details]![Hours_per_Week] & " Hours"),IIf([Full Course
Details]![Weeks_in_Qual]=36,"1 Year",IIf([Full Course
Details]![No_of_Years]=2,"2 Years",IIf([Full Course
Details]![Hours_per_Week]=0,"TBC",IIf([Full Course
Details]![No_of_Years]=1 &
[Full Course Details]![Weeks_in_Qual]<36,([Full Course
Details]![Weeks_in_Qual] & " Weeks"),IIf([Full Course
Details]![Hours_per_Week]=0,"TBC","Check"))))))
Click to expand...
Click to expand...
 
Back
Top