Date Fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a date field in my table for Invoices and I am trying to create a
report where users can print reports of outstanding Invoices based on the
date they enter. I am trying to provide for several options:

1. Based on month: user just enter the month and year and the report will
look up all outstanding invoices based on that.

2. Based on year: user enter the year and the report will print invoices for
that whole year

3. Based on a period: user enter the start date and end date and report will
print invoices only for that period specified

How do I achieve that? Thanks.
ck
 
I will recommend you use only your 3 option, that should work for the other 2
scenarios, i.e. for the year just have the user enter 1/1/2005 to 12/31/2005
(or any year) as well as months, in your query enter the criteria under the
invoice date an expresion like this:

between [forms]![MyForm]![FirstDate] and [forms]![MyForm]![LastDate]

where FirstDate and LastDate are the names of the text boxes in your form
 
I think I should re-phrase my question:

With a date field, how do I extract the month or year and use it to compare
with a value entered by a user for purposes of reports?

ck
 
I think I should re-phrase my question:

With a date field, how do I extract the month or year and use it to compare
with a value entered by a user for purposes of reports?

ck
Click to expand...

Put a calculated field in the query for each by typing
Month([datefield]) and Year([datefield]) in two separate vacant Field
cells.

John W. Vinson[MVP]
 
Hi.
With a date field, how do I extract the month or year
Click to expand...

Use the Month( ) function to extract the numerical month and the Year( )
function to extract the four-digit year from a date field. For example,

nMonth = Month(Nz(Me!txtReportDate.Value, Date))
nYear = Year(Nz(Me!txtReportDate.Value, Date))

.... where txtReportDate is the text box containing the date, nMonth is an
integer representing the month of the date in txtReportDate, and nYear is an
integer representing the year of the date in txtReportDate. Use the Date( )
as a default date in case the user doesn't type a date in the text box.
and use it to compare
with a value entered by a user for purposes of reports?
Click to expand...

Use the WHERE clause of the OpenReport( ) method to compare the month or
year in the date field. For example, to open a report with the same month
selected:

DoCmd.OpenReport "rptSales", acViewPreview, , "Month(SaleDate) = " &
nMonth

or with the same year selected:

DoCmd.OpenReport "rptSales", acViewPreview, , "Year(SaleDate) = " & nYear

.... where rptSales is the name of the report and SaleDate is the name of the
date field the report needs to filter for.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.
 
Thanks for the help. I've managed to get some part of my form working but am
facing problems with those where two fields need to be checked during opening
of the report.

Dim strWhere As String
strWhere2 = "Month([InvoiceDate]) =" & Me.txtMonth And
"Year([dteInvoiceDate]) = " & Me.txtMthYear
DoCmd.OpenReport "rptInvoiceListByDate", acPreview, , strWhere

Dim strWhere As String
strWhere4 = "[InvoiceDate] <= #" & Me.txtFrom & "#" And "[InvoiceDate] >= #"
& Me.txtTo & "#"
DoCmd.OpenReport "rptInvoiceListByDate", acPreview, , strWhere

In both cases, I get a Type mismatch error. Can you spot what's wrong? Thanks.
ck
 
Hi.
In both cases, I get a Type mismatch error. Can you spot what's wrong?
Click to expand...

You have more than just a type mismatch error. You have logic errors and no
way to avoid an ugly error message whenever the user forgets to type in a
date in any of the text boxes. Try the following syntax (watch out for word
wrap):

Dim strWhere2 As String
Dim strWhere4 As String

' Month and year report.

strWhere2 = "Month([InvoiceDate]) = " & _
Nz(Me!txtMonth.Value, Month(Date)) & _
" And Year([dteInvoiceDate]) = " & _
Nz(Me!txtMthYear.Value, Year(Date))

DoCmd.OpenReport "rptInvoiceListByDate", acPreview, , strWhere2

' Between two dates report.

strWhere4 = "[InvoiceDate] >= #" & Nz(Me!txtFrom.Value, Date) & _
"# And [InvoiceDate] <= #" & Nz(Me!txtTo.Value, Date) & "#"

DoCmd.OpenReport "rptInvoiceListByDate", acPreview, , strWhere4

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


CK said:
Thanks for the help. I've managed to get some part of my form working but am
facing problems with those where two fields need to be checked during opening
of the report.

Dim strWhere As String
strWhere2 = "Month([InvoiceDate]) =" & Me.txtMonth And
"Year([dteInvoiceDate]) = " & Me.txtMthYear
DoCmd.OpenReport "rptInvoiceListByDate", acPreview, , strWhere

Dim strWhere As String
strWhere4 = "[InvoiceDate] <= #" & Me.txtFrom & "#" And "[InvoiceDate] >= #"
& Me.txtTo & "#"
DoCmd.OpenReport "rptInvoiceListByDate", acPreview, , strWhere

In both cases, I get a Type mismatch error. Can you spot what's wrong? Thanks.
ck


'69 Camaro said:
Hi.


Use the Month( ) function to extract the numerical month and the Year( )
function to extract the four-digit year from a date field. For example,

nMonth = Month(Nz(Me!txtReportDate.Value, Date))
nYear = Year(Nz(Me!txtReportDate.Value, Date))

... where txtReportDate is the text box containing the date, nMonth is an
integer representing the month of the date in txtReportDate, and nYear is an
integer representing the year of the date in txtReportDate. Use the Date( )
as a default date in case the user doesn't type a date in the text box.


Use the WHERE clause of the OpenReport( ) method to compare the month or
year in the date field. For example, to open a report with the same month
selected:

DoCmd.OpenReport "rptSales", acViewPreview, , "Month(SaleDate) = " &
nMonth

or with the same year selected:

DoCmd.OpenReport "rptSales", acViewPreview, , "Year(SaleDate) = " & nYear

... where rptSales is the name of the report and SaleDate is the name of the
date field the report needs to filter for.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.
Click to expand...
Click to expand...
 
Thanks, John.
ck

John Vinson said:
I think I should re-phrase my question:

With a date field, how do I extract the month or year and use it to compare
with a value entered by a user for purposes of reports?

ck
Click to expand...

Put a calculated field in the query for each by typing
Month([datefield]) and Year([datefield]) in two separate vacant Field
cells.

John W. Vinson[MVP]
Click to expand...
 
Thanks, for the help.
ck

jl5000 said:
I will recommend you use only your 3 option, that should work for the other 2
scenarios, i.e. for the year just have the user enter 1/1/2005 to 12/31/2005
(or any year) as well as months, in your query enter the criteria under the
invoice date an expresion like this:

between [forms]![MyForm]![FirstDate] and [forms]![MyForm]![LastDate]

where FirstDate and LastDate are the names of the text boxes in your form


--
<a href="www.joshdev.com">jl5000</a>


CK said:
Hi,
I have a date field in my table for Invoices and I am trying to create a
report where users can print reports of outstanding Invoices based on the
date they enter. I am trying to provide for several options:

1. Based on month: user just enter the month and year and the report will
look up all outstanding invoices based on that.

2. Based on year: user enter the year and the report will print invoices for
that whole year

3. Based on a period: user enter the start date and end date and report will
print invoices only for that period specified

How do I achieve that? Thanks.
ck
Click to expand...
Click to expand...
 
Excellent! Since you've been helped, please consider signing in to the
Microsoft Online Community and marking any replies you received as answers to
your question, so that others can easily and quickly find the same solutions
you did, without having to post the same question themselves, then wait
around for hours or days for someone to answer their question.

Thanks!

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.
 
Back
Top