Error in IsNull query

[jsdba]

Hi

I have an expression in my query and i'd like to return a value of 0 is the expression yields a null value. Here is the expression i have:

IsNull(Sum(([qryTime].[hours]*[Rate])+([qryTime].[minutes]*[Rate]/60)+([qryTime].[seconds]*[qryTime].[Rate]/3600),0)

I'm getting an error "the expression you entered has a function containing the wrong number of arguments". How can i resolve this?

Any help will be much appreciated.

 

[plog]

5 opening parenthesis, 4 closing parenthesis.

 

[jsdba]

IsNull(Sum(([qryTime].[hours]*[Rate])+([qryTime].[minutes]*[Rate]/60)+([qryTime].[seconds]*[qryTime].[Rate]/3600),0))

With the above ^^ i still get the error highlighting the bold close parenthesis. Is there another way to do this? Maybe using Nz.

 

[plog]

5 was the correct number, not 4. You should have added one, not subtracted. When your code gets this complex its time to open a text editor and make it readable:

IsNull(
	Sum(
		([qryTime].[hours]*[Rate])
		+([qryTime].[minutes]*[Rate]/60)
		+([qryTime].[seconds]*[qryTime].[Rate]/3600,0)
		)

That's the last code you posted. In it, you never close the IsNull. Additional hint: you close the SUM parenthesis in the wrong spot.

You could do this with a NZ, but you're still going to be in a parenthesis jungle.

 

[vbaInet]

You could do this with a NZ, but you're still going to be in a parenthesis jungle.

I think this (i.e. Nz) is what the OP wanted to use in the first place because of the closing ", 0)"

 

[jsdba]

Thanks a bunch plog. I was using notepad++ to look open/close parenthesis but i certainly didn't lay it out as beautiful as that. Thanks again but now i know this wasn't the problem.

This query now become part of a bigger query (big query contains 4 queries connected by inner joins). If one value in any of the four queries are null then that entire records in the "bigger query" doesn't show.

Work around is using left inner join but hen i get records i don't need/want.

I'm not sure if i being clear with my problem.

IsNull(
	Sum(
		([qryTime].[hours]*[Rate])
		+([qryTime].[minutes]*[Rate]/60)
		+([qryTime].[seconds]*[qryTime].[Rate]/3600,0)
		)

 

[plog]

Do you have a datasource that has all the values that should appear in the final result? Maybe you are LEFT JOINING from the wrong query. If you can find one that has all the records that should be in the final output, that's the one you should use on the left side of the LEFT JOIN.

If you can post your database along with what data you expect I can look into it and give specific advice.

 

[Galaxiom]

The problems is confused by the incrorrect parantheses in the original post but I suspect the real issue is a misunderstanding about the IsNull function.

IsNull() in TSQL is the equivalent of Nz() in Access. They have a second argument which designates the value to be used if the expression in the first argument is null.

IsNull() in Access simply tests the expression in the argument for Null and returns True or False. There is no second argument which may be why you getting the error "the expression you entered has a function containing the wrong number of arguments".

 

[jsdba]

I cant post my db its far too big and complex. I'm trying to build a report from this query.

SELECT Employees.time_card_id, Employees.UserName, qryTime_report_summary_amount_OK.Amount AS OkAmount,
qryTime_report_summary_amount_notOK.Amount AS notOkAmount, qryTime_summary_report_BHBE.BHBE,  
qryTime_summary_report_IHBE.IHBE, 
Format([BHBE]/86400,"hh:nn:ss") AS txtBHBE, Format([IHBE]/86400,"hh:nn:ss") AS txtIHBE, 
EmployeesEmployees.dept_id

FROM (((Employees LEFT JOIN qryTime_report_summary_amount_notOK ON Employees.time_card_id = qryTime_report_summary_amount_notOK.time_card_id) 

LEFT JOIN qryTime_report_summary_amount_OK ON Employees.time_card_id = qryTime_report_summary_amount_OK.time_card_id) 

LEFT JOIN qryTime_summary_report_BHBE ON Employees.time_card_id = qryTime_summary_report_BHBE.time_card_id)

LEFT JOIN qryTime_summary_report_IHBE ON Employees.time_card_id = qryTime_summary_report_IHBE.time_card_id

WHERE (((Employees.dept_id)=14));

Here is a scenario:
Employee Bob

qryTime_report_summary_amount_OK.Amount - no record
qryTime_report_summary_amount_notOK.Amount - $100
qryTime_summary_report_BHBE.BHBE - 01:30:00
qryTime_summary_report_IHBE.IHBE - 00:45:00

In the senario ablove if i dont use Left join on the Employee's table then i dont see the record for Bob because the query "qryTime_report_summary_amount_OK.Amount - no record" doent have any record for him. Work around is to use Left Join which means i get all records from Employee tbl(which i dont want). I want to be able to pull employees that has data in any of the queries above. Sorry about the long post.

Do you have a datasource that has all the values that should appear in the final result? Maybe you are LEFT JOINING from the wrong query. If you can find one that has all the records that should be in the final output, that's the one you should use on the left side of the LEFT JOIN.

If you can post your database along with what data you expect I can look into it and give specific advice.

 

[plog]

I'm getting a feeling your table structure may not be proper, or at least how you have set up your sub-queries. For example, my guess is qryTime_summary_report_BHBE and qryTime_summary_report_IHBE are 90% the same query. I bet they use the same underlying data source and the only difference is the field you are using, or possibly some criteria. If that's the case those 2 queries probably don't need to exist independently, I bet you could get the BHBE data and the IHBE data together in the same query. The same goes for the Ok/notOk amounts.

I'm pretty sure your underlying queries could be better written, and my hunch is it might be a poor table structure at the root of this issue.

Can you make a copy of your database, strip out everything except the data sources (table and queries) involved in this issue you are dealing with? If necessary you can strip out all but a few records of data in the tables and replace any sensitive data with gibberish.

 

[jsdba]

Here is the DB with relevant objects. Access 2010 btw.

 

[Thales750]

Speaking only to complex code strings. Being an old man and having more difficulty remembering large text strings, I tend to start with the basic code and test it, then I add a the next phrase, and test that.

Finally I add whatever is left. and test that. At least then, I can tell when it broke.

Sometimes I even make several different versions in separate columns (for Queries) just to see how they each perform.

 

[vbaInet]

Being an old man and having more difficulty remembering large text strings, I tend to start with the basic code and test it, then I add a the next phrase, and test that.

Between you and BrianWarnock, I wonder who's older
And yes that's good advice, start from small, test, keep adding and keep testing. Iterative development! This is what all good developers do.

 

[plog]

I think I was right on both counts--you're queries could be combined and your table structure is a little off. It looks like tblTimedTasks is storing a lot of redundant data and storing it incorrectly. You've got a lot of date/time fields--how are they related:

TaskDateTime
duration
start_date
end_date
minutes
hours
seconds
duration_1
duration_original

Some of those are redundant/calculated right? I am pretty sure minutes, hours, seconds shouldn't be fields in your table, most likely you have other queries that calculate those and update the table. That's not how database are to work, if you need that data (which you don't), you create a query and reference the query when you need it.

Here's the calculation for BHBE:

BHBE: Sum([qryTime_task_week].[hours])*3600+Sum([qryTime_task_week].[minutes])*60+Sum([qryTime_task_week].[seconds])

I'm sure there's a query before this that calculated hours, minutes and seconds, and then in this query you are basically undoing it all and adding all those different units back together just to work in seconds.

Your 4 sub-queries can be combined into one. Queries are kind of like adding fractions--if you can get all the criteria the same and the datasources are the same, you can move all your fields into the same query. For all 4 of those queries the datasource is the same, so if you can get the criteria to the same in all of them, you can merge the fields together into 1 query.

Here's how you do that. First, work on combining the IHBE and the BHBE queries. What you do is you move the criteria to the field you are summing. For BHBE it would look like this:

1. Remove bill_status_id from the query
2. Add an Iif statement inside the SUM() on the BHBE field:

BHBE: SUM(Iif([bill_status_id]=1, [qryTime_task_week].[hours])*3600+Sum([qryTime_task_week].[minutes])*60+Sum([qryTime_task_week].[seconds], 0))

First, that time calculation code should go as I mentioned before, I am just using it here to show you how that IIF statement functions. Now, what I did is essentially move your criteria from the criteria area to the field by using that Iif statement. If bill_status_id = 1 then it does your calculation and adds it to the total, if not it adds 0 to your total (thus not affecting it). Make a copy of the BHBE query, implement my changes then run it and compare it against your query--the data will be the same.

If you do the same for IHBE you can combine them in the same query because they come from the same datasource and have the same criteria (none). Once you do that, try and merge the amountOk, amountnot_Ok queries. First and foremost though, get rid of that redundant data and stop relying on it. You need to keep the absolute minium time fields and not any that can be calcuated.

 

[Thales750]

Between you and BrianWarnock, I wonder who's older
And yes that's good advice, start from small, test, keep adding and keep testing. Iterative development! This is what all good developers do.

I'm 54, so I'm guessing Brian has me by a few years.

 

[jsdba]

Good stuff plog, very good. I'll have at this in a few and see if is solved my issue. Thanks