Between Dates query not working

[tbarkdull]

I have a query built by someone else that I think is the root of my problem. The query runs and selects Officers of an Insurance Company based on the dates in a table stating when they were officers. The problem is one of the date (enddate) is blank since he/she may be a current officer. The query reads ">[RPT_NAIC_AF1]![BeginDate] And <=[RPT_NAIC_AF1]![EndDate]" It is not returning all records after the start date which has no enddate. If I change the "And" to an "Or" I get the records I want, and a lot more so I know the problem lies here. Help please! I am a novice user but willing to learn.

 

[edtab]

You may want to substitute the beginning date as the ending date where ending date is null. This might solve your problem.

 

[tbarkdull]

Between Dates Query

How can I substitute the current date as the end date? I think that would work in this case.

 

[EdFred]

fldEndDate:IIf([RPT_NAIC_AF1]![EndDate] Is Null,Now(),[RPT_NAIC_AF1]![EndDate])

I also use this instead: BETWEEN [RPT_NAIC_AF1]![BeginDate] And [fldEndDate]

 

[rainman89]

probably in the onclick of the button
If isnull(endate)
endate=startdate
else
end if

 

[boblarson]

Check out my answer to you in your other post (try not to post the same question multiple times):
http://www.access-programmers.co.uk/forums/showthread.php?t=124661

 

[tbarkdull]

Query Between Date

EdFred,

Yours looks like the solution I need. However, How to I use it? My query now resides in the Criteria line of the Date Query. How do I structure both statements?

fldEndDate:IIf([RPT_NAIC_AF1]![EndDate] Is Null,Now(),[RPT_NAIC_AF1]![EndDate]) returns and invalid expression

Thanks Immensely for your help!

 

[rainman89]

fldEndDate:IIf(IsNull([RPT_NAIC_AF1]![EndDate]),Now(),[RPT_NAIC_AF1]![EndDate])

 

[tbarkdull]

Date Query

Still getting invalid expression. Where does the fldEndDate come from?

 

[boblarson]

Is the Query that has [RPT_NAIC_AF1]![EndDate] and the other one actually part of the query that you are referencing it in? In other words, it needs to be linked to the tables/query that is in the QBE grid.

 

[EdFred]

After you have the field written correctly, like this

fldEndDate:IIf(IsNull([RPT_NAIC_AF1]![EndDate]),Now(),[RPT_NAIC_AF1]![EndDate])

(I was brain dead when I wrote it the first time) replace your criteria of >start date <=end date with BETWEEN [RPT_NAIC_AF1]![StartDate] AND [fldEndDate]

 

[EdFred]

Still getting invalid expression. Where does the fldEndDate come from?

replace your field of EndDate in the query with the fldEndDate

Then in the criteria section of the Date you want to look up use the Between

 

[tbarkdull]

Date Query

Attached is what I have. I'm a little lost in the syntax. I apologize for my simple questions

 

[tbarkdull]

Date Query

After you have the field written correctly, like this (I was brain dead when I wrote it the first time) replace your criteria of >start date <=end date with BETWEEN [RPT_NAIC_AF1]![StartDate] AND [fldEndDate]

Still not returning info with null EndDate

 

[EdFred]

Attached is what I have. I'm a little lost in the syntax. I apologize for my simple questions

Should be something like this:

 

[tbarkdull]

Date Query

Thanks to all, I got it working AND I understand it!