When a recordsource returns a null value question 2

[Sydney1]

The following code changes the recordset of a form

Dim strRecordSource as String
strRecordSource = "Select * from qryvcqWIPCurrent WHERE StatusCode = " & Forms!frmFilterCriteria!cboStatus
  If Isnull(strRecordSource) then
msgbox "blah"
else
 Me.RecordSource = strRecordSource
end  if

The recordsource part works but not the null part. I'm not sure if I can assign it a null or have to use record count. Any help would be greatly appreciated.

Sydney

 

[PHV]

You may replace this:
If Isnull(strRecordSource) then
By this:
If DCount("*", "qryvcqWIPCurrent", "StatusCode=" & Forms!frmFilterCriteria!cboStatus) = 0 Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

A string cannot be Null, further you're testing a string whether a query returns Null, which I'm afraid doesn't work. You'll need to either open a recordset based on the query, or one of the domain aggregate functions, for instance:

[tt]if isnull(dlookup("StatusCode","qryvcqWIPCurrent ","StatusCode = " & _
Forms!frmFilterCriteria!cboStatus)) then
' no records
else
Me.RecordSource = strRecordSource
end if[/tt]

Roy-Vidar

 

[jaydeebe]

Could try something like this.

Dim count As Integer

count = DCount("StatusCode", "qryvcqWIPCurren")

If count > 0 Then
Me.RecordSource = strRecordSource
else
msgbox "blah"
End If

 

[Sydney1]

Thanks everybody for the help.

I've tried PHV's method and have changed the field names from the original post. I have the following code

If DCount("*", "qryMainForm", "SignOn=" & Forms!frmLitigationEntry!GoToQueue) = 0 Then
MsgBox "blah"
Else
'The rest

but am getting a syntax error (missing operator in query expression) 'Signon=Allen Backus'

Also would I be able to add another criteria to Dcount expression stating that [Completedate] is null. I tried myself but kept getting an error.

Thanks again for all your help.

Sydney

 

[PHV]

If DCount("*", "qryMainForm", "SignOn=[tt]'"[/tt] & Forms!frmLitigationEntry!GoToQueue & [tt]"'"[/tt]) = 0 Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

Try using single quotes on the criterion, since it is text:

[tt]..."SignOn='" & Forms!frmLitigationEntry!GoToQueue & "'")[/tt]

or double quoting, to avoid problems with names containing special characters

[tt]..."SignOn=""" & Forms!frmLitigationEntry!GoToQueue & """")[/tt]

Roy-Vidar

 

[Sydney1]

Thanks guys, the single quotes work great! If I can trouble you for one more thing. I've tried to add the complete date (smalldatetime field)to the DCount expression but get a data mismatch. I presently have the following

If DCount("*", "qryMainForm", "SignOn='" & Forms!frmLitigationEntry!GoToQueue & "'" And "CompleteDate" Is Null) = 0 Then

Thanks again for your help.

Sydney

 

[RoyVidar]

Mind the concatenation:

[tt]If DCount("*", "qryMainForm", _
"SignOn='" & Forms!frmLitigationEntry!GoToQueue & _
"' And CompleteDate Is Null") = 0 Then[/tt]

Only variables get's concatenated into the string, strict SQL syntax doesn't need any concatenation (using separate lines to visualize).

Roy-Vidar

 

[Sydney1]

That worked great. Thanks again for all your help.


Sydney