Clicking OPEN or DESIGN query closes my .mdb

[ajaeger]

A couple weeks ago, I had a problem with my database. See

http://www.tek-tips.com/viewthread.cfm?qid=283848

for info on it. Basically, I had to 1) save all the code from my corrupted .mdb to .txt files, 2) remove the code from my corrupted .mdb, 3) create a new .mdb, 4) import all forms, queries, tables, modules into the new .mdb, 5) manually put the code (from the .txt files) back into my new .mdb.

That seemed to work, though now as I'm getting deeper into testing, I am seeing a quirky problem.

On some of my queries, if I click on them and try to OPEN or DESIGN them, the .mdb closes. I've manually created some of these queries - looking at a backup .mdb - but when I save the query, the .mdb closes.

So strange. THe queries worked in the other database. Anyone seen this?

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[ajaeger]

OK, I think the part of the query that Access didn't like was the concatenated field that I create called StatesRepresented:

SELECT tblTimeSlots.EventID, EvaluatorEventAssignments.EvalStudentState & ' ' & EvaluatorEventAssignments.EvalAdviserState AS StatesRepresented, EvaluatorEventAssignments.EvalStudentState
FROM (tblTimeSlots INNER JOIN StarEvents ON tblTimeSlots.EventID = StarEvents.ID) INNER JOIN EvaluatorEventAssignments ON tblTimeSlots.EventID = EvaluatorEventAssignments.EventID

Putting brackets around the table/field names enabled this query (and all my other queries that referenced this one) to run.

SELECT tblTimeSlots.EventID, [EvaluatorEventAssignments].[EvalStudentState] & ' ' & [EvaluatorEventAssignments].[EvalAdviserState] AS StatesRepresented, EvaluatorEventAssignments.EvalStudentState
FROM (tblTimeSlots INNER JOIN StarEvents ON tblTimeSlots.EventID = StarEvents.ID) INNER JOIN EvaluatorEventAssignments ON tblTimeSlots.EventID = EvaluatorEventAssignments.EventID

Can anyone explain to me why this is? What do the brackets mean, when are they necessary?

THANKS!




Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[JerryKlmns]

Anna, the second field of your query is a calculated field. Since EvaluatorEventAssignments.EvalStudentState and EvaluatorEventAssignments.EvalAdviserState are from EvaluatorEventAssignments table and not values to be calculated (i.e 4+5), you have to use these brackets the way you did. Inside brackets the are recognized as fields from a table.