Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-17-2014, 06:17 PM   #16
sspreyer
Newly Registered User
 
Join Date: Nov 2013
Location: England Stanford-le-hope ,Essex
Posts: 243
Thanks: 66
Thanked 0 Times in 0 Posts
sspreyer is on a distinguished road
Re: how to filter calculated field in query using a criteria

so this needs to be added to the field criteria, as when I do it makes a new column

then when I run it shows no records but not asking for find last

cheer

shane

sspreyer is offline   Reply With Quote
Old 07-17-2014, 06:18 PM   #17
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: how to filter calculated field in query using a criteria

Then there's obviously no matching record within that range.
vbaInet is offline   Reply With Quote
Old 07-17-2014, 06:34 PM   #18
sspreyer
Newly Registered User
 
Join Date: Nov 2013
Location: England Stanford-le-hope ,Essex
Posts: 243
Thanks: 66
Thanked 0 Times in 0 Posts
sspreyer is on a distinguished road
Re: how to filter calculated field in query using a criteria

right

I made record in the criteria but still not showing I have attach strip down db with only my tables and query to show

I get what your say now just down understand why its now working

Test For Shane 11.zip

thanks

shane

sspreyer is offline   Reply With Quote
Old 07-17-2014, 06:56 PM   #19
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: how to filter calculated field in query using a criteria

Looks like I'm going to re-iterate and rephrase-->
Quote:
Originally Posted by vbaInet View Post
... build another query based on your original query and put the BETWEEN in the new query.
vbaInet is offline   Reply With Quote
Old 07-17-2014, 07:16 PM   #20
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: how to filter calculated field in query using a criteria

Attached !
Attached Files
File Type: accdb Test For Shane 11.accdb (796.0 KB, 26 views)
vbaInet is offline   Reply With Quote
The Following User Says Thank You to vbaInet For This Useful Post:
sspreyer (07-18-2014)
Old 07-17-2014, 07:20 PM   #21
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,383
Thanks: 21
Thanked 898 Times in 882 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: how to filter calculated field in query using a criteria

Tip toeing in...

Umm, when I look at the query I see the very last field "Next Service". That is never going to work, you can't run Between against a Text field. What are you trying to do?
__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is offline   Reply With Quote
Old 07-17-2014, 07:26 PM   #22
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,383
Thanks: 21
Thanked 898 Times in 882 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: how to filter calculated field in query using a criteria

Nevermind, I fixed it...

Code:
 
SELECT MaskTbl.MaskID, MaskTbl.MaskNotes, DMax("[MaskServiceDate]","[MaskServiceTbl]","([ImpMaskID]= " & [MaskID] & ")") AS FindLast, MaskTbl.ExMask, MaskTbl.MaskImpEmployeeID, DateAdd("m",[MaskServiceMonths],DMax("[MaskServiceDate]","[MaskServiceTbl]","([ImpMaskID]= " & [MaskID] & ")")) AS NextService, [NextService]-Date() AS tillDays, MaskTbl.MaskType, MaskTbl.MaskSerialNumber, MaskTbl.MaskServiceMonths
FROM MaskTbl
WHERE (((MaskTbl.ExMask)=No) AND ((DateAdd("m",[MaskServiceMonths],DMax("[MaskServiceDate]","[MaskServiceTbl]","([ImpMaskID]= " & [MaskID] & ")"))) Between Date() And Date()+30))
ORDER BY DMax("[MaskServiceDate]","[MaskServiceTbl]","([ImpMaskID]= " & [MaskID] & ")");
I replaced the [FindLast] in the NextService with the actual function because you can't run criteria when you include a function within a function. You have to actually perform the calculation. Hope I made sense there...

__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is offline   Reply With Quote
The Following User Says Thank You to GinaWhipp For This Useful Post:
sspreyer (07-18-2014)
Old 07-18-2014, 01:38 PM   #23
sspreyer
Newly Registered User
 
Join Date: Nov 2013
Location: England Stanford-le-hope ,Essex
Posts: 243
Thanks: 66
Thanked 0 Times in 0 Posts
sspreyer is on a distinguished road
Re: how to filter calculated field in query using a criteria

thanks Gina !!!! works great!!
thanks VbaInt!! For your Help

cheers

shane
sspreyer is offline   Reply With Quote
Old 07-18-2014, 01:46 PM   #24
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,383
Thanks: 21
Thanked 898 Times in 882 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: how to filter calculated field in query using a criteria

You're welcome!

__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
setting criteria for calculated query field from vba Ruzz2k Modules & VBA 4 05-16-2012 12:51 PM
filter on calculated field Karen999 Queries 2 05-12-2009 07:06 PM
calculated field in a query which needs a filter grapevine Queries 1 02-23-2009 04:02 AM
Criteria For Calculated Field In Query james_IT Queries 7 12-09-2006 07:32 PM
Whish is faster? - Calculated field in QUERY or Calculated field in REPORT? Cosmos75 Reports 3 03-18-2003 09:26 AM




All times are GMT -8. The time now is 11:50 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World