No current record error in Access 2000 on Office 2007 machine

[sargum]

Hi,

I created a DB in Access 2000. Recently we changed over to Office
2007 although I have not officially converted the DB to 2007.
Actually, it won't let me convert for some reason. That's another
issue. None of the crosstab queries work anymore as they were in
Access 2000. It shows up as Access 2000 file format. I did make sure
I have most current Windows, etc. I did use compact/repair on close
which I think was a mistake b/c older versions of the DB where I did
not use this option do show the same query as working. Already tried
importing the tables into the version that works but no luck.

I have been reading about "no current record"...in particular, using
Nz to make the query work with null values...how can I make the
following result in records instead of "no current record" Is there
any other reason besides null values from yes/no field that is
affecting this? I know I have many issues here but if you have a
quick solution to address this, that would be great.

SELECT [Hospital Info].[Hospital Info ID], [Harvester Info].[Territory
Manager Info ID], [Evaluation Score Info].[Territor Manager Name],
[Hospital Info].[Hospital Name], [Harvester Info].[VV4 User?],
Count([Harvester Info].[VV4 User?]) AS [CountOfVV4 User?]
FROM [Hospital Info] LEFT JOIN ([Evaluation Score Info] LEFT JOIN
[Harvester Info] ON [Evaluation Score Info].[Territory Manager Info
ID] = [Harvester Info].[Territory Manager Info ID]) ON [Hospital Info].
[Hospital Info ID] = [Evaluation Score Info].[Hospital Info ID]
GROUP BY [Hospital Info].[Hospital Info ID], [Harvester Info].
[Territory Manager Info ID], [Evaluation Score Info].[Territor Manager
Name], [Hospital Info].[Hospital Name], [Harvester Info].[VV4 User?];


Very Frustrated.

Thanks.
Sue

 

[Lord Kelvan]

first i suggest not converting as it will most likly create you more
problems than it will fix if it fixes any we are running access 97 and
office 2000 or office xp

regards
Kelvan

 

[sargum]

Reading three times and I still don't get to the point of your problems. Are
you having problem with the crosstab query or the no record problem?

Of course, there are problems using A2K with A2007. Please debug the problem
one at a time then post here if you need help and post the error code and
description with symptoms of your problems one at a time.(including code)

Hi,

I created a DB in Access 2000.  Recently we changed over to Office
2007 although I have not officially converted the DB to 2007.
Actually, it won't let me convert for some reason.  That's another
issue.  None of the crosstab queries work anymore as they were in
Access 2000.  It shows up as Access 2000 file format.  I did make sure
I have most current Windows, etc.  I did use compact/repair on close
which I think was a mistake b/c older versions of the DB where I did
not use this option do show the same query as working.  Already tried
importing the tables into the version that works but no luck.

I have been reading about "no current record"...in particular, using
Nz to make the query work with null values...how can I make the
following result in records instead of "no current record"  Is there
any other reason besides null values from yes/no field that is
affecting this?  I know I have many issues here but if you have a
quick solution to address this, that would be great.

SELECT [Hospital Info].[Hospital Info ID], [Harvester Info].[Territory
Manager Info ID], [Evaluation Score Info].[Territor Manager Name],
[Hospital Info].[Hospital Name], [Harvester Info].[VV4 User?],
Count([Harvester Info].[VV4 User?]) AS [CountOfVV4 User?]
FROM [Hospital Info] LEFT JOIN ([Evaluation Score Info] LEFT JOIN
[Harvester Info] ON [Evaluation Score Info].[Territory Manager Info
ID] = [Harvester Info].[Territory Manager Info ID]) ON [Hospital Info]..
[Hospital Info ID] = [Evaluation Score Info].[Hospital Info ID]
GROUP BY [Hospital Info].[Hospital Info ID], [Harvester Info].
[Territory Manager Info ID], [Evaluation Score Info].[Territor Manager
Name], [Hospital Info].[Hospital Name], [Harvester Info].[VV4 User?];

Very Frustrated.

Thanks.
Sue

--
Please Rate the posting if helps you

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200810/1- Hide quoted text -

- Show quoted text -

Hi,

I was giving lots of background but in the end, I really need the SQL
statement I state below to work. This is a crosstab query and when
run, I get no current record error. I saw posts that said it was a
yes/no issue with nulls or something. Is there anything you can do to
the SQL statement to help? This used to work fine when in Office 2000
but does not now that it is in Office 2007. I have not been able to
convert and so the heading of the DB says Access 2000 format.

SELECT [Hospital Info].[Hospital Info ID], [Harvester Info].[Territory
Manager Info ID], [Evaluation Score Info].[Territor Manager Name],
[Hospital Info].[Hospital Name], [Harvester Info].[VV4 User?],
Count([Harvester Info].[VV4 User?]) AS [CountOfVV4 User?]
FROM [Hospital Info] LEFT JOIN ([Evaluation Score Info] LEFT JOIN
[Harvester Info] ON [Evaluation Score Info].[Territory Manager Info
ID] = [Harvester Info].[Territory Manager Info ID]) ON [Hospital Info]..
[Hospital Info ID] = [Evaluation Score Info].[Hospital Info ID]
GROUP BY [Hospital Info].[Hospital Info ID], [Harvester Info].
[Territory Manager Info ID], [Evaluation Score Info].[Territor Manager
Name], [Hospital Info].[Hospital Name], [Harvester Info].[VV4 User?];

Advise/Help would be appreciated.

 

[sargum]

Make sure that the relationships are correctly set.

Another way is to import the tables into a new temporary 2007 format database
and create a same crosstab query as you have given and than try it out.

I was giving lots of background but in the end, I really need the SQL
statement I state below to work.  This is a crosstab query and when
run, I get no current record error.  I saw posts that said it was a
yes/no issue with nulls or something.  Is there anything you can do to
the SQL statement to help?  This used to work fine when in Office 2000
but does not now that it is in Office 2007.  I have not been able to
convert and so the heading of the DB says Access 2000 format.

SELECT [Hospital Info].[Hospital Info ID], [Harvester Info].[Territory
Manager Info ID], [Evaluation Score Info].[Territor Manager Name],

[quoted text clipped - 7 lines]

[Territory Manager Info ID], [Evaluation Score Info].[Territor Manager
Name], [Hospital Info].[Hospital Name], [Harvester Info].[VV4 User?];

Advise/Help would be appreciated.

--
Please Rate the posting if helps you

Message posted viahttp://www.accessmonster.com- Hide quoted text -

- Show quoted text -

Hi,

I have tried this already.

S