problem adding new field to query

[PeterK]

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

--
PeterK

[Allen Browne]

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.

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

"PeterK" <Pet...@discussions.microsoft.com> wrote in message
news:F26E10C0-8273-43C7...@microsoft.com...

[PeterK]

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]

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"PeterK" <Pet...@discussions.microsoft.com> wrote in message

news:09F5F485-B40F-45F1...@microsoft.com...

[PeterK]

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

[Allen Browne]

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

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

"PeterK" <Pet...@discussions.microsoft.com> wrote in message

news:28F16375-0E33-40BB...@microsoft.com...

[PeterK]

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]

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"PeterK" <Pet...@discussions.microsoft.com> wrote in message

news:6D6E8EF4-8B5A-425E...@microsoft.com...

> 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" wrote:
>
>> 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
>>

[PeterK]

Text box is now renamed – no change.

Format property is now Currency – no change.

I read your link on calculated fields – very enlightening.
Calculated field in the query is now
Fee: CCur(IIf(IsNull([dtLessonDate]) Or [ynLessonCharge]=0,0,[curLessonFee])).
In datasheet view every record in the Fee field shows a value in the format
$0.00 right justified.
No change in Report preview.

Any more ideas?

--
PeterK

[Allen Browne]

Peter, re-reading this thread, I think you changed the query so it is not
grouping in the yes/no field ynLessonCharge. I would therefore expect that
this field is not in the report.

You may have a field named something like FirstOfynLessonCharge. If so, you
could try using that in the Fee expression.

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

"PeterK" <Pet...@discussions.microsoft.com> wrote in message

news:FB247C8A-68D8-4CEE...@microsoft.com...
> Text box is now renamed - no change.
>
> Format property is now Currency - no change.
>
> I read your link on calculated fields - very enlightening.

[PeterK]

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.

--
PeterK

[Allen Browne]

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"PeterK" <Pet...@discussions.microsoft.com> wrote in message

news:69854E63-41AA-45DF...@microsoft.com...

> 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