Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

WHERE Statement 1

Status
Not open for further replies.

newToVBA2003

Programmer
Jan 7, 2009
12
US
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
 
Yeah, just do it the same way:
Code:
"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.
 
I use that and I keep getting a pop up window to enter someOtherDate...someOtherDate already has a value though.
 
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.
 
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'));
 
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.
 
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
 
The second "And" in the second comparison in the Where clause is not in my code, happend when I was copying over to here
 
Seems planStartDate appears to be a variable, you could add it like you've added the other variables:
Code:
(((#" & 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.
 
What about this ?
Code:
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
 
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.
 
Oh Phv in your version you have a GROUP BY but not a HAVING, what is the difference or why can HAVING be ommitted?
 
The HAVING clause should be used for testing aggregate values only, IMO.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top