Corrupt Querry -

  • Thread starter Thread starter Jim Bone
  • Start date Start date
J

Jim Bone

I have an existing query that uses several IIF functions.
When I run the original query it works fine. When I open
it up and change a parameter field=3 to field=4 and try to
run the same query I get an error message stating that I
am missing an operator. I know I have the right syntax
because I can do this on several other machines with
exactly the same database and query. I have tried
completely uninstalling and reinstalling office 97 but the
problem persists. Also when I look at the query I see
three quotes (""") instead of "," even though that is not
what I typed in. Again this only happens on this machine.

Any ideas on what to try next???


Thanks

Jim in Chicago
 
I have an existing query that uses several IIF functions.
When I run the original query it works fine. When I open
it up and change a parameter field=3 to field=4 and try to
run the same query I get an error message stating that I
am missing an operator. I know I have the right syntax
because I can do this on several other machines with
exactly the same database and query. I have tried
completely uninstalling and reinstalling office 97 but the
problem persists. Also when I look at the query I see
three quotes (""") instead of "," even though that is not
what I typed in. Again this only happens on this machine.
Click to expand...

Please post the SQL of the query. It sounds like you have something
very wierd going on!
 
Here is the SQL code:

SELECT Registration.SAL, Registration.FIRSTNAME,
Registration.LASTNAME, Registration.COMPANY,
PresentationDetail.Presentation, Registration.ADDRESS1,
Registration.ADDRESS2, Registration.CITY,
Registration.STATE, Registration.POSTALCODE,
Registration.COUNTRY, PresentationDetail.SpeakerTitle,
PresentationDetail.Bio,
PresentationDetail.PresentationTime, IIf([ohprojector]
=0,"NO","YES") AS OvrHdProject, IIf([lcddisplay]
=0,"NO","YES") AS [LCD Diplay], IIf([Videoprojector]
=0,"NO","YES") AS [Video Projector], IIf([VCR]
=0,"NO","YES") AS [V C R], IIf([Flipchart]=0,"NO","YES")
AS [Flip Chart], PresentationDetail.Other, IIf
([speciallights]=0,"NO","YES") AS [Special Lights], IIf
([phoneline]=0,"NO","YES") AS [Phone Line],
PresentationDetail.Moderator
FROM Registration INNER JOIN PresentationDetail ON
Registration.REGID = PresentationDetail.RegID
WHERE (((Registration.EventId)= 16))
ORDER BY PresentationDetail.Sort;

The problem arises when the user tries to change the
eventid=16 to some other value. They get an error that
there is a missing operator and when they look at the
query again they see that the IIF function are incorrect.
They have been changed from phoneline=0,"NO","YES" to
phoneline=1""NO"""YES". The comma's are changed to double
quotes. But they haven't changed them. They are also
unable to add a comma at this point.

Thanks

Jim
 
On Wed, 17 Sep 2003 07:26:52 -0700, "Jim Bone"

I'd get rid of all the IIF's entirely! You don't really need them; you
can attain the same effect using a Format on the textbox in which
these values will be displayed. Simply include the fields that you're
now using IIF in the query, as is; and set the Format property of the
form or report control in which you wish to display them to either

Yes/No

if the fields are Yes/No type fields, or to

"YES";"YES";"NO";"Unknown"

if they are numeric.

Why the code is being bollixed I don't know! I would suggest replacing
the 16 with

[Enter Event ID]

so the user gets a prompt and needn't edit the query at all; or better
yet, create a small Form frmCrit with a combo box cboEvent listing all
the events, and using a criterion

=Forms![frmCrit]![cboEvent]

so the user doesn't need to memorize event ID's.
Here is the SQL code:

SELECT Registration.SAL, Registration.FIRSTNAME,
Registration.LASTNAME, Registration.COMPANY,
PresentationDetail.Presentation, Registration.ADDRESS1,
Registration.ADDRESS2, Registration.CITY,
Registration.STATE, Registration.POSTALCODE,
Registration.COUNTRY, PresentationDetail.SpeakerTitle,
PresentationDetail.Bio,
PresentationDetail.PresentationTime, IIf([ohprojector]
=0,"NO","YES") AS OvrHdProject, IIf([lcddisplay]
=0,"NO","YES") AS [LCD Diplay], IIf([Videoprojector]
=0,"NO","YES") AS [Video Projector], IIf([VCR]
=0,"NO","YES") AS [V C R], IIf([Flipchart]=0,"NO","YES")
AS [Flip Chart], PresentationDetail.Other, IIf
([speciallights]=0,"NO","YES") AS [Special Lights], IIf
([phoneline]=0,"NO","YES") AS [Phone Line],
PresentationDetail.Moderator
FROM Registration INNER JOIN PresentationDetail ON
Registration.REGID = PresentationDetail.RegID
WHERE (((Registration.EventId)= 16))
ORDER BY PresentationDetail.Sort;

The problem arises when the user tries to change the
eventid=16 to some other value. They get an error that
there is a missing operator and when they look at the
query again they see that the IIF function are incorrect.
They have been changed from phoneline=0,"NO","YES" to
phoneline=1""NO"""YES". The comma's are changed to double
quotes. But they haven't changed them. They are also
unable to add a comma at this point.
Click to expand...
 
I've narrowed the problem down to trying to use any IIF
statement in any query causes the query to become
corrupt. I created a query from one table with one field
that contained an IIF statement and I get a syntax error
even though the syntax is correct. I'm unable to create
or modify a query that uses a IIF.

Any help would be appreciated.


Jim
 
I've narrowed the problem down to trying to use any IIF
statement in any query causes the query to become
corrupt. I created a query from one table with one field
that contained an IIF statement and I get a syntax error
even though the syntax is correct. I'm unable to create
or modify a query that uses a IIF.
Click to expand...

This appears to be the very common References bug. Open any
module in design view, or open the VBA editor by typing
Ctrl-G. Select Tools... References from the menu. One of the
..DLL files required by Access will probably be marked
MISSING. Uncheck it, recheck it, close and open Access.

If none are MISSING, check any reference; close and open
Access; then uncheck it again. This will force Access to
relink the libraries.
 
Back
Top