I have a very simple query that returns many fields, but only one is given a criteria. It is represented in SQL as follows.
SELECT Main.PropType, Main.CompNo, [Main]![Section] & [Main]![Township] & [Main]![Range] AS Expr3, Main.Grantor, Main.Grantee, [Main]![Month of Transaction] & "/" & [Main]![Year of Transaction] AS Expr4, Format([Price],"$#,##0"
AS Expr2, Format([Main]![Price]/[Main]![Number of Units],"$#,##0.00" AS Expr1, Main.Financing, Main.Legal, Main.[OR BK], Main.[OR PG], Main.[Improvements Description], Main.[Site Description], Main.Density, Main.Verification, Format([PGI],"$#,##0" AS Expr10, Format([V&C],"$#,##0" AS Expr15, Format([EGI],"$#,##0" AS Expr16, Format([Expenses],"$#,##0" AS Expr17, Format([NOI],"$#,##0" AS Expr18, " " AS Expr14, Format([Main]![NOI]/[Main]![Price],"#.00%" AS Expr11, [Main]![Price]/[Main]![EGI] AS Expr12, Main.EDR, Format([Main]![Expenses]/[Main]![EGI],"#%" AS Expr13, Main.Comments, Format([Main]![PGI]/[Main]![Number of Units],"$#,##0.00" AS Expr5, Format([Main]![V&C]/[Main]![Number of Units],"$#,###.00" AS Expr6, Format([Main]![EGI]/[Main]![Number of Units],"$#,##0.00" AS Expr7, Format([Main]![Expenses]/[Main]![Number of Units],"$#,###.00" AS Expr8, Format([Main]![NOI]/[Main]![Number of Units],"$#,##0.00" AS Expr9, Main.Location, Main.Name, Main.[Price Cont], Main.[Date Cont], Main.[OR BK/PG Cont], Main.[Type of Units], Main.[Price/Unit Cont]
FROM Main
WHERE (((Main.PropType)=15));
PropType can be any one of dozens of Number, Long Interger values. For some reason 15 comes up with an empty result, while others, 8, 12, 10 come up with the expected result - those records in which the criteria value of the query is the same as the Proptype field of the record.
I created a new query on the same table (Comps_be.mdb) with only the PropType field in the query and got the same result. For some reason the Criteria value 30 returns all the records with the value of 46, and no others. What gives? I Compacted and Repaired, and got the same results.
Is this indicative of a corrupt table? If so, absent a backup what is the best way to try to save the data in a new table, and leave the corruption behind?
Thanks in advance!
SELECT Main.PropType, Main.CompNo, [Main]![Section] & [Main]![Township] & [Main]![Range] AS Expr3, Main.Grantor, Main.Grantee, [Main]![Month of Transaction] & "/" & [Main]![Year of Transaction] AS Expr4, Format([Price],"$#,##0"
AS Expr2, Format([Main]![Price]/[Main]![Number of Units],"$#,##0.00" AS Expr1, Main.Financing, Main.Legal, Main.[OR BK], Main.[OR PG], Main.[Improvements Description], Main.[Site Description], Main.Density, Main.Verification, Format([PGI],"$#,##0" AS Expr10, Format([V&C],"$#,##0" AS Expr15, Format([EGI],"$#,##0" AS Expr16, Format([Expenses],"$#,##0" AS Expr17, Format([NOI],"$#,##0" AS Expr18, " " AS Expr14, Format([Main]![NOI]/[Main]![Price],"#.00%" AS Expr11, [Main]![Price]/[Main]![EGI] AS Expr12, Main.EDR, Format([Main]![Expenses]/[Main]![EGI],"#%" AS Expr13, Main.Comments, Format([Main]![PGI]/[Main]![Number of Units],"$#,##0.00" AS Expr5, Format([Main]![V&C]/[Main]![Number of Units],"$#,###.00" AS Expr6, Format([Main]![EGI]/[Main]![Number of Units],"$#,##0.00" AS Expr7, Format([Main]![Expenses]/[Main]![Number of Units],"$#,###.00" AS Expr8, Format([Main]![NOI]/[Main]![Number of Units],"$#,##0.00" AS Expr9, Main.Location, Main.Name, Main.[Price Cont], Main.[Date Cont], Main.[OR BK/PG Cont], Main.[Type of Units], Main.[Price/Unit Cont]FROM Main
WHERE (((Main.PropType)=15));
PropType can be any one of dozens of Number, Long Interger values. For some reason 15 comes up with an empty result, while others, 8, 12, 10 come up with the expected result - those records in which the criteria value of the query is the same as the Proptype field of the record.
I created a new query on the same table (Comps_be.mdb) with only the PropType field in the query and got the same result. For some reason the Criteria value 30 returns all the records with the value of 46, and no others. What gives? I Compacted and Repaired, and got the same results.
Is this indicative of a corrupt table? If so, absent a backup what is the best way to try to save the data in a new table, and leave the corruption behind?
Thanks in advance!
