Datepart To Show Month

Sep 6, 2006

I know this must be simple…

How do you show the actual month from the results of using the datepart function in a query

Query xMonth:DatePart("m",[ContractStartDate],[MaturityDate])

I want to show Jan, Feb march not the integer value.

Cheers

View Replies


ADVERTISEMENT

Show Month As Apr Instead Of 04

May 1, 2008

Below is the SQL for my query. The results will show month
year in a column that looks like: 03 2008. Is there
anyway for this to show as Mar 2008 instead. This
month is achieved by selecting a date range on a calendar
on the form. If the first date is 3/1/2008 and the last
date is 04/29/2008 then the query will show as:

03 2008
04 2008

I would like for it to show as:
Mar 2008
Apr 2008

Can anyone tell me how to do this?

SELECT
Format([TruckDate],'mm yyyy') AS [Month Year],
Count(*) AS TruckTotals
FROM TruckRegisterTbl
WHERE
(((TruckRegisterTbl.Company) Is Not Null)
AND ((TruckRegisterTbl.TruckDate) Between [Forms]![VisualInformationForm]![StartDateTxt] And [Forms]![VisualInformationForm]![EndDateTxt]))
GROUP BY
Format([TruckDate],'mm yyyy');

View 2 Replies View Related

Show This Month In Report

Feb 1, 2006

Hi There,

I tried the following to get this month/year automatically shown in my report (shown as the name i.e February 2006).

This is what I tried.
= MonthName(Month(Now()) -> did not return any value.

I tried with the expression builder:
Month («number») What do I need to fill in this string expression?

Thanks

View 2 Replies View Related

Show Last Month's Results

Jan 23, 2007

Hello All,

I am having problems getting the following lines to do what I need exactly. The formatting is kicking my butt basically.

Bob helped with these lines but I actually need the result to be last month. For instance it is now Jan. 07 and I need the query to total up the hours flown for Dec. 06. On Feb 1 07 I need it to now display Jan 07 totals and so on.

Query Field:
Format$([tblEntry].[tblDate],'mmmm yyyy')

Criteria:
Format(date(),"mmmm yyyy")

How can I change this to do what I stated above?

View 3 Replies View Related

Show All Dates In Any Given Month / Quarter

Aug 10, 2005

I need a query that lists each day of the month (or quarter) that is selected from a form. Meaning, if a user selects June 2005 on the form, the query will return to show every date from June 1, 2005 to June 30, 2005. Is this simple, tiny little thing possible?

The reason behind this is that on a report, I need to show data for every date. If there is not data entered for that date, it still needs to appear with the date listed, but with no entries represented.

Thanks!
Tess

View 3 Replies View Related

Show Records By Week Per Month

Feb 20, 2007

Good day!

I'm planning to create a form that would show total number of sales transaction per week: If today's month is February, i would like users to see records like this: (Week should start on a Monday and ends on Saturday)

GroupNo______Week1_____Week2_____Week3____Week4___ _Week5___MTD
GroupA________12_________2__________10________11__ _____15_____50
GroupB_________8_________5___________7_________1__ _____21_____42

I can't find a sample code for this particular date criteria. :-(

Thanks in advance!


Sheila

View 6 Replies View Related

How To Show All The Days In A Scpecific Month

Feb 12, 2008

Hi!

I want to show all the days in the specific month, e.g. there are 29 days in february 2008 and the records will display all the days that are available as below:
1/2/08
2/2/08
3/2/08
..
..
..
29/2/08

What is the syntax?

Thank you.

View 7 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 Selection Criteria - Show Last Day Of The Every Month

Apr 16, 2015

I have a big list of data, with a row of data for every day for each client.

I need a selection criteria that will provide show the last day of the every month (historic data) for each client.

I've had a go but I'm not making much progress.

View 4 Replies View Related

Forms :: Date Picker - Show Saturdays Only In Any Given Month?

Nov 11, 2014

Is there a way to force the Date picker to show only Saturdays in any given month?

View 6 Replies View Related

Queries :: Expression To Show Years And Month Difference Between 2 Dates?

May 6, 2014

I am wanting to get an expression that will return the difference in years and months between 2 dates. Specifically, I want to know peoples ages in years and months based on a person's D.O.B and todays date. I have managed to do it in years:

Expr1: DateDiff("yyyy",[TBL_EmployeeDetails]![D_O_B],Date())

and in months but I would like to know how to return the difference in years and months.

View 1 Replies View Related

Queries :: Dates In A Query - Show Orders By Month For Last 12 Months

Apr 22, 2013

I want to create a simple query from a list of orders dating back over 12 months.

The fields I have is Ord_Date, Qty

I need to show the orders by month for the last 12 months.

The problem I am getting is that the orders for the month of April (as we are in April now) contain orders from 1st - 22nd April 2013 and orders from 23rd - 30th April 2012, therefore confusing the figures.

I would just like orders grouped by month with a total qty dating back 12 months, but without any old orders for the current month.

View 6 Replies View Related

Queries :: Access 2010 - Query To Show Data On Every Monday In Current Month

Nov 8, 2013

is their a way to have a query to only show data on every monday in current month.

Month([datefield])=Month(Now()) And DateAdd("d",7,[datefield])

View 11 Replies View Related

DatePart And Between...And

Dec 13, 2006

Have been unable to find any solutions for this in the forum or the help files in Access.

Project has a [cutindate] and an [enddate].
A [flatamount] ($$ savings amount].

I am calculating the month savings by dividing the [flatamount] by the number of months between [cutindate] and [enddate] using DatePart().

With the help of someone on the forum I have a table "MonthOffset" which just has the numbers 1-12 in the [MonthOffSet] column. Using this, I can get my query to take the monthly savings amount and disperse it to each month for the next 12 months beginning with the [cutindate]. This was my original direction. They now don't want to see it dispersed over 12 months, but rather just show savings between the [cutindate] and [enddate]. I can get the correct monthly savings in my query, but it is still showing it for 12 months, i.e. [flatamount] is $50k, monthly savings is for 5 months, $10k showing for each of 12 months. I can just use this, then make another query to only show the monthly savings between the months I need but I would like to get it done in one query.

My query:

ProjectID CutInDate SavingsMonth
1 2/1/2006 2/1/2006
1 2/1/2006 3/1/2006
1 2/1/2006 4/1/2006
Through 1/1/2007

Project ID has a cut in date of 2/1/2006 but the savings will only last until 8/1/2006.

Has anyone had to do this before?

I have tried this in the criteria of my SavingsMonth field:

[MonthOffSet] Between DatePart("m",DateValue([cutindate]) AND DatePart("m",DateValue([enddate])))

But keep getting the message: You did not enter the keyword And in the Between...And operator. The correct syntax is "expression [Not] Between value1 and value2"

Does anyone know if this can be done?

Thanks,

Toni

View 3 Replies View Related

DatePart Help Please

Oct 15, 2006

If I can get any suggestions I would appreciate it. I need to run a report on total number of calls, daily and I need to sort it by week. I used the Sorting and Grouping options and I can sort them. what I need to accomplish is to have the week start on a Monday instead of the default Sunday. When the first data of the week does not start on a Monday, the heading date of that week will be the date of when it is first recorded. So, if i have no calls on Monday or Tuesday but there are records for Wednesday, it will have Wednesday date as the heading for that week. I went through the help online for Access and it mention I can create expressions to help using the Datepart heading. But, it didn't recognize the expression. Does anyone know how to solve this problem?

View 3 Replies View Related

This Is Odd...using An Iif With The Datepart Function

Jan 19, 2007

Here's the deal, I have a table that has a flag in it. It's either a 1 or a 0.

Expr1: IIf([Is it?]=1,[REQ DATE],IIf([Is it?]=0,(DatePart("yyyy",[REQ DATE])),"ERROR"))

If it's a 1, it displays the full date. If it's zero, it's supposed to display the year of the date.

The first part works fine. If it's a 1, it shows the date. However, if it's a 0...it shows a date not even remotely close...and they're all the same. 06/28/1905.

View 2 Replies View Related

DatePart In Query Use

Sep 5, 2007

Hi

Can anyone advise on the following query problem:

I have been using the function DatePart to select records from a field of a datetime type, and had seen somewhere (an old Access refrence book?) that it is possible to use DatePart to select more than one time setting, but I have not found out if this is actually possible, or the syntax for it if it is.

In selecting a time range, I would like to use a single query (eg Between 0900 and 1730) to represent a working day, with the criteria "hn" in the datePart function. At present I have to use two selection criteria to achive this (h Between 9 and 16; h = 17 AND n <30)

Ay help/suggestions, and especially an example, would be greatly appreciated. I am using Access 2003.

View 7 Replies View Related

DatePart Function

Jun 2, 2007

I am using a DatePart function to get the week number for various dates in my project (DatePart("ww",[date],vbsunday)). Is it possible to set the firstday setting of the function (vbsunday) by referring it to a field in another form. I have tried but get an error. I am hoping to achieve this because the database will be distributed to various agencies which have different first day of the week for their schedules. I want to avoid re-writing the code for each agency. Any help would be greatly appreciated.

View 3 Replies View Related

Can You Use Multiple Weeks In DatePart

Jun 27, 2005

I'm using the following in a query qwhich allows me to enter the week number as the criteria:

DatePart('ww',[Visitdate],2,2)

This works fine , no problems. What I would like to know, is it possible to enter muliptle week numbers in the criteria, say 14 16 21 to give me output for those weeks, I have tried different separators to no effect ie. : and ; It may be that it simply is not possible but it would be extremely useful if ti was.

View 12 Replies View Related

Datepart Function Problem

Jan 19, 2006

Hi,

I seem to be stuck when trying to get "mm-yy" from a "dd/mm/yyyy" field.

For example I want the query to bring back "Jan 05" or "January 2005" from "01/01/2005"

I've tried using the datepart function i.e. datepart("mm-yyyy",[date but to no avail.... can anyone help???

Many Thanks in advance

Jason

View 1 Replies View Related

Datepart Function Problem

Jan 19, 2006

Hi,

I seem to be stuck when trying to get "mm-yy" from a "dd/mm/yyyy" field.

For example I want the query to bring back "Jan 05" or "January 2005" from "01/01/2005"

I've tried using the datepart function i.e. datepart("mm-yyyy",[date]) but to no avail.... can anyone help???

Many Thanks in advance

Jason

View 2 Replies View Related

DatePart Function, String Functions

Nov 1, 2005

Hey all I have a I was wondering if you knew how I can get a part of my datepart function out, I have taken the year from the the date with the date part function and now I need only the 05, so what can I do to get it out? Thanks MY CODE: Dim Num as string Num = DatePart("yyyy", STRDATE)
I looked up the code for a right function and it is suppose to work on a string, so I tryed it on this. My CODE:
Dim Year As String
Year = RIGHT(Num, 2)
I obtained a type mismatch WHY???

View 7 Replies View Related

DatePart - Can I Change The Default «firstweek»

Apr 28, 2006

This is my first thread so be gentle.

I curently have a query based on a payments table. In the payments table there is a field called date (when the payment was processed).

I want to be able to display, in my query, the quarter that this payment was made in. It is based on financial quarters so quarter 1 starts on April the 1st.

The default of firstweek seems to be January the 1st. Can I change it to April the 1st - if so how?

Many Thanks

View 3 Replies View Related

DatePart Will Only Accept 6th Jan 2005 As Week 2

Jun 2, 2005

When using 'ww' as the criteria in the DatePart calculation it will not accept 1 for 6th Jan 2005 which is week 1, you have to enter 2 and it will select it, in fact all the weeks so far in 2005 are out by 1. To cure it you can add on -1 to the query and it works fine until you go back to the previous year and it screws up those dates.

Any ideas?

View 11 Replies View Related

Reports :: Filtering Report To Only Show Current Month Report?

Nov 16, 2014

The aim of what I am doing is to create a monthly statement to give to our intermediaries that shows the commission they will receive each month for the deals they have referred. I have managed to create this report, HOWEVER I can't figure out how to filter out which month I need, so I a report for Jan, Feb Mar etc... The idea is that at the end of each month I need to run the report so only the latest month shows...

View 3 Replies View Related

Forms :: DatePart Function To Filter Out Sales Tax By Quarter

Apr 26, 2013

Was wondering if there is a way to incorporate the DatePart function in the below statement to filter out sales tax by quarter? I have two drop down boxes that filter the year and the state but the below only totals the tax for the year and state. I am trying to add 4 text boxes to show the quarterly break down of sales tax.

The below text box is in a form pulling the data from a query.

Text Box

=DSum("[SalesTaxCharged]","[Sales Tax Calculation Qry]","Year([Order Date])=[SelectedYear] And
[StateProv]=[SelectedState] And Not [Tax Exempt]")

View 6 Replies View Related







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