Spontaneous 'Compile Error' In Queries

  • Thread starter Thread starter (PeteCresswell)
  • Start date Start date
P

(PeteCresswell)

I've got a half-dozen queries that contain this expression:

IssuerName: IIf(IsNull([tblIssuer].[IssuerID]),"[Issuer
Unknown]",[tblIssuer].[IssuerName])

Haven't touched any of them, but suddenly they were all throwing
"Compile error. in query expression 'IssuerName:
IIf(IsNull([tblIssuer].[IssuerID]),"[Issuer
Unknown]",[tblIssuer].[IssuerName])'."

tblIssuer.IssuerID is still there in a link.

My kneejerk was to somehow force recompiles of all the queries,
so I did a Compact/Repair.

After the compact/repair, all was well: the errors went away.

Can anybody elucidate?
 
Could have been a corruption issue that C&R fixed.

Also if Office 2003, the recent Service Pack 3 has been causing a sorts of
strange problems with Access and the rest of the Office suite.
 
Per Jerry Whittle:
Could have been a corruption issue that C&R fixed.

Also if Office 2003, the recent Service Pack 3 has been causing a sorts of
strange problems with Access and the rest of the Office suite.
Click to expand...

I'm only on SP2, so luckily that's out of the picture.

I'm wondering if the whole application DB is on the way out.

I've had a couple other weirdnesses.

In one of them, every time I changed a line of code in a certain
routine the constants defined in same stopped being recognized
(something about "constant value required") until I commented
them out, attempted another compile (which would throw something
like "no such field" at the refs), then un-commented them and
compiled again.

In the other, the call to a routine with a fairly long list of
passed parms stopped recognizing a couple of the controls on the
form in which the calling routine was written. Then, after some
poking/prodding/kicking it was OK again.

Maybe it's time to brush on my SOP for reducing an application
totally to text and then re-creating it from scratch by importing
the text.
 
(PeteCresswell) said:
I've got a half-dozen queries that contain this expression:

IssuerName: IIf(IsNull([tblIssuer].[IssuerID]),"[Issuer
Unknown]",[tblIssuer].[IssuerName])

Haven't touched any of them, but suddenly they were all throwing
"Compile error. in query expression 'IssuerName:
IIf(IsNull([tblIssuer].[IssuerID]),"[Issuer
Unknown]",[tblIssuer].[IssuerName])'."

tblIssuer.IssuerID is still there in a link.

My kneejerk was to somehow force recompiles of all the queries,
so I did a Compact/Repair.

After the compact/repair, all was well: the errors went away.

Can anybody elucidate?
Click to expand...


I agree with Jerry, it sounds like you had some sort of corruption. But I
thought I'd mention that you can insulate your queries from VBA problems by
avoiding the use of VBA functions in the queries wherever you can. Often
you can't, but in the case of an IsNull() expression, you can use Is Null
instead:

IssuerName: IIf([tblIssuer].[IssuerID] Is Null,
"[Issuer Unknown]",[tblIssuer].[IssuerName])

The IIf() function itself is also built into Jet SQL; it doesn't require a
call to the VBA function of the same name.
 
Per Dirk Goldgar:
I agree with Jerry, it sounds like you had some sort of corruption. But I
thought I'd mention that you can insulate your queries from VBA problems by
avoiding the use of VBA functions in the queries wherever you can. Often
you can't, but in the case of an IsNull() expression, you can use Is Null
instead:
Click to expand...

Thanks!.... I'm gonna fire up FindAndReplace ASAP and root those
things out.
 
Back
Top