IIf IsNull() Complile Error

[Guest]

In my query I keep receiving a compiling error. The error message says
"Compile Error in Query Expression..." and then the query expression is
given, as follows:

BldgName: IIf(IsNull([ParkName]),[AddressNum] & " " &
[Street],IIf([UseAddNotParkForName]=True,[AddressNum] & " " &
[Street],[ParkName] & " " & [BuildingNo]))

I cannot figure this out. I have stripped off the joins in order to make a
simpler query, and it still gets hung up on using IIf(isNull()). Is the
IsNull() statement a problem?

Note: I currently use the exact same query in a separate db and it works
fine....

FYI, the SQL is as follows, and thanks in advance for your help:

SELECT tblMarkets.Market, tblSubmarkets.Submarket, tblParks.ParkName,
IIf(IsNull([ParkName]),[AddressNum] & " " &
[Street],IIf([UseAddNotParkForName]=True,[AddressNum] & " " &
[Street],[ParkName] & " " & [BuildingNo])) AS BldgName,
tblEntities.BuildingID, tblEntities.TempBuildingID, tblEntities.BuildingNo,
tblStatus.StatusID, tblStatus.StatusDesc, tblEntities.AddressNum,
tblEntities.Street, tblEntities.City, tblEntities.State, tblEntities.Zip,
tblEntities.County, tblEntities.AcresGross, tblEntities.AcresNet,
tblEntities.APN, tblEntities.SF
FROM tblStatus RIGHT JOIN (((tblMarkets RIGHT JOIN tblEntities ON
tblMarkets.MarketID = tblEntities.MarketID) LEFT JOIN tblSubmarkets ON
tblEntities.SubmarketID = tblSubmarkets.SubmarketID) LEFT JOIN tblParks ON
tblEntities.ParkID = tblParks.ParkID) ON tblStatus.StatusID =
tblEntities.StatusID
WHERE (((tblEntities.TempBuildingID)=No) AND ((tblStatus.StatusID)>3))
ORDER BY tblMarkets.Market, tblSubmarkets.Submarket, tblParks.ParkName,
tblEntities.BuildingID;

 

[Guest]

Try

IsNull([ParkName])=True

Also check field names as it works in another database. Come to think about
it, also check references to make sure one isn't missing.

 

[Guest]

No, I'm afraid that does not work, either. Same error message.

Query field now reads: BldgName: IIf(IsNull([ParkName])=True,[AddressNum] &
" " & [Street],IIf([UseAddNotParkForName]=True,[AddressNum] & " " &
[Street],[ParkName] & " " & [BuildingNo]))

Try

IsNull([ParkName])=True

Also check field names as it works in another database. Come to think about
it, also check references to make sure one isn't missing.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

In my query I keep receiving a compiling error. The error message says
"Compile Error in Query Expression..." and then the query expression is
given, as follows:

BldgName: IIf(IsNull([ParkName]),[AddressNum] & " " &
[Street],IIf([UseAddNotParkForName]=True,[AddressNum] & " " &
[Street],[ParkName] & " " & [BuildingNo]))

I cannot figure this out. I have stripped off the joins in order to make a
simpler query, and it still gets hung up on using IIf(isNull()). Is the
IsNull() statement a problem?

Note: I currently use the exact same query in a separate db and it works
fine....

FYI, the SQL is as follows, and thanks in advance for your help:

SELECT tblMarkets.Market, tblSubmarkets.Submarket, tblParks.ParkName,
IIf(IsNull([ParkName]),[AddressNum] & " " &
[Street],IIf([UseAddNotParkForName]=True,[AddressNum] & " " &
[Street],[ParkName] & " " & [BuildingNo])) AS BldgName,
tblEntities.BuildingID, tblEntities.TempBuildingID, tblEntities.BuildingNo,
tblStatus.StatusID, tblStatus.StatusDesc, tblEntities.AddressNum,
tblEntities.Street, tblEntities.City, tblEntities.State, tblEntities.Zip,
tblEntities.County, tblEntities.AcresGross, tblEntities.AcresNet,
tblEntities.APN, tblEntities.SF
FROM tblStatus RIGHT JOIN (((tblMarkets RIGHT JOIN tblEntities ON
tblMarkets.MarketID = tblEntities.MarketID) LEFT JOIN tblSubmarkets ON
tblEntities.SubmarketID = tblSubmarkets.SubmarketID) LEFT JOIN tblParks ON
tblEntities.ParkID = tblParks.ParkID) ON tblStatus.StatusID =
tblEntities.StatusID
WHERE (((tblEntities.TempBuildingID)=No) AND ((tblStatus.StatusID)>3))
ORDER BY tblMarkets.Market, tblSubmarkets.Submarket, tblParks.ParkName,
tblEntities.BuildingID;

 

[Guest]

Just to add a little more info, I stripped out all the joined tables except
Park, and removed all the fields except the ones in use. I still get the
same error "Compile Error in Query Expression", and the SQL now reads as
follows:

SELECT tblParks.ParkName, IIf(IsNull([ParkName])=True,1,2) AS BldgName
FROM tblEntities LEFT JOIN tblParks ON tblEntities.ParkID = tblParks.ParkID
ORDER BY tblParks.ParkName;

Try

IsNull([ParkName])=True

Also check field names as it works in another database. Come to think about
it, also check references to make sure one isn't missing.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

In my query I keep receiving a compiling error. The error message says
"Compile Error in Query Expression..." and then the query expression is
given, as follows:

BldgName: IIf(IsNull([ParkName]),[AddressNum] & " " &
[Street],IIf([UseAddNotParkForName]=True,[AddressNum] & " " &
[Street],[ParkName] & " " & [BuildingNo]))

I cannot figure this out. I have stripped off the joins in order to make a
simpler query, and it still gets hung up on using IIf(isNull()). Is the
IsNull() statement a problem?

Note: I currently use the exact same query in a separate db and it works
fine....

FYI, the SQL is as follows, and thanks in advance for your help:

SELECT tblMarkets.Market, tblSubmarkets.Submarket, tblParks.ParkName,
IIf(IsNull([ParkName]),[AddressNum] & " " &
[Street],IIf([UseAddNotParkForName]=True,[AddressNum] & " " &
[Street],[ParkName] & " " & [BuildingNo])) AS BldgName,
tblEntities.BuildingID, tblEntities.TempBuildingID, tblEntities.BuildingNo,
tblStatus.StatusID, tblStatus.StatusDesc, tblEntities.AddressNum,
tblEntities.Street, tblEntities.City, tblEntities.State, tblEntities.Zip,
tblEntities.County, tblEntities.AcresGross, tblEntities.AcresNet,
tblEntities.APN, tblEntities.SF
FROM tblStatus RIGHT JOIN (((tblMarkets RIGHT JOIN tblEntities ON
tblMarkets.MarketID = tblEntities.MarketID) LEFT JOIN tblSubmarkets ON
tblEntities.SubmarketID = tblSubmarkets.SubmarketID) LEFT JOIN tblParks ON
tblEntities.ParkID = tblParks.ParkID) ON tblStatus.StatusID =
tblEntities.StatusID
WHERE (((tblEntities.TempBuildingID)=No) AND ((tblStatus.StatusID)>3))
ORDER BY tblMarkets.Market, tblSubmarkets.Submarket, tblParks.ParkName,
tblEntities.BuildingID;

 

[John Spencer]

Try

IIF([ParkName] is Null, ..., ...)

BldgName: IIf([ParkName] Is Null,
[AddressNum] & " " & [Street],
IIf([UseAddNotParkForName]=True,
[AddressNum] & " " & [Street],
[ParkName] & " " & [BuildingNo]))


I suspect that Jerry Whittle is right and you could have a library
references problem in your database. If so, IsNull (which is VBA function)
may be failing.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

No, I'm afraid that does not work, either. Same error message.

Query field now reads: BldgName: IIf(IsNull([ParkName])=True,[AddressNum]
&
" " & [Street],IIf([UseAddNotParkForName]=True,[AddressNum] & " " &
[Street],[ParkName] & " " & [BuildingNo]))

Try

IsNull([ParkName])=True

Also check field names as it works in another database. Come to think
about
it, also check references to make sure one isn't missing.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

In my query I keep receiving a compiling error. The error message says
"Compile Error in Query Expression..." and then the query expression is
given, as follows:

BldgName: IIf(IsNull([ParkName]),[AddressNum] & " " &
[Street],IIf([UseAddNotParkForName]=True,[AddressNum] & " " &
[Street],[ParkName] & " " & [BuildingNo]))

I cannot figure this out. I have stripped off the joins in order to
make a
simpler query, and it still gets hung up on using IIf(isNull()). Is
the
IsNull() statement a problem?

Note: I currently use the exact same query in a separate db and it
works
fine....

FYI, the SQL is as follows, and thanks in advance for your help:

SELECT tblMarkets.Market, tblSubmarkets.Submarket, tblParks.ParkName,
IIf(IsNull([ParkName]),[AddressNum] & " " &
[Street],IIf([UseAddNotParkForName]=True,[AddressNum] & " " &
[Street],[ParkName] & " " & [BuildingNo])) AS BldgName,
tblEntities.BuildingID, tblEntities.TempBuildingID,
tblEntities.BuildingNo,
tblStatus.StatusID, tblStatus.StatusDesc, tblEntities.AddressNum,
tblEntities.Street, tblEntities.City, tblEntities.State,
tblEntities.Zip,
tblEntities.County, tblEntities.AcresGross, tblEntities.AcresNet,
tblEntities.APN, tblEntities.SF
FROM tblStatus RIGHT JOIN (((tblMarkets RIGHT JOIN tblEntities ON
tblMarkets.MarketID = tblEntities.MarketID) LEFT JOIN tblSubmarkets ON
tblEntities.SubmarketID = tblSubmarkets.SubmarketID) LEFT JOIN tblParks
ON
tblEntities.ParkID = tblParks.ParkID) ON tblStatus.StatusID =
tblEntities.StatusID
WHERE (((tblEntities.TempBuildingID)=No) AND ((tblStatus.StatusID)>3))
ORDER BY tblMarkets.Market, tblSubmarkets.Submarket, tblParks.ParkName,
tblEntities.BuildingID;

 

[Guest]

Try this:

Do a Ctrl + g to bring up the Visual Basic windows.

In the Immediate window put the following:

Debug.Print IsNull(Null)=True

Press the Enter key. It should return True. If it returns an error, then
there is most likely something wrong with your references.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Just to add a little more info, I stripped out all the joined tables except
Park, and removed all the fields except the ones in use. I still get the
same error "Compile Error in Query Expression", and the SQL now reads as
follows:

SELECT tblParks.ParkName, IIf(IsNull([ParkName])=True,1,2) AS BldgName
FROM tblEntities LEFT JOIN tblParks ON tblEntities.ParkID = tblParks.ParkID
ORDER BY tblParks.ParkName;

Try

IsNull([ParkName])=True

Also check field names as it works in another database. Come to think about
it, also check references to make sure one isn't missing.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

In my query I keep receiving a compiling error. The error message says
"Compile Error in Query Expression..." and then the query expression is
given, as follows:

BldgName: IIf(IsNull([ParkName]),[AddressNum] & " " &
[Street],IIf([UseAddNotParkForName]=True,[AddressNum] & " " &
[Street],[ParkName] & " " & [BuildingNo]))

I cannot figure this out. I have stripped off the joins in order to make a
simpler query, and it still gets hung up on using IIf(isNull()). Is the
IsNull() statement a problem?

Note: I currently use the exact same query in a separate db and it works
fine....

FYI, the SQL is as follows, and thanks in advance for your help:

SELECT tblMarkets.Market, tblSubmarkets.Submarket, tblParks.ParkName,
IIf(IsNull([ParkName]),[AddressNum] & " " &
[Street],IIf([UseAddNotParkForName]=True,[AddressNum] & " " &
[Street],[ParkName] & " " & [BuildingNo])) AS BldgName,
tblEntities.BuildingID, tblEntities.TempBuildingID, tblEntities.BuildingNo,
tblStatus.StatusID, tblStatus.StatusDesc, tblEntities.AddressNum,
tblEntities.Street, tblEntities.City, tblEntities.State, tblEntities.Zip,
tblEntities.County, tblEntities.AcresGross, tblEntities.AcresNet,
tblEntities.APN, tblEntities.SF
FROM tblStatus RIGHT JOIN (((tblMarkets RIGHT JOIN tblEntities ON
tblMarkets.MarketID = tblEntities.MarketID) LEFT JOIN tblSubmarkets ON
tblEntities.SubmarketID = tblSubmarkets.SubmarketID) LEFT JOIN tblParks ON
tblEntities.ParkID = tblParks.ParkID) ON tblStatus.StatusID =
tblEntities.StatusID
WHERE (((tblEntities.TempBuildingID)=No) AND ((tblStatus.StatusID)>3))
ORDER BY tblMarkets.Market, tblSubmarkets.Submarket, tblParks.ParkName,
tblEntities.BuildingID;

 

[Guest]

Your suggestion does give me an error, so it does sound like a reference
problem.

I am a novice regarding reference matters.

When I open the Visual Basic Editor and go to My Tools/Reference, the
following items are checked:

- Visual Basic for Applications
- Microsoft Access 10.0 Object Library
- OLE Automation
- Microsoft Office XP Web Components

Should there be other checked items? Or is there a problem with these
references working correctly?

Thanks.

Try this:

Do a Ctrl + g to bring up the Visual Basic windows.

In the Immediate window put the following:

Debug.Print IsNull(Null)=True

Press the Enter key. It should return True. If it returns an error, then
there is most likely something wrong with your references.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Just to add a little more info, I stripped out all the joined tables except
Park, and removed all the fields except the ones in use. I still get the
same error "Compile Error in Query Expression", and the SQL now reads as
follows:

SELECT tblParks.ParkName, IIf(IsNull([ParkName])=True,1,2) AS BldgName
FROM tblEntities LEFT JOIN tblParks ON tblEntities.ParkID = tblParks.ParkID
ORDER BY tblParks.ParkName;

Try

IsNull([ParkName])=True

Also check field names as it works in another database. Come to think about
it, also check references to make sure one isn't missing.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

In my query I keep receiving a compiling error. The error message says
"Compile Error in Query Expression..." and then the query expression is
given, as follows:

BldgName: IIf(IsNull([ParkName]),[AddressNum] & " " &
[Street],IIf([UseAddNotParkForName]=True,[AddressNum] & " " &
[Street],[ParkName] & " " & [BuildingNo]))

I cannot figure this out. I have stripped off the joins in order to make a
simpler query, and it still gets hung up on using IIf(isNull()). Is the
IsNull() statement a problem?

Note: I currently use the exact same query in a separate db and it works
fine....

FYI, the SQL is as follows, and thanks in advance for your help:

SELECT tblMarkets.Market, tblSubmarkets.Submarket, tblParks.ParkName,
IIf(IsNull([ParkName]),[AddressNum] & " " &
[Street],IIf([UseAddNotParkForName]=True,[AddressNum] & " " &
[Street],[ParkName] & " " & [BuildingNo])) AS BldgName,
tblEntities.BuildingID, tblEntities.TempBuildingID, tblEntities.BuildingNo,
tblStatus.StatusID, tblStatus.StatusDesc, tblEntities.AddressNum,
tblEntities.Street, tblEntities.City, tblEntities.State, tblEntities.Zip,
tblEntities.County, tblEntities.AcresGross, tblEntities.AcresNet,
tblEntities.APN, tblEntities.SF
FROM tblStatus RIGHT JOIN (((tblMarkets RIGHT JOIN tblEntities ON
tblMarkets.MarketID = tblEntities.MarketID) LEFT JOIN tblSubmarkets ON
tblEntities.SubmarketID = tblSubmarkets.SubmarketID) LEFT JOIN tblParks ON
tblEntities.ParkID = tblParks.ParkID) ON tblStatus.StatusID =
tblEntities.StatusID
WHERE (((tblEntities.TempBuildingID)=No) AND ((tblStatus.StatusID)>3))
ORDER BY tblMarkets.Market, tblSubmarkets.Submarket, tblParks.ParkName,
tblEntities.BuildingID;

 

[Guest]

You may also need:
Microsoft DAO 2.5/3.5 Compatibility Library
Microsoft Visual Basic for Applications Extensibility ....
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Your suggestion does give me an error, so it does sound like a reference
problem.

I am a novice regarding reference matters.

When I open the Visual Basic Editor and go to My Tools/Reference, the
following items are checked:

- Visual Basic for Applications
- Microsoft Access 10.0 Object Library
- OLE Automation
- Microsoft Office XP Web Components

Should there be other checked items? Or is there a problem with these
references working correctly?

Thanks.

Try this:

Do a Ctrl + g to bring up the Visual Basic windows.

In the Immediate window put the following:

Debug.Print IsNull(Null)=True

Press the Enter key. It should return True. If it returns an error, then
there is most likely something wrong with your references.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Just to add a little more info, I stripped out all the joined tables except
Park, and removed all the fields except the ones in use. I still get the
same error "Compile Error in Query Expression", and the SQL now reads as
follows:

SELECT tblParks.ParkName, IIf(IsNull([ParkName])=True,1,2) AS BldgName
FROM tblEntities LEFT JOIN tblParks ON tblEntities.ParkID = tblParks.ParkID
ORDER BY tblParks.ParkName;


:

Try

IsNull([ParkName])=True

Also check field names as it works in another database. Come to think about
it, also check references to make sure one isn't missing.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

In my query I keep receiving a compiling error. The error message says
"Compile Error in Query Expression..." and then the query expression is
given, as follows:

BldgName: IIf(IsNull([ParkName]),[AddressNum] & " " &
[Street],IIf([UseAddNotParkForName]=True,[AddressNum] & " " &
[Street],[ParkName] & " " & [BuildingNo]))

I cannot figure this out. I have stripped off the joins in order to make a
simpler query, and it still gets hung up on using IIf(isNull()). Is the
IsNull() statement a problem?

Note: I currently use the exact same query in a separate db and it works
fine....

FYI, the SQL is as follows, and thanks in advance for your help:

SELECT tblMarkets.Market, tblSubmarkets.Submarket, tblParks.ParkName,
IIf(IsNull([ParkName]),[AddressNum] & " " &
[Street],IIf([UseAddNotParkForName]=True,[AddressNum] & " " &
[Street],[ParkName] & " " & [BuildingNo])) AS BldgName,
tblEntities.BuildingID, tblEntities.TempBuildingID, tblEntities.BuildingNo,
tblStatus.StatusID, tblStatus.StatusDesc, tblEntities.AddressNum,
tblEntities.Street, tblEntities.City, tblEntities.State, tblEntities.Zip,
tblEntities.County, tblEntities.AcresGross, tblEntities.AcresNet,
tblEntities.APN, tblEntities.SF
FROM tblStatus RIGHT JOIN (((tblMarkets RIGHT JOIN tblEntities ON
tblMarkets.MarketID = tblEntities.MarketID) LEFT JOIN tblSubmarkets ON
tblEntities.SubmarketID = tblSubmarkets.SubmarketID) LEFT JOIN tblParks ON
tblEntities.ParkID = tblParks.ParkID) ON tblStatus.StatusID =
tblEntities.StatusID
WHERE (((tblEntities.TempBuildingID)=No) AND ((tblStatus.StatusID)>3))
ORDER BY tblMarkets.Market, tblSubmarkets.Submarket, tblParks.ParkName,
tblEntities.BuildingID;

 

[Guest]

Another thought: If the query works on another computer, check its references!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Your suggestion does give me an error, so it does sound like a reference
problem.

I am a novice regarding reference matters.

When I open the Visual Basic Editor and go to My Tools/Reference, the
following items are checked:

- Visual Basic for Applications
- Microsoft Access 10.0 Object Library
- OLE Automation
- Microsoft Office XP Web Components

Should there be other checked items? Or is there a problem with these
references working correctly?

Thanks.

Try this:

Do a Ctrl + g to bring up the Visual Basic windows.

In the Immediate window put the following:

Debug.Print IsNull(Null)=True

Press the Enter key. It should return True. If it returns an error, then
there is most likely something wrong with your references.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Just to add a little more info, I stripped out all the joined tables except
Park, and removed all the fields except the ones in use. I still get the
same error "Compile Error in Query Expression", and the SQL now reads as
follows:

SELECT tblParks.ParkName, IIf(IsNull([ParkName])=True,1,2) AS BldgName
FROM tblEntities LEFT JOIN tblParks ON tblEntities.ParkID = tblParks.ParkID
ORDER BY tblParks.ParkName;


:

Try

IsNull([ParkName])=True

Also check field names as it works in another database. Come to think about
it, also check references to make sure one isn't missing.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

In my query I keep receiving a compiling error. The error message says
"Compile Error in Query Expression..." and then the query expression is
given, as follows:

BldgName: IIf(IsNull([ParkName]),[AddressNum] & " " &
[Street],IIf([UseAddNotParkForName]=True,[AddressNum] & " " &
[Street],[ParkName] & " " & [BuildingNo]))

I cannot figure this out. I have stripped off the joins in order to make a
simpler query, and it still gets hung up on using IIf(isNull()). Is the
IsNull() statement a problem?

Note: I currently use the exact same query in a separate db and it works
fine....

FYI, the SQL is as follows, and thanks in advance for your help:

SELECT tblMarkets.Market, tblSubmarkets.Submarket, tblParks.ParkName,
IIf(IsNull([ParkName]),[AddressNum] & " " &
[Street],IIf([UseAddNotParkForName]=True,[AddressNum] & " " &
[Street],[ParkName] & " " & [BuildingNo])) AS BldgName,
tblEntities.BuildingID, tblEntities.TempBuildingID, tblEntities.BuildingNo,
tblStatus.StatusID, tblStatus.StatusDesc, tblEntities.AddressNum,
tblEntities.Street, tblEntities.City, tblEntities.State, tblEntities.Zip,
tblEntities.County, tblEntities.AcresGross, tblEntities.AcresNet,
tblEntities.APN, tblEntities.SF
FROM tblStatus RIGHT JOIN (((tblMarkets RIGHT JOIN tblEntities ON
tblMarkets.MarketID = tblEntities.MarketID) LEFT JOIN tblSubmarkets ON
tblEntities.SubmarketID = tblSubmarkets.SubmarketID) LEFT JOIN tblParks ON
tblEntities.ParkID = tblParks.ParkID) ON tblStatus.StatusID =
tblEntities.StatusID
WHERE (((tblEntities.TempBuildingID)=No) AND ((tblStatus.StatusID)>3))
ORDER BY tblMarkets.Market, tblSubmarkets.Submarket, tblParks.ParkName,
tblEntities.BuildingID;

 

[Guest]

I thought we had something there, but still no go.

I changed the reference library to what you suggested - - and also compared
the checked items with the ones that were in the project that worked.

Now my new project matches the same checked items, in the same order, as my
other project.

I still receive a compile error on the IsNull statement.

Is there some reason that maybe the references are not yet recognized? I
have exited the program and restarted it, but still the same result.

Thanks for your help. Please advise if any further suggestions.....

Another thought: If the query works on another computer, check its references!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Your suggestion does give me an error, so it does sound like a reference
problem.

I am a novice regarding reference matters.

When I open the Visual Basic Editor and go to My Tools/Reference, the
following items are checked:

- Visual Basic for Applications
- Microsoft Access 10.0 Object Library
- OLE Automation
- Microsoft Office XP Web Components

Should there be other checked items? Or is there a problem with these
references working correctly?

Thanks.

Try this:

Do a Ctrl + g to bring up the Visual Basic windows.

In the Immediate window put the following:

Debug.Print IsNull(Null)=True

Press the Enter key. It should return True. If it returns an error, then
there is most likely something wrong with your references.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Just to add a little more info, I stripped out all the joined tables except
Park, and removed all the fields except the ones in use. I still get the
same error "Compile Error in Query Expression", and the SQL now reads as
follows:

SELECT tblParks.ParkName, IIf(IsNull([ParkName])=True,1,2) AS BldgName
FROM tblEntities LEFT JOIN tblParks ON tblEntities.ParkID = tblParks.ParkID
ORDER BY tblParks.ParkName;


:

Try

IsNull([ParkName])=True

Also check field names as it works in another database. Come to think about
it, also check references to make sure one isn't missing.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

In my query I keep receiving a compiling error. The error message says
"Compile Error in Query Expression..." and then the query expression is
given, as follows:

BldgName: IIf(IsNull([ParkName]),[AddressNum] & " " &
[Street],IIf([UseAddNotParkForName]=True,[AddressNum] & " " &
[Street],[ParkName] & " " & [BuildingNo]))

I cannot figure this out. I have stripped off the joins in order to make a
simpler query, and it still gets hung up on using IIf(isNull()). Is the
IsNull() statement a problem?

Note: I currently use the exact same query in a separate db and it works
fine....

FYI, the SQL is as follows, and thanks in advance for your help:

SELECT tblMarkets.Market, tblSubmarkets.Submarket, tblParks.ParkName,
IIf(IsNull([ParkName]),[AddressNum] & " " &
[Street],IIf([UseAddNotParkForName]=True,[AddressNum] & " " &
[Street],[ParkName] & " " & [BuildingNo])) AS BldgName,
tblEntities.BuildingID, tblEntities.TempBuildingID, tblEntities.BuildingNo,
tblStatus.StatusID, tblStatus.StatusDesc, tblEntities.AddressNum,
tblEntities.Street, tblEntities.City, tblEntities.State, tblEntities.Zip,
tblEntities.County, tblEntities.AcresGross, tblEntities.AcresNet,
tblEntities.APN, tblEntities.SF
FROM tblStatus RIGHT JOIN (((tblMarkets RIGHT JOIN tblEntities ON
tblMarkets.MarketID = tblEntities.MarketID) LEFT JOIN tblSubmarkets ON
tblEntities.SubmarketID = tblSubmarkets.SubmarketID) LEFT JOIN tblParks ON
tblEntities.ParkID = tblParks.ParkID) ON tblStatus.StatusID =
tblEntities.StatusID
WHERE (((tblEntities.TempBuildingID)=No) AND ((tblStatus.StatusID)>3))
ORDER BY tblMarkets.Market, tblSubmarkets.Submarket, tblParks.ParkName,
tblEntities.BuildingID;