WHERE Statement 1

[newToVBA2003]

I have a "WHERE" statement as such:
"WHERE (((ClaimsBenefits.DateOfVisit)>= #" & BeginElig & "#))

where BeginElig = "1/1/" & (Format(ClaimDate, "yyyy"))

Is there a way to compare two var within the Where statement? EX: I want to compare BeginElig against another date variable within the WHERE, so I guess I'm asking how do I do this or is this possible:?

"WHERE (((ClaimsBenefits.DateOfVisit)>= #" & BeginElig & "#)) And someOtherDate >= BeginElig

 

[HarleyQuinn]

Yeah, just do it the same way:

"WHERE (((ClaimsBenefits.DateOfVisit)>= #" & BeginElig & "#)) And (((someOtherDate)>= #" & BeginElig & "#))

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[newToVBA2003]

I use that and I keep getting a pop up window to enter someOtherDate...someOtherDate already has a value though.

 

[HarleyQuinn]

What's the exact SQL you're using?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[newToVBA2003]

SQL for access 2003 if that's what you are asking. Here is a snippet of the code if this helps:

sqlstmt = "SELECT ClaimsBenefits.Dependent, ADACodes.Type, Sum(ClaimsBenefitsDetails.AmtPaid) AS SumOfAmtPaid INTO TmpValues " & _
"FROM (ClaimsBenefits INNER JOIN ClaimsBenefitsDetails ON ClaimsBenefits.BenefitID = ClaimsBenefitsDetails.BenefitID) INNER JOIN ADACodes ON ClaimsBenefitsDetails.[Type of Procedure] = ADACodes.Description " & _
"WHERE (((ClaimsBenefits.DateOfVisit)>= #" & BeginElig & "#)) and (((ClaimsBenefits.DateOfVisit)<= #" & enddate & "#)) and ((ClaimsBenefits.Estimate)=False)" & _
"GROUP BY ClaimsBenefits.Dependent, ADACodes.Type " & _
"HAVING (((ClaimsBenefits.Dependent)=" & dependid & ") AND ((ADACodes.Type)='basic' Or (ADACodes.Type)='major'));

 

[HarleyQuinn]

Yeah, that's what I meant, sorry for any confusion caused.

Would you be able to post the version containing someOtherDate compared to BeginElig that asks for the value please?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[newToVBA2003]

BeginElig = "1/1/" & (Format(ClaimDate, "yyyy"))
enddate = "12/31/" & (Format(ClaimDate, "yyyy"))
'and (((ClaimsBenefits.DateOfVisit)<= #" & enddate & "#)) #" & BeginElig & "#
'And (((planStartDate)>= #" & BeginElig & "#))
planStartDate= DLookup("[DentalPlanStartDate]", "Dependent", "id = " & dependid)
planStartDate = (Format(planStartDate, "mm/dd/yyyy"))
If codetype = "basic" Or codetype = "major" Then


sqlstmt = "SELECT ClaimsBenefits.Dependent, ADACodes.Type, Sum(ClaimsBenefitsDetails.AmtPaid) AS SumOfAmtPaid INTO TmpValues " & _
"FROM (ClaimsBenefits INNER JOIN ClaimsBenefitsDetails ON ClaimsBenefits.BenefitID = ClaimsBenefitsDetails.BenefitID) INNER JOIN ADACodes ON ClaimsBenefitsDetails.[Type of Procedure] = ADACodes.Description " & _
"WHERE (((ClaimsBenefits.DateOfVisit)>= #" & BeginElig & "#)) and (((planStartDate)>= #" & BeginElig & "#)) and And (((planStartDate)<= #" & enddate & "#)) and ((ClaimsBenefits.Estimate)=False)" & _
"GROUP BY ClaimsBenefits.Dependent, ADACodes.Type " & _
"HAVING (((ClaimsBenefits.Dependent)=" & dependid & ") AND ((ADACodes.Type)='basic' Or (ADACodes.Type)='major'));"
EndIf
DoCmd.SetWarnings False
DoCmd.RunSQL sqlstmt
DoCmd.SetWarnings True

 

[newToVBA2003]

The second "And" in the second comparison in the Where clause is not in my code, happend when I was copying over to here

 

[HarleyQuinn]

Seems planStartDate appears to be a variable, you could add it like you've added the other variables:

(((#" & planStartDate & "#)>= #" & BeginElig & "#))

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[newToVBA2003]

OM Goosh, thanks Harley that worked. Thanks once again.

 

[PHV]

What about this ?

Dim BeginElig As Date, enddate As Date, planStartDate
planStartDate = DLookup("DentalPlanStartDate", "Dependent", "id=" & dependid)
If IsDate(planStartDate) And (codetype = "basic" Or codetype = "major") Then
  BeginElig = DateSerial(Year(ClaimDate), 1, 1)
  enddate = DateSerial(Year(ClaimDate), 12, 31)
  If planStartDate >= BeginElig And planStartDate <= enddate Then
    sqlstmt = "SELECT B.Dependent, A.Type, Sum(D.AmtPaid) AS SumOfAmtPaid INTO TmpValues " & _
              "FROM (ClaimsBenefits B INNER JOIN ClaimsBenefitsDetails D ON B.BenefitID=D.BenefitID) " & _
              "INNER JOIN ADACodes A ON D.[Type of Procedure]=A.Description " & _
              "WHERE B.DateOfVisit>=#" & BeginElig & "# AND B.Estimate=False" & _
              " AND B.Dependent=" & dependid & " AND (A.Type='basic' Or A.Type='major') "
              "GROUP BY B.Dependent, A.Type"
    CurrentDb.Execute sqlstmt
  End If
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[newToVBA2003]

That is a lot clearer and easier to read than the code I have now, so thank you as well. I like this "DateSerial" format for "BeginElig" and "enndate" better also.

 

[newToVBA2003]

Oh Phv in your version you have a GROUP BY but not a HAVING, what is the difference or why can HAVING be ommitted?

 

[PHV]

The HAVING clause should be used for testing aggregate values only, IMO.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886