Gasman
Enthusiastic Amateur
- Local time
- Today, 11:12
- Joined
- Sep 21, 2011
- Messages
- 14,394
Hi all,
I have a table that allows me to run the same report with some criteria from a field in the table.
So for one report I can use
[PaidDate] IS NOT NULL to get all paid invoices
and [PaidDate] IS NULL to get all unpaid invoices.
Works as it should.
However if I schedule invoices for next week on the 23rd, those records get picked up in the Invoices Unpaid report as the paid date is null, but I have not requested them yet.?
I tried various permutations of "[InvoiceDate] < " & Format(Date(),"\#mm\/dd\/yyyy\#") & "AND [Paiddate] IS NULL" , also I have a constant
Public Const strcJetDate = "\#mm\/dd\/yyyy\#" for when I use SQL in VBA, but could not get that to work.
As a temporary measure I have now made it [InvoiceDate] < [What Date] AND [Paiddate] IS NULL, so it prompts for the What Date but would like to get rid of the prompt and have it automatic.
How can I combine the format and values of the field to get a valid where clause for the open report prompt.?
TIA
I have a table that allows me to run the same report with some criteria from a field in the table.
So for one report I can use
[PaidDate] IS NOT NULL to get all paid invoices
and [PaidDate] IS NULL to get all unpaid invoices.
Works as it should.
However if I schedule invoices for next week on the 23rd, those records get picked up in the Invoices Unpaid report as the paid date is null, but I have not requested them yet.?
I tried various permutations of "[InvoiceDate] < " & Format(Date(),"\#mm\/dd\/yyyy\#") & "AND [Paiddate] IS NULL" , also I have a constant
Public Const strcJetDate = "\#mm\/dd\/yyyy\#" for when I use SQL in VBA, but could not get that to work.
As a temporary measure I have now made it [InvoiceDate] < [What Date] AND [Paiddate] IS NULL, so it prompts for the What Date but would like to get rid of the prompt and have it automatic.
How can I combine the format and values of the field to get a valid where clause for the open report prompt.?
TIA
Code:
Private Sub cmdOpen_Click()
' Either open a basic report/query, or open form for same with parameters
Dim strName As String, strForm As String, strWhere As String
Dim iPreview As Integer
strName = Me.cboObject.Column(2)
strForm = Me.cboObject.Column(3)
strWhere = Nz(cboObject.Column(4), "")
If Me.chkPreview Then
iPreview = 2 'acPreview
Else
iPreview = 0 ' acNormal
End If
If Nz(strForm, "") = "" Then
Select Case Me.txtObjectType
Case "Report"
If strWhere = "" Then
DoCmd.OpenReport strName, iPreview
Else
DoCmd.OpenReport strName, iPreview, , strWhere
End If
Case "Query"
DoCmd.OpenQuery strName
Case "Form"
DoCmd.OpenForm strName
Case Else
MsgBox "Object Type not catered for"
End Select
Else
DoCmd.OpenForm strForm, , , , , , strName
End If
End Sub