The months in my query are recorded as numbers 1 for January, 2 for February, etc. I was wondering how I could format in a query or report the months so that they automatically format themselves into text.
Is there a way to format the day of the month as an ordinal number? For example instead of formatting the day as January 1, I would like to format the day as the 1st, 2nd, 3rd etc. day of the month. I need to do this in order to match the format of a contract that we want to use as a mail merge.
I am creating a line graph from a running total query to show our income from items shipped for each month. Currently I have the following Code in my query which works but it displays the month as a number in my graph and I would like it to show the Month name.
Code: SELECT DatePart("yyyy",[ShippedDate]) AS AYear, DatePart("m",[ShippedDate]) AS AMonth, DatePart("d",[ShippedDate]) AS ADay, Format(DSum("SalesPrice","tblJobs","DatePart('d', [ShippedDate])<=" & [ADay] & " AND DatePart('m', [ShippedDate])<=" & [AMonth] & " AND DatePart('yyyy', [ShippedDate])<=" & [AYear] & ""),"Currency") AS RunTot FROM tblJobs WHERE (((tblJobs.ShippedDate) Is Not Null)) GROUP BY DatePart("yyyy",[ShippedDate]), DatePart("m",[ShippedDate]), DatePart("d",[ShippedDate]) ORDER BY DatePart("yyyy",[ShippedDate]), DatePart("m",[ShippedDate]), DatePart("d",[ShippedDate]);
I tried this solution, but I get an error in the RunTot field, I'm assuming because Access can't use the month name in dsum.
Code: SELECT DatePart("yyyy",[ShippedDate]) AS AYear, MonthName(DatePart("m",[ShippedDate])) AS AMonth, DatePart("d",[ShippedDate]) AS ADay, Format(DSum("SalesPrice","tblJobs","DatePart('d', [ShippedDate])<=" & [ADay] & " AND MonthName(DatePart('m', [ShippedDate]))<=" & [AMonth] & " AND DatePart('yyyy', [ShippedDate])<=" & [AYear] & ""),"Currency") AS RunTot FROM tblJobs WHERE (((tblJobs.ShippedDate) Is Not Null)) GROUP BY DatePart("yyyy",[ShippedDate]), MonthName(DatePart("m",[ShippedDate])), DatePart("d",[ShippedDate]) ORDER BY DatePart("yyyy",[ShippedDate]), MonthName(DatePart("m",[ShippedDate])), DatePart("d",[ShippedDate]);
In my Access forms, i display data by month wise; and i want to give the conditional formatting with two condition;
If date of today is greater than target date for that month and if got value more than 0 - Red color If date of today is lesser than target date for that month and if got value more than 0 - green color
Target date for every month in available in a separate table.
I have a subform that is purely to display information (no edits will be made on it). The subform has a special color on it (company color) that I can not deviate from. My problem is the first record in the subform (displayed as a continuous form) gets the focus. The font is white and standard access has a white background. My end goal would be to elimnate the focus for any record in the subform (if it is possible). I know that I could change the color with conditional formatting but the colors in conditional formatting do not match the company color.
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
I have a form in datasheet with the fields "Date In" and "Date out" among other.
I want to be able to Change the backcolour of the "Date In" to red, yellow, or green depending on the length of time elapsed between current date and date in. This works fine on the conditional formatting, but now I only want to apply this formatting IF the "Date Out" is empty.
I would like to change the text formatting (color, italics, bold etc) of the contents of a control based on a boolean value in the underlying datasource of the report.
For instance, I have a report that generates a "Proforma Invoice" i would like to ability italicize the prices of certain items based based on a boolean value (EstimatedPrice) in the underlying datasource.
I have a continuous form with a text field that says "Select". There are two other fields, one of which is Brand. When the Brand Combo box has nothing in it I want the text box to appear (instructing the user to select a Brand) But once the user selects a Brand and goes to the next records, I would like the "Select" in only that record to become not visible. I tried conditional formatting but can't apply that to an unbound control ( or at least it is grayed out when I select the text box) and any other possible solutions I have found changes all of the selects - not only the one in the changed record.
I have a subform with two fields [status] and [process], i am trying to get the backcolor of procces to change to red if the status field contains the text "expired".
I have tried vba. plced on the after update
if me.status = "expired" then me.process.backcolor = vbred else me.process backcolor = vbwhite end if this
Changes the color of the field [process] but also all process field colors to red even if the status field does not contain "expired".
I think conditional formatting is the way to go but i think i need an expression to complete this.
Need to confirm whether the Default formatting option in the Conditional formatting Dialog won't work in datasheet view of a form. Please note that all conditional criteria are working fine but not the Default Formatting option - only in the Datasheet view (In Single Form view the default formatting is working fine)
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 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...
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?
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
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..
I am trying to change the button color on a subform if a related form data changes.Main form is products with a continuous subform with serial numbers of products i.e, serial number, location, price and a button to add addtional issues if there are any for this particular serial number (this will open up another form related to the serial number so I can add an issues if there are any).The reason I would like the button to be a different color is so I can quickly see if there are any additional notes been added to the serial number. Just in case you may ask why not add the field to the continuos form is that the issues and be quite lengthy and there may be lots of serial numbers on the form
I have not done much work in later version of Access. Now I found if I change a design in one form and similar forms (names are different) which are linked to the same tables got changed as well without openning them up and making changes. Is this something new with Access 2003?
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!
Really simple one for all you folks out there, but I've just been struggling for 20minutes and thats it I've decided to to seek proffesional advice. Though its been said I need it in other areas of my life too (so my ex says but..), any way I digress.
I put a tab control on a form all I want to do is format the title of each individual Tab name, i.e. font size etc, type. Had a look at the properties but nothing there seems to register.
Hey do any of you guys know if its possible to change the colour of the tabs or set them as transparent in a form plz??? I know you can set the background as transparent, but i dnt know if you can do that to the actual tabs.
Ok, don't know what I should title this problem as, but maybe you guys can help. The data stored on my table is formatted as Currency, however, when I Queue up the data, I also multiply it by a factor (exchange rate). Which turns the data into just a regular number. When I display this data on my form, I reformat it into Currencies again. However, when I click on the field, it shows the number in its raw format. How can I fix this so that when the user click on the field, they won't see the raw number but just regular Currency format? Much thanks in advance.
I am creating a database of private equity funds. I want to have a currency field for an input entitled fund size. However, I need to be able to display whether the size of the fund is in dollars or euros. How can customize a number/currency field to allow me to input the correct currency symbol?