If/Then statements?

[hsevigny]

I have built a system for my employer to track individuals that are out on Leave's of Absence to help them asses our needs. I am trying to build a query that will do the following:

Pull those that are out within a specific date range (this I know how to do).

Also pulls anyone that might have gone out prior to the date range that I have entered but are still out

Also pulls those that started their Leave after the date range but still may be out

I'm not sure how to fix the query so it pulls all three categories. Right now it only pulls those that are out between the date range indicated.

Please help!

Thank you!
Heather

 

[MSAccessRookie]

I have built a system for my employer to track individuals that are out on Leave's of Absence to help them asses our needs. I am trying to build a query that will do the following:

Pull those that are out within a specific date range (this I know how to do).

Also pulls anyone that might have gone out prior to the date range that I have entered but are still out

Also pulls those that started their Leave after the date range but still may be out

I'm not sure how to fix the query so it pulls all three categories. Right now it only pulls those that are out between the date range indicated.

Please help!

Thank you!
Heather

Locating the personnel records that you want should be simple, as all of the people will have at least one thing in common. They will all have at least one day Leave of Absence and still be out. Organizing them according to your three groups will take a bit more effort.

Perhaps you could test the start date for the leave with IIf and return "Before", "During", and "After" for the appropriate date ranges?

 

[accessaspire219]

Hi,
I have a question -- I have a database that contains a PO number and two date columns -- lets call them Date1 and Date2; Now I want a third column -- Date3; which essentially will be Date1 unless Date1 is blank in which case it will be Date2.

The logic is, Date3=Date1 (if Date1 is populated) else = Date2 (if Date1 is blank)

I am sure this can be done with a If/then/else statement or something..but just don't know how.

Can anyone help??

Thanks.

 

[Brianwarnock]

accessaspire219
don't know why you added to this thread , but what you require is
IIf(isnull(date1),date2,date1)

Brian

 

[Brianwarnock]

Heather

I think that to see who is on leave at any time in a given date range you need

If leavestart<=rangeend and (leaveend >=rangestart or leavend is null)

Brian

 

[accessaspire219]

DUEDT:IIF(IsNull([SNDATE]),[EINDT],[SNDATE])
I am using this expression to make a column which contains the SNDATE if the field is populated or the EINDT if the SNDATE field is not populated. It was working fine, however, all of sudden it is giving me a "visual basic module syntax error" message. Can anyone tell me why?? The only significant change that I did in the database was that I created a module to run all the queries automatically, and then I removed the module since it wasn't working. Could this be the reason?