Is Null with Between

[mykebass]

Hi. I'm using the following in a criteria, but Access just hangs. Any thoughts what I'm doing wrong?

IIf(IsNull([Forms]![frmMain]![txtbegdate]-[Forms]![frmMain]![txtenddate]),*,between [Forms]![frmMain]![txtbegdate] and Forms![frmMain]![txtenddate] )

 

[Trendsetter]

I might be reading this wrong but..........
1) you are checking for Null by subtracting 2 dates - is this right
2) you are multyiplying the outcome - not a string "*"
3) If the first bit is not Null you want to enter a range of dates from 'beg' to 'end'.

It may be possible but I've never seen it done!

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[mykebass]

1) you are checking for Null by subtracting 2 dates - is this right

ACtually, no. The - sign in this case acts as an OR statement. I'm stating that if A or B textbox are null then criteria should just be * (give me all records), if false criteria should be between A and B textbox. But just doesn't work.

 

[lespaul]

If you subtract those two dates you will end up with 0 (zero) which is not the same as NULL.


Leslie

 

[KornGeek]

Leslie,

Actually, I think it will technically work. If A and/or B is Null, then A-B is Null. It's not the most clear way to do it, but it should work.

mykebass,

You mention you have this in a query as criteria. Can you please post the SQL of the query to help us understand exactly what you're doing?

 

[Hap007]

Dear mykebass,

Several things:
1) If I remember correctly from my past battles, iif() funtion does not work in Criteria.

So, I would place your test of parameter fields in vba code on your form and place the result in a hidden field on the form and use that hidden form as your criteria.
Second, if dates are null, then you want all records, so instead of the '*', just leave criteria blank

Define Me.HiddenChoice
IF IsNull(Me.txtbegdate) or IsNull(txtenddate) Then
Me.HiddenChoice = ""
else
Me.HiddenChoice = "between #" & Me.txtbegdate & "# and #" & Me.txtenddate & "#"
end if

Then use [Forms]![frmMain]![HiddenChoice]

as your criteria in your Query.

To Correct the Statement
IIf(IsNull([Forms]![frmMain]![txtbegdate]-[Forms]![frmMain]![txtenddate]),*,between [Forms]![frmMain]![txtbegdate] and Forms![frmMain]![txtenddate] )

Try this, but I do not like the '*' in either case
iif(Len(Trim$([Forms]![frmMain]![txtbegdate] & [Forms]![frmMain]![txtenddate] & " "))=0," Like *","between #" & [Forms]![frmMain]![txtbegdate] & "# and #" & Forms![frmMain]![txtenddate] & "#" )

or

iif(Len(Trim$([Forms]![frmMain]![txtbegdate] & [Forms]![frmMain]![txtenddate] & " "))=0,"","between #" & [Forms]![frmMain]![txtbegdate] & "# and #" & Forms![frmMain]![txtenddate] & "#" )

Hope This Helps,
Hap


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121