Dealing with dates and null values in parameter query

[accessnoob123]

Forgive me I am a newbie at the whole access thing... but I am trying to design a query using a form that will take user entered values and display a report based on those values. Everything works great except I am having trouble with including null values in the criteria. I want to return all records between two dates(I have that part down) including null values(don't have that part down), also want to return all values(including nulls) when the box on the user form is left blank. Any help you could give me would be most appreciated!

Thanks

 

[WayneRyan]

Noob,

Select Field1, Field2, FieldEtc
From   YourTable
Where  TheDateField Between Forms!YourForm!StartDate And Forms!YourForm!EndDate Or
      (Forms!YourForm!StartDate Is NUll Or
       Forms!YourForm!EndDate Is NUll)

Wayne

 

[WayneRyan]

Noob,

Just realized that the previous advice had no chance of succeeding.

To me, VBA seems to be the only answer:

Your query using a public function:

SELECT Field1, Field2, Field3, FieldEtc
FROM YourTable
WHERE CheckRange([YourDateField])=True;

Put the Public Function in a module:

Public Function CheckRange(strDate As Variant) As Boolean
CheckRange = False
'
' If the data is Null, or either parameter is Null, return the record.
'
If IsNull(strDate) Or Len(strDate & "") = 0 Or Not IsDate(strDate) Or _
   Len(Forms!frmTest!txtStartDate & "") = 0 Or Len(Forms!frmTest!txtEndDate & "") = 0 Then
   CheckRange = True
   Exit Function
End If
'
' If All values are dates, only return true if Data is Between the Parameters
'
If CDate(strDate) >= CDate(Forms!frmTest!txtStartDate) And CDate(strDate) <= CDate(Forms!frmTest!txtEndDate) Then
   CheckRange = True
End If
End Function

It seems to work fine.

hth,
Wayne

 

[accessnoob123]

Thanks for the reply Wayne. I am very new to this. So I got your function code into a a module and named it CheckRange. I changed the wording in the code to the code below to include the location of the date input boxes. I don't know how to call the function in the query though. Do I simply put CheckRange9[Date to IMP]) in the criteria section?? The whole VBA thing is new to be me so bear with me.

Thanks!

SELECT Main.AREA, Main.[Entry Date], Main.MENTOR, Main.Submitter, Main.DESCRIPTION, Main.Status, Main.TEAM1, Main.[Date to IMP], Main.[Co-Author(s)]
FROM Main
WHERE (((Main.AREA) Like [Forms]![Main Search Form]![AREA] & "*")
AND ((Main.[Entry Date]) Between [Forms]![Main Search Form]![entrystartdate] And [Forms]![Main Search Form]![entryenddate])
AND ((Main.MENTOR) Like "*" & [Forms]![Main Search Form]![MENTOR] & "*")
AND ((Main.Submitter) Like [Forms]![Main Search Form]![Submitter] & "*")
AND ((Main.Status) Like [Forms]![Main Search Form]![Status] & "*")
AND ((Main.TEAM1) Like "*" & [Forms]![Main Search Form]![TEAM] & "*")
AND(CheckRange([Date to IMP])=True))


here is the code changes I made



Public Function CheckRange(strDate As Variant) As Boolean
CheckRange = False
'
' If the data is Null, or either parameter is Null, return the record.
'
If IsNull(strDate) Or Len(strDate & "") = 0 Or Not IsDate(strDate) Or _
Len(Forms![Main Search Form]![impstartdate] & "") = 0 Or Len(Forms![Main Search Form]![impenddate] & "") = 0 Then
CheckRange = True
Exit Function
End If
'
' If All values are dates, only return true if Data is Between the Parameters
'
If CDate(strDate) >= CDate(Forms![Main Search Form]![impstartdate]) And CDate(strDate) <= CDate(Forms![Main Search Form]![impenddate]) Then
CheckRange = True
End If
End Function

 

[WayneRyan]

Noob,

Yes, just put the --> CheckRange([Date to IMP]) = True

in the criteria under any field in the query and you'll be fine.

Wayne

 

[accessnoob123]

It keeps giving me an "undefined function "CheckRange" In expression error

Thoughts?

Thanks!

 

[jal]

It keeps giving me an "undefined function "CheckRange" In expression error

Thoughts?

Thanks!

I just got burned on a similar issue so I made some notes in my notebook. Not sure if they will help you.

If you get an error such as "Undefined Function" or "Unknown Function Name", in respect to a custom function you have created - a user-defined function (UDF) - here are some possible causes. First, make sure you declared your function Public:
.......Public Function MyCustomFunction(ByVal x as integer)
- Second, make sure you put the UDF in a standard module as opposed to a class module.
- Third, realize that UDFs (User Defined Function) might not work if unsafe expressions are blocked. Ideally, you can hve the best of both worlds. This is made possible by sandBox mode, which permits UDFs AND blocks unsafe expressions. ("Unsafe expressions" does not refer to all dangerous VBA code, but only the use of DANGEROUS VBA code used in an EXPRESSION).
-Sandbox mode is supposed to be possible with Jet 4.0 Service pack 8, but I couldn't get it to work. So I had to allow unsafe expressions. Supposedly a registry setting can enable sandbox mode. I looked for it in regedit, but that category didn't even exist in my registry, much less the correct value for that category. And in my company, I don't have permission to edit the registry anyway.
- So how do you permit unsafe expressions? By responding appropriately to the question when it pops up. If you see:
........"Block Unsafe Expressions?" - Answer NO
If you see
........"Allow unsafe expressions? - Answer YES
- Generally, these questions pop up only when you change your security setting from say, Low to Medium, at Tools > Macros > Security. Try doing a flip flop - change it from Medium to Low, and then Low to Medium. WARNING: DON'T CHANGE IT TO HIGH UNLESS YOU HAVE INSTALLED A DIGITAL CERTIFICATE AND SIGNED YOUR PROJECT. YOU COULD GET LOCKED OUT OF ACCESS PERMANENTLY!
- These are not the only possible causes of "Undefined Function" - but it's a start.

 

[accessnoob123]

I have enabled all macro options and it still has done nothing... I am sooo confused! Any help anyone can offer would be great

Thanks!

 

[boblarson]

I have enabled all macro options and it still has done nothing... I am sooo confused! Any help anyone can offer would be great

Thanks!

Are you absolutely sure you

1. Put the CheckRange function into a new STANDARD module (not class module, not form module).

2. If you did put it into a Standard module, you didn't name the module the same CheckRange as the function also which would cause an error.

 

[jal]

I have enabled all macro options and it still has done nothing... I am sooo confused! Any help anyone can offer would be great

Thanks!

When you say it now does "nothing" do you mean it is still throwing the error message?

 

[accessnoob123]

Ok I think I have it working, however it returns the null dates as well as the dates between the criteria I have entered... Don't know if it makes a difference but at one time I changed this field type to text and entered a "" as a default value to the field to get rid of nulls. I have since changed the type back to date/Time...do the zero lenght strings auto change to nulls or do I have to somehow manually do that.... If so how do I do that?

Thanks again guys for all your help!

 

[jal]

The code WayneRyan gave you returns True for null dates, in the CheckRange function. He thought that's what you wanted. Maybe what you want is to return False.

 

[accessnoob123]

Jal,

Thanks for the reply.

I did want it to return all records including nulls if no date parameters were entered. I do not want to include nulls if date parameters are included..Just the records in between them. I tried messing around with the code a little but my ideas did not work? Any ideas how I could modify the code to get what I want?

Thanks again to everyone for all your help!

 

[boblarson]

How about checking out my sample here:
http://downloads.btabdevelopment.com/Samples/combos/FormSampleFromMultipleCriteria.zip

 

[accessnoob123]

Ok...next question I modified the code to return what I wanted....It returns all records between the dates when there are no nulls present in an entrydate field....however on the impdate field where there are nulls present.... I got it to only return dates between the criteria entered however not all the records with dates between the criteria are being returned..ANy ideas what I have to do to the code to fix this???

Thanks In advance!

Here is the code

Public Function CheckRange(strDate As Variant) As Boolean
CheckRange = False

If IsNull(Forms![Main Search Form]![impstartdate]) Or Forms![Main Search Form]![impstartdate] = "" And IsNull(strDate) Then
CheckRange = True
ElseIf (Not IsNull(Forms![Main Search Form]![impstartdate]) And Forms![Main Search Form]![impstartdate] <> "") And (Not IsNull(Forms![Main Search Form]![impenddate]) Or Forms![Main Search Form]![impenddate] <> "") And (strDate) >= (Forms![Main Search Form]![impstartdate]) And (strDate) <= (Forms![Main Search Form]![impenddate]) Then
CheckRange = True
End If

End Function

 

[jal]

You should try to make your code slightly more readable - You are trying to pack too many IFs, ANDs and BUTs into one statement which makes the code difficult to read and debug. Try to handle one condition at a time. If that condition fails then

CheckRange = False
Exit Function


AnyWho, one line that looks suspicious to me is this:

Not IsNull(Forms![Main Search Form]![impenddate]) Or Forms![Main Search Form]![impenddate] <> "")

because I wouldn't usually try to wrap ONE IsNull call around TWO different textboxes. (I'm just making a wild guess here).