Modules & VBA :: Getting A String With Month / Year From A Date

Apr 7, 2015

I need to use VBA to get a string which is essentially just a month and year (so date, not including day)

Would Prefer it to be 04/15 as opposed to April-15, as the number is easier to sort than the word (how to sort month names in numerical order as opposed to alphabetical id).

So that I assume i could do by =format(now(), mm/yy) (though havent actually tested it)

However to add to the complexity id also need to add a month to it, so for instance todays date is 08/04/15, id need the string to read "05/15"

or if the date was 10/12/15 id need the string to read "01/16"

View Replies


ADVERTISEMENT

Date - Month And Year Only

Dec 18, 2005

can i change date format that contain day, month, and year to month and year only..
i try change at fromat at porperties, but it change back into dd/mm/yy at combo box..
this is bcoz i want to filter up my subform that contain parts that purchased by customer by month..

thanks..

View 3 Replies View Related

Year To Date Totals, Month To Date, Week To Date

Oct 9, 2005

Can someone tell me how to get year to date totals, month to date totals, week to dates in a query? I need to get all three for three different fields.

I was not able to get the totals with the formulas given. I received the totals for each day instead. Are there any other suggestions? I am trying to different formulas, but they are not working either. I did try doing different queries with the formulas to see if that would work.

View 9 Replies View Related

Grouping Date By Year And Month In ADP+SQL

Feb 19, 2007

Hi all.

I have chart in ADP project/MS SQL Express and I want to see in chart table (date Field, data field) grouped by year and month: 2005.01, 2005.02, 2005.03....
I try this str = DATENAME(yyyy,date)+ DATENAME(m,date), SUM(data) FROM table GROUP BY DATENAME(yyyy,date)+ DATENAME(m,date) ORDER BY DATENAME(yyyy,date)+ DATENAME(m,date)

problem is with sorting: 2005April, 2005februar,2005,Januar - its sorting not by date, but alpabhetic.

Please, help with other grouping way!

View 2 Replies View Related

How To Extract Month & Year Together From A Date

Apr 19, 2007

Hey guys.... stucked in one thing..... I need to extract Month and Year together from a date in a Query. for example if date is 4/19/2007 i want to put this as April 2007.

Any idea ...?

thanks

View 5 Replies View Related

How To Get Year And Month Value From Date Field

Aug 16, 2005

hi friends,

I want to get year and month field of datetime field so that i can get data by comparing them with other table fields.

ex: date field column contains 1/28/2005 as data
how to get year(1/28/2005)
how to get month(1/28/2005)


expecting your help

Thanks,
Spec

View 3 Replies View Related

How To Format Date From Year Month And Day?

Aug 20, 2007

I have a table a
year month day
2005 1 1
2005 3 1
2006 5 1
I want to generate the associateed
1/1/2005
3/1/2005
5/1/2005
which funciton I can use to do that?

Jeff

View 3 Replies View Related

Format Date To Show Month & Year Only

Oct 22, 2006

I have a need to enter dates so that they always appear as the first of the month. These dates are used in vlookup functions in Excel and need to be constantly recorded as eg 1 Nov 06. Currently I have to rely on notes and training to make sure users only insert the date in this way.

What I would like to do is get them to enter Nov 06 only (without a day) and have that stored as 1 nov 06. I have searched for date formatting functions and nothing addresses this. Perhaps it is something that could be achieved using VB if it is possible to do it.

Thanks for the help.

View 4 Replies View Related

Date Query (last Week, Last 2 Weeks, Last Month, Last Year, All)

May 31, 2007

Hi

Would be great if I could get some help on this:

I have big table that gets updated almost every day with new data. There is a date column. I have a Form where I can enter queries. I need to add a panel where I can spcify if the query should involve the data should involve the date from last week only, from the last 2 weeks, from the last month or if the query should involve the overall data.:confused:

It somhow need to be check what the date is today and then caclulate back.

Any help on this would be much appreciated.:D

Thanks
Daniel

View 6 Replies View Related

Queries :: Calculate Fiscal Year And Month From A Date

Jul 13, 2015

I want to build a query that calculates the fiscal year and the month from a date on-the-fly. I tried to do it in VBA, but it's more complicated than in query I guess. So this is my table:

The date is on the left, in the middle I want to have calculated Jan 14 and on the right I want 14/15.

In VBA I started with this: strMonth = MonthName(Month(A), True) & " " & Format(A, "YY") which gave me "Jan 08". But the date was hard-coded and not from my table. Then I tried to store the information by SQL statement into a string. But this also didn't work, the types were different.

Code:
UPDATE tblSAPOD
SET sapOD_month = Year([sapOD_OrderDate])-IIf([sapOD_OrderDate]<
DateSerial(Year([sapOD_OrderDate]),6,16),1,0)
WHERE sapOD_OrderDate Like "*/*/94";

.. but I guess it's wrong, it gives me a syntax error.

View 11 Replies View Related

Queries :: Query Using Date From Form In Month And Year?

Apr 3, 2013

My question is that I wish to have a query that looks up on a name in a form (No problem got this bit) and also the Date field from the same form so that from the date :-

example 24/03/2013 only cares about the Month and Year so would look up only March in 2013

The end result would be to supply me with a number (using count) of how many times this persons name has occurred within the calendar month and year of the date supplied in the form

View 1 Replies View Related

Queries :: Convert 3-char Month And Year To Date

Aug 17, 2015

I have 2 columns that are listed as such:

AssumptionMo AssumptionYr
MAY 2014
JUN 2015
JUL 2015
OCT 2016

I need to create a field called AssumpDate that converts the month into a date field on the 1st day of the month. ex May 2014 needs to read 5/1/2014. When I use the expression AssumpDate: DateValue("1-" & [Assumption_Month] & "-" & Year(Date())) of course the year changes to the current one--2015. How can I I change the expression so that the year is based on the AssumptionYr column?

View 3 Replies View Related

Queries :: Set Month Or Year Default As Per System Date

Oct 16, 2013

How can I set Month or Year default as per the system date. ie Only select records by Month (October) or Year (2013) as per system date?

View 3 Replies View Related

Reports :: Date Fields (Month And Year) Of Reporting Period

Mar 5, 2013

I need to put a date field in that is the month and year of the reporting period...

for example

February 2013

and then another date field that is the 10th day of the month following the reporting period.

example: March 10, 2013

I have tried a number of different formats and can't get it right.

View 4 Replies View Related

Modules & VBA :: Determine Year From Month Count?

Jun 16, 2014

I have a table that has a field for month represented by a number. I would like to report by month but group by year. The report would group months 1-12 into year 1, months 13-24 into year 2, months 25-36 into year3, etc..... up to possible 20 years. Is there an easy way to identify and group the year? I was thinking of adding another column to the table and when I write the values to it include a year. I would rather do it some other way as the data is spilt and would require a lot of table modifications to distributed db. It would be easier to do it with a query or some code in the front end.

View 2 Replies View Related

Modules & VBA :: Open Report - Filter Month And Year

Feb 25, 2015

I have vba code

PHP Code:

    DoCmd.OpenReport "MonthlyData2", acPreview, , " YEAR(CourseDate) = '" & Me.txtCourseDateMonth & "' " 

this works sucessfuly how can I add the following to the code so it filters month and year?

PHP Code:

'" MONTH(CourseDate) = '" & Me.txtCourseDateYear & "' " 

View 5 Replies View Related

Creating Combobox With Unique Month And Year Entries From A Date Field In A Table

Jul 18, 2005

Hi,

Anyone know how to create a combo box that has unique month and year entries from a table (month in one column and year in another - i.e. 2 columns)?

I've been able to get the textbox of the combobox to output the correct format using a custom format but it does not affect the combobox data. Also, I'm not sure how to separate this by two columns...

Any help would be much appreciated!

View 2 Replies View Related

Month String To Numerical Month

Oct 13, 2006

HiI have a field that is text which holds the month in form "oct" etc. I need to pass the numerical value of that month to a function so how do I create an expression that will change it (in this instance) to 09.kind regards in advance.peter

View 5 Replies View Related

HELP: Changing Dates To FY (fiscal Year) And YTD (year-to-date) Values

Apr 25, 2006

I have a huge table with transaction dates. I need to slice and dice
this data (sum, %'s, etc), but group by FY. Our fiscal year is from
7/1 thru 6/1.

For example:
1/8/2004 = FY 2004,
8/12/2004 = FY 2005,
2/3/2006 = FY 2006

THEN . . . . I need to also isolate certain periods, for example July-
March for YTD (year-to-date) analysis and compare YTD of 2006 with that
of 2005.

What do you suggest? Many thanks.

Mehran

View 7 Replies View Related

Modules & VBA :: How To Add A String To A Date

Feb 27, 2014

I want to add a string as year to a date.

Somehow it doesn't work out. It should extract all records with Valid_from = 01.01.2013 and valid_to = 31.12.2013. The Year assignment works.

Code:
Public Sub BEN()
Dim strSQL As String
Dim t As Date, s As Date
DoCmd.SetWarnings False
Year = Right(pricedate, 4)
t = 1 / 1 / " & Year & "
s = 12 / 31 / " & Year & "
strSQL = "SELECT TRANSFER_PRICES.* INTO [TEMP] " & _
" FROM TRANSFER_PRICES " & _
" Where [Valid_from] = " & Format(t, "#mm/dd/yyyy#") & _
" AND [Valid_to] = " & Format(s, "#mm/dd/yyyy#")
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub

View 3 Replies View Related

Sum Of Current Year Minus The Year Of A Date In Past?

Apr 25, 2014

I'm trying to add a couple of fields to the Contact database in Access 2010.

In the Contacts table, I created a field called "Sobriety Date" that has dates formatted like 12/27/1995

I am trying to add a calculated field called "Years Sober" which should be the current year minus the year in the 'Sobriety Date' field (1995 in the example above).

I have been trying to tweak this:

SUM(DatePart("yyyy",[Date]) - DatePart("yyyy",[Sobriety Date]))

but it's not working. Keeps giving me "The expression that you entered is not valid for web-compatible calculated columns"

View 2 Replies View Related

Modules & VBA :: Change Date By Month?

Dec 4, 2014

I use textboxes to limit the time periods for report the defaults are set to first and last day of previous month I'd like to add a button that would set you a month back/forward, but I'm stuck on how to change values to correspond to first/last day ofprevious month

this is my onload code

Quote:

LastDay = DateSerial(Year(Date), Month(Date), 0)
FirstDay = LastDay - Day(LastDay) + 1
Me.tdat1 = FirstDay
Me.tdat2 = LastDay

View 3 Replies View Related

Month / Year Queries

Apr 5, 2006

I always have a problem getting this to work without a lot of effort and I know it should be easier than I make it out to be. Problem is say I have a bunch of records I need to summarize by month. Say Dec 05, Jan 06, Feb 06, etc. When I bring down the date col, I do something like:

Format([date],"mmm - yy")

And do a totals query with a second col that returns say a count for each period. This works fine except that when I sort I get Dec 05, Feb 06, Jan 06, etc.

How can I do this with out having to create a third col to do the sorting stuff? Should I somehow wait until the data gets to the report to do the date format stuff???

:confused: :confused:

View 1 Replies View Related

Month End And Year End Reports

May 1, 2006

For the record, I have indeed searched the forum for help with this, but no luck so far, so here goes. (C++ programmer, little background in access).

I have a table that stores a person's name, gross amount spent, gst spent, pst spent, and total amount spent.

I need to be able to create a query or something that asks me to enter a year, and it will sum up the amounts, gst, and pst over the course of that year, and print it neatly on screen.

Also, I need to be able to create a similar query that asks for a year and a month (does access possess Java's ability to easily create a pop-up calendar applet-like instance that provides a GUI for selecting months/years, or does the data need to be entered through a command prompt?) which will create a month-end report in the same manner, but only spans a 1 month period?

Any information that could be provided to me, even a means of getting started on this problem, would be great. Thank you and cheers everyone!

View 2 Replies View Related

Distinct Month AND Year

Aug 28, 2006

if i have a table like this:

4/3/2006
4/3/2007
4/12/2006
4/12/2008
5/2/2003
5/5/2003

i would like to extract only the distinct month and year date, so my query should return

4/12/2006
4/3/2007
4/12/2008
5/5/2003


can anyone plz help me with the sql query? thanks

View 2 Replies View Related

Grouping By Month & Year

Jul 22, 2007

I have another...

How do I group the dates on my query to show only a certain month and year?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved