Movian
01-06-2009, 02:54 PM
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
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#];
This report is called via code
DoCmd.OpenReport "AccountsReport", acViewPreview, , sWhere
and sWhere is dynamicly created by the users filter options.
Here is the complete code section as its based of multiple controls.
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
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?
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
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#];
This report is called via code
DoCmd.OpenReport "AccountsReport", acViewPreview, , sWhere
and sWhere is dynamicly created by the users filter options.
Here is the complete code section as its based of multiple controls.
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
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?