Hi,
I seem to be stuck when trying to get "mm-yy" from a "dd/mm/yyyy" field.
For example I want the query to bring back "Jan 05" or "January 2005" from "01/01/2005"
I've tried using the datepart function i.e. datepart("mm-yyyy",[date]) but to no avail.... can anyone help???
Many Thanks in advance
Jason
Here's the deal, I have a table that has a flag in it. It's either a 1 or a 0.
Expr1: IIf([Is it?]=1,[REQ DATE],IIf([Is it?]=0,(DatePart("yyyy",[REQ DATE])),"ERROR"))
If it's a 1, it displays the full date. If it's zero, it's supposed to display the year of the date.
The first part works fine. If it's a 1, it shows the date. However, if it's a 0...it shows a date not even remotely close...and they're all the same. 06/28/1905.
I am using a DatePart function to get the week number for various dates in my project (DatePart("ww",[date],vbsunday)). Is it possible to set the firstday setting of the function (vbsunday) by referring it to a field in another form. I have tried but get an error. I am hoping to achieve this because the database will be distributed to various agencies which have different first day of the week for their schedules. I want to avoid re-writing the code for each agency. Any help would be greatly appreciated.
View 3 Replies View RelatedHi,
I seem to be stuck when trying to get "mm-yy" from a "dd/mm/yyyy" field.
For example I want the query to bring back "Jan 05" or "January 2005" from "01/01/2005"
I've tried using the datepart function i.e. datepart("mm-yyyy",[date but to no avail.... can anyone help???
Many Thanks in advance
Jason
Hey all I have a I was wondering if you knew how I can get a part of my datepart function out, I have taken the year from the the date with the date part function and now I need only the 05, so what can I do to get it out? Thanks MY CODE: Dim Num as string Num = DatePart("yyyy", STRDATE)
I looked up the code for a right function and it is suppose to work on a string, so I tryed it on this. My CODE:
Dim Year As String
Year = RIGHT(Num, 2)
I obtained a type mismatch WHY???
Was wondering if there is a way to incorporate the DatePart function in the below statement to filter out sales tax by quarter? I have two drop down boxes that filter the year and the state but the below only totals the tax for the year and state. I am trying to add 4 text boxes to show the quarterly break down of sales tax.
The below text box is in a form pulling the data from a query.
Text Box
=DSum("[SalesTaxCharged]","[Sales Tax Calculation Qry]","Year([Order Date])=[SelectedYear] And
[StateProv]=[SelectedState] And Not [Tax Exempt]")
Have been unable to find any solutions for this in the forum or the help files in Access.
Project has a [cutindate] and an [enddate].
A [flatamount] ($$ savings amount].
I am calculating the month savings by dividing the [flatamount] by the number of months between [cutindate] and [enddate] using DatePart().
With the help of someone on the forum I have a table "MonthOffset" which just has the numbers 1-12 in the [MonthOffSet] column. Using this, I can get my query to take the monthly savings amount and disperse it to each month for the next 12 months beginning with the [cutindate]. This was my original direction. They now don't want to see it dispersed over 12 months, but rather just show savings between the [cutindate] and [enddate]. I can get the correct monthly savings in my query, but it is still showing it for 12 months, i.e. [flatamount] is $50k, monthly savings is for 5 months, $10k showing for each of 12 months. I can just use this, then make another query to only show the monthly savings between the months I need but I would like to get it done in one query.
My query:
ProjectID CutInDate SavingsMonth
1 2/1/2006 2/1/2006
1 2/1/2006 3/1/2006
1 2/1/2006 4/1/2006
Through 1/1/2007
Project ID has a cut in date of 2/1/2006 but the savings will only last until 8/1/2006.
Has anyone had to do this before?
I have tried this in the criteria of my SavingsMonth field:
[MonthOffSet] Between DatePart("m",DateValue([cutindate]) AND DatePart("m",DateValue([enddate])))
But keep getting the message: You did not enter the keyword And in the Between...And operator. The correct syntax is "expression [Not] Between value1 and value2"
Does anyone know if this can be done?
Thanks,
Toni
If I can get any suggestions I would appreciate it. I need to run a report on total number of calls, daily and I need to sort it by week. I used the Sorting and Grouping options and I can sort them. what I need to accomplish is to have the week start on a Monday instead of the default Sunday. When the first data of the week does not start on a Monday, the heading date of that week will be the date of when it is first recorded. So, if i have no calls on Monday or Tuesday but there are records for Wednesday, it will have Wednesday date as the heading for that week. I went through the help online for Access and it mention I can create expressions to help using the Datepart heading. But, it didn't recognize the expression. Does anyone know how to solve this problem?
View 3 Replies View RelatedHi
Can anyone advise on the following query problem:
I have been using the function DatePart to select records from a field of a datetime type, and had seen somewhere (an old Access refrence book?) that it is possible to use DatePart to select more than one time setting, but I have not found out if this is actually possible, or the syntax for it if it is.
In selecting a time range, I would like to use a single query (eg Between 0900 and 1730) to represent a working day, with the criteria "hn" in the datePart function. At present I have to use two selection criteria to achive this (h Between 9 and 16; h = 17 AND n <30)
Ay help/suggestions, and especially an example, would be greatly appreciated. I am using Access 2003.
I'm using the following in a query qwhich allows me to enter the week number as the criteria:
DatePart('ww',[Visitdate],2,2)
This works fine , no problems. What I would like to know, is it possible to enter muliptle week numbers in the criteria, say 14 16 21 to give me output for those weeks, I have tried different separators to no effect ie. : and ; It may be that it simply is not possible but it would be extremely useful if ti was.
I know this must be simple…
How do you show the actual month from the results of using the datepart function in a query
Query xMonth:DatePart("m",[ContractStartDate],[MaturityDate])
I want to show Jan, Feb march not the integer value.
Cheers
This is my first thread so be gentle.
I curently have a query based on a payments table. In the payments table there is a field called date (when the payment was processed).
I want to be able to display, in my query, the quarter that this payment was made in. It is based on financial quarters so quarter 1 starts on April the 1st.
The default of firstweek seems to be January the 1st. Can I change it to April the 1st - if so how?
Many Thanks
When using 'ww' as the criteria in the DatePart calculation it will not accept 1 for 6th Jan 2005 which is week 1, you have to enter 2 and it will select it, in fact all the weeks so far in 2005 are out by 1. To cure it you can add on -1 to the query and it works fine until you go back to the previous year and it screws up those dates.
Any ideas?
So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies View RelatedI have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total
-----------------------
2 | 1234 | 80.00
------------------------
1 | 43526 | 20.00
------------------------
> | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer)
Dim SPSelect As String
SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE"
SPSelect = SPSelect & " ItemID = '" & ItemID
SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
=SUM(CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit]))
#Error
We have a date function that converts a text date format. Can someone help me with time function to do the same thing? We want military time. The field is like this now: txt fields.
160037
213137
224356
235716
235800
12341
21708
22732
Here is the date function we use:
Function f2Date(strDateOld As String)
Dim strDate As String, strMonth As String, strYear As String
strMonth = Mid(strDateOld, 5, 2)
strDate = Right(strDateOld, 2)
strYear = Left(strDateOld, 4)
f2Date = strMonth & "-" & strDate & "-" & strYear
f2Date = CDate(f2Date)
f2Date = Format(f2Date, "mmmm d yyyy")
End Function
Hi all,
I need a little help. In my DB, I have a command button set up (I was tired of typing in dates) for date, but I used the Now function, which also gives me the time.
Now I have over 3000 subrecords of the main ones. I now need to queries transaction for that specific date, but it also retrieves the time.
I tried to go back and change the NOW to DATE in VB, but the code does not run.
How do I change all records that have date and time (using NOW function) and only click that command button to show only the date (mm/dd/yyyy)?
Thanks in advance.
Is there any function that can convert either a True, False, "Yes", "No" or Null value.... to a yes/no (check box) data type?
What I want to do is create a make table query. I would like to add a checkbox field from the query. So if there was a function that could convert a data type to a Yes/No checkbox, this would be very helpful...
To give you and idea of what I'm talking about...and how I want to use this... If I am making a table from a query and I want to add a number field, but leave it empty for the time being, what I can do is make a new field in the query maybe call it "Number1". What I would do is Add this as a field in my make table query:
Number1: Cint("")
This will add a blank field that is formatted as a number field when I run the make table query.
So using the same logic, I'd like to make a blank (or unchecked) Yes/No check box field when I run my make table query.
Anyway...that's what I'm hoping for.... I hope someone can help!
Gary
I'm having a problem getting a form to open. I'm using access 97 on on XP machine and whan I open my database I get Function isn't available in expressions in query expression '[Case Types]![theName]&"-"&Format([SourceDocs]![CaseNumber]&"","00")&"-"&Format([SourceDocs]![CaseYear]&"","00")'.
This database works fine on a Windows 2000 machine running Access 97 but none of the XP machines will open it.
Any suggestions?
Thanks,
Rick
I have a database in which I am designing a report which includes a name/address block. I am surprised to find that the report does not recognise the Chr() function in the text box control source string "=[Organisation] & Chr(13) & Chr(10) & [Address]..."; designed to insert a CRLF between the contents of the named fields.
I know the syntax is correct because I've checked by using the expression builder to replicate the code. (Expression builder includes the Chr() function, as expected.)
The interesting thing is that the function is recognised in other databases used on the same machine, both those created remotely and locally.
Anyone know what could be going wrong?
Stuart
I am using a crosstab query to gather info from a table. I am using the Format [DateAppt], "mmm". The query ran successfully before but I now get the error "Function is not abailable in expression in query expression 'Format ([DateAppt], "mmm")'. This is used for column headings in a report that gives results by month.
I am using Microsft Office 2000 Premium with XP Professional operating system.
I used the original disk to repair Office thinking that the function was missing from the function library but to no avail.
Does anyone know how to restore this function?
Thank you,
Jack
Just wonder if anyone could give me some help on an IIF function used in my query:
I have a table which has numerous “W” codes, the charging for an order does not occur if it has a W12 or a W14 code and has no “[Authorising Name]” BUT does however charge if the weight of the order is over 100kg
IIf([WCode]="W12" Or [WCode]="W14",IIf([Authorising Name]="",0,[charge], IFF ([weight]>99,[charge],0)))
I can get this working for a single W code but not for the 2 OR I can get it working for both Codes but then the weight side doesn’t.
I have tried all combinations of <> = < etc etc but still no luck., I think I need another OR but no matter where I put it I hit problems either with the function or the result.
Another small issue, my [authorising Name] field can appear to be empty, but in the field are spaces what is the best way for ensuring I capture both empty and theses space records ?
Thanks in advance
Got on more question (I hope) about functions.
I have a table that contains a year (school year) and an End Status Code, I need to flag students that have the end status code of 45 in school year 2005.
When I use the below code the column popluates with "#ERROR".
IIf([ST010]![SCH_YR]="2005" And [ST010]![END_STAT]="45","Y","N")
Any Ideas?
Again thanks for the help..
--pete
I have a question:
SELECT DateSerial(2000+mid([enter],3,2),Mid([enter],1,2),1) AS datevalue;
it works in 2000 windows,
but it doesn't works in XP windows.
Why?
The error message the function mid has a problem.
How can I fix the problem in XP widows.
Thanks.
hi there
am new to access and i want to know how to use the function now in a query
like i want to know all the birthdays in the date base by using the function now.. firstly i did date part for the day and the month,, then in the criteria box of the day i inserted the function now () but dont know what to put.. any1 can tell me what to do pleaseeeeeeeeee ???????/:) :) :)
hi there
am new to access and i want to know how to use the function now in a query
like i want to know all the birthdays in the date base by using the function now.. firstly i did date part for the day and the month,, then in the criteria box of the day i inserted the function now () but dont know what to put.. any1 can tell me what to do pleaseeeeeeeeee ???????/:) :) :)