Solved: Report From query issue (access SQL)

[Movian]

Hey,
wasn't sure if i should put this in the access or the SQL secion so i ended up here.

I am trying to create an access report based on a query with a passed Where section. The Query the Report is based on is as follows

[VBA]SELECT Billing.PatID, tblPatient.FirstName, tblPatient.LastName, Billing.DOS, Billing.Procedure, Billing.Charge, Billing.Payments, Billing.Balance
FROM Billing, tblPatient
WHERE Billing.PatID=tblPatient.[MedicalID#];
[/VBA]

This report is called via code

[VBA]DoCmd.OpenReport "AccountsReport", acViewPreview, , sWhere[/VBA]

and sWhere is dynamicly created by the users filter options.

Here is the complete code section as its based of multiple controls.

[VBA]Option Compare Database
Option Explicit

Public sQuery As String
Public sPeriod As String
Public sRemove0 As String
Public sWhere As String
Public sDates As String

Private Sub DatesUpdate()
If (IsNull(Me.After) Or Me.After = "") And (IsNull(Me.Before) Or Me.Before = "") Then
sDates = ""
Else
If (Not IsNull(Me.After) And Not Me.After = "") And (IsNull(Me.Before) Or Me.Before = "") Then
sDates = " AND billing.SQLDate >= #" & CStr(Format(Me.After, "yyyy-mm-dd")) & "# "
Else
If (IsNull(Me.After) Or Me.After = "") And (Not IsNull(Me.Before) Or Not Me.Before = "") Then
sDates = " AND billing.SQLDate <= #" & CStr(Format(Me.Before, "yyyy-mm-dd")) & "# "
Else
sDates = " AND billing.SQLDate >= #" & CStr(Format(Me.After, "yyyy-mm-dd")) & "# AND billing.SQLDate <= #" & CStr(Format(Me.Before, "yyyy-mm-dd")) & "# "
End If
End If
End If
RefreshList
End Sub

Private Sub After_AfterUpdate()
DatesUpdate
End Sub

Private Sub Before_AfterUpdate()
DatesUpdate
End Sub

Private Sub Command0_Click()
DoCmd.Close acForm, "Admin Reports"
DoCmd.OpenForm "Logon"
End Sub

Private Sub RefreshList()
sWhere = " [tblPatient].[MedicalID#] = [Billing].[Patid] " & sPeriod & sRemove0 & sDates
sQuery = "SELECT Billing.PatID, tblPatient.FirstName, tblPatient.LastName, Billing.DOS, Billing.Procedure, Billing.Charge, Billing.Payments, Billing.Balance FROM Billing, tblPatient WHERE " & sWhere & " ORDER BY Billing.PatID ASC, Billing.DOS ASC"
Me.Report_List.RowSource = sQuery
Me.Report_List.Requery
End Sub

Private Sub Command20_Click()
If Len(sWhere) > 52 Then
sWhere = Mid(sWhere, 52, Len(sWhere) - 51)
Else
sWhere = ""
End If
MsgBox sWhere
DoCmd.OpenReport "AccountsReport", acViewPreview, , sWhere
End Sub

Private Sub Form_Open(Cancel As Integer)
'set var defaults
sPeriod = ""
sRemove0 = ""
sDates = ""

UpdateDateDiff
RefreshList
End Sub

Private Sub Receivables_BeforeUpdate(Cancel As Integer)

Dim iDays As Integer

Select Case Me.Receivables.Value
Case 1
sPeriod = ""
Case 2
sPeriod = " AND Billing.DateDiff < 30"
Case 3
sPeriod = " AND Billing.DateDIff > 30 AND Billing.DateDiff < 60"
Case 4
sPeriod = " AND Billing.DateDiff > 60 AND Billing.DateDiff < 90"
Case 5
sPeriod = " AND Billing.DateDiff > 90 AND Billing.DateDiff < 120"
Case 6
sPeriod = " AND Billing.DateDiff > 120"
Case Else
sPeriod = ""
End Select
RefreshList
End Sub

Private Sub UpdateDateDiff()
Dim mydb As DAO.Database
Dim myrs As DAO.Recordset

Set mydb = CurrentDb()
Set myrs = mydb.OpenRecordset("Billing")

myrs.MoveFirst
While Not myrs.EOF
myrs.Edit
myrs.Fields("DateDiff") = DateDiff("d", myrs.Fields("DOS"), Date)
myrs.Update
myrs.MoveNext
Wend
myrs.Close
Set myrs = Nothing
mydb.Close
Set mydb = Nothing
End Sub

Private Sub Remove0_Click()
If Me.Remove0.Value = True Then
sRemove0 = " AND NOT (Billing.Balance = 0)"
Else
sRemove0 = ""
End If
RefreshList
End Sub[/VBA]

This same query is used to show a bried list in a list box with now problems. The same SQL code when running the reports prompts me to enter the Billing.DateDIff value (which it should pick up itself from the field)


any thoughts?

[Movian]

Resolved it-

After much more research i discovered that it is required to be a selected field.... Wasn't aware i had to have it as a selected field in order for the docmd.openreport to be able to do something with it.