Formating Month([field]) To "mmmm" ? Please Help Me.

Mar 16, 2005

Dear All,

I really need your help my problem is

i had data such table FPMas

NoseriItem Date Amount

01 1/1/2005 200

02 1/1/2005 100

03 2/2/2005 300

04 2/2/2005 100



when i using query to count and grouping by Bulan.

SELECT Count(FPMas.NoseriItem) AS CountOfNoseriItem, Month([Date]) AS Bulan, Year([Date]) AS Tahun, Sum(FPMas.Amount) AS SumOfAmount,
FROM FPMas
WHERE ((FPMas.Date)<[BlnSetor]))
GROUP BY Month([Date]), Year([Date]),

HAVING (((FPMas.Date)=[Forms]![SPM_Frm]![Bulan]));


The Query got data

CountofNoseriItem Bulan SumOfAmount

2 1 300

2 2 400

Now the problem is when i put the field Bulan in to Report and i want to Change the format of Bulan to "mmmm" to display goes wrong value it's change to December 19080 it's should be January and Febuary. I'thing the problem is the value got by the Month([Bulan]) is a number not date format where it's should.
How to Change the value when month([Bulan]) got into date time format? or any body have another way?
Thank's for any help.

From
H3ndr@

View Replies


ADVERTISEMENT

Formating Date Field In Query Grid

Nov 13, 2007

Hey.

How do I format Date field in query grid to make field into "11/11/2007" if the data originally has time, or in form "11 Nov, 2007" ?

View 2 Replies View Related

MMMM:SS Conversion To Seconds

Mar 12, 2008

Hi can someone please guide me into getting this right in a query. I have searched, but all of the solutions I have seen are HH:MM:SS.

I need to convert MMMM:ss to seconds. My pitiful attempts.

Is 5 the limit of characters and starting from 2 from the right?

Left(Right([Duration],5),2)*60)+Right([Duration],2)

e.g. 19340 = 11620 seconds.

Thanks in advance.:)

Dean

View 4 Replies View Related

Queries :: Converting Mmmm Yyyy To Including All Dates In That Range In Query

Oct 14, 2014

My table has many records for each month. I am creating a report that will display only the records in one month of a year. I have been able to create a form that gives the user the choice of the Month and Year for which to create the report. The code I am using to create the combo box is:

Code:
SELECT DISTINCT Month([QTDate]) AS MoNum, Format([QTDate],"mmmm yyyy") AS MoName
FROM MainTBL
ORDER BY Month([QTDate]);

What I need to do now is create the query for the report that displays all records for the chosen month and year. If I simply reference the combo box, all it shows is mmmm yyyy and the days are missing so the query doesn't work. What do I need to do to create the query so it displays all days within the month and date selected?

View 6 Replies View Related

Formating.

Sep 27, 2005

Hello everyone.
I have a multi user database that is used across 2 sites. In one of the sites the regional settings for 'short date format' is set to dd/mm/yy - the other site its set to dd/mm/yyyy. All the data that its the tables are set to dd/mm/yy. When the second site try to use the reports and queries they permanently error due to the fact that the date look up box is setting its self to dd/mm/yyyy as per its short date format. The IT manager there refuses to change this as its - and I quote 'its not y2k compliant !!!! :mad: :eek: :confused: - anyway. I cant change the data in the table to yyyy as its a text feed and Im not in control of that.

I planned on using a hidden text box to format the date to dd/mm/yy and hence bypass the regional settings, but this queries arent recognising the new foramt and are returning null and blank tables. Im now completely lost and stuck. Can anyone help ??

View 7 Replies View Related

Month And Date Only Field

Jun 29, 2005

Is there a way to create a field (Table or Query) that contains the Month and Date only and not the year?

View 5 Replies View Related

Month And Date Only Field

Jun 29, 2005

Is there a way to create a field (Table or Query) that contains the Month and Date only and not the year?

View 1 Replies View Related

Conditional Formating Help Please

Apr 17, 2006

Good morning, all...

I have an unbound box on a form and, within the box are 25 strategically placed "X" marks (text fields) named X1 - X25. I want only one of the X marks visible based on the value of two fields, i.e., if field "Impact" = 0.5 and "Probability" = 0.3 then X23 will be visible.

Could someone please get me started with the code to accomplish this?

Thanx in advance for your assistance.
Karen :)

View 2 Replies View Related

Conditional Formating

Jul 13, 2005

Trying to setup a conditional format for a field on a form based on conditions from another field.

I have a form with [Field1] and [Field2]

I have a 3rd field called [New SMF]

What I want to do is have conditions on [New Field] so that if the value is greater than Field1 or less than Field2 the background of new field turns red.

How can I do this?

Jon

View 4 Replies View Related

Number Formating

Apr 25, 2006

Hi All,

I'm new and have a question:

I would like to display the following number 2,550,567 as 2,550 (K)

Does anyone know how to apply this custom format

Thanks in advance

Twin:)

View 3 Replies View Related

Formating Number

Mar 9, 2008

Have searched for a while, but could not find an answer. Probably is very simple.

I have a DB with orders, in order to create an ordernumber i count the orders made this year, get a result and want that result to be 3 digits (eg counted 3 should be "003")
Here is my code, but wont get what i want.

strDatumNu = Format(Now(), "yy")
lngVolgnummer = DCount("[Jaartal]", "tblLevOrder", "[Jaartal] = '" & strDatumNu & "'")
lngVolgnummer = FormatNumber(lngVolgnummer, "000")

If i put "&&&" or "___" instead of "000" i get an type mismatch error

If i have 3 records, it always returns me 3 instead of 003.

Any help?
Thanks, Tom

View 1 Replies View Related

Re Help Me About Date Formating

Sep 28, 2006

hello

i am using a 2 tables where in one table my startdate format is general.
where as in another table date format is shortdate

now i want to compare 2 dates in select query

my query is

rsBreakDown.Open "select Breakdown.*,EquipmentMaster.McName from BreakDown inner join EquipmentMaster on reakdown.McNo=EquipmentMaster.McNo where startdate <=#" & dtpshow.value", conn, adOpenKeyset, adLockOptimistic

here startdate is in general format n dtpshow is in shortdate format

i want to compare these 2 dates

how can i?
is anybody help me?

View 2 Replies View Related

SQL If Statement For Formating

May 17, 2007

Can you do an SQL IIF Statement for formating? I am trying to Bold a value only if the value is greater than a value in another column.

View 3 Replies View Related

Formating Date

Feb 1, 2005

Hi...I am new to access...not sure if we can do that...
is there any way we can enter the date in access this way..

if we enter 01012004..the slash is added automatically..i.e the date becomes 01/01/2004.

It would be great if anyone could help me out..

Regards
Rahul

View 3 Replies View Related

Hel P On Formating A Form Please?

Dec 5, 2005

at the minute i have a form displaying all my customers in one list, i would like to display one customers details one at a time. i was to do this so i can search through my customers etc.

there is something in the properties of the form i need to change so it only displays one customer details per form. can anyone help me?

View 3 Replies View Related

I Need Help With Formating Numbers

May 25, 2006

Hi: I am new at this so forgive the crudity of this code.
I have a form I am trying to do calculations on I am using code to do this as follows.

Private Sub Text108_AfterUpdate()

Dim east As Single
Dim Center As Single
Dim west As Single
Dim Ans As Single

east = Text104
Center = Text106
west = Text108
Ans = east + Center + west

Text110 = Ans / 3




End Sub

Example
East=24.0
Center=25.0
West=25.3

Ans=74.3

Text110=24.76666

I want Text110 to show 24.7

View 1 Replies View Related

Formating Case

Nov 1, 2005

I have a linked table that updates regularly. It stores all words in Upper Case. I would like to mail merge out of a query but I would like to format the words so that the first letter is Upper Case and the rest of the letters are Lower Case. I have tried formating the field in the query but this doen't work.

Any way to do this?

View 7 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

Only The Month Required From The Date Field

Nov 8, 2005

Hello everyone

I am wanting to pull out only the month part from a date field, 11/10/2005.

In the query I have made an expression to pull the month, eg 10 for October.

I need to convert this number of the month to the Title of the Month.

I made a combo box on the report based on the Expression and in the row source put

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

Unfortunately it still comes out with the number of the month.

Can anyone tell me please where I am going wrong.

Thank You in advance.

View 2 Replies View Related

Grouping By Month From Date Field

Sep 16, 2004

I have a Date field, (an Expiry Date) and I want to use this in a Report that with Group the Report into Months. So we know, such and such a month, these are set to expire. Rather than have a separate Month column... how can I make use of exisiting Date in expiry field. Some one must know... Thanks in advance.

View 1 Replies View Related

Validation Rule For A Month Field

Aug 10, 2011

In the forms for my database, I require the user to input a month and year. Due to how the reports are structured, I need the format of the month and year to be very specific. I tried to set the format as 'mmmm yyyy' however even though it is visibly displayed as this, in the table, the values are still stored as sometimes 6/1/2011 or June 2011 and Jun 2011, which in Access' eyes are two different values.

In the report, it will display June multiple times because of this format discrepancy. I am trying to create a validation rule to ensure the user will enter the month and year in the correct format, but how to go about writing the expression.

View 7 Replies View Related

Particular Field Value Changes Every Month - How To Keep Historical Information

Nov 12, 2014

I have 2 tables.

Table A contains a list of Projects that evolve over time. Example:

Table A
ID Project Name Comment Comment Date
__________________________________________________ ________
1 Name 1 Comment 1.1 12/22/13
2 Name 2 Comment 2.1 12/20/13
3 Name 3 Comment 3.1 12/02/13

Now, let's say that Table A changes over time - just with the Comment portion. Example:

Table A
ID Project Name Comment Comment Date
__________________________________________________ ________
1 Name 1 Comment 1.2 01/20/14
2 Name 2 Comment 2.2 02/14/14
3 Name 3 Comment 3.2 01/02/14

Obviously, I would use an Update query to override the previous information.

But let's say that I want to preserve the previous information for historical use? How would I set this up?

View 2 Replies View Related

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

Custom Formating Of Dates

Sep 16, 2005

Hi

I am looking for a way to format a date in a textfield troughtout my application. I created a module containing a Function that receive a Date as parameter and returns a string. I put : =formatCustomDate([dbColumnName]) in the source field. Everything works fine when the date is not null. However if the date in the DB is null, empty, etc. The function is not even executed and I get a #Error in the field.

Why is the function not executed? Is there another way to do that? The reason I want to do that is because I want to avoid using the formating embeded in Access. I need the Function to return mm/dd/yyyy (00/00/0000).

Thanks

Jonathan

Here is the code I used to do that:

'************************************************* ********
' Function formatCustomDate(dbDate as String) As String
'************************************************* ********

Public Function formatCustomDate(dbDate As String) As String

'MsgBox dbDate

On Error GoTo err_Wrong_Date_Type

Dim formatedDate As String

If IsNull(dbDate) Or IsEmpty(dbDate) Or dbDate = CStr("") Then
formatedDate = Empty
Else
formatedDate = Format(dbDate, "mm/dd/yyyy")
End If

'MsgBox formatedDate

'MsgBox formatedDate
formatCustomDate = formatedDate

err_Wrong_Date_Type:
If Err.Number <> 0 Then
formatedDate = Empty
formatCustomDate = formatedDate
'MsgBox formatedDate
'MsgBox Err.Number & ":" & Err.Description
Else
formatedDate = Format(dbDate, "mm/dd/yyyy")
'MsgBox formatedDate
formatCustomDate = formatedDate
End If

End Function

View 11 Replies View Related

String Formating Question

Nov 15, 2006

I have a comment that I pulled with a dlookup from a table. Example below

Interface request received and filed (nk-11/14/2006) Test file sent to vendor. (nk-11/15/2006).

I want to seperate the data based on date. Example below

11/14/2006 Interface request received and filed
11/15/2006 Test file sent to vendor.

Is there an easy way to create a loop to walk through each character and format the original data to the formatted data?

thank you

View 3 Replies View Related

Conditional Formating Of Query

Jul 7, 2006

HI
I am working on a database that holds communications from treatment episodes.
the sub form holds the communications related to that client and that treatment episode.
when you enter a date into the communication record a mesage box asks if this is a new treatment episode and if yes opens a new treatment record. If it is the same treatment episode it just remind you that you are in the same treatment episode.
what i would like to do as an additional cue to the user is that once a client has been discharged from the episode that all communication records change color.
I can get the field that says dc to change color but I want all the fields in all the records from that episode to change color.
any ideas?
thanks

View 1 Replies View Related







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