General discussion

Locked

month to date in access

By tatkins ·
I need an expression that will return month-to-date info on sales.changing the year manually is not a problem. I would like to not have to change the month. my year to date looks like this:
Between #1/1/00# And Date()-1
this is an expression fromthe criteria field for an ODBC query to a Unix system. the field name is INV_DATE.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

month to date in access

by Bob Sellman In reply to month to date in access

Your basic problem is to determine the month of the current date (or report date) and produce a date which is the first of the month and year.

You can use some non-visible fields on your report or form to work the conversion. I'll go through an example so you can get a control equal to the first of the current month. You can then use the value of that control (or the equivalent in code) for the appropriate expressions you need.

For simplicity, I'll start with a control called txtToday for the current/report date and we'll set it equal to Date() (today's date).

A second hidden control, called txtMthNbr, will have as its control source:
=Month([txtToday])
This will control the number of the current month.

A third hidden control, called txtBeginMth, will have as its control source:
=CDate([txtMth] & "/01/00")

Note that you can also extract the year from txtToday with a separate control (txtYear) with a control source = Year([txtToday]) and use it for txtBeginMth as
=CDate([txtMth] & "/01/" & [txtYear])

(The CDate function converts the string to a date value.)

You can then use the value in txtBeginMth for your month to date calculations.

Good luck.

Collapse -

month to date in access

by tatkins In reply to month to date in access

I should have been a little clearer. i am trying to automate queries and consolidate them into one report.

Collapse -

month to date in access

by roc180 In reply to month to date in access

If you want to keep all your syntax within your query use the expression:

Between CDate(DatePart("m",Date()) & "/01/00") And Date()-1

This will always grab the current month.

Neal Martinelli

Collapse -

month to date in access

by tatkins In reply to month to date in access

that is exactly what i'm looking for
thanks

Collapse -

month to date in access

by tatkins In reply to month to date in access

This question was closed by the author

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums