Conditional Expression

[Nurse Nancy]

I have a Marketing Campaign Table.
Users a fill out a profile for each new campaign that is being booked for an
advertiser.

The campaign will have
start date
number of weeks
and then I calculate the end date.

i am going to need to calucate the individual week start dates till the
campaign is over to be used for billing, invoicing and many other queries and
reports.

Where should i be doing this, and where should i store these dates?


I thought I would calculate the week start dates based on adding 7, 14, 21,
ect to the campaign start date and diaplaying them on the maintain campaign
form (unbound) but i am not sure

So on the form, I have Campaing ID, Flight Start date, Calc End Date, Number
of Weeks,
WEEK 1 thru WEEK 6 start dates (bc 6 weeks is the max)

How do i only display the Week Number Start Date if it is less than the
Calculated End Date,, since these fields are all expressions in the same
query

If it is greater than the calculated end date,, then i want it to be null on
the form.

i am not sure i should be doing this in the underlying Query, or in the form


heres my sql
SELECT [CAMPAIGN PRODUCT TABLE].[Campaign ID], [CAMPAIGN PRODUCT
TABLE].[Customer ID], [CAMPAIGN PRODUCT TABLE].ProductName, [CAMPAIGN PRODUCT
TABLE].[Product Category ID], [CAMPAIGN PRODUCT TABLE].[Target Audience ID],
[CAMPAIGN PRODUCT TABLE].[Market Region ID], [CAMPAIGN PRODUCT TABLE].[Market
ID], [CAMPAIGN PRODUCT TABLE].[Flight Start Date], [CAMPAIGN PRODUCT
TABLE].[Flight End Date], [CAMPAIGN PRODUCT TABLE].[Number of Weeks], [Flight
Start Date]+([Number of Weeks]*7) AS [Calc End Date], PRODUCTS
TABLE].[Products ID], [CAMPAIGN PRODUCT TABLE].[Disc Markup Rate], [Flight
Start Date]+7 AS [Week 2], [Flight Start Date]+14 AS [Week 3], [Flight Start
Date]+21 AS [Week 4], [Flight Start Date]+28 AS [Week 5], [Flight Start
Date]+35 AS [Week 6]
FROM ([PRODUCT CATEGORY TABLE] INNER JOIN [CAMPAIGN PRODUCT TABLE] ON
[PRODUCT CATEGORY TABLE].[Product Category ID] = [CAMPAIGN PRODUCT
TABLE].[Product Category ID]) INNER JOIN [PRODUCTS TABLE] ON [CAMPAIGN
PRODUCT TABLE].ProductName = [PRODUCTS TABLE].ProductName
ORDER BY [CAMPAIGN PRODUCT TABLE].[Customer ID], [CAMPAIGN PRODUCT
TABLE].ProductName;

 

[Ken Snell]

In order to answer how/where to do what you want, can you tell us how you'll
use the date values? Are you going to run queries based on them? Are you
going to add them to expense/donation records? Sounds like you want a
combobox on your form that will let you choose the date from the dropdown
list?

 

[Nurse Nancy]

Ok, Here Goes,, hope this is not too confusing!
This DB is for a company that is a broker for advertisers and radio
stations. Based on the target audience and other critieria of the
Advertiser’s Campaign/Product Profile, I have a bunch of append queries
that trie to find Radio Stations that match the criteria chosen on the
campaign profile form


Tables:
-Campaign/Product Table - contains the product info being advertised and
criteria to find the most suitable Radio Stations. Also has a campaign start
date, number of weeks, Weekly Budget

-Radio Station Table – list of radio stations with formats, ranks, markets
Criteria Tables:

-Format Table – list of Radio Station formats. (Each Radio Station has 1
format, such as Rock, News, Sports, Hip Hop, Jazz, etc)

-Target Audience Table – each target audience has a multiselect lookup to the
Format Table, ie if the Product is Burger King Whopper, the Target Audiences
might be Spanish, and Teens.

Teens in the Target Audience Table might have the following formats: ‘hip
hop’, ‘rock’, Top 40.

Spanish Target Audience might have formats: Hip Hop, Hot Urban, Span CHR,
Urban, etc

-Rank Table – can be Top 5, Top 10, Top 50, Top 100, the advertiser may
only want to go with Radio stations that are ranked in the Top 10, Top 20,
Top 100 etc.

-Market Table – Each Radio Station has 1 market, ie LA, NY, Boston, etc

One query matches Target Audience from the multiselec drop down -> Format
-> Radio Station and this works fine.

Another query asks them to choose specific radio stations from a multiselect
drop down,, this works too except I will have over 5000 stations in the
actual db,, (only 75 in my test db) so i'll probably have to do something
different, if you have any suggestions...

There are a couple of other queries,, but basically they all go out and
return matching radio stations and then I have controls that add the stations
to a 'Perspective Buy table'

The sales rep then either adds more stations or deletes stations from the
prospective buy table to reach the budget of the adverstiser,,

Sales Reps then will need to send the 'prospective buys' to each station and
see if they will confirm the buy.

If a station declines,, that station gets deleted and a new station will
take it's place.

Till finally they have confirmed buys for the week.

I am using the the campaing start date, for the first week. Each campaign
can be 1 - 6 weeks

But each week I need to send prospective buys to the stations and then
record what actually posts.

However they don't want to send anything if the station confirmed for the
whole campaign.


however i do need to bill the advertiser on what posted each week and pay
the station on what was bought each week.


How do i get week 2 - week 6 into the buy table with the same stations that
confirmed for week1, and if the campaign is only 3 weeks, I don't want to
display weeks 4, 5, 6 anywhere


Right now i have the logic on the campaign/product table where based on week
start date and number of weeks,, i fill in the week2, week3, week4, week5,
week6 as unbound controls on my form.

But not sure where/how i should be doing this,, or how to replicate the data
for week2 when week1 is finalized.



Also the sales reps would rather see check boxes on the buy table form for
the whole campaign that show week1, week2, week3, week4, etc, and they just
want to check that for each station,,,

I have no idea how i would make that happen and still provide invoices weekly

I know this is super confusing,, i am probably doing a horrible job at
explaining,, but you did ask!!!!!






Some stations will confirm for the whole campaign, and some just for the 1st
week or 2 weeks, etc.


I somehow need to copy the 1st week

For Rank I check that the Radio Station Rank is less than or eq the drop
down selection from the Campaign Product Table Rank, which is a lookup field
to the
Rank Table.
This also works fine

Except if they don’t select anything in the Rank Drop Down, then no radio
--
Nancy

In order to answer how/where to do what you want, can you tell us how you'll
use the date values? Are you going to run queries based on them? Are you
going to add them to expense/donation records? Sounds like you want a
combobox on your form that will let you choose the date from the dropdown
list?
--

Ken Snell
http://www.accessmvp.com/KDSnell/

I have a Marketing Campaign Table.
Users a fill out a profile for each new campaign that is being booked for
an
advertiser.

The campaign will have
start date
number of weeks
and then I calculate the end date.

i am going to need to calucate the individual week start dates till the
campaign is over to be used for billing, invoicing and many other queries
and
reports.

Where should i be doing this, and where should i store these dates?


I thought I would calculate the week start dates based on adding 7, 14,
21,
ect to the campaign start date and diaplaying them on the maintain
campaign
form (unbound) but i am not sure

So on the form, I have Campaing ID, Flight Start date, Calc End Date,
Number
of Weeks,
WEEK 1 thru WEEK 6 start dates (bc 6 weeks is the max)

How do i only display the Week Number Start Date if it is less than the
Calculated End Date,, since these fields are all expressions in the same
query

If it is greater than the calculated end date,, then i want it to be null
on
the form.

i am not sure i should be doing this in the underlying Query, or in the
form


heres my sql
SELECT [CAMPAIGN PRODUCT TABLE].[Campaign ID], [CAMPAIGN PRODUCT
TABLE].[Customer ID], [CAMPAIGN PRODUCT TABLE].ProductName, [CAMPAIGN
PRODUCT
TABLE].[Product Category ID], [CAMPAIGN PRODUCT TABLE].[Target Audience
ID],
[CAMPAIGN PRODUCT TABLE].[Market Region ID], [CAMPAIGN PRODUCT
TABLE].[Market
ID], [CAMPAIGN PRODUCT TABLE].[Flight Start Date], [CAMPAIGN PRODUCT
TABLE].[Flight End Date], [CAMPAIGN PRODUCT TABLE].[Number of Weeks],
[Flight
Start Date]+([Number of Weeks]*7) AS [Calc End Date], PRODUCTS
TABLE].[Products ID], [CAMPAIGN PRODUCT TABLE].[Disc Markup Rate], [Flight
Start Date]+7 AS [Week 2], [Flight Start Date]+14 AS [Week 3], [Flight
Start
Date]+21 AS [Week 4], [Flight Start Date]+28 AS [Week 5], [Flight Start
Date]+35 AS [Week 6]
FROM ([PRODUCT CATEGORY TABLE] INNER JOIN [CAMPAIGN PRODUCT TABLE] ON
[PRODUCT CATEGORY TABLE].[Product Category ID] = [CAMPAIGN PRODUCT
TABLE].[Product Category ID]) INNER JOIN [PRODUCTS TABLE] ON [CAMPAIGN
PRODUCT TABLE].ProductName = [PRODUCTS TABLE].ProductName
ORDER BY [CAMPAIGN PRODUCT TABLE].[Customer ID], [CAMPAIGN PRODUCT
TABLE].ProductName;

 

[Nurse Nancy]

After thinking about this for a while, that is exactly what i need,, a combo
box with the campaign week start dates, but how do i create this for each
campaign, when the only info i have is the start date and number of weeks.

the values will be different for every campaign,

If someone can tell me how to create the combo box that would be awesome,,
and then could i use it as input paramater to a query?

--
Nancy

In order to answer how/where to do what you want, can you tell us how you'll
use the date values? Are you going to run queries based on them? Are you
going to add them to expense/donation records? Sounds like you want a
combobox on your form that will let you choose the date from the dropdown
list?
--

Ken Snell
http://www.accessmvp.com/KDSnell/

I have a Marketing Campaign Table.
Users a fill out a profile for each new campaign that is being booked for
an
advertiser.

The campaign will have
start date
number of weeks
and then I calculate the end date.

i am going to need to calucate the individual week start dates till the
campaign is over to be used for billing, invoicing and many other queries
and
reports.

Where should i be doing this, and where should i store these dates?


I thought I would calculate the week start dates based on adding 7, 14,
21,
ect to the campaign start date and diaplaying them on the maintain
campaign
form (unbound) but i am not sure

So on the form, I have Campaing ID, Flight Start date, Calc End Date,
Number
of Weeks,
WEEK 1 thru WEEK 6 start dates (bc 6 weeks is the max)

How do i only display the Week Number Start Date if it is less than the
Calculated End Date,, since these fields are all expressions in the same
query

If it is greater than the calculated end date,, then i want it to be null
on
the form.

i am not sure i should be doing this in the underlying Query, or in the
form


heres my sql
SELECT [CAMPAIGN PRODUCT TABLE].[Campaign ID], [CAMPAIGN PRODUCT
TABLE].[Customer ID], [CAMPAIGN PRODUCT TABLE].ProductName, [CAMPAIGN
PRODUCT
TABLE].[Product Category ID], [CAMPAIGN PRODUCT TABLE].[Target Audience
ID],
[CAMPAIGN PRODUCT TABLE].[Market Region ID], [CAMPAIGN PRODUCT
TABLE].[Market
ID], [CAMPAIGN PRODUCT TABLE].[Flight Start Date], [CAMPAIGN PRODUCT
TABLE].[Flight End Date], [CAMPAIGN PRODUCT TABLE].[Number of Weeks],
[Flight
Start Date]+([Number of Weeks]*7) AS [Calc End Date], PRODUCTS
TABLE].[Products ID], [CAMPAIGN PRODUCT TABLE].[Disc Markup Rate], [Flight
Start Date]+7 AS [Week 2], [Flight Start Date]+14 AS [Week 3], [Flight
Start
Date]+21 AS [Week 4], [Flight Start Date]+28 AS [Week 5], [Flight Start
Date]+35 AS [Week 6]
FROM ([PRODUCT CATEGORY TABLE] INNER JOIN [CAMPAIGN PRODUCT TABLE] ON
[PRODUCT CATEGORY TABLE].[Product Category ID] = [CAMPAIGN PRODUCT
TABLE].[Product Category ID]) INNER JOIN [PRODUCTS TABLE] ON [CAMPAIGN
PRODUCT TABLE].ProductName = [PRODUCTS TABLE].ProductName
ORDER BY [CAMPAIGN PRODUCT TABLE].[Customer ID], [CAMPAIGN PRODUCT
TABLE].ProductName;

 

[John W. Vinson]

After thinking about this for a while, that is exactly what i need,, a combo
box with the campaign week start dates, but how do i create this for each
campaign, when the only info i have is the start date and number of weeks.

the values will be different for every campaign,

If someone can tell me how to create the combo box that would be awesome,,
and then could i use it as input paramater to a query?

This can be done with the help of a small auxiliary table. I call mine Num,
with a single Long Integer field N, containing values from 1 through 10000 (or
any reasonable high number).

If you have a form frmCampaign with a textbox txtStartDate and a second
textbox txtNumWeeks, you can create a query

SELECT DateAdd("ww", [N], [Forms]![frmCampaign]![txtStartDate])
FROM Num
WHERE N < [Forms]![frmCampaign]![txtNumWeeks]
ORDER BY N;

This will have weekly date values, starting at the date in txtStartDate, and
running for txtNumWeeks weeks; and you can use it as the Rowsource of a combo
box.

 

[Nurse Nancy]

okay,,, i am excited to try this,, but not sure what i am doing wrong
I created a table called Num
I have one field called N
It has values 1,2,3,4 thru 26 just for testing

I created an unbound text box on my form [Maintain Products Form] with this
sql in the row source, and when i run it i get no results.

SELECT DateAdd("ww",[N],[Forms]![Maintain Products Form]![Flight Start
Date]) AS Expr1
FROM Num
WHERE (((Num.N)<[Forms]![Maintain Products Form]![Number of Weeks]))
ORDER BY Num.N;

--
Nancy

After thinking about this for a while, that is exactly what i need,, a combo
box with the campaign week start dates, but how do i create this for each
campaign, when the only info i have is the start date and number of weeks.

the values will be different for every campaign,

If someone can tell me how to create the combo box that would be awesome,,
and then could i use it as input paramater to a query?

This can be done with the help of a small auxiliary table. I call mine Num,
with a single Long Integer field N, containing values from 1 through 10000 (or
any reasonable high number).

If you have a form frmCampaign with a textbox txtStartDate and a second
textbox txtNumWeeks, you can create a query

SELECT DateAdd("ww", [N], [Forms]![frmCampaign]![txtStartDate])
FROM Num
WHERE N < [Forms]![frmCampaign]![txtNumWeeks]
ORDER BY N;

This will have weekly date values, starting at the date in txtStartDate, and
running for txtNumWeeks weeks; and you can use it as the Rowsource of a combo
box.

 

[Nurse Nancy]

oh boy, scratch that last post,, it worked!!!

The only thing, is its starting with the 2nd week,, I need the first week,,,
ie Flight Start Date to also be in the drop down,, is that doable?

--
Nancy

After thinking about this for a while, that is exactly what i need,, a combo
box with the campaign week start dates, but how do i create this for each
campaign, when the only info i have is the start date and number of weeks.

the values will be different for every campaign,

If someone can tell me how to create the combo box that would be awesome,,
and then could i use it as input paramater to a query?

This can be done with the help of a small auxiliary table. I call mine Num,
with a single Long Integer field N, containing values from 1 through 10000 (or
any reasonable high number).

If you have a form frmCampaign with a textbox txtStartDate and a second
textbox txtNumWeeks, you can create a query

SELECT DateAdd("ww", [N], [Forms]![frmCampaign]![txtStartDate])
FROM Num
WHERE N < [Forms]![frmCampaign]![txtNumWeeks]
ORDER BY N;

This will have weekly date values, starting at the date in txtStartDate, and
running for txtNumWeeks weeks; and you can use it as the Rowsource of a combo
box.

 

[John W. Vinson]

oh boy, scratch that last post,, it worked!!!

The only thing, is its starting with the 2nd week,, I need the first week,,,
ie Flight Start Date to also be in the drop down,, is that doable?

Put a row in Num with 0 for N. You're adding 1 week to the Flight Start Date
because the smallest record in Num is 1. If you add 0 you'll get... well, you
get it!

 

[Nurse Nancy]

that worked great!!! Thanks so much,, Except I of coure decided to get fancy,
in the combo box I wanted to show the week number as well as the
weekstartdate,,,, so i took out the 0 that i added to Num Table and tried
this instead..

Week Start Date Calc: DateAdd("ww",[N],[Forms]![Maintain Products
Form]![Flight Start Date]-7)

seems to be working but will this cause problems down the line?

Now for my next trick,,

It stays filled with the same value i selected when I move from record to
record on my form,
Ultimately I would like it to default to week 1 when I create a new record
and also when I move to a record where i have not chosen a value,,, is this
doable? If not then I need it to be nulled out.

And lastly need some advise.
I either need to pull this combo box into a bunch of queries,, approx 5,,
where the user will have to choose that same date 5 times,

or force them to fill it in for the first query and then pass that value to
the other queries until the record changes

Or leave it on the form and force them to fill it in if it is blank before
they run any of the queries. (with this option i would need to null it out
after they move to a new record)

Have i totally confused eveyone yet?

thanks
nancy

 

[John W. Vinson]

that worked great!!! Thanks so much,, Except I of coure decided to get fancy,
in the combo box I wanted to show the week number as well as the
weekstartdate,,,, so i took out the 0 that i added to Num Table and tried
this instead..

Week Start Date Calc: DateAdd("ww",[N],[Forms]![Maintain Products
Form]![Flight Start Date]-7)

seems to be working but will this cause problems down the line?

Not that I can think of... it's just arithmatic.

Now for my next trick,,

It stays filled with the same value i selected when I move from record to
record on my form,
Ultimately I would like it to default to week 1 when I create a new record
and also when I move to a record where i have not chosen a value,,, is this
doable? If not then I need it to be nulled out.

Sure. Use the form's Current event and set its value:

Private Sub Form_Current()
If Me.NewRecord Then
Me!comboboxname = [Flight Start Date]
End If
End Sub

or whatever value you want.

And lastly need some advise.
I either need to pull this combo box into a bunch of queries,, approx 5,,
where the user will have to choose that same date 5 times,

or force them to fill it in for the first query and then pass that value to
the other queries until the record changes

If the query references

=[Forms]![formname]![comboboxname]

then it will use whatever date is there...

Or leave it on the form and force them to fill it in if it is blank before
they run any of the queries. (with this option i would need to null it out
after they move to a new record)

Well... you should NOT have the user viewing query datasheets. Is that what
you mean by "run"? Or are these Append or Update queries which get executed?
If so, check to see if the combo box is NULL before the qry.Execute line, and
prompt the user rather than running the query. Perhaps you could post your
current code.

Have i totally confused eveyone yet?

yes... <g>

 

[Nurse Nancy]

John, thanks for your help again

I tried your code and it works for adding a new record, it defaults to
[Flight Start DAte] but I am still having problems with navigating from
record to record on my form. Once i fill in the date in the WeekStartCB on a
record it stays populated in the combo box when i move to the next record.

Can you help with that,, reason being when i check for nulls it is not null.

And the users are not viewing queries,, the queries are inbedded in the
forms that they are viewing and yes, i am passing the value from the combo
box to all of the queries from my form.

Here is my code




Private Sub Command194_Click()
On Error GoTo Err_Command194_Click

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Weekly Buy Table Form" ' This is form that gets opened
based on the Week Number selected


stLinkCriteria = "[Campaign ID]=" & Me![Campaign ID]
DoCmd.save acForm, "Maintain Products Form" ' this is the form where
the combobox is and I am trying to force a save incase they hit the open form
button before saving

DoCmd.RunCommand acCmdRefresh 'I thought this would be a good idea, but
not sure why ....


If IsNull(Me![WeekStartCB] Then
MsgBox "Please Select the Flight Week Start Date and try again",
vbInformation, "No Week Selected"
Else

Const conMESSAGE = _
"No Records Found."

If DCount("*", "Weekly Buy Query") > 0 Then


DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox conMESSAGE, vbInformation, "Warning"
End If
End If


Exit_Command194_Click:
Exit Sub

Err_Command194_Click:
MsgBox Err.Description
Resume Exit_Command194_Click


End Sub










--
Nancy

that worked great!!! Thanks so much,, Except I of coure decided to get fancy,
in the combo box I wanted to show the week number as well as the
weekstartdate,,,, so i took out the 0 that i added to Num Table and tried
this instead..

Week Start Date Calc: DateAdd("ww",[N],[Forms]![Maintain Products
Form]![Flight Start Date]-7)

seems to be working but will this cause problems down the line?

Not that I can think of... it's just arithmatic.

Now for my next trick,,

It stays filled with the same value i selected when I move from record to
record on my form,
Ultimately I would like it to default to week 1 when I create a new record
and also when I move to a record where i have not chosen a value,,, is this
doable? If not then I need it to be nulled out.

Sure. Use the form's Current event and set its value:

Private Sub Form_Current()
If Me.NewRecord Then
Me!comboboxname = [Flight Start Date]
End If
End Sub

or whatever value you want.

And lastly need some advise.
I either need to pull this combo box into a bunch of queries,, approx 5,,
where the user will have to choose that same date 5 times,

or force them to fill it in for the first query and then pass that value to
the other queries until the record changes

If the query references

=[Forms]![formname]![comboboxname]

then it will use whatever date is there...

Or leave it on the form and force them to fill it in if it is blank before
they run any of the queries. (with this option i would need to null it out
after they move to a new record)

Well... you should NOT have the user viewing query datasheets. Is that what
you mean by "run"? Or are these Append or Update queries which get executed?
If so, check to see if the combo box is NULL before the qry.Execute line, and
prompt the user rather than running the query. Perhaps you could post your
current code.

Have i totally confused eveyone yet?

yes... <g>