problem adding new field to query

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

Guest

I have a table and a working query based on it. I added a new field to the
table, and tried to add it to the query, but now when I try to run it I get a
message box saying “No current record.†Delete the field and the query works
fine again.


I'm taking this as an error message - if there were no records that matched
the query, Access would still produce a column layout based on the query
fields.

The new field is a yes/no type (if that makes a difference).

Any ideas.

Access 2002, Windows xp
 
Sounds like Access is confused about the names of things, or may have a bad
index.

To fix it, try this sequence:
1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

If the problem still persists, does the query involve more than one table?
Switch it to SQL View, and post the query statement, and indicate which is
the new yes/no field.
 
Thanks for your prompt response.

I tried what you said, and the problem is still there. So here is the query
- I trust it means more to you than it does to me!

There are 2 tables:
tblMusicTeaching
tblLessons

The problem field is ynLessonCharge

SELECT tblMusicTeaching.intStudentID, tblMusicTeaching.strSurname,
tblMusicTeaching.strFirstName, tblMusicTeaching.strStatus1,
tblMusicTeaching.strStatus2, tblLessons.intLessonID,
tblLessons.intStudentAndLessonLink, tblLessons.dtLessonDate,
DatePart("m",[dtLessonDate]) AS LessonMonth,
IIf(IsNull([dtLessonDate]),0,[curLessonFee]) AS Fee,
Count(tblLessons.dtLessonDate) AS LessonCount, tblLessons.strLessonNotes,
IIf(IsNull([dtPaymentDate]),0,[curLessonFee]) AS Payment,
tblLessons.dtPaymentDate, tblLessons.intReceiptNumber,
IIf(IsNull([dtLessonDate]),[dtPaymentDate],[dtLessonDate]) AS dtDateSort,
DatePart("m",IIf(IsNull([dtLessonDate]),[dtPaymentDate],[dtLessonDate])) AS
dtMonthSort, tblLessons.ynLessonCharge
FROM tblMusicTeaching LEFT JOIN tblLessons ON tblMusicTeaching.intStudentID
= tblLessons.intStudentAndLessonLink
GROUP BY tblMusicTeaching.intStudentID, tblMusicTeaching.strSurname,
tblMusicTeaching.strFirstName, tblMusicTeaching.strStatus1,
tblMusicTeaching.strStatus2, tblLessons.intLessonID,
tblLessons.intStudentAndLessonLink, tblLessons.dtLessonDate,
DatePart("m",[dtLessonDate]), IIf(IsNull([dtLessonDate]),0,[curLessonFee]),
tblLessons.strLessonNotes, IIf(IsNull([dtPaymentDate]),0,[curLessonFee]),
tblLessons.dtPaymentDate, tblLessons.intReceiptNumber,
IIf(IsNull([dtLessonDate]),[dtPaymentDate],[dtLessonDate]),
DatePart("m",IIf(IsNull([dtLessonDate]),[dtPaymentDate],[dtLessonDate])),
tblLessons.ynLessonCharge
HAVING (((tblMusicTeaching.strStatus1)="Student") AND
((tblMusicTeaching.strStatus2)<>"School"))
ORDER BY tblMusicTeaching.strSurname,
IIf(IsNull([dtLessonDate]),[dtPaymentDate],[dtLessonDate]);
 
Yes, posting the query was helpful, Peter.

This is a bug in Access.
It occurs when you group by a yes/no field that contains nulls.
You can work around it by choosing First instead of Group By in the Total
row under the ynLessonCharge field.

Details:
Unfortunately, Microsoft designed the yes/no field wrongly, so it cannot
handle the Null value. Then they assumed that the query engine would never
have to handle nulls in a yes/no field. But there are many cases where there
are nulls in y/n fields. The classic example is a query like yours with an
outer join.

There are various errors associated with this core issue. The "No current
record" error results from JET being unable to GROUP BY the Yes/No field
that contains Nulls. Other queries actually crash Access (shut down by
Windows) when JET can't handle the nulls.

I really should write this bug up, as I can't see it in the knowledgebase,
but it has been a problem since version 1.

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

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

PeterK said:
Thanks for your prompt response.

I tried what you said, and the problem is still there. So here is the
query
- I trust it means more to you than it does to me!

There are 2 tables:
tblMusicTeaching
tblLessons

The problem field is ynLessonCharge

SELECT tblMusicTeaching.intStudentID, tblMusicTeaching.strSurname,
tblMusicTeaching.strFirstName, tblMusicTeaching.strStatus1,
tblMusicTeaching.strStatus2, tblLessons.intLessonID,
tblLessons.intStudentAndLessonLink, tblLessons.dtLessonDate,
DatePart("m",[dtLessonDate]) AS LessonMonth,
IIf(IsNull([dtLessonDate]),0,[curLessonFee]) AS Fee,
Count(tblLessons.dtLessonDate) AS LessonCount, tblLessons.strLessonNotes,
IIf(IsNull([dtPaymentDate]),0,[curLessonFee]) AS Payment,
tblLessons.dtPaymentDate, tblLessons.intReceiptNumber,
IIf(IsNull([dtLessonDate]),[dtPaymentDate],[dtLessonDate]) AS dtDateSort,
DatePart("m",IIf(IsNull([dtLessonDate]),[dtPaymentDate],[dtLessonDate]))
AS
dtMonthSort, tblLessons.ynLessonCharge
FROM tblMusicTeaching LEFT JOIN tblLessons ON
tblMusicTeaching.intStudentID
= tblLessons.intStudentAndLessonLink
GROUP BY tblMusicTeaching.intStudentID, tblMusicTeaching.strSurname,
tblMusicTeaching.strFirstName, tblMusicTeaching.strStatus1,
tblMusicTeaching.strStatus2, tblLessons.intLessonID,
tblLessons.intStudentAndLessonLink, tblLessons.dtLessonDate,
DatePart("m",[dtLessonDate]),
IIf(IsNull([dtLessonDate]),0,[curLessonFee]),
tblLessons.strLessonNotes, IIf(IsNull([dtPaymentDate]),0,[curLessonFee]),
tblLessons.dtPaymentDate, tblLessons.intReceiptNumber,
IIf(IsNull([dtLessonDate]),[dtPaymentDate],[dtLessonDate]),
DatePart("m",IIf(IsNull([dtLessonDate]),[dtPaymentDate],[dtLessonDate])),
tblLessons.ynLessonCharge
HAVING (((tblMusicTeaching.strStatus1)="Student") AND
((tblMusicTeaching.strStatus2)<>"School"))
ORDER BY tblMusicTeaching.strSurname,
IIf(IsNull([dtLessonDate]),[dtPaymentDate],[dtLessonDate]);


--
PeterK


Allen Browne said:
Sounds like Access is confused about the names of things, or may have a
bad
index.

To fix it, try this sequence:
1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the database
by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

If the problem still persists, does the query involve more than one
table?
Switch it to SQL View, and post the query statement, and indicate which
is
the new yes/no field.
Click to expand...
Click to expand...
 
Thanks, the query now runs ok.

Next related question. I have a report based on this query. It has
controls to total the Fee and Payment fields. When I preview the report it
displays "Error" in these controls (when it is a student who has no related
lesson records yet.).

How can I correct this to show $0 or a blank field in this situation? (I
thought I had done it by dealing with the null values in the query. In
datasheet view, every row displayed has a dollar value, including names that
display error when the report is run.)


-- Cheers,
PeterK
 
The trick will be to identify what is causing the #Error.

When you changed GroupBy to First in the query, the field name changed. Did
you change the Control Source and Name of the control on your report to
match?

Note that Access gets confused if a control has the same Name as a field,
but is bound to something else.

If that is not the problem, post the Control Source and Name of the text box
that gives the error.

BTW, there is now an article on the bug you struck:
Outer join queries fail on Yes/No fields
at:
http://allenbrowne.com/bug-14.html
 
The yes/no field does not appear in the report. It acts as a filter in the
query.

While I was waiting for your earlier post, I tried deleting the field from
the query. With the field, I got the ‘No record’ message when I previewed
the report. Without the field, I got #Error, so I think it’s something else.

Here are the control details in the report:

In the detail section…
Text box name: curLessonFee
Control source: Fee

In the MonthSort section… a text box that sums the above control.
Text box name: Text41
Control source: =(Sum([Fee])
{I also tried =Nz(Sum([Fee]),0) but no difference}
Running sum property = No
This box produces an error.

A third text box produces a total of the monthly sub totals, and this is
wrong also…
Text box name: TotalFee
Control source: =(Sum([Fee])
Running Sum property = Over group
 
Try changing the name of the text box from curLessonFee to Fee (i.e. so it
has the same Name as Control Source), and see if it sums. Also, set its
Format property to Currency.

If it still fails, is Fee a calculated query field? If so, it may need to be
typecast in the query:
http://allenbrowne.com/ser-45.html

You should be able to use the yes/no field you your query if you choose
Where in the Total row instead of Group By. That will let you add the
criteria.

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

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

PeterK said:
The yes/no field does not appear in the report. It acts as a filter in
the
query.

While I was waiting for your earlier post, I tried deleting the field from
the query. With the field, I got the 'No record' message when I previewed
the report. Without the field, I got #Error, so I think it's something
else.

Here are the control details in the report:

In the detail section.
Text box name: curLessonFee
Control source: Fee

In the MonthSort section. a text box that sums the above control.
Text box name: Text41
Control source: =(Sum([Fee])
{I also tried =Nz(Sum([Fee]),0) but no difference}
Running sum property = No
This box produces an error.

A third text box produces a total of the monthly sub totals, and this is
wrong also.
Text box name: TotalFee
Control source: =(Sum([Fee])
Running Sum property = Over group


--
PeterK


Allen Browne said:
The trick will be to identify what is causing the #Error.

When you changed GroupBy to First in the query, the field name changed.
Did
you change the Control Source and Name of the control on your report to
match?

Note that Access gets confused if a control has the same Name as a field,
but is bound to something else.

If that is not the problem, post the Control Source and Name of the text
box
that gives the error.

BTW, there is now an article on the bug you struck:
Outer join queries fail on Yes/No fields
at:
http://allenbrowne.com/bug-14.html
Click to expand...
Click to expand...
 
Allen,
I believe the problem has nothing to do with the yes/no field. I have
created a second query / report with minimum number of fields and controls,
and the problem persists.

I wonder if I’m misunderstanding something basic about how calculated
controls in reports work – it’s not the first time I’ve struggled.

To help you help me, here’s all the information on the minimised query /
report.

First, the tables.

There are two tables:
tblMusicTeaching
tblLessons

Relationship is one to many.
Join type is “Include ALL records from 'tblMusicTeaching' and only those
records from 'tblLessons' where the joined fields are equal.â€

Second, the query.

Query name – qryLessonHistoryTest
3 fields:
intStudentID – from tblMusicTeaching
dtLessonDate – from tblLessons
Fee: CCur(IIf(IsNull([dtLessonDate]) Or [ynLessonCharge]=0,0,[curLessonFee]))

intStudentID is filtered with [Forms].[frmMusicTeaching].[txtStudentID] so I
can open the report from the related form with just the current student’s
history.

In SQL view:
SELECT tblMusicTeaching.intStudentID, tblLessons.dtLessonDate,
CCur(IIf(IsNull([dtLessonDate]) Or [ynLessonCharge]=0,0,[curLessonFee])) AS
Fee
FROM tblMusicTeaching LEFT JOIN tblLessons ON tblMusicTeaching.intStudentID
= tblLessons.intStudentAndLessonLink
GROUP BY tblMusicTeaching.intStudentID, tblLessons.dtLessonDate,
CCur(IIf(IsNull([dtLessonDate]) Or [ynLessonCharge]=0,0,[curLessonFee]))
HAVING
(((tblMusicTeaching.intStudentID)=[Forms].[frmMusicTeaching].[txtStudentID]));


Third, the report.

I simply put the three fields from qryLessonHistoryTest into the detail
section of a report.
Controls are all text boxes, and Access has set the query field names as
both the control name and control source in the report:

intStudentID
dtLessonDate
Fee

Then a control in the Page Footer section as follows:
Name: Text3
Control source: = Sum([Fee])


Finally, some sample output from running the report.

intStudentID LessonDate Fee Text3
35 5/7/06 $0.00
#error

43 Null $0.00
#error

55 3/6/06 $22.00
55 17/6/06 $22.00
55 24/6/06 $22.00
#error

I’ve just noticed something. I’m actually going backwards. In my earlier
more detailed report, #error only appeared when there were no records from
tblLessons. Eg, using the above data, #error only appears for Student 43, who
has no lessons entered yet. How exciting!

I realise this is a lot of info, but I thought if I was specific rather than
general, you might see a detail I have overlooked.

Thanks, Peter.
 
In the query:
Fee: CCur(Nz(IIf(IsNull([dtLessonDate]) Or Nz([ynLessonCharge],0)=0,
0,[curLessonFee]),0))

In the report, set the Format property of the Fee box to Currency.

If that still fails, I'm out of suggestions.

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

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

PeterK said:
Allen,
I believe the problem has nothing to do with the yes/no field. I have
created a second query / report with minimum number of fields and
controls,
and the problem persists.

I wonder if I'm misunderstanding something basic about how calculated
controls in reports work - it's not the first time I've struggled.

To help you help me, here's all the information on the minimised query /
report.

First, the tables.

There are two tables:
tblMusicTeaching
tblLessons

Relationship is one to many.
Join type is "Include ALL records from 'tblMusicTeaching' and only those
records from 'tblLessons' where the joined fields are equal."

Second, the query.

Query name - qryLessonHistoryTest
3 fields:
intStudentID - from tblMusicTeaching
dtLessonDate - from tblLessons
Fee: CCur(IIf(IsNull([dtLessonDate]) Or
[ynLessonCharge]=0,0,[curLessonFee]))

intStudentID is filtered with [Forms].[frmMusicTeaching].[txtStudentID] so
I
can open the report from the related form with just the current student's
history.

In SQL view:
SELECT tblMusicTeaching.intStudentID, tblLessons.dtLessonDate,
CCur(IIf(IsNull([dtLessonDate]) Or [ynLessonCharge]=0,0,[curLessonFee]))
AS
Fee
FROM tblMusicTeaching LEFT JOIN tblLessons ON
tblMusicTeaching.intStudentID
= tblLessons.intStudentAndLessonLink
GROUP BY tblMusicTeaching.intStudentID, tblLessons.dtLessonDate,
CCur(IIf(IsNull([dtLessonDate]) Or [ynLessonCharge]=0,0,[curLessonFee]))
HAVING
(((tblMusicTeaching.intStudentID)=[Forms].[frmMusicTeaching].[txtStudentID]));


Third, the report.

I simply put the three fields from qryLessonHistoryTest into the detail
section of a report.
Controls are all text boxes, and Access has set the query field names as
both the control name and control source in the report:

intStudentID
dtLessonDate
Fee

Then a control in the Page Footer section as follows:
Name: Text3
Control source: = Sum([Fee])


Finally, some sample output from running the report.

intStudentID LessonDate Fee Text3
35 5/7/06 $0.00
#error

43 Null $0.00
#error

55 3/6/06 $22.00
55 17/6/06 $22.00
55 24/6/06 $22.00
#error

I've just noticed something. I'm actually going backwards. In my earlier
more detailed report, #error only appeared when there were no records from
tblLessons. Eg, using the above data, #error only appears for Student 43,
who
has no lessons entered yet. How exciting!

I realise this is a lot of info, but I thought if I was specific rather
than
general, you might see a detail I have overlooked.

Thanks, Peter.
Click to expand...
 
Back
Top