SQL changes in two queries by itself???

[aldeb]

Below is the code I am using for three queries. The only
difference between the 3 queries are the Series. They work
good until I save the form they are on or leave the database
and come back to it. The second query below stays the same
and works just fine. They are activated from 3 different
combo boxes. The Class I query and the Class III query SQL
Changes somehow. This has really got me stumped!!

Class I
SELECT vTbl.TodaysDate, Count(*) AS [WU per day]
FROM (SELECT DISTINCT WorkUnit, TodaysDate FROM GrnWorkUnitsFaultsMainTBL 
     WHERE Series In ("A219","A883","A908","C814","C839","E098","E210","F114","F807","G108","J160","A268","A968","G807","K160") 
     And NonConformanceDescription Not In ("Out of Stock") 
     AND TodaysDate BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]
) AS vTbl
GROUP BY vTbl.TodaysDate;

Class II
SELECT vTbl.TodaysDate, Count(*) AS [WU per day]
FROM (SELECT DISTINCT WorkUnit, TodaysDate FROM GrnWorkUnitsFaultsMainTBL 
     WHERE Series In ('A265','A295','C264','C801','C861','D174','D470','D815','D826','G118') 
     And NonConformanceDescription Not In ("Out of Stock") 
     AND TodaysDate BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]
) AS vTbl
GROUP BY vTbl.TodaysDate;

Class III
SELECT vTbl.TodaysDate, Count(*) AS [WU per day]
FROM (SELECT DISTINCT WorkUnit, TodaysDate FROM GrnWorkUnitsFaultsMainTBL 
     WHERE Series In ('A229','A230','A231','A233','A234','A476','A477','A478','A479','A495','A891','A894','A895','A896','A897','A901','A902','A903','A904','A905','B218','B230','B233','B453','B454','B455','B827','B890','B896','C802','C819','C820','C821','D215') 
     And NonConformanceDescription Not In ("Out of Stock") 
     AND TodaysDate BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]
) AS vTbl
GROUP BY vTbl.TodaysDate;

The Class I code changes to:

SELECT vTbl.TodaysDate, Count(*) AS [WU per day]
FROM [SELECT DISTINCT WorkUnit, TodaysDate FROM GrnWorkUnitsFaultsMainTBL WHERE Series In ("A219","A883","A908","C814","C839","E098","E210","F114","F807","G108","J160","A268","A968","G807","K160") And NonConformanceDescription Not In ("Out of Stock") And Today] AS vTbl
GROUP BY vTbl.TodaysDate;

The Class III code changes to:

SELECT vTbl.TodaysDate, Count(*) AS [WU per day]
FROM [SELECT DISTINCT WorkUnit, TodaysDate FROM GrnWorkUnitsFaultsMainTBL WHERE Series In ('A229','A230','A231','A233','A234','A476','A477','A478','A479','A495','A891','A894','A895','A896','A897','A901','A902','A903','A904','A905','B218','B230','B233','B453','] AS vTbl
GROUP BY vTbl.TodaysDate;

 

[namliam]

looks like they are beeing truncated somehow... dont exactly know how or why...

Why is this 'leaving' your database?

 

[aldeb]

I meant when I close and reopen the Database I can no longer run two of the
three queries without editing them.

 

[MSAccessRookie]

I meant when I close and reopen the Database I can no longer run two of the
three queries without editing them.

I think that your problem is similar to one of mine. I have discovered that MS Access does not always handle SubQueries properly. Sometimes the Jet Engine reformats SubQueries, and loses the original code. To get around these instances, I have taken to making separate queries and then joining them together to get the result. I will be following this looking for additional suggestions as well.