IF Date Between two Fields

[noletwin1]

I have a table that has three columns, Beg_Date, End_Date, and Rpt_Month. I then have another table that has a Cal_Date. I want to create a field in the new table that if the Cal_Date is Between the Beg_Date and End_Date that it put out Rpt_Month.

 

[63falcondude]

Try something of the form

=IF(AND(Beg_Date<Cal_Date,Cal_Date<End_Date),Rpt_Month,"")

You will have to include table names as well. I recommend clicking on the first value of the column when building the formula to avoid errors from typing it in manually.

Edit: Just noticed this is for Access not Excel. Never mind...

 

[Joe4]

In Access there is no need to store any field which can be calculated (and it usually actually undermines the Rules or Normalization to do so). You can simply return this value in a query, like this:

SELECT Table_2.Cal_Date, Table_1.Rpt_Month
FROM Table_1, Table_2
WHERE (((Table_2.Cal_Date)>=[Table_1].[Beg_Date] And (Table_2.Cal_Date)<=[Table_1].[End_Date]));

or you could look it up in a Calculated Field in a Query using a DLOOKUP formula.

 

[noletwin1]

I used the below but it asked me for dates for

Field: expr: [Date]
((([EUR_COMP].[CAL_DT])>=[FYC_Calendar].[Beg_Date] And ([EUR_COMP].[CAL_DT])<=[FYC_Calendar].[End_Date]))

Couple of issues:
It won't give me Rpt_Date which is the third Field in the FYC_Calendar. The CAL_DT is between Beg_Date and End_Date fields and would yield Rpt_Date if between those two.
It also asks me for dates Beg_Date and End_Date
It won't display results.

 

[Joe4]

You need to include BOTH tables in your query. If it asking for those fields like parameters, it means it doesn't recognize those fields (probably because you are missing the table from the query!).

Get rid of [Date]. I am not sure what you are trying to do with that. You only include existing field names or parameters in square brackets. Date is a reserved word anyway, and should not be used for the name of fields or variables.

 

[noletwin1]

It says cannot display because Total Line has where so I could put formula in. I need the field to have a name and to be displayed.

 

[Joe4]

It says cannot display because Total Line has where so I could put formula in. I need the field to have a name and to be displayed.

I am sure I understand what you are saying here.

Try do the following:
Create a new query.
Change to SQL View
Copy and Paste this code into the SQL View window:

SELECT [EUR_COMP].Cal_Date, [FYC_Calendar].Rpt_Month
FROM [FYC_Calendar], [EUR_COMP]
WHERE ((([EUR_COMP].Cal_Date)>=[FYC_Calendar].[Beg_Date] And ([EUR_COMP].Cal_Date)<=[FYC_Calendar].[End_Date]));

View the results.
Does that do what you want?
You can switch to design view to see what the structure looks like.

 

[noletwin1]

I need it in a query that I am building, not it's own query.

 

[Joe4]

Please change your existing query to SQL View, and copy and paste the SQL code here so we can see exactly how you current query is constructed.

 

[noletwin1]

SELECT EUR_ENT.MRG_ENT_NB, EUR_ENT.MRG_ENT_NM, EUR_COMP.MMID, EUR_PRD.LOB_CD, EUR_PRD.PRD_SUM_CD, [INV_TOT_AM]+[PRM_AM] AS Amount, [Rpt_Mo_Date] AS Expr1, EUR_DSTRB_CH.DSTRB_CH_GP_NM, "Premium" AS Level_2_Description, EUR_PRD.PRD_SUM_DS INTO tbl_FYC_2016
FROM FYC_Calendar, (EUR_ENT INNER JOIN (EUR_PR INNER JOIN (EUR_PRD INNER JOIN EUR_COMP ON EUR_PRD.PRD_ID = EUR_COMP.PRD_ID) ON EUR_PR.MMID = EUR_COMP.MMID) ON EUR_ENT.ENT_NB = EUR_COMP.ENT_NB) INNER JOIN EUR_DSTRB_CH ON EUR_ENT.DSTRB_CH_CD = EUR_DSTRB_CH.DSTRB_CH_CD
WHERE (((EUR_COMP.POL_STS_CD)="02"))
GROUP BY EUR_ENT.MRG_ENT_NB, EUR_ENT.MRG_ENT_NM, EUR_COMP.MMID, EUR_PRD.LOB_CD, EUR_PRD.PRD_SUM_CD, [INV_TOT_AM]+[PRM_AM], [Rpt_Mo_Date], EUR_DSTRB_CH.DSTRB_CH_GP_NM, "Premium", EUR_PRD.PRD_SUM_DS, EUR_COMP.CON_CD, EUR_PR.ACT_IND, EUR_COMP.CAL_DT
HAVING ((([INV_TOT_AM]+[PRM_AM])<>0) AND ((EUR_COMP.CAL_DT)>=[Start Date] And (EUR_COMP.CAL_DT)<=[End Date]));