Formatting The Day Of The Month As An Ordinal Number

Feb 9, 2006

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.

View Replies


ADVERTISEMENT

Queries :: Rolling 12 Month Query - Keeping Track Of Orders Placed For Given Part Number By Month

May 5, 2014

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.

View 2 Replies View Related

Formatting Change Month From # To Mmm

Aug 31, 2005

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.

Thank you

View 1 Replies View Related

Auto Number, Year, Month Number Combination

Apr 20, 2008

I need to create an auto number for service calls that show the following:

year as 08, 09 etc, month as a letter, Jan = A, Feb = B etc then an incremental number starting at 300 for each month/year combination.

So for example: 08A300

Can anyone help me as I am stuck?

View 10 Replies View Related

Queries :: Formatting Month Name In Running Total Query

Dec 18, 2013

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]);

Do any of you know a way I can make this work?

View 2 Replies View Related

Forms :: Display Data Month Wise - Conditional Formatting

Sep 2, 2014

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.

View 1 Replies View Related

Continuous Form, Ordinal Index By Function, #Error

Jun 16, 2005

I want the recordnumber displayed on a continuous form. The code below works with the command button, but not afterinsert.

Here's the code: (function GetPosition() is the control source)


Private Sub Command28_Click()
numbers
End Sub

Private Sub Form_AfterInsert()
numbers
End Sub

Private Function getPosition() As String
If Me.RecordsetClone.RecordCount > counter Then
counter = counter + 1
getPosition = "#" & counter
Else
getPosition = "#new"
End If
End Function

Private Sub numbers()
counter = 0
Me.Requery
End Sub


So it should do something like this:

#1 [ctrl 1]
#2 [ctrl 1]
#3 [ctrl 1]
#new [ctrl 1]


But called from after insert only the records on the screen get updated unless I scroll up, so simplified it looks like this:

----------------- top of subform after scrolling up
#3 [ctrl 1]
----------------- top of subform before scrolling up
#1 [ctrl 1]
#2 [ctrl 1]
#new [ctrl 1]


Where else can I put it or how can I modify the code to make it work?
Aidan

View 2 Replies View Related

Number Formatting

Jun 28, 2007

I'm having an issue with the way my number fields are displayed on my form.

I want the number to be dislayed with 2 decimal places. I have formatted the numbers so that they are 'fixed' and specified 2 in the decimal places property but my numbers are rounding themselves down.

eg. I type 1.25 but it comes up as 1.00

Thank you in advance for your help.

View 3 Replies View Related

Number Formatting

Jun 1, 2006

I am trying to calculate the square footage on a form. The formula I have is

(WxH)/144


For example:

W = 6.375
H = 11.5

Square footage should be 0.509115

However I am getting 632.500000


The width (W) and height (H) are formatted as double. So is the square footage. Is this not correct?

View 4 Replies View Related

Problem With Number Formatting

Jul 10, 2005

Hi,

I have a problem with a table that has number formats setup as usual - #,##0.00;(#,##0.00) and formatted to 2dp. However when inputting say 1.58 into the table it will round to 2.00 and does not show any decimals except as .00. When I remove the number formatting and set the decimal place to Auto, or 1 the same occurs but no decimal places are showing.

Can anybody explain why this is happening? I have tried searching through the properties, etc. to no avail.

Thanks very much for your help,


Roly

View 2 Replies View Related

Number Formatting In Queries

Dec 1, 2005

Hi,

Im trying to combine 2 fields below into one column.

ID - 001 (This is formatted in table using 000)

Sometext - "BLAH"

In the query I have written this ...

Newfield: [Sometext] & [ID]

But when I preview the query the data displays as "BLAH1" and not "BLAH001"

what should i do to correct this formatting issue?

many thanks!

View 2 Replies View Related

Telephone Number - Formatting Issue

Mar 10, 2005

I am trying to get a "formatted phone" number to appear on a "make table" query. I have revised the input mask properties to format the field to parantheses around the area code and a dash after the first three digits. The query works fine as long as I run a "Select Query" but when I change the query to a "Make Table" query, the new table does not come across with the telephone number in the correct ( )xxx-xxxx format! Using the Input Mask wizard, I've tried both options of storing the data and still get the same results. I do NOT want to have to go into this new table each time and reformat the telephone number as I'm sending the table directly to a switchboard in a different database. Any ideas on how to keep the formatted phone number intact?

View 1 Replies View Related

Convert Month Name To Number

Apr 9, 2008

Hi All - I would like the user to be able to import a month name (April for example) and then have access convert that name to a number (4) so that I can search for records that were opened before the month entered. I have been using:

MonthName(Month([CompletionDate])) = [Enter Month]

but

MonthName(Month([CompletionDate])) <= [Enter Month]

does not work.

Thanks!

View 1 Replies View Related

Convert Number To Month Name

Feb 4, 2008

Hi
I am struggling to get a number to change to a month name.
When the user chooses a month from a drop-down:

Value List
1;"January";2;"February";3;"March";4;"April";5;"May";6;"June";7;"July";8;"August";9;"September";10;"October";11;"November";12;"December"

I use the month number to run the query, ie: 1, 2, 3 etc.,
But I want the Month Name to use in the subsequent report.

I have no other data in the query other than a month number to use so I thought about an invisible text box that get's the month number and I could reference it from the report, but I just can't get the text box to get the month name from the drop-down.

=Month([Month4Query].[Column](2))

Maybe there is a better, easier way?

Martin

View 3 Replies View Related

General :: Change Formatting Of Number Columns?

Nov 21, 2014

Is there a way to change the formatting of a numbers column to the formatting :

1 = 0001

11 = 0011

111 = 0111

1111 = 1111

But that would have to apply to this also

1 - 2 = 0001 - 0002

11 - 22 = 0011 - 0022

111 - 222 = 0111 - 0222

1111 - 2222 = 1111 - 2222

If that makes sense ....

View 14 Replies View Related

Tables :: Number Formatting - Comma For Thousands

Jul 15, 2013

I am trying to make sure that my double will have a comma for thousands, but will also be able to display more than 2 decimal places (probably 4).

I am struggling with what format to use - if the number doesn't have any decimal places, the point is still put in.
#,##0.####

Examples:
23.2 = 23.2
64587.255 = 64,587.255
5000 = 5,000.

View 5 Replies View Related

Formatting A Number Field For Datasheet View

Feb 8, 2012

How do I make a number display as "001" and not "1" in datasheet view? The field type is number not text.

View 3 Replies View Related

Week Number Of The Current Month

May 24, 2005

Hello everyone.

I would like to ask for help on my current problem.

I need to input a date on a text box and another text box say txtweeknumber should determine the week number the date falls on the current month.

for example:

May 1, 2005 should fall on week 1 and txtboxweeknumber should say Week 1
May 9, 2005 should fall on week 2 and txtboxweeknumber should say Week 2
May 17, 2005 should fall on week 3 and txtboxweeknumber should say Week 3
May 25, 2005 should fall on week 4 and txtboxweeknumber should say Week 4
May 30, 2005 should fall on week 5 and txtboxweeknumber should say Week 5

Same goes for all months.
Is there a function to determine which week number of the current month the input date falls?

Please help.

Thanks,

Edwin

View 2 Replies View Related

How Can I Convert A Week Number To A Month?

Sep 29, 2005

Help!!
I have 2 fields in a table one for a year and one for a week number that data is being collected against.
I need to change these to a month and year for the report. Is there a way to do this??

View 1 Replies View Related

Date Range By Month Number

Mar 7, 2006

I am trying to make a query that finds all records by the "Month Number" and have problems.

What I did:
Month(DateField)
I got month numbers like I wanted but when I typed in the criteria below, I got February thru December Records, but when I search on anything "up to" 9 it works fine.

I want:
Between{month1] and [month2]
Month1= 9 (example)
Month2= 12 (example)
Need results to be from the requested months but any year

I have to imagine it's how the date is converted to an integer, but not sure.

Any help would be great!

View 4 Replies View Related

Expression Help Access Month Name To Number

Dec 29, 2004

I have an Access database query with a month (with full names of months in them) column. I need to build an expression that will take

DECEMEBER and make it 12
November and make it 11
October and make it 10 and so on.

I tried this and it worked for 1 month at a time ****** Expr1: IIf([month]="January","1") ****** but how would I format it to make all the months corespond with the right number?

The table value is not a month field it is a text field. so I don't think any of the date variables will work. I hope this is simple one for you access guys out there. I really need some help on this one.

View 1 Replies View Related

Forms :: Turning The Month Into A Number Using Vba?

May 7, 2013

im doing my ict unit 7 edexcel ict exam atm and as one of the tasks we have been asked to generate the testid. the test id consits the year a underscore and then the number of the month so for example if a test happened this year and this month the test id should be 2013_5. i can get the year and the underscore into the code to generate the testid but im not sure how to get the month to change to a number so for example if i select may from a drop down the code needs to take that and generate it into a number.

View 6 Replies View Related

Tables :: Add Prefix As Month Number?

Jan 18, 2015

I want my JobCardNumber field in table T-JobCards to add automatically month number.

e.g. For January
1-00001
1-00002
1-00003

For February
2-00001
2-00002
2-00003

OR

Jan14-0001
Jan14-0002

I know the method of setting format in tbl as 000 etc.. but this is one specific.

View 3 Replies View Related

Year / Month And Incremental Number (14-11-001)

Nov 6, 2014

I am working on an access database for my team. We keep and maintain a trail log for different departments.

The person will send a trail sheet to us we will input the info and attach what we call Production Instructions Number in this format (YEAR-Month-001), the next trail will get (YEAR-Month-002), the last 3 digits will start over when the month changes, I would like the PI number to populate automatically when the info is inserted.

View 11 Replies View Related

How To Get Specific Number For Current Month

Jun 14, 2012

Is there a way to look at only data from a specific month? It's June now so let's say I want to look up only data from June without having to hardcore the number 6 or letters "June" in my query. How would I go about doing this?

Another question would be how can I convert this to a number to multiply the numbers in my data with?

So I want to retrieve only data for the current month, June, and then multiply numbers associated with that data with the number of the month for June (that is, 6). How would I go about doing this?

View 1 Replies View Related

Forms :: Conditional Color Formatting For Plus And Minus Number

Apr 18, 2014

how to have a different conditional colour formatting for a plus and a minus number

If the field value is a Plus Number
The background colour will be Green

If the field value is a Negative Number
The background colour will be Pink

View 6 Replies View Related







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