calculated Date in query

[Johnmad86]

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]

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]

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]

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]

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]

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]

Simply use,

CallbackOn: DateAdd("m", Val([Candidates].[CallbackFrequency]), [Candidates].[LastContacted])

 

[Johnmad86]

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]

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