Re: DateAdd against textbox value
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Thu, 14 Feb 2008 23:20:28 +0900
Tim, there's a problem here with the data type of the text box. I'm not sure if Access is treating it as Text or a Number, but it certainly is not a date.
Also, the Format() function in your query does convert the date field into a string. This is going to be inefficient for selecting records (if you have lots.)
The best solution would be to add a button to your form for opening the report. You can leave the criteria out of the query, and the button will build the WhereCondition string for OpenReport to filter it for the desired month, or show all months if the user did not fill in any date.
The code for the Click event procedure of your command button would be something like this:
Dim strWhere As String
Dim dtStart As Date
Const strcJetDate = "\#mm\/dd\/yyyy\#"
If Not IsNull(Me.txtMonthYear) Then
dtStart = DateSerial(Mid(Me.txtMonthYear, 3,4), Left(Me.txtMonthYear,2),1)
strWhere = "([d_received] >= " & Format(dtStart, strcJetDate) & _
") AND ([d_received] < " & Format(DateAdd("m", 1, dtStart), strcJetDate) & ")"
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
Advantages of this approach:
a) The WhereCondition is crafted so Access can use any index you have on the date field.
b) Dates on the last day of the month work correctly, even if they have a time component.
c) The code can be extended to include an end date (e.g. results for a quarter.)
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Tim Long" <TimLong@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:180A5D8A-E38E-409F-A461-9AFA59313A53@xxxxxxxxxxxxxxxx
Hello, can you please tell me what I'm doing wrong here?
I have a textbox (txtMonthYear) on a form (frmReports). The textbox has no
format, but has an input mask 00/0000
I want to run a variety of queries/reports off the value the user enters in
txtMonthYear. So when the user enters 01/2008 I want to run a report, for
example, on the data from January 2008.
Testing how I could do this, I ran the following query. It works fine:
SELECT tblClients.Client_ID, tblClients.d_received, Format([d_received],
"mmyyyy") AS MonthYear
FROM tblClients
WHERE Format([d_received], "mmyyyy") = [Forms]![frmReports].[txtMonthYear]
This gives me the data for January, no problem. Trying to test for the data
for the month prior to that, I ran the following query:
SELECT tblClients.Client_ID, tblClients.d_received, Format([d_received],
"mmyyyy") AS MonthYear
FROM tblClients
WHERE Format([d_received], "mmyyyy") = Format(DateAdd("M",
-1,[Forms]![frmReports].[txtMonthYear]), "mmyyyy")
This returned nothing.. no error message, just no data (but there is plenty
of data for that month).
If I run the following, however, it works fine (returns last month's data):
SELECT tblClients.Client_ID, tblClients.d_received, Format([d_received],
"mmyyyy") AS MonthYear
FROM tblClients
WHERE Format([d_received], "mmyyyy") = Format(DateAdd("M", -1,Date(),"mmyyyy")
I've checked that txtMonthYear doesn't have the focus (tabbed out of it to
make sure) when I ran the above queries.
Eventually I want to run 12-month reports off the user-provided month/year,
so when the user enters 01/2008, a report can be generated for the twelve
months to that month (Feb 2007 tio Jan 2008 inclusive). I am planning on
using DateAdd and "M", -12 to give me the 12 months worth of data but can't
get it working. It's driving me up the wall!
Any help would be much appreciated.
Many thanks
Tim Long
.
- Follow-Ups:
- Re: DateAdd against textbox value
- From: Tim Long
- Re: DateAdd against textbox value
- References:
- DateAdd against textbox value
- From: Tim Long
- DateAdd against textbox value
- Prev by Date: DateAdd against textbox value
- Next by Date: Re: Showing number series in query for grouped items
- Previous by thread: DateAdd against textbox value
- Next by thread: Re: DateAdd against textbox value
- Index(es):
Relevant Pages
|
