Restricting Query Records

[khwaja]

I am using a query to feed to report. In this query, I am using following field to provide category statement to serve as report groups. It works fine but I cannot seem to get rid of null records. When I try to exclude null records, I get a parameter prompt for 'survey due'. All other fields are actual fields but 'survey due' is a calculated field. Will appreciate some help in excluding null values.

Survey Alert: IIf([Survey Due]<Date() And IsNull([surrecvd]) And IsNull([spedateord]),"1-Survey due now",IIf([Survey Due]<Date() And [surrecvd] Is Not Null And IsNull([spedateord]),"2-Order survey now",IIf([Survey Due]<Date() And [surrecvd] Is Not Null And [spedateord] And DateDiff("d",[surrecvd],[spedateord])>15,"3-Survey ordered late",Null)))

 

[plog]

Time for a function. You can't just cram a ton of logic into a line and hope to ever have anyone make sense of it (yourself included).

So, create a function in a module, pass it all the data it needs to return a value, construct the logic in as many lines as you need and return the value.

 

[arnelgp]

Form!yourFormName![Survey Due]

 

[spikepl]

Is Not Null is not the inverse function of IsNull()

VBA:
IsNull(MyValue) or
Not IsNull(MyValue)

SQL:

MyField IS Null
or
MyField IS Not Null

 

[khwaja]

Thanks Spikepl. I tried to change statements as you have described but i still have the same issue.

Plog, could you guide me constructing the function. Just a couple of lines will get me going.

 

[plog]

Here's a function skeleton:

Function get_SurveyStatus(in_SurveyDue, in_surrecvd, in_spedateord)
    ' returns numerical status of survey

	ret=0			' return value, default value is 0


	' logic for each status here

	get_SurveyStatus = ret

	End Function

Then in your query you put a calculated field like so:

SurveyAlert: get_SurveyStatus(SurveyDue, surrecvd, spedateord)

 

[khwaja]

Many thanks. I will give it a go.

 

[khwaja]

Could you kindly look at what I have got here. It is currently not working as I am getting object required as runtime error.

Function get_SurveyStatus(SurveyDue, Surrecvd, Spedateord)
' returns numerical status of survey

ret = 0 ' return value, default value is 0


If SurveyDue < Date And IsNull([Surrecvd]) And IsNull([Spedateord]) Then

get_SurveyStatus = "1-Survey due now"

ElseIf SurveyDue < Date And [Surrecvd] Is Not Null And IsNull([Spedateord]) Then

get_SurveyStatus = "2-Order survey now"

ElseIf SurveyDue < Date And [Surrecvd] Is Not Null And [Spedateord] And DateDiff("d", [Surrecvd], [Spedateord]) > 15 Then

get_SurveyStatus = "3-Survey ordered late"

End If

get_SurveyStatus = ret

End Function

 

[plog]

What runtime error? What line?

 

[khwaja]

Thanks.

It is Runtime error '424': Object required. Code stops at:

ElseIf SurveyDue < Date And [SurRecvd] Is Not Null And IsNull([SpeDateOrd]) Then

 

[spikepl]

I wrote this not for fun but for you to learn from and use. You didn't. That's why you get the error.

VBA:
IsNull(MyValue) or
Not IsNull(MyValue)

SQL:

MyField IS Null
or
MyField IS Not Null

 

[plog]

You shouldn't have brackets around your variable names. They aren't fields, they are values. That's why in my skeleton I gave them names other than your field names.

Think of the function existing outside your database. It doesn't know anything about your tables, it only knows what you pass it. You are sending it data in the form of those 3 variables. It does its thing and sends you back an answer.

 

[khwaja]

Plog, Thank you so much for being patient and the kind guidance.

If I use the function in my query, how would function know which fields it has to relate to in conjunction with variables names? The syntax for function you provided refers to actual field names.

In the body of code (the logic), do I use the variable name or actual field names?

Also, should the default value be a number when I am returning a string?

Kindly note this business emanated from my need to exclude records which do not match any of the conditions.

I got it this far:

Function get_SurveyStatus(in_Survey_Due, in_SurRecvd, in_SpeDateOrd)
' returns numerical status of survey

ret = 0 ' return value, default value is 0


If in_Survey_Due < Date And in_SurRecvd Is Null And in_SpeDateOrd Is Null Then

get_SurveyStatus = "1-Survey due now"

ElseIf in_Survey_Due < Date And in_SurRecvd Is Not Null And in_SpeDateOrd Is Null Then

get_SurveyStatus = "2-Order survey now"

ElseIf in_Survey_Due < Date And in_SurRecvd Is Not Null And in_SpeDateOrd And DateDiff("d", in_SurRecvd, in_SpeDateOrd) > 15 Then

get_SurveyStatus = "3-Survey ordered late"

End If

get_SurveyStatus = ret

End Function

 

[plog]

I doesn't know your fields, it only knows the data you pass it. The function skeleton I provided did not contain any field names. It contained variables that you pass it:

Function get_SurveyStatus(in_SurveyDue, in_surrecvd, in_spedateord)


The function knows nothing of your table. You pass it values and those values are stored in the variables. You reference the variables and have access to the values. Therefore, in your code you need to reference in_SurveyDue, in_surrecvd and in_spedateord instead of your field names, because the function knows nothing of your fields, just the variables you pass it.

As for the return value, you are trying to jam 2 pieces of data in there--a number and a string. Why? Does the number part actually do anything for you? Why is it included? My function assumed it was necessary and it returned just a number. My plan was to build a table that linked numbers to descriptions, that way, you could build another query and link to that table to get the string portion.

Lastly, I wouldn't assign get_SurveySTatus any value until the last line. I would instead values to ret, then as the last line assign ret to get_SurveyStatus.

 

[khwaja]

Thanks a lot for the clarification. I have amended the code and it should only returns text values.

Function get_SurveyStatus(in_Survey_Due, in_SurRecvd, in_SpeDateOrd)
' returns numerical status of survey

ret = Null ' return value, default value is null


If in_Survey_Due < Date And in_SurRecvd Is Null And in_SpeDateOrd Is Null Then

get_SurveyStatus = "1-Survey due now"

ElseIf in_Survey_Due < Date And in_SurRecvd Is Not Null And in_SpeDateOrd Is Null Then

get_SurveyStatus = "2-Order survey now"

ElseIf in_Survey_Due < Date And in_SurRecvd Is Not Null And in_SpeDateOrd Then

get_SurveyStatus = "3-Survey ordered late"

End If

get_SurveyStatus = ret

End Function

And I am using following in the query where there is a calculated field 'SurveyDue.

SurveyAlert: get_SurveyStatus([SurveyDue],[surrecvd],[spedateord])

I am still getting the same runtime error '424': Object required. Code stops at the very first line.

If in_Survey_Due < Date And in_SurRecvd Is Null And in_SpeDateOrd Is Null Then

 

[plog]

And I am using following in the query where there is a calculated field 'SurveyDue

That's the issue. You normally shouldn't reference a calculated field in the same query it is calculated. So that means, either build a subquery to calculate SurveyDue, or pass the necessary data to your function so it can calculate it as well.

 

[khwaja]

OK thanks.