Criteria in a calculated query field

Hi,

I have the below formula in the query field and run fine.

Expiration: (([shelf Life (Months)]/12*365)+[Date]) 

But when I write the criteria below and not not get the data I want 

Between [enter start date] And [enter end date]

Example: when I run just the formula I get for example dates in April 2010. 
But when I use the criteria and ask for data between 04/01/10 and 04/30/10 I 
get blank results.

What am I doing wrong?

Thank you for your help.

Rubem
0
Utf
4/19/2010 12:09:01 AM
access 16762 articles. 3 followers. Follow

6 Replies
602 Views

Similar Articles

[PageSpeed] 55

On Sun, 18 Apr 2010 17:09:01 -0700, Rubem <Rubem@discussions.microsoft.com>
wrote:

>Hi,
>
>I have the below formula in the query field and run fine.
>
>Expiration: (([shelf Life (Months)]/12*365)+[Date]) 
>
>But when I write the criteria below and not not get the data I want 
>
>Between [enter start date] And [enter end date]
>
>Example: when I run just the formula I get for example dates in April 2010. 
>But when I use the criteria and ask for data between 04/01/10 and 04/30/10 I 
>get blank results.
>
>What am I doing wrong?
>
>Thank you for your help.
>
>Rubem

One thing is using Date as a fieldname. It's a reserved word for the builtin
Date() function, which returns today's date from the system clock. It may be
looking at Date() - 4/18/2010 - instead of the table field. Another is putting
parentheses and blanks in fieldnames; I'd be inclined to use ShelfLife as the
fieldname, and put explanatory information such as (Months) on a form or in
the Caption property of the fieldname.

You may also want to reconsider your algorithm for Expiration. Another option
would be to use the DateAdd() function:

Expiration: DateAdd("m", [shelf life (Months)], [Date])

-- 

             John W. Vinson [MVP]
0
John
4/19/2010 1:06:44 AM
"Rubem" <Rubem@discussions.microsoft.com> wrote in message 
news:A7DE5E75-61AF-4D79-B379-3D5902C25913@microsoft.com...
> Hi,
>
> I have the below formula in the query field and run fine.
>
> Expiration: (([shelf Life (Months)]/12*365)+[Date])
>
> But when I write the criteria below and not not get the data I want
>
> Between [enter start date] And [enter end date]
>
> Example: when I run just the formula I get for example dates in April 
> 2010.
> But when I use the criteria and ask for data between 04/01/10 and 04/30/10 
> I
> get blank results.
>
> What am I doing wrong?
>
> Thank you for your help.
>
> Rubem 

0
Ernie
4/20/2010 1:54:30 PM
John,

Thank you for your suggestions.  I changed them as you suggested.

I am using the formula you posted which seems to be easier.  The problem is 
that I still can use the criteria.

Using my formula if i use, for example "40229" and 05/01/10 I can get an 
answer.  But if I use what you recommended I get the following message "Data 
type mismatch in query expression".

Rubem

"John W. Vinson" wrote:

> On Sun, 18 Apr 2010 17:09:01 -0700, Rubem <Rubem@discussions.microsoft.com>
> wrote:
> 
> >Hi,
> >
> >I have the below formula in the query field and run fine.
> >
> >Expiration: (([shelf Life (Months)]/12*365)+[Date]) 
> >
> >But when I write the criteria below and not not get the data I want 
> >
> >Between [enter start date] And [enter end date]
> >
> >Example: when I run just the formula I get for example dates in April 2010. 
> >But when I use the criteria and ask for data between 04/01/10 and 04/30/10 I 
> >get blank results.
> >
> >What am I doing wrong?
> >
> >Thank you for your help.
> >
> >Rubem
> 
> One thing is using Date as a fieldname. It's a reserved word for the builtin
> Date() function, which returns today's date from the system clock. It may be
> looking at Date() - 4/18/2010 - instead of the table field. Another is putting
> parentheses and blanks in fieldnames; I'd be inclined to use ShelfLife as the
> fieldname, and put explanatory information such as (Months) on a form or in
> the Caption property of the fieldname.
> 
> You may also want to reconsider your algorithm for Expiration. Another option
> would be to use the DateAdd() function:
> 
> Expiration: DateAdd("m", [shelf life (Months)], [Date])
> 
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
4/21/2010 12:41:01 AM
On Tue, 20 Apr 2010 17:41:01 -0700, Rubem <Rubem@discussions.microsoft.com>
wrote:

>John,
>
>Thank you for your suggestions.  I changed them as you suggested.
>
>I am using the formula you posted which seems to be easier.  The problem is 
>that I still can use the criteria.
>
>Using my formula if i use, for example "40229" and 05/01/10 I can get an 
>answer.  But if I use what you recommended I get the following message "Data 
>type mismatch in query expression".

Try a criterion of #05/01/10#, or a Parameter Query with the parameter defined
as a Date/Time.
-- 

             John W. Vinson [MVP]
0
John
4/21/2010 4:02:34 AM
On Tue, 20 Apr 2010 17:41:01 -0700, Rubem <Rubem@discussions.microsoft.com>
wrote:

>Using my formula if i use, for example "40229" and 05/01/10 I can get an 
>answer.  But if I use what you recommended I get the following message "Data 
>type mismatch in query expression".

Please post the complete actual SQL and indicate the datatype sof the fields.
-- 

             John W. Vinson [MVP]
0
John
4/21/2010 4:58:40 AM
You might try declaring the parameters or using

Between CDate([enter start date]) And CDate([enter end date])

or
Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2
Repeat for the second parameter.

IN SQL View you would see a line added to the beginning of the query.
Parameters [enter start date] DateTime, [enter end date] DateTime;
SELECT ...
FROM ...

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

John W. Vinson wrote:
> On Tue, 20 Apr 2010 17:41:01 -0700, Rubem <Rubem@discussions.microsoft.com>
> wrote:
> 
>> Using my formula if i use, for example "40229" and 05/01/10 I can get an 
>> answer.  But if I use what you recommended I get the following message "Data 
>> type mismatch in query expression".
> 
> Please post the complete actual SQL and indicate the datatype sof the fields.
0
John
4/21/2010 1:09:39 PM
Reply:

Similar Artilces:

If Statement and Multiple Criteria
I am using a formula to bring back some text about some data. I am using the following formula: =IF(N12>M12,"is larger for girls","is larger for boys"), so that if the data in cell N12 is greater than the data in cell M12 then it says "is larger for girls" and vice versa. This works fine, apart from if negative numbers are in the cells then it brings back the value closest to zero - rather than the largest negative number. Does anyone know how to get round this. In addition - I would ideally like to alter the formula so that it says that if the two...

Help creating a script in SQL or Calculated field in Crystal
How do I take this script and manipulate it to give me one long string with static text as well as SQL data in specific positions within the string. Example result: Positions / Data: 1-3 / 173 (Static text) 4-6 / spaces 7-10 / "X_UPR30300"."YEAR1", (has to show up as 2007. Showing up as 2,007) 11 / 4 (Static text) 12-22 / "UPR00100"."SOCSCNUM" 23-57 / "UPR00100"."LASTNAME" 58-92 / "UPR00100"."FRSTNAME" 93 / "UPR00100"."MIDLNAME" 94-120 / "UPR00102"."ADDRESS1" 121-148 / &q...

macro for comparing fields in two work books
Hello, I have two excel spread sheets (say A1 & A2). A1- is the master sprea sheet and A2 is smaller spread sheet with very few details. Suppose Column B in A2 has 100 partnumbers and Column C in A1 has th superset of partumbers (1000) and corresponding details for each par number, I need to: 1. Check if all the 100 part numbers in A2 has a corresponding match i A1 2. Extract the info for the matched partnumbers from A1 and list in seprate sheet. 3. Even if there in no match A1, let's say for 40 of them, it shoul still list those parts in the new sheet. Can anybody help me with t...

Calculating totals by month from whole date
I'm trying to figure out the proper calculation to pull totals by dat in Excel. Here's my problem: I have a workbook with 2 worksheets; one is the main data, the other i the statistics from the main data. Within the main data, there is column titled "date paid" and another column titled "total paid" (ther are a bunch more columns, but they don't matter for this problem). Th "date paid" column will contain dates such as 1/1/04, 5/15/04 etc. O the stats page, I have columns titled for each month of the year an would like each to include how much was pa...

Does Multi-Field Index Work For Date/Time Values Only
I created a MS-Access DB table with the following 3 columns: ColID - PRIMARY KEY Col1 - Number Col2 - Date/Time Next I created a multi-field index using Col1 & Col2. I entered the following row in the 1st row: 1 5/10/2007 Next when I tried to add the above row again, as expected, I wasn't allowed to enter the same row. Next I entered the following 2 rows: 1 5/11/2007 2 5/10/2007 Both the rows were accepted. After this I deleted the 3 records, went back to the design view & changed the data type of Col2 from Date/Time to Number keeping the multi-field index...

partially turning off auto calculate?
Any way to NOT auto caluculate 1 particular formula, while leaving the rest of excel to auto calculate? In particular, I have an SQL query I would prefer to run manually, while not interferring with the rest of my workbook(s) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Calculation is an all or nothing setting. You can't prevent calculation of specific ranges. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com ...

Vlookup based on multiple criteria
I have 2 workbooks: Workbook one: Account number, name, January to September + total (columns A9 to L240) Workbook two: I need to populate with data from Workbook one Column A has the account numbers all accounts that contains department 71(xxxxx-71-xx) must go to location 1 (Jan - column B, Feb =96 column E etc) and all others goes to location 4 (Jan =96 column C, Feb =96 column F etc) Is there a basic a VLOOKUP or other function that can automatic the tasks? Thanks in advance. Workbook one Account Number Jan-10 Feb-10 Mar-10 41001-71-01 6,000.00 - 1,000.00 41001-71...

dynamic field content of parameter combobox
Greetings, I have to filter my Crystal 9.0 report with a parameter combobox field in Microsoft CRM. The data for this parameter field has to be loaded out of a database field dynamically. I have found the possibility to load database field values in a parameter field but this will not be dynamically. If there is a change in the database content the parameter field content will not be updated. I hope to create a combobox control is no problem. Is there a way to turn this into reality? Thanks for your help Thomas Ott (ITVT germany) ...

counting using multiple criteria
Hi, I have a problem for which I can't seem to find a working solution. On the one hand I have a vertical table with employee names, the number of years they have been with the company, and all this sorted by the employee's age (in years). This table will be updated from time to time, and as such it doesn't have a fixed length. On the other hand I have a table which shows the number of employees in a particular age category (-20, 21-25, 26-30, etc) horizontally, and the number of years they have been with the company (again in groups: 0-5 yrs, 5-10 yrs, etc) vertically. ...

Deleting a single character in a text field
In one of my tables i have a field that has a text values like '123.23123' i would like , to delete the full stop. How can i do this. I cant do it in excel as the number of records that i have is over 200000. hi, ma1000 wrote: > In one of my tables i have a field that has a text values like '123.23123' i > would like , to delete the full stop. How can i do this. I cant do it in > excel as the number of records that i have is over 200000. Create a update query, use Replace([yourField], ".", "") as new value. mfG --> stefan <-- ...

Validating two fields
I am trying to create a database in which some of the fields should be mandatory only under certain conditions. For example: In my "Type of Injury" column, I want the user to be required to enter a value in this column, only if the value in the "Type of Document" column is "Nursing Report". Is this possible? I am only just beginning to teach myself Access, so please bear with me. Thank you. Put a validation rule on the *table* (not field.) 2nd example under the the Validation Rules for Tables section here: http://allenbrowne.com/Validati...

Adding Fields and Tables to MSCRM
Is there a link or some reference on the ability to add fields to existing tables in the CRM database and/or adding complete tables to the CRM database?? I am asking this from the point of view relating to other existing CRM products (saleslogix, frontrange - shh). From what I have played with so far from playing with my SBS2k/MSCRM install is I my need to have a MS developer skillset. Robb, Using the schema manager in the deployment Manager MMC snap-in, you can add fields to the existing tables. However, there is no way to add new tables. Matt "Robb D" <robbd@eproductscons...

Calling VBA subroutine from a query?
Can I call a VBA subroutine from within an Access query? I wrote some English to Metric conversion routines in the Access VBA code and would like to run a query on the data that will return coverted values. I need to be able to execute this from outside the database (run the query from another program). Yes. The function must be a Public Function in a standard module. You use a calculated control to return the results of the query. ConvertedValue: MyFunction([SomeField]) -- Dave Hargis, Microsoft Access MVP "DavidY" wrote: > Can I call a VBA subroutine from within an ...

Button on Form to Run Report based on Query with Parameters
Hi Guys. I am busy working on a stock control database (and job control). When I have a Form Open, it shows several fields, including JobID (Which is the PK for that job) (frm_Invoicing) I also have a Query that when Run, it prompts for the JobIDNo. (qry_CustJobsInvStock) I have a report based on that query. (rpt_CustJobsInvStock) I have added a button onto the form (onclick -> DoCmd.OpenReport) to open and automatically print the report, But I just cannot figure out how to get it to get my JobID from the form, and to Automatically add it as a Parameter for the que...

Modify Calculation
When a user enters data into a cell, he has to tab out of it before he can push the "calculate" button. Is there any way to change this so that the calculation can be performed while the cell is still selected? I appreciate any help. Change Tools>Options>Edit>Move selection after enter, and use Enter to complete entry rather than Tab. This prevents the selection from moving to another cell. Note that calculation can never be done while the cell is being edited. "Sisilla" <sookdeoss@bowater.com> wrote in message news:0b4e01c34b9b$d59ebcf0$a301280a@p...

I'm trying to display the next month in a text field
Hi there, I've gone through all the forms and can't seem to get the code straight for displaying the next month on a text field on my form. I just want the entire month name and nothing else and I have been struggling with: DateSerial(Year(Date()), Month(Date()) + 1, 1) but its showing the day and year too and thats not what I need. Thanks in advance! GM gmazza wrote: >Hi there, >I've gone through all the forms and can't seem to get the code straight for >displaying the next month on a text field on my form. >I just want the entire month name and nothing else...

Mail (outlook 2002), 'To' field, general question
Why do some names of some messages in the 'To' Field appear with single quotes in my Sent Box? I'm using Outlook 2002. I'm sure there's an easy explanation but it stumps me. ...

Changing the names of fields in tables after creating other object
I just leaned about the naming conventions after I have created my tables, reports and several queries. Can I change the name of fields in my tables (to remove the spaces and give them unique names ie not just last name but childlastname) without destroying the work I have done in queries, reports, and forms. Mary -- Positive Direction for Youth & Families, Inc. (www.pdfyinc.com) Possibly. If you are using a newer version of Access, say 2003 or 2007 AND you have Name Autocorrect, and all it's options, enabled, it MIGHT work. I found it somewhat buggy. Before you...

extracting numbers from a text field and applying formula
here's my question... i have a cell that has a text field of this type: '9-19' (a-b) I want to create a formula that will subtract 19-9 (b-a) and return the result as a number// can anyone help?! is this even doable? I'd really appreciate it! PS also, i'm very fairly new at excel so a detailed response with th formula would really help! ALe ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Try: =MID(A1,FIND("-",A1)+1,99)-LEFT(A1,FIND("...

Conditional formatting formula with multiple criteria
Hi all, Have been puzzling over this for a while now, and can't get it right! Using conditional formatting, I want to: If any of the cells in Column B are blank, and the cells in Column C are blank, and the date in Column D is less than todays date - 3 days, turn the cell bold and red Can someone please help? I have tried all different variations and just can't get it! Thanks, Kirstie Kirstie Your questions sound like you wish to pick up whether there is a blank ANYWHERE in column B AND (not or) ALL cells in column C are blank AND that the date in a single cell in colum...

Calculate data on 2 different worksheets
I have a very large workbook and for efficiency reasons I have calculation set to manual. I need to calculate 2 different worksheets, but because they refer to each other, I have to calculate them a couple of times to make all of the formulas update correctly. I've tried naming a single range: Range3 = range1,range2 then I use Range("Range3").calculate but the result if only one iteration thru the formulas and the final result still needs one more calculation. Short of putting all the data on one sheet, is there anyway I can insure 2 separate worksheets are completel...

Problems with add new field
Hi I want add new field to the opportunityproduct object but I get the follow Error in the Eventlog. dmLog: Failed to grant access to the regenerated view OpportunityProduct after inserting new attribute 'attributename' Source: DMSAPIN I'm login as local administrator with crmroll 'systemadmin'. Note, in other object's I can add new fields. Why I get these Error and how can I solve it? thanks Harald ...

Do all fields have "onchange" event script blocks??
Hi, I was wondering if there are onchange event handlers for all fields in CRM? I need to automatically send an email from CRM when the "statuscode" of an Incident changes to "Closed", which is a value that I added to the schema. I have seen onchange script blocks in other objects like the Contact object for the "accountrolecode" field, but there doesn't seem to be one for the "statuscode" field in the incident object? Have I got this wrong, or do not all drop down lists have "onchange" event handling script blocks?? Thanks in advance,...

Matching Zip coded in a Query
I have query that joins two tables using the Zip Codes (only show the records where the Zip codes match). The problem is that in one table, the listing of zip codes, it is 5 digit zip codes. In the main table some of the zip codes are 5+4 zip codes. My current query leave out the 5+4 Zip codes. I want all the records as long as the 5 digit zip codes matches. I quess I could delete the - and the last 4 digits on the main table, but it would be nice to retain the whole zip codes. Is there a way to do this? Thanks!!!!! Use Left(MainTableZipCodeField,5) to compare to the original ...

mobilephone field
Hi, Anybody have idea why mobilephone field for lead object has max length 20, but same field for contact object has max length 50? Is it a bug? Renatas. without checking it sounds more like a simple design flaw ie someone forgot to make both fields the same length -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Community/CRMFaqLanding.aspx "Renatas" <Renatas@discussions.microsoft.com> wrote in message news:05EFAFF4-E5AA-4E83-B733-B3AD61BC10B4@microsoft.com... > Hi, > > Anybody have idea why mobilephone field for lead object...