Formula In A Query - 138072

Hi
I am trying to create a formula that checks to see if a date in a record is 
from the same month as the current date and if so return a value
The formula is not falling over but always returns the OR value.
Could someone take a look and point out my error

MonthNo: IIf([Date SubmittedtoLab]=Month(Date()),MonthName(Month([Date 
SubmittedtoLab])),5)



Thanks

Richard
0
Utf
1/31/2008 11:30:06 AM
access 16762 articles. 3 followers. Follow

1 Replies
725 Views

Similar Articles

[PageSpeed] 19

Not sure what you're trying to do with this.  I you just want the month 
number you could simply use
DatePart("m",[Date SubmittedtoLab]) 

This is (I think??) what your 1st post was looking for - but I don't 
understand

MonthNo: IIf([TableName]![Date SubmittedtoLab] Between 
DateSerial(Year(Date()),Month(Date()),1) And 
DateSerial(Year(Date()),Month(Date())+1,0),Format(Date(),"mmmm"))

To get the month number you could use this - but the simple DatePart is better

OtherExample: IIf([TableName]![Date SubmittedtoLab] Between 
DateSerial(Year(Date()),Month(Date()),1) And 
DateSerial(Year(Date()),Month(Date())+1,0),DatePart("m",Date()),DatePart("m",[Date SubmittedtoLab]))


You could change the TableName and just paste this to see what I mean

SELECT IIf([TableName]![Date SubmittedtoLab] Between 
DateSerial(Year(Date()),Month(Date()),1) And 
DateSerial(Year(Date()),Month(Date())+1,0),Format(Date(),"mmmm")) AS MonthNo, 
IIf([TableName]![Date SubmittedtoLab] Between 
DateSerial(Year(Date()),Month(Date()),1) And 
DateSerial(Year(Date()),Month(Date())+1,0),DatePart("m",Date()),DatePart("m",[Date SubmittedtoLab])) AS OtherExample
FROM TableName;


Post back if this is not what you are after.




-- 
Wayne
Manchester, England.



"richard" wrote:

> Hi
> I am trying to create a formula that checks to see if a date in a record is 
> from the same month as the current date and if so return a value
> The formula is not falling over but always returns the OR value.
> Could someone take a look and point out my error
> 
> MonthNo: IIf([Date SubmittedtoLab]=Month(Date()),MonthName(Month([Date 
> SubmittedtoLab])),5)
> 
> 
> 
> Thanks
> 
> Richard
0
Utf
1/31/2008 12:20:01 PM
Reply:

Similar Artilces:

Salary Tax Formula,
Dears, If any one can help me to write a formula for the following salary tax: From $1 to $5,000 tax should be 0, from $5,001 to $12,500 Tax should be charged 2%. From $12,501 to $100,000 tax should be 10%. From more than 100,000 the tax should be 20%. Thanks in Advance, Regards, KN =LOOKUP(A2,{0,5001.12501,100000},{0.2,10,12}) -- Steve "NOORZAD" <NOORZAD@discussions.microsoft.com> wrote in message news:A8C83829-6B89-40FF-9D7E-6A7A3DC7A15F@microsoft.com... > Dears, > > If any one can help me to write a formula for the following salary tax: > >...

Apostrophes in formula bar
I have been given a worksheet with hunderds of rows and a couple dozen columns. All the data entry has been preceeded by an apostrophe in the formula input bar. I tried find/replace, but it does not see the apostrophes. Could someone please tell me how to do a global erasure of these apostrophes throughout the entire worksheet? Thanks so much for you help Joanne Joanne, Select all your cells, and run this macro Sub PutValues() Set myRange = Intersect(Selection, ActiveSheet.UsedRange) myRange.Value = myRange.Value End Sub HTH, Bernie MS Excel MVP "Joanne" <RobJo65@comcast.net...

Importing Excel Formulas in Publisher
is it possible to import a working excel formula into publisher and then make the calculations in publisher cjw064 <cjw064@discussions.microsoft.com> was very recently heard to utter: > is it possible to import a working excel formula into publisher and > then make the calculations in publisher No. -- Ed Bennett - MVP Microsoft Publisher Hi Ed Thx for the reply regarding formulas, could you please advise me how i would do this as i imported a formula in from excel and it wouldnt perform the calculations "Ed Bennett" wrote: > cjw064 <cjw064@discussions....

query question 02-27-08
On a query field lets say for an item # field, in the criteria can I put like "123456" or "123457" etc for as many items as I want, either 1 or 100? What would be the best way to do this if I have a lot of item #'s I want the query only to look at? Thanks in advance. Ryan With a LOTS you should use a 'criteria table'. Add this table in your query and use the field as criteria. If you might use the same items many times but not all every time then add a Yes/No as second field for 'this time' and select as needed. Then add the field to the query wi...

Why formula doesn't change on column insertion?
Hello: I know I will feel stupid when I hear the answer, but... If I have a formula ( =average(a1:a5) ), and I insert a new column, the formula, being relative, changes to ( =average(a1:a6) ). This works fine in a test. BUT I have a working spreadsheet in which inserting a column does *not* change the relative formula. Why? I can't see anything different in my working spreadsheet... -- Fred Boer Never mind - resolved. -- Fred Boer "Fred Boer" <fredboer1@NOyahooSPAM.com> wrote in message news:uin$VfrDIHA.5160@TK2MSFTNGP05.phx.gbl... > Hello: > >...

Auto-update column formula
I have a simple formula as follows A B C 1 100 200 2 3 4 5 (4*100)+(5*200) I have a macro that can insert any number of desired columns but i need the formula in column C to update and include the new inserted columns. I would then need to paste this all the way down column C (but only in certain cells). Why not use the formula: =SUMPRODUCT(A$1:B$1,A2:B2) Then, if you insert columns between A and B, the formula will automatically expand? -- Best Regards, Luke M *Remember to click "yes" if this post helpe...

Create Modeless Dialog Boxes Query?
Hi Experts I have an SDI explorer type application with a tree view and a list view. In the listview i show the thumbnails of all the bmp files in a specific folder. Now when the user double clicks on any thumbnail i want to dynamically create a modeless dialog box. Now is it possible to specify the width & height of the modeless dialog box at the time of creation, say i want the dialog box to be of the size (400,300). If this is possible how can this b dun? Waiting for comments Regards mathu mathu, See below "Snav" <davinder_76@rediffmail.com> skrev i meddelandet ...

Not plotting blank cells with formulas until number appears
If a formula is in the cell but is blank or equals "0", how do you get a chart not to plot until a number appears? You can use an IF formula with NA(). For example: =IF(OR(B2="",B2=0),NA(),B2-A2) The chart will interpolate over the #N/A errors. To hide these errors, you can use conditional formatting. http://www.contextures.com/xlCondFormat03.html#Errors Excel Charts and Formulas wrote: > If a formula is in the cell but is blank or equals "0", how do you get a > chart not to plot until a number appears? -- Debra Dalgleish Excel FAQ, ...

formula help #24
I am trying to create a formula where it takes the result of one formula and adds .60 if the answer to a question is yes but adds zero if it is no. The first part of my formula is as follows: =10+(IF(F44>1,(F44-1)*8.5)) It works fine. I just cannot get the second part to work with the first part. Any help would be appreciated. =IF(M44="yes",(10+(IF(F44>1,(F44-1)*8.5)))+0.6,10+(IF(F44>1,(F44-1)*8.5))) replace M44 with the cell that holds the answers, you know I assume that if the conditions in your formula isn't TRUE it will return 10? -- Regards, Peo Sjoblom ...

formula for cash on cash payback
how do you create a formula for cash on cash payback in months? I think you will need to describe what you mean. Are you talking about "return on investment"? kpc help wrote: > how do you create a formula for cash on cash payback in months? ...

query error
Hi, I am running a query based on 3 queries q1 has this PurchDoc Item Rel Vendor Vendor name Order Type PO Date Material Short Text MRPCn Del Date Deliv Date PO Quantity GR Quantity QtyRed RemQty PD release Reschdate CC Created on Exc Net Order Value ID 46353228 190 F 10000019 RA LALLI NB 11/6/2006 109-0718-13-101 PASSACINGHIA AK7 6/13/2013 44 42 0 0 1/2/2010 11,396.00 14632 q2 has this PurchDoc Item Rel Vendor Vendor name Order Type PO Date Material Short Text MRPCn Del Date Deliv Date PO Quanti...

Fairly Easy Formula Question....
......the answer to which is eluding me! I am using this formula =VALUE(C1&" "&A1) to return a date (month in col A and day in col C). Can someone tell me how to modify the formula to avoid #VALUE! being returned when col C contains an empty cell. TIA Zorro What exactly do you have in C1. How is C1 formatted and what did you enter (type in) for a "month" --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm ...

How do i get the product of max. price in access2007 query?
in query i want to get product ID with the Max. or Min. price The SQL statement for a query to do that would be SELECT ProductID FROM SomeTable WHERE Price = DMax("Price","SomeTable") OR SELECT TOP 1 ProductID FROM SomeTable ORDER BY Price DESC IF you need something more specific post more details on what you are attempting to do. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County amr wrote: > in query i want to get product ID with the Max. or Min. price ...

Linking parameter query from Access to pivot table in Excel
I am trying to link an Access query that prompts for several criteria to an Excel pivot table. When I refresh the pivot table, I want it to open the database and run the query to update the data in the pivot table automatically. When I try to link to the data source, when I get to the end of the "Query Wizard", it gives me the error of "Not enough parameters. Expected 4". But it never prompts me for the parameters. Please help. Is there a setting I need to change or do I link the query differently to avoid "Microsoft Query" tool? You can't cr...

Query results where gaps exist Min & Max do not work
Hello all, I was using a min and max on the specimen id here to show the first and last specimen worked on, however; that doesn't work when a gap in the specimen id exists. Here is an example of the data: Year SampleID SpecimenID 2009 200999137 001 2009 200999137 002 2009 200999137 003 2009 200999137 004 2009 200999137 005 2009 200999137 006 2009 200999137 007 2009 200999137 008 2009 200999137 009 2009 200999137 010 2009 200999137 011 2009 200999137 012 2009 200999137 013 2009 200999137 014 2009 200999137 015 2009 200999137 016 2009 200999137 017 2009 200999137...

Advanced Find 'Set' queries
Is it possible to use advanced find to perform any type of set query? Example: have setup a m-m relationship to a contact type lookup table (tried using v4 m-m feature and older technique (xref table and m-1 relationships to contact and lookup table). The issue of course is that you can't ask a question such as 'give me all contacts that are contact type A AND contact type B since you need to somehow define the set of records to look at instead of evaluating them one by one. One approach that seems to work is to use the marketing list functionality to add all contacts with co...

Please help!!!!! How do I enter this formula???
I want to enter a count formula for a rota I'm doing for my staff using the 24 hour clock. Basically I want to be able to count the number of staff working in the rota between two selected times without having to put dates into the rota. The staff work 24 hour shifts so the count has to take account of the fact that if the rota says a staff member worked between, say, 20:00 and 8:00 that member of staff worked between 8:00 pm on one day to 8:00 am on the following day. For Monday, for instance, cells b6:b21 contain the start times for the respective members of staff and cells c6...

Age formula
Hi all, I need to compute ages (as of 6-1-04) from dates of birth on a speadsheet I'm working on. Anyone have the formula for that? Thanks in advance! Trish Trish, You can use the DATEDIF function to do this. E.g., =DATEDIF(date_of_birth,DATE(2004,6,1),"y") See www.cpearson.com/excel/datedif.htm for more details about DATEDIF. "Trish" <anonymous@discussions.microsoft.com> wrote in message news:1a54e01c44e50$7fbadf40$a501280a@phx.gbl... > Hi all, > > I need to compute ages (as of 6-1-04) from dates of birth > on a speadsheet I'm working ...

Amending a Formula
I have the following formula and it works fine. =AVERAGE(OFFSET($AH$14,COUNT($AH14:$AH5001)-1,,-14)) I want to amend this formula so that when I have O/A (Off/Average) in column V it ignores that line as if it wasn't there and omits it from the averages. Thank you On May 11, 4:03=A0pm, Blake <sfc...@gmail.com> wrote: > I have the following formula and it works fine. > =3DAVERAGE(OFFSET($AH$14,COUNT($AH14:$AH5001)-1,,-14)) I don't believe that. As written, you have a height of -14. But height must positive. Do you mean 14 instead of -14? Or did you mean "-1,-14...

querying excel spreadsheets like database
I'm making a payroll journal, it has a column for the names, and the the cheque amt, cheque no., and some other stuff. In a separate sheet I need to pull up individual information pe employee and total. Eg. Payroll Journal: Name: cheque amt: cheque no: date: jane doe 2000.00 0001 01/01/01 john doe 1500.00 0002 01/01/01 jane doe 1800.00 0003 15/01/01 Employee Details: Name: cheque amt: cheque no: date: jane doe 2000.00 0001 0...

formula to separate text
Having a cell A1 with this text: Gomez....Fernando...Cost accountant How can I get this content broken in 3 different cells as follow: A2 = Gomez A3 = Fernando A4 = Cost Accountant Your help is much appreciated. Fernando Fernando It depends on what there is to distinguish the end of one section and the beginning of the next. If it's a space, or a comma (or similar), try Data / Text to columns. Post back if it's more complex than that! Andy. "Fernando Gomez" <fgomez@golden.net> wrote in message news:uoJ7LffAEHA.3024@TK2MSFTNGP10.phx.gbl... > Having a cell A1 ...

Microsoft Query #3
I am trying to create a query in Microsoft Query that gets data from an Access Database. I click on New Query, which brings up a box for me to Choose Data Source. I select MS Access Database, then I select the the actual database. Next, a box titled Add Tables comes up, but there is nothing to select. I have done this successfully in the past, but I cannot figure out what is wrong. ...

What's wrong with this formula?
=IF(B27>0,+B27:B32-B28,"") Working with numbers only. If false I want the cell to be blank. I'm getting #### Doesn't help to widen cell format of cell is general Thanks hi try =(B27>0,sum(B27:B32)-B28,"") regards FSt1 "Ray" wrote: > =IF(B27>0,+B27:B32-B28,"") > Working with numbers only. If false I want the cell to be blank. > I'm getting #### > Doesn't help to widen cell > format of cell is general > Thanks Are you trying to add the numbers B27 to B32, omitting B28 ? If so, try th...

Find cell which returned maximum value in formula
-- De De, try this, =CELL("address",INDEX(A:A,MATCH(MAX(A:A),A:A,0))) will show the first one if there are more than one -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "De" <De@discussions.microsoft.com> wrote in message news:A17DFFDA-BE05-463C-B68D-65FE235E6098@microsoft.com... > > -- > De Thanks, Paul, but I found this hard to follow. Excel kept returning that I had disabled macros but yo...

Last transaction in a query?
I have a table with 5 fields ID PersonID EDate Amount and TransType I want a query with the last record of each person based on date and I want the results like this PersonID LastOfEDate Amount TransType if a person has tow transactions in the max date I want only one of them How can i do that? thanks You must have a "main" table with a single list of PersonID. Lets assume it is called tblPeople, PK = "ID" So, use that table to provide a unquite list of PersonID. The query can be like: select FirstName, Lastname, WorkPhone, PersonID, EDate, Amount, Tr...