I have a query which shows all our current orders and I am trying to filter the query results via an expression so that it only shows those that are overdue and those orders that are due in the current month.

I entered into the criteria of the due date field, the following:

<Date()+(Month(Date()))

The overdue bit works and the month sort of does except it is also returning orders which are due on 1st September.

Does anyone know why it is doing this / know of an expression to acheive what I want?

Thanks very much in anticipation.

It would help if you were to post all SQl for eh query.


The above does not make much sens for calculating the ones that are due.

Using the above logic for 8/25/2011 then it should find orders for 9/1/2011.

As you can see 9/1/2011 is less than 9/2/2011. So <Date()+(Month(Date())) should find 9/1/2011.

It is working correctly for what you have programmed.


What date range are you looking for?

__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015

Hi,

Thans for replying, the SQL is as follows:

SELECT dbo_ORDR.CardName AS [Customer Name], dbo_ORDR.DocNum AS SO, dbo_RDR1.ItemCode, dbo_RDR1.OpenQty AS [Open Qty], dbo_RDR1.ShipDate AS [Due Date], dbo_RDR1.Price AS [Unit Price], [price]*[OpenQty] AS [Open Line Value]
FROM dbo_RDR1 LEFT JOIN dbo_ORDR ON dbo_RDR1.DocEntry = dbo_ORDR.DocEntry
GROUP BY dbo_ORDR.CardName, dbo_ORDR.DocNum, dbo_RDR1.ItemCode, dbo_RDR1.OpenQty, dbo_RDR1.ShipDate, dbo_RDR1.Price, [price]*[OpenQty]
HAVING (((dbo_RDR1.OpenQty)<>0) AND ((dbo_RDR1.ShipDate)<Date()+(Month(Date()))));

I am looking for the ShipDate to display only overdue orders + orders due in the current month.

Hope this makes sense, thanks fo your help.

OK. I think I are figuring this out.

From this a would think the "Due Date" is the ship date.

To get current orders you want any record that has a ship date in the same month as the date the report is run (Date)

You also want past due orders. I would think that these would be orders that are before the current month above.

Does this sounds like what you want?

__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015

Yes the ship date is the due date - I renamed the field.

What you said is exactly right - that is what I need!

--------
Whilst I have you...you may be able to assist me with another question also?

Another lovely user gave me the following VBA code to make a date form to open a report within specific dates:

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working.
'Purpose: Filter a report to a date range.
'Documentation: http://allenbrowne.com/casu-08.html
'Note: Filter uses "less than the next day" in case the field has a time component.
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Do NOT change it to match your local settings.

'DO set the values in the next 3 lines.
strReport = "Current Open Orders" 'Put your report name in these quotes.
strDateField = "[Due Date]" 'Put your field name in the square brackets in these quotes.
lngView = acViewPreview 'Use acViewNormal to print instead of preview.

'Build the filter string.
If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate, strcJetDate) & ")"
End If

'Close the report if already open: otherwise it won't filter properly.
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

'Open the report.
'Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

I then had to put the following control source onto the start date on the report: =[Forms].[frmWhatDates].[txtStartDate]
and the following on the end date: =[Forms].[frmWhatDates].[txtEndDate]

My problem is that when I run the report, both dates appear as #name? but I would like them to appear as the specified dates I enter into the form.

Is this possible?

Thanks very much for your help.

Let's try to get the first question answered before we go on.


Note sure what you really are trying to do here:
The above does not have anything really to do with what you need.

Here is how you do some date calculations:

The first day current month:
DateSerial(Year(Date()), Month(Date()), 1)

The last day of the current month:
DateSerial(Year(Date()), Month(Date()) + 1, 0)


You will uses there to get the desired date ranges.

To get the Ship dates in the current month you would use:

To get the past due records you would use:


If you combined the two together you would want records on or before then end of the current month.

So you would use this:

I now see where you might have got the Date()+(Month(Date()). Unfortunately that was not the correct calculation to get eh end of the current month.

__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015

that is perfect thanks. Would you mind explaining the breakdown of how that works so that I learn how to do it myself in future? Everything I have so done so far I have taught myself so am not particularly clued up with how to use expressions other than very simple ones (having said that I am sure the one you have just done for me is very simple!!)

Thanks so much for your help - much appreciated!!

The key is knowing how to use DateSerial().


My article on the subject may help: Displaying specific dates

__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015