how to filter calculated field in query using a criteria (1 Viewer)

sspreyer

Registered User.
Local time
Yesterday, 18:40
Joined
Nov 18, 2013
Messages
251
hi

I have query with a calculate field to finds the next service due date but I'm having a problem getting it to only show services due dates in the next 30 days

for some reason I cant add a criteria date()-30

here's what I have to calculate the next service

it takes service intervals from maskservicemonths field then find the last service date and generates the next service due date

NextService: DateAdd("m",[MaskServiceMonths],[FindLast])




any ideas

thanks in advance :)

shane
 

vbaInet

AWF VIP
Local time
Today, 01:40
Joined
Jan 22, 2010
Messages
26,374
Plus in the future, minus is in the past. Within the next 30 days is?
 

sspreyer

Registered User.
Local time
Yesterday, 18:40
Joined
Nov 18, 2013
Messages
251
hi

vbaInet nice to see you again

so yes it would be + 30 Days

thanks

shane
 

vbaInet

AWF VIP
Local time
Today, 01:40
Joined
Jan 22, 2010
Messages
26,374
Likewise!

Now you need to use BETWEEN as well.
 

sspreyer

Registered User.
Local time
Yesterday, 18:40
Joined
Nov 18, 2013
Messages
251
sorry I am totally lost where the hell would I use between it is 2am nearly lol

does it need to be in my statement above
NextService: DateAdd("m",[MaskServiceMonths],[FindLast] between ??)

thanks
shane
 

vbaInet

AWF VIP
Local time
Today, 01:40
Joined
Jan 22, 2010
Messages
26,374
We're in the same time zone Shane ;)

Perhaps you've not properly explained what you're trying to do.

1. If you want to find services due within the next 30 days then you need BETWEEN
2. If you want to create a field that calculates a Next Service date then it's your date field + 30
 

sspreyer

Registered User.
Local time
Yesterday, 18:40
Joined
Nov 18, 2013
Messages
251
you must not need a lot sleep :)

right lets start again
In my query
I find last service date from my table
using a calculated field like so
FindLast: DMax("[MaskServiceDate]","[MaskServiceTbl]","([ImpMaskID]= " & [MaskID] & ")")

then I calculated the next service date by what user inputs on my form servicemonths field
e.g 12 = 12months

NextService: DateAdd("m",[MaskServiceMonths],[FindLast])

now I would like the query to only show services due in the next 30 days or over

hope this help abit more

cheers

shane
 

vbaInet

AWF VIP
Local time
Today, 01:40
Joined
Jan 22, 2010
Messages
26,374
Right, so it would still be between. That is BETWEEN today and 30 days from today.
 

sspreyer

Registered User.
Local time
Yesterday, 18:40
Joined
Nov 18, 2013
Messages
251
right I have tried

[NextService] between date+30

with no luck

:banghead::banghead::confused:

cheers

shane
p.s will this need to be in my criteria
 

sspreyer

Registered User.
Local time
Yesterday, 18:40
Joined
Nov 18, 2013
Messages
251
so that say on that link it should be
value 1 value2
between [FindLast],[NextService]- Date() + 30

god know what I'm doing wrong here

thanks

shane
 

sspreyer

Registered User.
Local time
Yesterday, 18:40
Joined
Nov 18, 2013
Messages
251
right I now I got this

between [FindLast] And [NextService]-Date()+30

but it asking for a value for find last and next service when run

thanks

shane
 

sspreyer

Registered User.
Local time
Yesterday, 18:40
Joined
Nov 18, 2013
Messages
251
ok lol

right I now tried :banghead:

Between Date() And +30
now its only asking for find last value when I run it

thanks

shane
 

vbaInet

AWF VIP
Local time
Today, 01:40
Joined
Jan 22, 2010
Messages
26,374
I'm not sure how you interpreted that to mean Date() And + 30
Code:
NextService BETWEEN Date() And Date() + 30
If it's asking you for FindLast save the query you have now, build another one based on the query and put the BETWEEN there.
 

sspreyer

Registered User.
Local time
Yesterday, 18:40
Joined
Nov 18, 2013
Messages
251
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
 

vbaInet

AWF VIP
Local time
Today, 01:40
Joined
Jan 22, 2010
Messages
26,374
Then there's obviously no matching record within that range.
 

sspreyer

Registered User.
Local time
Yesterday, 18:40
Joined
Nov 18, 2013
Messages
251
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

View attachment Test For Shane 11.zip

thanks

shane
 

vbaInet

AWF VIP
Local time
Today, 01:40
Joined
Jan 22, 2010
Messages
26,374
Attached !
 

Attachments

  • Test For Shane 11.accdb
    796 KB · Views: 138

Users who are viewing this thread

Top Bottom