Query produce error field

[Benjamins via AccessMonster.com]

Hi,

I was doing a query and when i ran it, it prompt me "No current record is
found" and 2 of the 11 fields show error. When i add in 2 more field (Which
is the 2 error field), all query work fine and all the records shows.

Do anyone know what actually happens?

 

[Jerry Whittle]

There's only one practical way for us to know: Show us the SQL. Open the
query in design view. Next go to View, SQL View and copy and past it here. Do
this for both the query that works and doesn't work. Also mention which
fields produce the error message. Maybe some sample data would be nice too.

 

[Benjamins via AccessMonster.com]

Hi,

The SQL that produce error:
SELECT Id, 'Course Fee(December-2007)' as Expr1, Class, Amt, Gst, Inv, Pay,
12 as Expr2, 2007 as Expr3, #01-Dec-07# as Expr4 FROM Get_Fee WHERE Inv = 2
AND Pay Not In (SELECT DISTINCT InvPay FROM Invoice) AND Sdate <= #01-Dec-07#
AND Edate >= #01-Dec-07#

An error message saying "No current record is found". will appear base on the
number of record i have.
The Id and Amt field will show #error while the rest of the field will show
the correct data

The SQL that does not produce error
SELECT Id, Id, 'Course Fee(December-2007)' as Expr1, Class, Amt, Amt, Gst,
Inv, Pay, 12 as Expr2, 2007 as Expr3, #01-Dec-07# as Expr4 FROM Get_Fee WHERE
Inv = 2 AND Pay Not In (SELECT DISTINCT InvPay FROM Invoice) AND Sdate <= #01-
Dec-07# AND Edate >= #01-Dec-07#

No error message appears after i had add an extra Id and Amt field. All
records is shown.

There's only one practical way for us to know: Show us the SQL. Open the
query in design view. Next go to View, SQL View and copy and past it here. Do
this for both the query that works and doesn't work. Also mention which
fields produce the error message. Maybe some sample data would be nice too.

Hi,

[quoted text clipped - 3 lines]

Do anyone know what actually happens?

 

[Jerry Whittle]

That is strange.

Are you running this query though code or just as a normal query? The reason
that I ask is that the "No current record is found" message shouldn't show up
on a normal select query being run.

If it is just a normal query, I think that adding the second ID field fixes
the problem in a strange way. Access automatically indexes fields with ID in
the name. It's possible that something is corrupt either in the query or
table where that field's index isn't working correctly.

Therefore I think that it might be a corruption problem. Probably your best
bet is to make a complete backup of the database now and put it away for safe
keeping. Next do a Compact and Repair. If that doesn't work, create a new
database file and import everything into it. Remember if you have your
database split, you have to do the C&R and/or import on both database files.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi,

The SQL that produce error:
SELECT Id, 'Course Fee(December-2007)' as Expr1, Class, Amt, Gst, Inv, Pay,
12 as Expr2, 2007 as Expr3, #01-Dec-07# as Expr4 FROM Get_Fee WHERE Inv = 2
AND Pay Not In (SELECT DISTINCT InvPay FROM Invoice) AND Sdate <= #01-Dec-07#
AND Edate >= #01-Dec-07#

An error message saying "No current record is found". will appear base on the
number of record i have.
The Id and Amt field will show #error while the rest of the field will show
the correct data

The SQL that does not produce error
SELECT Id, Id, 'Course Fee(December-2007)' as Expr1, Class, Amt, Amt, Gst,
Inv, Pay, 12 as Expr2, 2007 as Expr3, #01-Dec-07# as Expr4 FROM Get_Fee WHERE
Inv = 2 AND Pay Not In (SELECT DISTINCT InvPay FROM Invoice) AND Sdate <= #01-
Dec-07# AND Edate >= #01-Dec-07#

No error message appears after i had add an extra Id and Amt field. All
records is shown.

There's only one practical way for us to know: Show us the SQL. Open the
query in design view. Next go to View, SQL View and copy and past it here. Do
this for both the query that works and doesn't work. Also mention which
fields produce the error message. Maybe some sample data would be nice too.

Hi,

[quoted text clipped - 3 lines]

Do anyone know what actually happens?

 

[Benjamins via AccessMonster.com]

Hi,

The error message is "No current record" This happen when is am using on a
normal query.

Thanks for the advise, i will give it a try and hope it works well.

That is strange.

Are you running this query though code or just as a normal query? The reason
that I ask is that the "No current record is found" message shouldn't show up
on a normal select query being run.

If it is just a normal query, I think that adding the second ID field fixes
the problem in a strange way. Access automatically indexes fields with ID in
the name. It's possible that something is corrupt either in the query or
table where that field's index isn't working correctly.

Therefore I think that it might be a corruption problem. Probably your best
bet is to make a complete backup of the database now and put it away for safe
keeping. Next do a Compact and Repair. If that doesn't work, create a new
database file and import everything into it. Remember if you have your
database split, you have to do the C&R and/or import on both database files.

Hi,

[quoted text clipped - 27 lines]

 

[Benjamins via AccessMonster.com]

Hi,

I do a trial and found out where the problem.

It happen at the : "Pay Not In (SELECT InvPay FROM Invoice)" but when i
remove the NOT, the whole query works fine.

Can tell me what actually goes wrong?

That is strange.

Are you running this query though code or just as a normal query? The reason
that I ask is that the "No current record is found" message shouldn't show up
on a normal select query being run.

If it is just a normal query, I think that adding the second ID field fixes
the problem in a strange way. Access automatically indexes fields with ID in
the name. It's possible that something is corrupt either in the query or
table where that field's index isn't working correctly.

Therefore I think that it might be a corruption problem. Probably your best
bet is to make a complete backup of the database now and put it away for safe
keeping. Next do a Compact and Repair. If that doesn't work, create a new
database file and import everything into it. Remember if you have your
database split, you have to do the C&R and/or import on both database files.

Hi,

[quoted text clipped - 27 lines]

 

[John Spencer]

I have no idea why you are getting the behavior you are seeing, but I would
write the query as follows. It should be much more efficient than using a NOT
IN clause.

SELECT Id, 'Course Fee(December-2007)' as Expr1
, Class, Amt, Gst, Inv, Pay
, 12 as Expr2, 2007 as Expr3
, #01-Dec-07# as Expr4
FROM Get_Fee LEFT JOIN Invoice
ON Get_Fee.Pay = Invoice.InvPay
WHERE Invoice.InvPay is Null
AND Inv = 2
AND Sdate <= #01-Dec-07#
AND Edate >= #01-Dec-07#


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Hi,

I do a trial and found out where the problem.

It happen at the : "Pay Not In (SELECT InvPay FROM Invoice)" but when i
remove the NOT, the whole query works fine.

Can tell me what actually goes wrong?

That is strange.

Are you running this query though code or just as a normal query? The reason
that I ask is that the "No current record is found" message shouldn't show up
on a normal select query being run.

If it is just a normal query, I think that adding the second ID field fixes
the problem in a strange way. Access automatically indexes fields with ID in
the name. It's possible that something is corrupt either in the query or
table where that field's index isn't working correctly.

Therefore I think that it might be a corruption problem. Probably your best
bet is to make a complete backup of the database now and put it away for safe
keeping. Next do a Compact and Repair. If that doesn't work, create a new
database file and import everything into it. Remember if you have your
database split, you have to do the C&R and/or import on both database files.

Hi,

[quoted text clipped - 27 lines]

Do anyone know what actually happens?