My intentions: I am trying to build the WHERE clause in VBA for a pivottable form. I want to condition the results on dates and "premium" amounts.

Layout: I have a search form where I enter the beginning date and ending date(I have no problem with this portion). I have two unbound fields, one for min and max Premium amounts in my search form.

My code:If Not IsNull(Me.txtminprem) Or Me.txtminprem <> Null Then
strWhere = strWhere & "HAVING ((ccur((Sum(Sales.Premium))>= " & CCur(Me.txtminprem) & "))) AND "
'([SumOfPremium] >= " & Me.txtminprem & ") AND "
End If
If Not IsNull(Me.txtmaxprem) Or Me.txtmaxprem <> Null Then
strWhere = strWhere & "HAVING ((ccur((Sum(Sales.Premium)) <= " & CCur(Me.txtmaxprem) & "))) AND "

My assumptions: One thought I had was that the HAVING clause was causing the result to be a string and not in a number format for comparing. The original format was Dbl in the table. However, I have tried converting the variables with no success.

More Info: I can get this SQL statement to work from a regular query. I just am missing something when building the WHERE clause in VBA.

I have experience building WHERE clauses and have never been so hung up on one before. Please let me know if more code is needed or any information from the scenario I have inadvertently omitted. Any help would be so greatly appreciated. Thanks in advance.

-WhathaveIdone?

On Thu, 19 Jan 2012 15:34:00 -0800 (PST), "WhathaveIdone?"
<brasus04 (AT) gmail (DOT) com> wrote:



1) You have the possibility of two HAVING clauses.

2) You have the possibility of an expression ending in AND.

3) I am not sure that that comparison to null is valid.

Sincerely,

Gene Wirchenko

On 19/01/2012 23:33:56, "WhathaveIdone?" wrote:
A number of pointers
If Not IsNull(Me.txtmaxprem) is OK
Or Me.txtmaxprem <> Null may or may not be OK, but is unneccessary as even if
it evaluates, it is the same as the first statement. I suspect Me.txtminprem
should be CCur(Me.txtminprem)

Let's put some figures in, and you will see the problem
Assumimg Not IsNull(Me.txtmaxprem) is true the WHERE clause looks like
HAVING £50.00 >= £20 AND £60 > 20 AND HAVING £50.00 <=£100 AND

Doesn't look good
Try

If Not IsNull(Me.txtmaxprem) then
strWhere = strWhere & "HAVING (CCur(Sum(Sales.Premium)>= " &
CCur(Me.txtminprem) & ")) AND _ ([SumOfPremium] >= " & CCur(Me.txtminprem) &
") AND (CCur(Sum(Sales.Premium) <= " & CCur(Me.txtmaxprem) & "))

Probably the wrong number of brackets

Phil

On Jan 20, 1:40*am, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:

Thank you, Phil. this is very good advice. So I am understanding, I
should limit my WHERE statement to only one HAVING clause. One concern
I have for your line of code (which is very good btw) is that it does
not account for the field([txtminprem]) being Null. Providing the
possibility for both "max" and "min" fields to be Null was part of the
reason why I had them in 2 separate statements which I concatenate at
the end. I have modified my code to the following based on your code
model:

If Not IsNull(Me.txtminprem) Then
If Not IsNull(Me.txtmaxprem) Then
'Both max and min are not null
strWhere = strWhere & "HAVING(CCur(Sum(Sales.Premium)>= "
& CCur(Me.txtminprem) & ")) AND ([SumOfPremium] >= " &
CCur(Me.txtminprem) & ") AND (CCur(Sum(Sales.Premium) <= " &
CCur(Me.txtmaxprem) & ")) AND "
Else
'just the min is not null
strWhere = strWhere & "HAVING(CCur(Sum(Sales.Premium)>= "
& CCur(Me.txtminprem) & ")) AND ([SumOfPremium] >= " &
CCur(Me.txtminprem) & ") AND "
End If
ElseIf Not IsNull(Me.txtmaxprem) Then
'Just the max is not null
strWhere = strWhere & "HAVING(CCur(Sum(Sales.Premium) <= " &
CCur(Me.txtmaxprem) & ")) AND ([SumOfPremium] <= " &
CCur(Me.txtmaxprem) & ") AND "
End If

I would appreciate any further advise. Also, Gene, thank you for
responding. The AND is omitted after all my filters are evaluated.

-WhathaveIdone?

On Jan 20, 9:34*am, "WhathaveIdone?" <brasu... (AT) gmail (DOT) com> wrote:
I also must add in that this code did not work. I don't think I
mentioned that before. Also, here is what my finished WHERE statement
looks like:
HAVING(CCur(Sum(Sales.Premium)>= 500000) AND ([SumOfPremium] >=
500000)) AND ([DateSold]>= #10/01/2011#) AND ([DateSold] <=
#12/31/2011#)

Any thoughts...?

On 20/01/2012 16:45:24, "WhathaveIdone?" wrote:
I always have thoughts ... usually wrong.

Dates are a pain in the arse for at least 2 reasons
1 is that the totally illogical American format that you are using
(12/31/2011) is slightly more illogical than we Brits use (31/12/2011) and I
don’t know which country used the more logical format of 2011/12/31 Now
Microsoft in their wisdom provide a number of date manipulation functions,
and certainly with your problem you should look at the DateDiff function.
They are very unreliable as if you throw them a date like 3/5/2012 - is that
the third of May (as I would have it) or the 5th of March?
Next problem you have is your date of 12/31/2011 is not what I expect you
think is midnight on New Year’s Eve, but midnight on 30th December. What is
implied is 31st Dec 00:00:00 and what you want is 31st Dec 23:59:59 so you
might just as well use 1/1/2012 (Jan 1st in England & US) Wouldn't it be nice
if the functions had 2 additional parameters, one to say what the input
format was and one to say how to output the result So try DateDiff(“d”,
#10/01/2011#, DateSold) > 0 AND DateDiff(“d”, #01/01/2012#, DateSold) <0

DateDiff gives positive number if the second date > first date
What results are you getting from your query
Have you actually built a query using the Query Design and seeing what the
SQL is (My normal method as I find it difficult to write an SQL off the cuff)

Phil

On Jan 20, 12:55*pm, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
I had to chuckle at your response but I can see how valid your point
is. Actually, I am already using DateDiff to calculate the date
functions. I am formatting them to the current format before they go
into the calculation. "\#mm\/dd\/yyyy\#" is my format constant for
dates. Here is my code for one side of that:
If Not IsNull(Me.startdate) Then
strWhere = strWhere & "([DateSold]>= " & Format(Me.startdate,
conJetDate) & ") AND "
End If
ConJetDate is the format previously described.

I do not have a problem with the dates. Back to the prem strings. The
verbage that I am trying to solve for would say: Select all the Sales
where the date sold >= #5/31/2011# before it is grouped together AND
where the SumOfPremium is greater than (lets say 500000 as a min)
after it is grouped together by Salesperson and Organization.

Here is my actual code from a query that works just fine:

SELECT qryTemp.Agency.Agency, qryTemp.Agent.Agent, qryTemp.Company,
Sum(Sales.Premium) AS SumOfPremium
FROM (SELECT Agency.Agency, Agent.Agent, Sales.DateSold,
Company.Company, Sales.Premium
FROM (Company INNER JOIN Product ON Company.ID = Product.CompanyID)
INNER JOIN (Agency INNER JOIN (Agent INNER JOIN Sales ON Agent.ID =
Sales.AgentID) ON Agency.ID = Agent.AgencyID) ON Product.ID =
Sales.ProductID
WHERE (((Sales.DateSold)>#5/31/2011#))) AS qryTemp
GROUP BY qryTemp.Agency.Agency, qryTemp.Agent.Agent, qryTemp.Company
HAVING (((Sum(Sales.Premium))>=500000));


Let me know what you think. Anyone else, please chime in here as well!
Thank you!

-WhathaveIdone?