Bizzarre Query Error

[Ray1127]

I work for a Health Insurance Company. We contract with a number of Medical Groups. Occassionaly throughout the year we have what Joint Operations Meetings with the various Medical Groups. There are a group of 5 Reports run out of MS Access 2000 for these meetings. In addition for some of these groups we print out these same 5 reports on a quarterly basis. Ok that's the background the issue is that If I run the reports for the JOC Meeting and use the date range similar to a quarter 10/1/2010 through 12/31/2010 for example all 5 reports print no problem. If I run the batch routine to print the quarterly reports even if it's the same group as the JOC but 1 of the Report Queries errors.

I have a table that links the contact persons address with the IPA ID's for these quartely reports

Here's the code for the quarterly reports bear in mind that it does not error here.

Sub QuarterlyReports()
Dim strsql As String, oldipa As String, itm As Variant, loopctr As Long
Dim rs As New ADODB.Recordset, rsgp As New ADODB.Recordset
'This pulls all of the IPA's for the quarterly Reports
strsql = "Select ipa from tbl_mailing_groups group by ipa"
If Not IsNull(Me.txtGroupid) Then oldipa = Me.txtGroupid
rs.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
Do Until rs.EOF
'set the textbox to the current IPA
Me.txtGroupid = rs!ipa
'Change the Combobox of the Provider Groups for that IPA
'To the Groups for the IPA for the Quarterly report
Call txtGroupid_Change
Refresh the Combo Box for the Provider Groups
Me.Combo10.Requery
strsql = "Select group from tbl_mailing_groups " & _
"Where ipa = """ & rs!ipa & """ Group By group"
rsgp.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
Do Until rsgp.EOF
'set the Selected flag in the combo box based on the
'IPA In the Mailing List Table
For loopctr = 0 To Forms!frmJOMreports.Combo10.ListCount - 1
If Forms!frmJOMreports.Combo10.ItemData(loopctr) = rsgp!group Then
Forms!frmJOMreports.Combo10.Selected(loopctr) = True
End If
Next loopctr
rsgp.MoveNext
Loop
rsgp.Close
'Print the reports
Call cmdprintScoresReport_Click
rs.MoveNext

Loop
rs.Close
Set rs = Nothing

End Sub

The call cmdprintscoresreport_click calls the command button used when printing the JOC Report

There is 1 column in the query that errors where the column name is eff_date the Criteria = Between CVDate(format(forms!frmReport!dtstart,"mm/dd/yyyy")) and CVDate(format(forms!frmreport!dtend,"mm/dd/yyyy")) If the criteria is in the query it errors if I take it out it works, but why would it work for the JOC and not for the quarterly when everything in the report and on the form are the same?

 

[MajP]

Is this a user defined function: CVDate?
or did you mean
Cdate?

 

[Ray1127]

Not a user defined function as far as I know. It's included in Access 2000 and 2003 not positive about 2007. I originally used CDate but had a problem with it the query would never return data if I hardcoded the dates using the #mm/dd/yyyy# it would return data but using CDate(forms!frmReporting!dtstart) always errored changed it to CVDAte and it worked. No idea why. Also Not sure what changed between last November and now but this procedure worked fine last november added the October, November and December data and now it fails but only on this 1 report all of the other reports return the data as expected. I tried running for the 3rd Quarter 07/01/2010 through 09/30/2010 as it worked perfectly last november None of this data has been changed and it still fails.

 

[TheAceMan1]

How are ya Ray1127 . . .

[blue]A CVDate function is also provided for compatibility with previous versions of Visual Basic. The syntax of the CVDate function is identical to the [green]CDate[/green] function, however, CVDate [purple]returns a Variant whose subtype is Date instead of an actual Date type.[/purple] Since there is now an intrinsic Date type, there is no further need for CVDate.[/blue]

The subtyping could be the problem.

In any case you really don't need the extra conversion with [blue]CVDate()[/blue]. Try:

[blue]Between "#" & Format(forms!frmReport!dtstart,"mm/dd/yyyy") & "# AND #" Format(forms!frmreport!dtend,"mm/dd/yyyy") & "#"[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]