asked on

MS Access Query Build

Hi all,

I have a query in MS Access. It holds two fields:

StartDate

NCyearActual

I want a third field that calculates a value based on the two fields above. I need it to do it based on this method:

If the NCyearActual is 'N1' and the StartDate is between 1st September to 31st December (in this calendar year) then show 'Nursery 2' - else, show the value held in the NCyearActual field.

I've tried a few things but I can't get it to work.

Can someone help me with this one please?

Your help would be much appreciated.

Thanks,

Tom

Ganapathi

Try this.

SELECT
   StartDate,
   NCyearActual,
   IIf([NCyearActual]='N1' And [StartDate] between '01/09/2014' And '12/31/2014', Nursery 2, NCyearActual) AS Column3
FROM
   mytable

Open in new window

Vitor Montalvão

Just add a new column with something like this:

iif ( N1=1, iif ( StartDate BETWEEN #09-01-2014# AND #12-31-2014#, 'Nursery 2', NCyearActual), NCyearActual)

Open in new window

Vitor Montalvão

For the current year you need to build the date on runtime:

iif ( N1=1, iif ( StartDate BETWEEN DateValue("09-01-" + Str(YEAR(Date()))) AND DateValue("12-31-" + Str(YEAR(Date()))), 'Nursery 2', NCyearActual), NCyearActual)

Open in new window

tom_optimum

ASKER

Great - thanks. I'm just about to go into a meeting. I will try these when I am out.

I do need it to work with current calendar year because I don't want to have to update the code each year.

Gustav Brock

Never use string handling for dates:

SELECT
StartDate,
NCyearActual,
IIf(N1 = 1,
IIf(StartDate BETWEEN DateSerial(Year(Date()), 9, 1) AND DateSerial(Year(Date()), 12, 31)),
'Nursery 2', NCyearActual), NCyearActual) AS NC
FROM
tblYourTable

/gustav

tom_optimum

ASKER

Hi all,

None of these work.. I get some form of error for them all.

For:

iif ( N1=1, iif ( StartDate BETWEEN #09-01-2014# AND #12-31-2014#, 'Nursery 2', NCyearActual), NCyearActual)

Open in new window

I get this error:

For:

iif ( N1=1, iif ( StartDate BETWEEN DateValue("09-01-" + Str(YEAR(Date()))) AND DateValue("12-31-" + Str(YEAR(Date()))), 'Nursery 2', NCyearActual), NCyearActual)

Open in new window

I get this:

And for this one:

SELECT
   StartDate,
   NCyearActual,
       IIf(N1 = 1,
           IIf(StartDate BETWEEN DateSerial(Year(Date()), 9, 1) AND DateSerial(Year(Date()), 12, 31)),
           'Nursery 2', NCyearActual), NCyearActual) AS NC
FROM
    tblYourTable

Open in new window

I get this:

Also, I think the code saying things like "N1 = 1" is wrong... I dont have a field called N1 my field is called NCyearActual and one of the values is 'N1'.

Does this help?

Cheers

Tom

Gustav Brock

This should do:

SELECT
StartDate,
NCyearActual,
IIf(NCyearActual = 'N1',
IIf(StartDate BETWEEN DateSerial(Year(Date()), 9, 1) AND DateSerial(Year(Date()), 12, 31),
'Nursery 2', NCyearActual), NCyearActual) AS NC
FROM
tblYourTable

/gustav

tom_optimum

ASKER

Hi Gustav,

I am pasting this into the syntax box in the query view. So, I right click in the new coloumn > click Build > and paste into the syntax box.

I get this error:

I am using this code for the table name:

FROM
[BasicPupilData]

Any ideas?

Cheers

Tom

Gustav Brock

Try this:

SELECT
StartDate,
NCyearActual,
IIf(NCyearActual = 'N1' And (StartDate BETWEEN DateSerial(Year(Date()), 9, 1) AND DateSerial(Year(Date()), 12, 31)), 'Nursery 2', NCyearActual) AS NC
FROM
BasicPupilData

/gustav

tom_optimum

ASKER

Hi,

Im still getting this error:

Tom

Gustav Brock

But there is no subquery. Could you paste your full SQL please?

/gustav

tom_optimum

ASKER

Hi,

Here it is:

SELECT CTFImport.UPN, CTFImport.Surname, CTFImport.Forename, CTFImport.DOB, Get_FSM_Calc.FSM AS FSM, YearGroupLookUp.OTrackYearGroup AS NCyearActual, CTFImport.Gender, CTFImport.Ethnicity, EALLookUp.OTrack_EAL, Max(CTFImport.StartDate) AS MaxOfStartDate, Get_InCare_Calc.InCareStatus
FROM (((CTFImport LEFT JOIN EALLookUp ON CTFImport.EnglishAsAdditionalLanguage = EALLookUp.CTF_EAL) LEFT JOIN Get_FSM_Calc ON CTFImport.UPN = Get_FSM_Calc.UPN) LEFT JOIN Get_InCare_Calc ON CTFImport.UPN = Get_InCare_Calc.UPN) LEFT JOIN YearGroupLookUp ON CTFImport.NCyearActual = YearGroupLookUp.CTFYearGroup
GROUP BY CTFImport.UPN, CTFImport.Surname, CTFImport.Forename, CTFImport.DOB, Get_FSM_Calc.FSM, YearGroupLookUp.OTrackYearGroup, CTFImport.Gender, CTFImport.Ethnicity, EALLookUp.OTrack_EAL, Get_InCare_Calc.InCareStatus, CTFImport.EnglishAsAdditionalLanguage
ORDER BY YearGroupLookUp.OTrackYearGroup;

Open in new window

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tom_optimum

ASKER

Hi Gustav,

Thanks for your help.

I am now getting this error: