access generates error when i open a query

[9815402440]

hi
i am using acess 2003 as back end of my application.
database was fine a few days back. i have a table named Ac_LedgerMaster .
i have several tables that are related to this table. now relations to a field in this table have reached the maximum limit i.e. i can no more relate this paricular field to any other table. but this database is still fine. after that i added 7 union queries. there are 3 other quereis that are drawing data from the 7 union queries.
now whenever i add a new table or query in the database one of the 3 queries gets currupted. when i double clik on a paricular query (out of aforementioned 3 quereis) then following error message is displayed
"Microsoft access database has encountred a problam and needs to be closed."
on clicking 'Dont Send' button database closes.

any minor change in that query solve the problam. i open it in design view. and replace double quotes with single quotes e.g. numAmount:val(n umAmount & "") is replaced with numAmount:val(n umAmount & '')

whenever i add a new query or alter an existing query any one of the 3 quereis gets corrupted. now i replace the single quotes with double quotes. it again solve the problam. but now a query is currupted again. i could not resolve the plroblam by all of my efforts.


please help
regards
manpreet singh dhillon hoshiarpur

[puppydogbuddy]

assuming that numAmount is numeric, change this:
numAmount:val(n umAmount & '')

To this:
numAmount:nz(nu mAmount,0)

[nico5038]

Sounds like your database is corrupted, check:
Corrupt Microsoft Access MDBs FAQ

Personally I would start with importing all objects into a new database with File/Get external data/Import.

Nic;o)

[9815402440]

hi nico5038
i tried importing all objects in new database. when i run a query (only out of 3 queries mentioned above) at first it gives message 'Query is too complex'
when i double click again then same error (Send/Dont Send) reporting box occurs.
i unable to know why only one of these three queries gets corrupted.
please help

[nico5038]

Can you create a small sample .mdb and add that zipped to your comment (click the Advanced button)
so I can have a look ?

Nic;o)

[NeoPa]

I had a problem similar to that once. I found the query was un-editable.

I got around it by taking the SQL out of the query (CurrentDB.Quer yDefs("Query Name").SQL) and checking it manually. I fixed any problems I found then built up a string with the new version. This was then injected back into the query :

CurrentDB.QueryDefs("[U]Query Name[/U]").SQL = strSQL

Hope this is some use.