using IIF inside a Dcount function

[buratti]

I'm trying to create an inventory tracking form (well not exactly, but its best described as that). I'm using a simple dcount function to count records in the Scheduled Invoices query with certain criteria. My problem is, I am trying to use an IIF function in part of the dcount criteria. First of all is this possible. Second, if so, I cant seem to get it right. Here first is s verbal explination of what I'm counting (the criteria)...

count all where the delivery day = "past"
and Pickup Day <> "past"
and size = itemSize -----> itemSize is a variable received from the value of the item on the invoice

The tricky part is as follows... The Itemsize varialbe could be one of 6 possibilities. If size = 10, 15, 20, or 30, then the criteria would be just Size = itemsize, but if the itemSize Variable = "Concrete" or "Stumps" I need to look in the item description for the Itemsize and add, the literal term, where description includes 10, 15, 20, or 30.

The VBA code I came up with is as follows but does not work... Gives error "Argument not Optional"

DCount("*", "[Scheduled Invoices]", [Delivery Day] = "past" _
& "and [Pickup Day] <> 'past'" _
& IIf([Scheduled Invoices]![Size] <> "Concrete Load" Or [Scheduled Invoices]![Size] <> "Stumps", " and [Size] ='" & itemSize & "', " and InStr([Scheduled Invoices]![[Description], [COLOR=red]'20'[/COLOR])>0)"))

the '20' above would also be a variable getting its value form the control calling the function, but for sake of this question lets just keep it as a constant for now

Does anyone see anything wrong with this expression, or is it that I just cant use the IIF within the criteria of a Dlookup?

 

[Rabbie]

It's not easy to see the full text of your IIF statement but it looks if your single quotes aren't paired correctly on the false path.

 

[buratti]

So you can actually use the IIF within a dcount???

here's my full code not within the code window, color coded expression, true, false for easier viewing:

DCount("*", "[Scheduled Invoices]", [Delivery Day] = "past" _
& "and [Pickup Day] <> 'past'" _
& IIf([Scheduled Invoices]![Size] <> "Concrete Load" Or [Scheduled Invoices]![Size] <> "Stumps", " and [Size] ='" & itemSize & "', " and InStr([Scheduled Invoices]![Description], '20')>0)")

I dont, at least in my knowledge, notice a problem with single quotes in false path. (There was a typo of two ['s right before [description] and an extra ) at the end, but that was just in this post. My actual code did not have those)

 

[Rabbie]

So you can actually use the IIF within a dcount???

here's my full code not within the code window, color coded expression, true, false for easier viewing:

DCount("*", "[Scheduled Invoices]", [Delivery Day] = "past" _
& "and [Pickup Day] <> 'past'" _
& IIf([Scheduled Invoices]![Size] <> "Concrete Load" Or [Scheduled Invoices]![Size] <> "Stumps", " and [Size] ='" & itemSize & "', " and InStr([Scheduled Invoices]![Description], '20')>0)")

I dont, at least in my knowledge, notice a problem with single quotes in false path. (There was a typo of two ['s right before [description] and an extra ) at the end, but that was just in this post. My actual code did not have those)

AS I said it was not easy to see the whole of your IIF statement at one go. Also We cannot tell what is wrong in your code and what are typos.

 

[gemma-the-husky]

wjy not avoid having the iif inside the docunt .... put the dcount in an if loop

if stumps or concrete then
one dcount stement
else
anotherdcount statement
end if

 

[buratti]

OK thanks Gemma, that does seem like a more simple idea will try it out, however, I've spent most of the day working on this, and I figured it out to an extent, and just for the sake of learning and so I don't consider my day a waste, I want to figure it out the original way too.
I figured out the IIF part and have it working just fine, my problem now is the expression that lays inside of the false part of that iff statement. I narrowed the problem down and simplified the entire expression for the sake of this post to the following:

Dim sizelookup as string
sizelookup = "20 Yard"

Dcount("[Description]", "[Scheduled invoices]", "InStr([scheduled invoices]![Description], 'Left(Sizelookup, 2)' )>0")

The problem specifically is the 'Left(Sizelookup, 2)' (I think)

What I'm trying to accomplish is to count records in the [Scheduled Invoices] Query where, for this example, the string "20" is somewhere in the description field. The way it is now, when run I get the error "invalid use of Null"

What I have tried to fix it...
-debug.print Left(Sizelookup, 2) returns 20, which is what I'm looking for
-If I replace the whole Left() function with just '20' it works fine
-If I remove the single quotes from around the left() function I get an error "The expression you entered as a query parameter produced this error: 'Sizelookup' "

I don't understand... the left() function returns the same value as when I manually (hard code) the value, but using the left() returns an error. What could be wrong?

 

[c_smithwick]

Your criteria statement is not contained within quotes, hence access thinks you are missing an argument, plus you had some misplaced or missing single quotes, plus your Instr was missing the first argument - the starting character: Look at your original below:

DCount("*", "[Scheduled Invoices]", [Delivery Day] = "past" _
& "and [Pickup Day] <> 'past'" _
& IIf[COLOR=darkgreen][COLOR=black]([/COLOR][Scheduled  Invoices]![Size] <> "Concrete Load" Or [Scheduled Invoices]![Size]  <> "Stumps[/COLOR][COLOR=darkgreen]"[/COLOR], [COLOR=royalblue]" and [Size] ='" & itemSize & "'[/COLOR], [COLOR=darkorchid]" and InStr([Scheduled Invoices]![Description],  '20')>0[/COLOR])")

and then the corrected one:

DCount("*", "[Scheduled Invoices]", "[Delivery Day] = 'past'" _
& " and [Pickup Day] <> 'past'" _
& IIf[COLOR=darkgreen][COLOR=black]([/COLOR][Scheduled  Invoices]![Size] <> "Concrete Load" Or [Scheduled Invoices]![Size]  <> "Stumps[/COLOR][COLOR=darkgreen]"[/COLOR], [COLOR=royalblue]" and [Size] ='" & itemSize & "'"[/COLOR], [COLOR=darkorchid]" and InStr(1,[Scheduled Invoices]![Description],  '20')>0[/COLOR])")

 

[Galaxiom]

The whole syntax would have been much simpler if the words "concrete", "stumps", "past" etc had been normalized into integers. No quote marks required around numeric values.

Another good reason to normalize data.

 

[buratti]

So, I finally figured the whole thing out, for the most part... I got the IIF working, I got the instr() inside the IIF working, and I got the Left() inside the Instr() working. For anybody that is following, what was wrong with the Left() function is that it was returning the correct value, but since it was inside the Instr() function, the value that it returned needed to be within quotes. The final code is as follows (corrected mistakes in RED)

DCount("*", "[Scheduled Invoices]", "[Delivery Day] = 'past'" _
& "and [Pickup Day] <> 'past'" _
& "and iif([Scheduled Invoices]![Size] <> 'Concrete Load' Or [Scheduled Invoices]![Size] <> 'Stumps' , [Size] ='" & Sizelookup & "', InStr([scheduled Invoices]![Description], """ & Left(Sizelookup, 2) & """)>0)")

I found a good article online about how, when and where to use quotes, which is where I figured out the Left() function above if anyone is interested. Everything else, I thank you guys for your help! http://www.aspfree.com/c/a/Microsoft-Access/VBA-Details/

I do still have one problem, but found a workaround for it. Just so I understand correctly, can anyone tell me if my assumptions are correct on this:

Using the IIF inside the criteria of a dcount (in my case at least) will only count the number of records where the first record of IIF expression is met. For a simple example (dont worry about syntax here just an example):

dcount ("*", "Table1", "iif(Status = "exported", field2 = 5, field2 = 10)")

lets say [Status] in the first record in table1 has the value "exported". This Dcount will then ONLY count records where [status] = "exported" and field2 = 5, nothing else. BUT, if [status] in the first record did NOT have the value "Exporrted" it would ONLY count where [status] <> "exported" and field2 = 10, and nothing else.

My logic would think it would cycle through each record, evaluate the IIF expressionfor each record, then evaluate the True or False expression to add it to the count list or not, then move on to the next record and repeat.

For example, if first record [status] = "exported" and field2 = 5, then add record to count list and go to next record if [status] <> "exported and field2 = 10 then add to count list and go to next record and if [status] = "exported" and filed2 = 5 then do not add to count list and so on. But like I tried to explain before it would only count all records of whatever the IIF evaluates the first record to be.

What I'm trying to explain is hard to put into words in a post like this, and I may have confused people with the wayI worte it, but the question is quite simple.