Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-31-2017, 08:27 AM   #1
pke8jt
Newly Registered User
 
Join Date: Oct 2017
Location: colorado
Posts: 27
Thanks: 17
Thanked 0 Times in 0 Posts
pke8jt is on a distinguished road
sudden compile error in query expression, IIF

Hi, I used an actual aggregation function in my IIF statement, so that my query at execution time know what the column SumOfDollarsSold is.

Then I get compile error in query expression, 'IIF(ISNULL(Sum(dbo_SO_SalesHistory.DollarsSold)), 0,Sum(dbo_SO_SalesHistory.DollarsSold)'.

It is bizzare because it worked fine until now.. Any advise would be highly appreciated. Thank you!

SQL:
PARAMETERS [BeginDate] DateTime, [EndDate] DateTime;
SELECT IIF(ISNULL(Sum(dbo_SO_SalesHistory.DollarsSold)),0 ,Sum(dbo_SO_SalesHistory.DollarsSold)) AS SumOfDollarsSold
FROM dbo_SO_SalesHistory
WHERE (((dbo_SO_SalesHistory.InvoiceDate) Between [BeginDate] And [EndDate]) AND ((dbo_SO_SalesHistory.CustomerNo)="B"));

pke8jt is offline   Reply With Quote
Old 10-31-2017, 08:55 AM   #2
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,487
Thanks: 68
Thanked 2,723 Times in 2,608 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: sudden compile error in query expression, IIF

you can use:

SumOfDollarsSold: Sum(Nz(Dbo_SO_SalesHistory.DollarsSold,0))
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 10-31-2017, 08:57 AM   #3
pke8jt
Newly Registered User
 
Join Date: Oct 2017
Location: colorado
Posts: 27
Thanks: 17
Thanked 0 Times in 0 Posts
pke8jt is on a distinguished road
Re: sudden compile error in query expression, IIF

Thank you for the reply. I'm getting syntax error at Nz.
Sorry that I have mentioned earlier, I'm using MS-Access

pke8jt is offline   Reply With Quote
Old 10-31-2017, 09:01 AM   #4
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,487
Thanks: 68
Thanked 2,723 Times in 2,608 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: sudden compile error in query expression, IIF

you can use:

SumOfDollarsSold: Sum(Nz(Dbo_SO_SalesHistory.DollarsSold,0))

on design view:

Totals: Expression
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 10-31-2017, 09:03 AM   #5
pke8jt
Newly Registered User
 
Join Date: Oct 2017
Location: colorado
Posts: 27
Thanks: 17
Thanked 0 Times in 0 Posts
pke8jt is on a distinguished road
Re: sudden compile error in query expression, IIF

I really appreciate that you are keep trying to help me out.

I have image attached for your regard, about the error message.

Please don't hesitate to tell me what I did wrong! Thank you!
Attached Images
File Type: jpg Capture.JPG (45.8 KB, 46 views)
pke8jt is offline   Reply With Quote
Old 10-31-2017, 09:08 AM   #6
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,487
Thanks: 68
Thanked 2,723 Times in 2,608 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: sudden compile error in query expression, IIF

There is no vba in sql, what version r u using.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 10-31-2017, 09:10 AM   #7
pke8jt
Newly Registered User
 
Join Date: Oct 2017
Location: colorado
Posts: 27
Thanks: 17
Thanked 0 Times in 0 Posts
pke8jt is on a distinguished road
Re: sudden compile error in query expression, IIF

I'm using Access 2007- 2010. And I make the query out of table, dbo_SO_SalesHistory which was ODBCed from database.

Can I provide you any other information that you may need?

pke8jt is offline   Reply With Quote
Old 10-31-2017, 09:13 AM   #8
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,487
Thanks: 68
Thanked 2,723 Times in 2,608 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: sudden compile error in query expression, IIF

If you can go to vbe, tools->reference, check if you have reference to Microsoft Office XX.X Access database engine Object, if not add it.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 10-31-2017, 09:18 AM   #9
pke8jt
Newly Registered User
 
Join Date: Oct 2017
Location: colorado
Posts: 27
Thanks: 17
Thanked 0 Times in 0 Posts
pke8jt is on a distinguished road
Re: sudden compile error in query expression, IIF

I'm not using any vba for this query. I just use SQL view to type and run my SQL code.

Is there anyway that I can print out 0 instead of null value on my query, not using VBA?
pke8jt is offline   Reply With Quote
Old 10-31-2017, 09:18 AM   #10
George Moore
Access 2002,2010 & 2016
 
Join Date: Aug 2013
Location: Wales UK
Posts: 44
Thanks: 9
Thanked 8 Times in 6 Posts
George Moore is on a distinguished road
Re: sudden compile error in query expression, IIF

Hi

I am using Access 2010 and I have just copied your SQL into a query

After I swapped the table name and field name for ones in my database, the SQL compiled and ran without an error so.......

If the Query used to work but now it doesn't, something external may have changed. Have you changed any settings recently ?
George Moore is offline   Reply With Quote
Old 10-31-2017, 09:19 AM   #11
pke8jt
Newly Registered User
 
Join Date: Oct 2017
Location: colorado
Posts: 27
Thanks: 17
Thanked 0 Times in 0 Posts
pke8jt is on a distinguished road
Re: sudden compile error in query expression, IIF

My alternate code is
PARAMETERS [BeginDate] DateTime, [EndDate] DateTime;
SELECT FORMAT(Sum(dbo_SO_SalesHistory.DollarsSold)),0 AS SumOfDollarsSold
FROM dbo_SO_SalesHistory
WHERE (((dbo_SO_SalesHistory.InvoiceDate) Between [BeginDate] And [EndDate]) AND ((dbo_SO_SalesHistory.CustomerNo)="BOEING"))


But I also get Compile error in query expression FORMAT(Sum(dbo_SO_SalesHistory.DollarsSold)

Any help would be highly appreciated.
pke8jt is offline   Reply With Quote
Old 10-31-2017, 09:26 AM   #12
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,487
Thanks: 68
Thanked 2,723 Times in 2,608 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: sudden compile error in query expression, IIF

The error message you got means something is missing on vba reference. If yiu have broken reference, weird things are happening
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
pke8jt (10-31-2017)
Old 10-31-2017, 09:27 AM   #13
pke8jt
Newly Registered User
 
Join Date: Oct 2017
Location: colorado
Posts: 27
Thanks: 17
Thanked 0 Times in 0 Posts
pke8jt is on a distinguished road
Re: sudden compile error in query expression, IIF

Hi, George.

Yeah I tried running the query through VBA and Macro, and It worked. I think when I run the query from SQL view or design view, It gives me the compile error.
pke8jt is offline   Reply With Quote
Old 10-31-2017, 09:31 AM   #14
George Moore
Access 2002,2010 & 2016
 
Join Date: Aug 2013
Location: Wales UK
Posts: 44
Thanks: 9
Thanked 8 Times in 6 Posts
George Moore is on a distinguished road
Re: sudden compile error in query expression, IIF

What version of access are you using ?
George Moore is offline   Reply With Quote
The Following User Says Thank You to George Moore For This Useful Post:
pke8jt (10-31-2017)
Old 10-31-2017, 09:34 AM   #15
pke8jt
Newly Registered User
 
Join Date: Oct 2017
Location: colorado
Posts: 27
Thanks: 17
Thanked 0 Times in 0 Posts
pke8jt is on a distinguished road
Re: sudden compile error in query expression, IIF

I'm using Access 2007 - 2010.

I guess I will just keep running the query from VBA. Sorry for the dumb question..

pke8jt is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
sudden error message suz320 General 3 12-16-2014 10:05 AM
compile error: expected: expression AC-Dev Forms 12 08-23-2013 02:59 AM
Sudden 'compile error. in query expression' songofsolon Queries 2 01-13-2010 10:16 AM
Access 2k3 Compile error. in query expression Date() yallmf Queries 0 07-27-2006 06:03 AM
Compile Error - Expected Expression meandmyhorse Modules & VBA 1 08-01-2002 06:26 PM




All times are GMT -8. The time now is 10:49 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World