calculated Date in query (1 Viewer)

Johnmad86

Registered User.
Local time
Today, 20:36
Joined
Jul 15, 2013
Messages
36
Hi,

I've been struggling with this for a while, and even though I understand the theory, I can't get my code to work correctly.

I have 2 fields:
CallbackFrequency & Last Contacted.

I want to use these fields to populate a third field (callBackOn) so that we can have a list of candidates that need to be called from a certain date.

CallbackFrequency is added from a combobox, so all values are either 1 Month, 3 Months, 6 Months, or 12 Months.

I have tried creating a calculated field directly in the candidates table, but apparently this is not possible.

I've therefore tried to create a query using IIF statement that will calculate this value.

To get to grips with the code, I'm only dealing with 1 callbackFrequency at the moment. So far I have:

CallbackOn: IIf([Candidates].[CallbackFrequency]="1 Month", [LastContacted],)

I have not factored in the date manipulation yet, as the query does not display the LastContacted date of the record it finds, it only shows a blank cell, so I'm missing something fundamental.

Is anyone able to help me?
 

pr2-eugin

Super Moderator
Local time
Today, 20:36
Joined
Nov 30, 2011
Messages
8,494
Could you please give some example data of what you have and what is the result you would like to see? With your description it is not 100% clear TBH !
 

Johnmad86

Registered User.
Local time
Today, 20:36
Joined
Jul 15, 2013
Messages
36
Actually the above does work, the record I was finding had no "LastContacted" Date!

My next question is how to manipulate this date to add 1 month to the resultant date.

I've tried the below, but am definitely doing something wrong:

CallbackOn: IIf([Candidates].[CallbackFrequency]="1 Month",[CallbackOn]=DateAdd("m",1,([Candidates].[LastContacted])))
 

namliam

The Mailman - AWF VIP
Local time
Today, 21:36
Joined
Aug 11, 2003
Messages
11,696
Well for starters it will be easy(er) if your combobox is based on table that actually has these values as well as a PK to join it to your table.
Key-Text-Number
1-1 month-1
2-3 month-3

You can then use the number to easily work with your date.
You can use the DateAdd function to "shift" dates around including months.
Given your probable current state, without above table, you probably need something like:
CallbackOn: Dateadd("M", left([CallbackFrequency],2), [LastContacted])
 

Johnmad86

Registered User.
Local time
Today, 20:36
Joined
Jul 15, 2013
Messages
36
For example, 1 record has the following field contents:

CallbackFrequency:
1 Month

Last Contacted:
01/05/2014

I need the query to add 1 month to the 01/05/2014 date in a new field (CallBackOn), if the callback frequency is set to 1 Month.
 

Johnmad86

Registered User.
Local time
Today, 20:36
Joined
Jul 15, 2013
Messages
36
For example, 1 record has the following field contents:

CallbackFrequency:
1 Month

Last Contacted:
01/05/2014

I need the query to add 1 month to the 01/05/2014 date in a new field (CallBackOn), if the callback frequency is set to 1 Month.
 

pr2-eugin

Super Moderator
Local time
Today, 20:36
Joined
Nov 30, 2011
Messages
8,494
Simply use,
Code:
CallbackOn: DateAdd("m", Val([Candidates].[CallbackFrequency]), [Candidates].[LastContacted])
 

Johnmad86

Registered User.
Local time
Today, 20:36
Joined
Jul 15, 2013
Messages
36
Thank you so much for your swift response, and simplification of something I was making far too complex. Its easy when you know how! :)
 

namliam

The Mailman - AWF VIP
Local time
Today, 21:36
Joined
Aug 11, 2003
Messages
11,696
Well for starters it will be easy(er) if your combobox is based on table that actually has these values as well as a PK to join it to your table.
Key-Text-Number
1-1 month-1
2-3 month-3

You can then use the number to easily work with your date.
You can use the DateAdd function to "shift" dates around including months.
Given your probable current state, without above table, you probably need something like:
CallbackOn: Dateadd("M", left([CallbackFrequency],2), [LastContacted])

Strongly suggest the table solution though
 

Users who are viewing this thread

Top Bottom