Query Criteria and Grouping 2 Date Fields (1 Viewer)

mark365

New member
Local time
Today, 02:14
Joined
Jun 10, 2020
Messages
10
Hello,

I am trying to write a query which needs to meet a condition / criteria and then if so place into different groups?

for example:

if a person collected a prescription >2 days and <=7 days from attending doctor surgery then place in group named “Sufficient Collection”.

Or

if a person collected a prescription >8 days and <= 14 days from attending doctor surgery then place in group named “Late Collection”.

I have 2 fields one named Prescription collection date and Doctor Surgery Attendance date and I don’t know under which of these fields I need to place the above discussed criteria.

I really do appreciate anyone taking the time to read this and to help.

Thanks,

Mark (new to the group from UK)
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:14
Joined
Oct 29, 2018
Messages
21,358
Hi Mark. Welcome to AWF!

Have a look at the DateDiff() function. It will give you the number of days between prescription and collection. You can then use an IIf() expression to assign the group.
 

mark365

New member
Local time
Today, 02:14
Joined
Jun 10, 2020
Messages
10
Thanks for the guidance. DateDiff function worked well.

I am a bit stumped with the IIF function.

In the field row I have put:

Group: llf([DaysbetweenPrescriptionDateandAttendanceDate]>2and<=7, "sufficient collection")

the error says the expression I have entered contains invalid syntax



Hi Mark. Welcome to AWF!

Have a look at the DateDiff() function. It will give you the number of days between prescription and collection. You can then use an IIf() expression to assign the group.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:14
Joined
Oct 29, 2018
Messages
21,358
Thanks for the guidance. DateDiff function worked well.

I am a bit stumped with the IIF function.

In the field row I have put:

Group: llf([DaysbetweenPrescriptionDateandAttendanceDate]>2and<=7, "sufficient collection")

the error says the expression I have entered contains invalid syntax
Hi. Quick question... What group would it be if the number of days is 1, or 2, or 8, or >=15?
 

mark365

New member
Local time
Today, 02:14
Joined
Jun 10, 2020
Messages
10
if the day is 0 or 1 group would be “immediate collection” and sorry “late collection” should be >=8 days but <=14 days. Then collection >=15 days would be “Other”.

the reason for naming the group over 15 as other is that I’m only really interested in those between 2 and 14 days.

Hope that provides some clarity

Hi. Quick question... What group would it be if the number of days is 1, or 2, or 8, or >=15?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:14
Joined
Oct 29, 2018
Messages
21,358
if the day is 0 or 1 group would be “immediate collection” and sorry “late collection” should be >=8 days but <=14 days. Then collection >=15 days would be “Other”.

the reason for naming the group over 15 as other is that I’m only really interested in those between 2 and 14 days.

Hope that provides some clarity
Thanks for the clarification. Try adding this calculated column in your query.
Code:
Group: IIf([DaysbetweenPrescriptionDateandAttendanceDate]<2,"Immediate Collection",IIf([DaysbetweenPrescriptionDateandAttendanceDate]<8,"Sufficient Collection",IIf([DaysbetweenPrescriptionDateandAttendanceDate]<15,"Late Collection","Other")))
Hope that helps...
 

mark365

New member
Local time
Today, 02:14
Joined
Jun 10, 2020
Messages
10
Thank you for all your help.

Thanks for the clarification. Try adding this calculated column in your query.
Code:
Group: IIf([DaysbetweenPrescriptionDateandAttendanceDate]<2,"Immediate Collection",IIf([DaysbetweenPrescriptionDateandAttendanceDate]<8,"Sufficient Collection",IIf([DaysbetweenPrescriptionDateandAttendanceDate]<15,"Late Collection","Other")))
Hope that helps...
 

Users who are viewing this thread

Top Bottom