By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,222 Members | 1,395 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,222 IT Pros & Developers. It's quick & easy.

Self Destructing Query Expressions?

P: 25
All right I am new at access but why would my calculated fields in my query self destruct all of a sudden? I tested these expressions, saved the query and closed it. I reopened them and tested it. Then left access for a minute, but left it open, and when I return to it I get the following:

Compile error . in query expression '{quotes expression}

This is the expression that is quoted when I try to run the query:

Format(Mid(Project.ProjectNo,(InStr(1,Project.Proj ectNo,"-")+1),4),"#") AS RouteN

This is the expression that is quoted when I try to open the query in design view:

Format(parcelRangeQuery.Expr1,"0") AS Expr1005

It then opens it in SQL view and here is the whole text of the query:

SELECT Owners.MailCity, Owners.OwnName, Parcels.ParcelNo, SubParcels.SubParcelNo, Owners.OwnCounty, Owners.MailState, Owners.OwnerType, SubParcels.IsRefParcel, Project.ProjectNo, Format(Mid(Project.ProjectNo,(InStr(1,Project.Proj ectNo,"-")+1),4),"#") AS RouteNo, Parcels.SideOfRoad, Parcels.LowSta, Parcels.HighSta, SubParcels.Description, SubParcels.IsNA, SubParcels.IsLA, Properties.TaxID, Prop_isIn_Municipality.MunicCounty, Format(parcelRangeQuery.Expr1,"0") AS Expr1005
FROM ((Project INNER JOIN (((Parcels INNER JOIN parcelRangeQuery ON Parcels.ParcelNo = parcelRangeQuery.ParcelNo) INNER JOIN Properties ON Parcels.ParcelNo = Properties.ParcelNo) INNER JOIN SubParcels ON Parcels.ParcelNo = SubParcels.ParcelNo) ON Project.ProjectNo = Parcels.ProjectNo) INNER JOIN Prop_isIn_Municipality ON Properties.TaxID = Prop_isIn_Municipality.PropTaxID) INNER JOIN (Owners INNER JOIN Property_has_Owners ON Owners.OwnName = Property_has_Owners.Owners) ON Properties.TaxID = Property_has_Owners.PropTaxID
WHERE (((Parcels.ParcelNo)=[Enter Parcel No]))
ORDER BY Owners.MailCity DESC , Owners.OwnName DESC , SubParcels.SubParcelNo, SubParcels.IsRefParcel DESC , Properties.TaxID;
Jun 27 '07 #1
Share this Question
Share on Google+
3 Replies


P: 25
I am even more confused now, it is working again.

Can anyone tell me what is going on?
Jun 27 '07 #2

JKing
Expert 100+
P: 1,206
The only thing I can see wrong is a misplaced space in the line within the InStr between Proj and ectNo:

Format(Mid(Project.ProjectNo,(InStr(1,Project.Proj ectNo,"-")+1),4),"#") AS RouteN

Now I'm assuming you closed it without saving and upon reopening it's now working again. Misplaced spaces can cause errors... so perhaps you hit spacebar accidentally?
Jun 27 '07 #3

P: 25
The only thing I can see wrong is a misplaced space in the line within the InStr between Proj and ectNo:

Format(Mid(Project.ProjectNo,(InStr(1,Project.Proj ectNo,"-")+1),4),"#") AS RouteN

Now I'm assuming you closed it without saving and upon reopening it's now working again. Misplaced spaces can cause errors... so perhaps you hit spacebar accidentally?

Actually I saved when I closed and had noticed the space after I posted (it doesn't show in the actual query). I guess if it happens again I'll have more information. Thank you for your reply.
Jun 28 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.