Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 25th, 2011, 03:59 AM
Registered User
 
Join Date: Aug 2011
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Date formatting

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.
 
Old August 25th, 2011, 12:20 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

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


Code:
<Date()+(Month(Date()))
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.

Code:
? date()
8/25/2011 
? Date()+(Month(Date()))
9/2/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
 
Old August 30th, 2011, 07:16 AM
Registered User
 
Join Date: Aug 2011
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default date ranges

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.
 
Old August 30th, 2011, 09:53 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

Quote:
Originally Posted by reidynem View Post
Hi,

I am looking for the ShipDate to display only overdue orders + orders due in the current month.
OK. I think I are figuring this out.

Code:
... dbo_RDR1.ShipDate AS [Due Date] ...
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
 
Old August 30th, 2011, 10:24 AM
Registered User
 
Join Date: Aug 2011
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old August 30th, 2011, 02:53 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

Quote:
Originally Posted by reidynem View Post
Yes the ship date is the due date - I renamed the field.

What you said is exactly right - that is what I need!
Let's try to get the first question answered before we go on.


Note sure what you really are trying to do here:
Code:
( (dbo_RDR1.ShipDate)<Date()+(Month(Date()))
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:

Code:
(dbo_RDR1.ShipDate) between DateSerial(Year(Date()), Month(Date()), 1) and DateSerial(Year(Date()), Month(Date()) + 1, 0)
To get the past due records you would use:

Code:
(dbo_RDR1.ShipDate) < DateSerial(Year(Date()), Month(Date()), 1)

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

So you would use this:

Code:
HAVING (((dbo_RDR1.OpenQty)<>0) AND ((dbo_RDR1.ShipDate)<(DateSerial(Year(Date()), Month(Date()) + 1, 0))));
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
 
Old August 31st, 2011, 06:46 AM
Registered User
 
Join Date: Aug 2011
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!!
 
Old August 31st, 2011, 12:25 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Formatting date and time SouthendSupporter BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3 2 July 28th, 2009 06:06 PM
Date Formatting MargateFan XSLT 3 August 30th, 2006 07:42 AM
Date formatting jroxit Classic ASP Basics 3 February 26th, 2006 10:07 PM
Date Formatting ozPATT Excel VBA 2 October 31st, 2005 12:03 PM
Formatting to a Date trekmp ADO.NET 2 March 16th, 2004 01:37 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.