sudden compile error in query expression, IIF (1 Viewer)

pke8jt · Oct 31, 2017

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"));

arnelgp · Oct 31, 2017

you can use:

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

pke8jt · Oct 31, 2017

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

arnelgp · Oct 31, 2017

you can use:

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

on design view:

Totals: Expression

pke8jt · Oct 31, 2017

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!

arnelgp · Oct 31, 2017

There is no vba in sql, what version r u using.

pke8jt · Oct 31, 2017

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?

arnelgp · Oct 31, 2017

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.

pke8jt · Oct 31, 2017

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?

George Moore · Oct 31, 2017

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 ?

pke8jt · Oct 31, 2017

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.

arnelgp · Oct 31, 2017

The error message you got means something is missing on vba reference. If yiu have broken reference, weird things are happening

pke8jt · Oct 31, 2017

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.

George Moore · Oct 31, 2017

What version of access are you using ?

pke8jt · Oct 31, 2017

I'm using Access 2007 - 2010.

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

arnelgp · Oct 31, 2017

Do you have a local table and test if using query builder produces error too, if it doesnt run then you should resolved an fix this issue.

sudden compile error in query expression, IIF (1 Viewer)

pke8jt

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

pke8jt

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

pke8jt

Registered User.

Attachments

arnelgp

..forever waiting... waiting for jellybean!

pke8jt

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

pke8jt

Registered User.

George Moore

Access 2002,2010 & 2016

pke8jt

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

pke8jt

Registered User.

George Moore

Access 2002,2010 & 2016

pke8jt

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread