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