Getting correct syntax for where clause (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 00:51
Joined
Sep 21, 2011
Messages
14,048
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

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
 

George Moore

Access 2002,2010 & 2016
Local time
Yesterday, 17:51
Joined
Aug 29, 2013
Messages
44
Hi Gasman

I believe your hashes are in the wrong place

"[InvoiceDate] < " & Format(Date(),"\#mm\/dd\/yyyy\#")

should be

"[InvoiceDate] <#" & format(Date,"mm/dd/yyyy") & "# AND [PaidDate] is null"
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:51
Joined
Sep 21, 2011
Messages
14,048
Hi George,
Thanks for the reply.

No, I think that syntax is correct to be done that way. That is actually the format of the constant I mentioned.
You did give me an idea though, so I went with your syntax, but still get this as strWhere
Code:
? strwhere
"[InvoiceDate] < #" & format(Date(),"mm/dd/yyyy") & "# AND [PaidDate] IS NULL"
I believe it is the combination of the quotes for the format of the date.?

I did try your Date field and it prompted me for that value.
I have tried with quotes at start and end of field and without and even CHR(34).


Hi Gasman

I believe your hashes are in the wrong place

"[InvoiceDate] < " & Format(Date(),"\#mm\/dd\/yyyy\#")

should be

"[InvoiceDate] <#" & format(Date,"mm/dd/yyyy") & "# AND [PaidDate] is null"
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:51
Joined
Sep 21, 2011
Messages
14,048
Got it !

[InvoiceDate] < Date() AND [PaidDate] IS NULL

That is all that is in the field and the same in the VBA.:D

No need for any formatting.:eek:
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:51
Joined
Feb 28, 2001
Messages
27,001
[PaidDate] IS NOT NULL to get all paid invoices

and [PaidDate] IS NULL to get all unpaid invoices.

Works as it should.

OK, that proves that your [PaidDate] is null when you expect it to be. That is an important finding because it eliminates one set of problems.

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.

OK, let's clarify. Does this method work correctly? I understand that it isn't what you wanted because of having it ask the question, but does it work correctly when you use it? Or does it include or exclude things of interest? Because I am taking a "divide and conquer" approach right now on the two parts of this: Using [InvoiceDate] this way and the format of HOW you use [InvoiceDate].

Here is my thought: If [PaidDate] is null, then for those payments you have not yet requested, is the [InvoiceDate] null also? Because it might be so simple as

Code:
SELECT .... FROM .... WHERE  ( NOT ( [InvoiceDate] IS NULL ) ) AND ( [PaidDate] IS NULL ) ...

I added extra parentheses for emphasis.
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:51
Joined
Sep 21, 2011
Messages
14,048
Hi Doc,
Thanks for the reply.

No, the way it works at present is that when I add a record, I set the invoice date for the next week, normally a Monday. Then I run a query against that date that picks up all the records and creates a csv file to import into the invoice system. This allow me to have the option of delaying any records should the need arise.

That is why I wanted to exclude anything in the report less than today. I have already added another entry for a report for those records about to be invoiced now I have the syntax correct.
 

Users who are viewing this thread

Top Bottom