Hi
I 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
I am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?
Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.
I have a MS access table with 12 numerical columns for 12 Months payments for our customer base.
I need to calculate variance of payments month on month and identify set of customers who have made huge payments.
Solution to parse the strings below and get number of months
1) 10 years 5 months 5 days - Should give 5
2) 6 years 10 months 22 days - Should give 10
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"
I have a table full of dates of meetings through out the year...
Example
tMeetingDates
16th August 2005
18th September 2005
19th October 2005
23rd November
-----
i also have a report that i print out each month that has the date of the meeting on it... i currently edit the date manually.
I was wondering if there was a way to automate this facility, so that the report looked to the table of dates and looked for the current Months Date that is stored i the table.
i then want this date to be displayed in the Report.
So in this instance if i am running a report for tomorrows meeting being the 19th October it would display that date in the report....regardless of when i run the report...
Obviously if i run the report on the first of November because the month has changed it would then display the date of the November Meeting...
we only ever have one meeting a month!!!!
Please help
Andy
Using Access 2010..I have form with a date on it. For this control I have show date picker set to "For dates" and lo and behold I get calendar! I can scroll through this calendar month by month. Great if I just want to go back or forward a month or three. What I'd like to be able to do is scroll through the calendar year by year. Can I do this with the method I'm using at the moment? If not is there a way round it that isn't over complicated?
View 6 Replies View Relatedplease help me
i have a date field called open & i need to generate a query to count all entries by month, so far i can get all cases each day, but i'm unable to get the query to count them just based on the month & year..
any ideas please
Hi,
Could you help me with the following.
Lets say I have table with one of the column header as notes.
The notes is of type 'text'
In the notes column information like the following is typed out
MAX O.D 3.456" Min ID 1.2" and OAL 3.4"
or
MAX O/D 5.456" Min I.D 1.2" and Min Length 4.4 inches
I want to pull out the first numerical value (3.456 - first example, 5.456 -second example) and the last numerical value (3.4 -first example and 4.4 in second example). How would I be able to do this.
So the end result should be 2 more columns with max od as one and min length as the other.
Regards
George
Hello,
I've been looking through the archives and learnt a fair amount about date manipulation, but am still a bit stuck. I'm sure there is a way to do this and would appreciate any ideas:
Basically I am monitoring changes in Fuel level over months. For each month, I have a variety of readings, each on a different table, including sales from that tank, delivery in to it, and most importantly a closing dip reading taken at the end of the month.
In order to figure out the change in Fuel level over each month, you need an "opening" figure : this is taken to be the closing dip value of the previous month. And this is where I'm getting stuck!
I am writing a query to generate new table which combines all the relevent values. I have managed to link them through the date field, even though the specific date is not identical what matters is that it is the same month. Effectively I am trying to add a new 'column' showing the opening value, where opening value = previous months closing value for this tank.
I noticed there is a post talking about previous month data <a href="http://www.access-programmers.co.uk/forums/showthread.php?t=39708&highlight=previous+month">here</a>, which gives me some ideas but I'm not sure how to go about putting this into the SQL query. I can't use Date() because I'm interested in more than just previous to THIS month - the data spans back over a year!
I'm still learning Access so this might be a simple question - if so please point me to the relevent parts of the help file!
many thanks
Catherine
Hi,
I am a very new guy in access. One very simple question.
How can I give crieteria for to get a query result for month and year
ie. i know DATE() is for today
between[start date]and [end date] gets from a date to a date
but i wanted to get particular month and year. How to write this?
Hope somebody will help in this matter.
thx and regards
nowfal
I need to return the last date of the month not for this month, but 6 months down the road. When the FAA says 6 calendar months from a certain date it would mean go 6 months forward and then to the end of the month. So with today being April 11th, I would count 6 months forward to October, and then to the end of the month - October 31. Which would be the date I wish to return. I didn't see anything as far as formatting goes that would allow it to be done easily, and I've already attacked it two different ways, but I am wondering how you might solve the problem.
No rush in this. And I'm going to choke myself if I missed something very simple somewhere.
Hi All, HAPPY NEW YEAR!
I have a 2 fields, one is start date in the format dd,mm,yyyy - the other is end date in the same format. I want to create a field in my query called "month" which will give the month for any record. This will be a parameter field.
So that when I type in January in my parameter - I will see all the records for that month. How can this be done?
Hello,
I have a query which pulls data based on a date range.
However, instead of using this, I'd like to only get data for last month.
I have tried doing this by adding 2 fields :
Month: Month(myDate)
and
Year : Year(myDate)
In each of these fields I have added a criteria of :
Month(Date())-1
and
Year(Date())
This allows me to get the data for last month (say February), but also ensures it is only the data for this year. (as the DB holds data for the past 4 or 5 years)
The problem is though, when the year rolls over to January, this will not work.
Can anyone shed any light on an alternative please ?
Thanks,
J.
auto date no problem but just an automatic month ??
any ideas???
My problem: I work on a German machine (i.e. the regional settings are set for Germany) but have created reports in Access where the name of the month needs to appear in English. Is there a way to do this without monkeying with the regional settings?
Thanks in advance...
Hi,
How can I get a 2 digit month when I use month(now())? I've getting only 1 instead of 01. Please advise.
if you are asked to store only the month, or only the month and year, how do you do it? tnx.
View 3 Replies View RelatedI have a month field on my table, I have tried to ass "Now()" on the Default Value, but its not seems to be working, what I want is when the form is opened the default value would be the current month.
Thanks.
Hi
Probably an easy one here.. Got a table that I will like to do a query on.. All I need is to list all records for the current month. So obviously, when it goes to the next month, the query would update and show only records for that month.
What do I need to put under the date criteria to get this query?!?.. Any ideas?
Thanks
Saifon
Hello, I have the following query which gives me a count by day.
However, it sorts by day instead of day/month.
For example:
01/01/05
01/02/05
01/03/05
02/01/05
02/02/05 and so on...
Can I sort so it will show me all of January, then all of Febuary etc?
Thanks.
J.
Hi
This is probally really easy........
I have data with dates like 15/05/2005, 30/09/2005.
I need to convert the dates to 01/05/2005 and 01/09/2005
Can anyone help!
Hi All,
I have the following calculated field in a query to grab the Month name:
MnthName: Format((Month([DateComplete])),"mmm")
where [DateComplete] is a Date/Time field from a table
Is this dubious at all?
Because the results come back with either "Jan" or "Dec" (NO other month names)
(Ive also tried the DatePart("m",[DateComplete]) function)
Strangely, comparing the error against the Month number (Mnth: Month([DateComplete])) it seems that 1 is always "Dec" and all the other month numbers are "Jan".
Example query results:
Mnth MnthName
3Jan
11Jan
10Jan
9Jan
9Jan
11Jan
12Jan
1Dec
1Dec
2Jan
11Jan
7Jan
7Jan
6Jan
2Jan
1Dec
Can someone please enlighten me?
Thanks...
I need a query to look between two dates. The first date needs to be the first of the month of date given in [forms]![frmflash]![enddate] and the last date needs to be [forms]![frmflash]![enddate]. How can I make a between statement that looks between these two dates?
View 2 Replies View RelatedI have looked and tried a lot of things that I have seen in this forum, but I am not able to make this work. I have a query that I am trying to sort a table field, "Date" by month. The field has a "General" time/date format in the table. This is the last code I tried, but as you can see I am not much with VB. Please see if you can help me. The table name is "Batch Data" and the field name is "Date".
Thanks,
Robert
SELECT Format([Batch Data].[Date],'General Date') AS formDate
FROM [Batch Data]
ORDER BY [Date];
Anyone know if i can use DateAdd or something like in a query to show current month I want the user to be able to see records for the month we`re in Thanks all
View 1 Replies View Related