DatePart Difficulties

[Guest]

I am using the DatePart() function in a macro to set a value. After the user
enters an invoice date into a text box, I want the macro to enter just the
month and year into another field. I would like to use to calculate monthly
budgets. I am using "After Update" to trigger a "Set Value" macro. For the
expression I have entered...

=DatePart("mm,yy",[Invoice Date])

This results in "Invalid Procedure or Call Argument" as shown below.

[Forms]![PO Log]![Month of Invoice Textbox], =DatePart("mm\,yy",[Invoice
Date])

What am I missing?

Ryan

 

[tina]

you'd have to extract the month and year separately, and concatenate the
values, as

DatePart("m", [InvoiceDate]) & "/" & DatePart("yyyy", [InvoiceDate])

it might be easier to use the following, as

Format([InvoiceDate], "mm/yy")

but i would advise that you don't do it at all. first, saving the same data
twice is a violation of data normalization rules. second, the saved value
will be Text data type, not Date/Time, so you'll run into problems when you
try to work with the data as dates; for instance, what if you want to write
a query to pull "last month's" budget, every month? or if you want to pull
quarterly data for a report? and lastly, you don't need the extra field at
all. when you want to pull data for a monthly report (or quarterly, or
yearly), just create calculated fields in a your query to extract the year /
quarter / month from the date field as needed *at runtime*, as

InvoiceMonth: Month([InvoiceDate])
InvoiceYear: Year([InvoiceDate])
InvoiceQuarter: DatePart("q", [InvoiceDate])

and set your criteria on the calculated field(s) to pull the records you
want.

hth

 

[John Vinson]

I am using the DatePart() function in a macro to set a value. After the user
enters an invoice date into a text box, I want the macro to enter just the
month and year into another field. I would like to use to calculate monthly
budgets. I am using "After Update" to trigger a "Set Value" macro. For the
expression I have entered...

=DatePart("mm,yy",[Invoice Date])

This results in "Invalid Procedure or Call Argument" as shown below.

[Forms]![PO Log]![Month of Invoice Textbox], =DatePart("mm\,yy",[Invoice
Date])

As Tina says, don't store this value AT ALL. You can calculate it on
the fly if needed using the expression
=Format([Invoice Date]", "mm,yy")

(note Format not Datepart), but there is no need and no benefit to
storing the value in the table, since you can extract it from the
already stored date whenever needed.

John W. Vinson[MVP]