Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# MS Access Query Build

Posted on 2014-09-19
Medium Priority
141 Views
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
0
Question by:tom_optimum
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 7
• 7
• 2
• +1

LVL 8

Expert Comment

ID: 40332386
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
``````
0

LVL 52

Expert Comment

ID: 40332388
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)
``````
0

LVL 52

Expert Comment

ID: 40332402
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)
``````
0

Author Comment

ID: 40332415
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.
0

LVL 51

Expert Comment

ID: 40332697
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
0

Author Comment

ID: 40333564
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)

``````

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)

``````
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
``````

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
0

LVL 51

Expert Comment

ID: 40333593
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
0

Author Comment

ID: 40333631
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
0

LVL 51

Expert Comment

ID: 40333649
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
0

Author Comment

ID: 40333663
Hi,

Im still getting this error:

Tom
0

LVL 51

Expert Comment

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

/gustav
0

Author Comment

ID: 40333741
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;
``````
0

LVL 51

Accepted Solution

Gustav Brock earned 2000 total points
ID: 40334103
Well, that was quite different. Still, try this:

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,
IIf(YearGroupLookUp.OTrackYearGroup = 'N1'
And (StartDate BETWEEN DateSerial(Year(Date()), 9, 1) AND DateSerial(Year(Date()), 12, 31)),
'Nursery 2', YearGroupLookUp.OTrackYearGroup) AS NC
FROM
(((CTFImport
LEFT JOIN EALLookUp
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,
IIf(YearGroupLookUp.OTrackYearGroup = 'N1'
And (StartDate BETWEEN DateSerial(Year(Date()), 9, 1) AND DateSerial(Year(Date()), 12, 31)),
'Nursery 2', YearGroupLookUp.OTrackYearGroup)
CTFImport.Gender,
CTFImport.Ethnicity,
EALLookUp.OTrack_EAL,
Get_InCare_Calc.InCareStatus,
ORDER BY
YearGroupLookUp.OTrackYearGroup;

/gustav
0

Author Comment

ID: 40334357
Hi Gustav,

I am now getting this error:

0

LVL 51

Assisted Solution

Gustav Brock earned 2000 total points
ID: 40334365
I forgot a comma after the IIf sentence.

/gustav
0

Author Closing Comment

ID: 40334368
Thanks foyour help on this - very impressed.

Thanks

Tom
0

LVL 51

Expert Comment

ID: 40334370
You are welcome!

/gustav
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
###### Suggested Courses
Course of the Month5 days, 15 hours left to enroll