No Current Record Error 3

[jadams0173]

I added a field to my query and now it says NO Current record when I try to run it. If I remove the field it runs fine. The field is a yes/no checkbox.

SELECT J.JobNumber, J.PreReq, T.CourseNo, T.EmpNo, Max(T.CourseDate) AS TakenOn, [red]T.Disqualified[/red], tblSubCourse.SubCourseName, tblSubCourse.ExpirationInMonths, Max(CDate(Format(IIf(IsNull(T.EmpNo),#1/1/1900#,IIf([ExpirationInMonths]=0,#12/31/2099#,DateAdd("m",CInt(([expirationInMonths])),[coursedate]))),"mm/dd/yyyy"))) AS ExpireDate
FROM (tblJobReq AS J LEFT JOIN [SELECT * FROM TblTrainingRecords WHERE EmpNo='12345' ]. AS T ON J.PreReq = T.CourseNo) LEFT JOIN tblSubCourse ON J.PreReq = tblSubCourse.SubCourseNumber
WHERE (((J.JobNumber)='100'))
GROUP BY J.JobNumber, J.PreReq, T.CourseNo, T.EmpNo, [red]T.Disqualified[/red], tblSubCourse.SubCourseName, tblSubCourse.ExpirationInMonths;

Any ideas.





I tried to have patience but it took to long! -DW

 

[AlexCuse]

This may be a stupid question, but because you just added the field do you need to run an update to assign yes or no in the pre existing rows?

Without looking at your data, I can't think of anything else that would cause problems for that query.

I've never had a query return 'No Current Record', normally I just get an empty result pane.

Let me know what you find out,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[cmmrfrds]

Is the query going against an sql server or access mdb?

 

[jadams0173]

Access 2000.

I tried to have patience but it took to long! -DW

 

[jadams0173]

If I changed the data type from yes/no to text the query will run. If worst comes to worst I'll leave it as text but would rather have the checkbox.

I tried to have patience but it took to long! -DW

 

[AlexCuse]

That is odd. What values do you have in the checkbox field currently?

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[jadams0173]

Currently all the checkbox fields are unchecked. I did check a couple to see if that would work but I still had the error.

I tried to have patience but it took to long! -DW

 

[cmmrfrds]

I have seen this problem with sql server data in an Access query, but not against access data. I believe in this case, if you do a Alex points out initialize ALL the records values to zero that would take care of the problem. You could give it a default value in the design of false or zero.

 

[jadams0173]

Thanks to both of you for responding. I tried setting the default value in the table to 0 and FALSE for the checkbox but still had the same error of No Current Record.

I tried to have patience but it took to long! -DW

 

[AlexCuse]

Default value for a checkbox should be No, that could have something to do with your problem.

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[jadams0173]

Setting the default value to No didn't work either.

I tried to have patience but it took to long! -DW

 

[AlexCuse]

Did you try running an update query to populate checkbox for all records (to yes or no)? The Default only applies to new records. I have tried to replicate your error but I am having trouble getting it to 'not work'.

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[jadams0173]

Hey AlexCuse,

Good point on the default value. I ran an update query and set all the check boxes to No, 0, Yes, -1 and it still had the same error after each attempt.

I tried to have patience but it took to long! -DW

 

[cmmrfrds]

I think we need more basic information.

When do you get the error? Are you trying to update with the recordset? What is the query being used for? Is it on a bound form?

 

[jadams0173]

I get the error when I try to run or view the query from within Access. The error also occurs when running the query inside a vb6 application.

I'm not trying to update with the recordset. Just select records to display and output to a file

The query is used in a VB6 app that returns the recordset and then the records are placed in an xls file for the user.

It is not on a form.

I tried to have patience but it took to long! -DW

 

[cmmrfrds]

If you change the left join on the derived table (i.e. T) to an equal join, do you still get the error?

 

[PHV]

Anyway, the correct syntax:
... LEFT JOIN [!]([/!]SELECT * FROM TblTrainingRecords WHERE EmpNo='12345'[!])[/!] AS T ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jadams0173]

If I change the join the error goes away. But I don't get all the records that I want.

I tried to have patience but it took to long! -DW

 

[jadams0173]

FROM (tblJobReq AS J LEFT JOIN (SELECT * FROM TblTrainingRecords WHERE EmpNo='29391' ) AS T...

Still get No Current Record.

I tried to have patience but it took to long! -DW

 

[AlexCuse]

Could the problem be connected to null values in t.Disqualified?

You could try

iif(t.Disqualified is null, 0, t.Disqualified)

perhaps?

You signature seems especially applicable today...

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.