Returns error when the look up value do not exist

[AJLJ2015]

When the Look up value under the [Status]='For Signature' do not exist in the current record, the code returns an error where it says error [disc] = 'books' cannot be found error..however the code works when the look up [status] value is on the list/record.

I want to have the code to continue the execution and return empty values even the look up [status] value do not exist anymore.

Please help, thanks

Public Function fAppendIANum3(Disc) As String
Dim intNoOfIA3 As String, strNames3 As String
 intNoOfIA3 = DCount("*", "TBXQuery", "Disc AND [Status]=""For Signature""")
 
If intNoOfIA3 = 0 Then

 fAppendIANum3 = DLookup("[IA_Number_]", "TBXQuery", "[Disc]=" & Disc & "' AND [Status]='For Signature'")

    Exit Function
Else
  
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs2 As DAO.Recordset
    Dim MyRS As String

    MyRS = "SELECT [TBXQuery].* " & _
         "FROM [TBXQuery] " & _
        "WHERE [Disc] = [which_id] AND [Status]='For Signature'"
        
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef(vbNullString, MyRS)

    qdf.Parameters("which_id") = Disc

    Set rs2 = qdf.OpenRecordset

     
    rs2.FindFirst ("[Status]= 'For Signature'")
    
    Do While Not rs2.EOF
      If Len(strNames3) = 0 Then
        strNames3 = rs2![IA_Number_]
        
        
      Else
        strNames3 = strNames3 & ", " & rs2![IA_Number_]
        
        End If
        rs2.MoveNext
    Loop
    fAppendIANum3 = strNames3
    

End If

rs2.Close

End Function

[zmbd]

Hello Welcome to Bytes.

You don't mention what error is being given, I suspect it's an invalid use of null.

Line3 of your posted code looks a bit off.
Line7 if the record count, intNoOfIA3, is zero then the Dlookup() returns a null value which you then attempt to assign to the function [fAppendIANum3] type-cast as a string; however, strings do not accept the null value so you get an error.

String in Line17 you start your record-set... I think you should just go ahead and open this record-set from the start and go from there.

I'm not sure why you are adding the query to your definitions, it doesn't appear to be needed.

Let me take a careful look at what it appears you have going on here....

[AJLJ2015]

Hi Zmbd, thank you for the response.

The code works perfectly fine, if the criteria ('For Signature') is listed in the field. It only becomes an error, once the criteria (i.e. for signature) is not listed in the current [Status] field.

the TBXQuery is my table record source (no null fields). (I have to create it, because I am having errors for null values).

The error becomes like this:

syntax error (missing operator) in query expression '[Disc] = yyyy' AND [Status] = 'For Signature'.

[zmbd]

that will be the first error :)

I highly advise reading thru our basic trouble shooting section, I'll PM you a copy of my boiler plate that has that link and other, hopefully, helpful information.

Also, because you've put so much effort into the code you've posted, I thought I'd see if there was a fix.

Try the following....

In a copy of your database (I always recommend a copy!)
From the ribbon, create a new module and then cut and paste the following code (hint, click on the line numbers, then expand, finally Select then <ctrl><C> )

Option Compare Database
Option Explicit
'
Public Function fAppendIANum3(ByVal zINdisc As Long) As String
    Dim zdb As DAO.Database
    Dim zrs As DAO.Recordset
    Dim zsql As String
    Dim zRtrn As String
    Dim zDiscID As Long
    '
    'pull the passed parameter
    zDiscID = zINdisc
    '
    'Setup for the recordset
    Set zdb = CurrentDb
    '
    'I really dislike the star function so I'm going to guess at your fields
    zsql = "SELECT Disc, IA_Number_, Status" & _
        " FROM TBXQuery" & _
        " WHERE([Disc]=" & zDiscID & _
            " AND [Status]='For Signature'"
    '
    'opening a snapshot of the recordset.
    'because it appears we're not editing records.
    'so a little less overhead.
    Set zrs = zdb.OpenRecordset(name:=zsql, type:=dbOpenSnapshot)
    '
    'start the search if there's even one record returned then the count will evaluate as true
    If zrs.RecordCount Then
        '
        'get an accurate record count.
        With zrs
            .MoveLast
            .MoveFirst
            If .RecordCount > 1 Then
                '
                'Read all of the records and return them...
                Do Until .EOF
                    zRtrn = zRtrn & ![IA_Number_] & ","
                Loop
                '
                'chop the last comma
                zRtrn = Left(zRtrn, (Len(zRtrn) - 1))
            Else
                '
                'There's only on record so return the information...
                zRtrn = ![IA_Number_]
            End If
        End With
    Else
        '
        'pass back that nothing was found
        zRtrn = "No Records"
    End If
    '
    'clean up after ourself
zcleanup:
    If Not zrs Is Nothing Then
        zrs.Close
        Set zrs = Nothing
    End If
    If Not zdb Is Nothing Then Set zdb = Nothing
    '
    'return the IA_Numbers, or no records found message
    fAppendIANum3 = zRtrn
    '
    'setup for error trapping
    Exit Function
zerrortrap:
        MsgBox Prompt:="ErrS: " & Err.Source & vbCrLf & _
            "ErrN: " & Err.Number & Err.Source & vbCrLf & _
            "ErrD: " & Err.Description, _
            Title:="Error in record lookup function fAppendIANum3"
        zRtrn = "Error"
        Resume zcleanup
End Function

TO test this:
<ctrl><G> to open the immediate window
?fAppendIANum3( (valid_ID_Here_ where_status_is _good))

Press enter.... try again with a valid id where the status does not equal "For Signature"

I should mention,
+ I used the same name as your current function; thus, you may have to alter one name or the other due to "Ambiguous Name Detected: fAppendIANum3" error...

+ I have assumed that the "Disc" data type is a long integer; therefore, you may have to alter the type-cast for both the passed value variable and the local variables; "zINdisc" and "zDiscID"

+ I'm off to bed now, four kids to wake up and get to Church in a few hours.

Zzzzzzz

[AJLJ2015]

Hi,

the code above still have the same error, this time, error: "data mismatch"..and I tried changing the Disc as String, the error is in Line 26, "missing operator ],..."

to add this function is being called in a query, where each type of signature are grouped (separated by commas) and total counts.

The error comes as stated is when the "criteria", i,e, "for signature" when it is not included in list. The [status] field will all be "Signed" later. Therefore, the changes of status varies. The goal is to execute the count, even the criteria is missing in the current list and return count = 0 and [IA_Number] as "empty" or blank.

The Disc is a text format, the desired results should be like this:



All fields are text format.


Disc(Text) ---CTsigned(count)---IA Signed (Text)
-------------------------------------------------------------
TEXT1--- 0---
TEXT2--- 5--- 002, 005, 012, 014, 020
TEXT3--- 2--- 033, 049
TEXT4--- 2--- 010, 052
TEXT5--- 4--- 054, 059, 063, 073

+++++++++++++++ +++++++++++

And by the way, below is the working SQL if the [status] criteria exists. This desired results should be like this.

SELECT DISTINCT TBXQuery.Disc
   , Sum(IIf([Status]="signed",1,0)) 
      AS CTsigned
   , fAppendIANum([Disc]) 
      AS IASigned
   , Sum(IIf([Status]="On Going",1,0)) 
      AS CTongoing
   , fAppendIANum2([Disc]) 
      AS IAOng
   , Sum(IIf([Status]="For Signature",1,0))
       AS CTForSignature
FROM TBXQuery
GROUP BY TBXQuery.Disc
   , fAppendIANum([Disc])
   , fAppendIANum2([Disc]);

[zmbd]

Just a quick look... once again it's late here and I have an early start in the lab.

Disc(Text)

That maybe the ticket.

To handle the string datatype of my code you will need to alter the following lines in my posted code to read:
Line 1:

Public Function fAppendIANum3(ByVal zInDisc As String) As String

Line9:

Dim zDiscID As String

Line18 thru 21 (noticed a missing ")") :

zsql = "SELECT Disc, IA_Number_, Status" & _
        " FROM TBXQuery" & _
        " WHERE(([Disc]='" & zDiscID & "')" & _
            " AND ([Status]='For Signature'));"

Thus if you pass in "testmevalu e" then zSQL will resolve to

SELECT Disc
   , IA_Number_
   , Status 
FROM TBXQuery 
WHERE(([Disc]='testmevalue') 
   AND 
      ([Status]='For Signature'));

I'll have to take a look at the SQL later.

-z

[AJLJ2015]

Hi Zmbd,

I tried your code still have errors and my database actually "not responding" when running it.. luckily I have a back up, I was able to get the simple fix, finally. Thanks for the kind help and tips.

I will post and ask again if there's any issues that will be encountered later. Thanks again, appreciate the help, I have learned a lot with your tips.

working code replace Line 7 in my original posted code in the beginning,

        'fAppendIANum3 = DLookup("[IA_Number_]", "TBXQuery", "[Disc]=" & Disc & "' AND [Status]='For Signature'")
         fAppendIANum3 = "No Record"

[zmbd]

+ what errors? Hard to TS code w/o that info.
- did you make the changes as indicated in Post#6?
- Thing is, the code I posted compiles and runs fine on my test databases... of course, I don't have your data tables; thus, that might be the difference.

+ "not responding" that could indicate a many issues with the easiest to solve being a complete power off (not a restart) of the PC waiting at least 30sec and rebooting. I've had to do this once or twice over the years when developing... the Gremlins move in and take over the memory :)

+ Yes, your simple fix should work for most things; however, the method you are using creates a lot of overhead and will eventually bloat your database beyond use... and may be the root cause of the slow/not-responding condition you ran into - that is, however, only a guess on my part :)

[AJLJ2015]

Hi,

Yes I did test all you have suggested, but the query do not execute as it "hangs up" without any message of "error". I have to use the task manager to disable it and restart the Database. I wanted to use your code or at least give me an error message so I can state it here. i will further analyze your code for the meantime I will apply this simple fix. Thank you.

[AJLJ2015]

Hi,

might be the cause of hang up was the "select statement" in line 18, without checking the record first if it exists or not? this is only my guess. Thanks

[zmbd]

Doubt if it's line 18 as that is a simple string value, Line 29 checks to see if any record is returned.

Let's make sure the function itself is working as it appears from Post#5 that this function is being called within another query... that refers to the same root query. There-in may be part of the issue.

In a clean copy of your database.
In a new module.
Copy and paste the following:
>> Do not duplicate the first two lines of this code block if they already exist in the new module.

Option Compare Database
Option Explicit
 '
 Public Function fAppendIANum3_24AXW3F(ByVal zINdisc As String) As String
     Dim zdb As DAO.Database
     Dim zrs As DAO.Recordset
     Dim zsql As String
     Dim zRtrn As String
     Dim zDiscID As String
'>
Stop
'>
     '
     'pull the passed parameter
     zDiscID = zINdisc
     '
     'Setup for the recordset
     Set zdb = CurrentDb
     '
     'I really dislike the star function so I'm going to guess at your fields
     
     zsql = "SELECT Disc, IA_Number_, Status" & _
         " FROM TBXQuery" & _
         " WHERE(([Disc]='" & zDiscID & "')" & _
             " AND ([Status]='For Signature'));"
'>
Debug.Print "24AXW3F= " & zsql
'>
     '
     'opening a snapshot of the recordset.
     'because it appears we're not editing records.
     'so a little less overhead.
     Set zrs = zdb.OpenRecordset(Name:=zsql, Type:=dbOpenSnapshot)
     '
     'start the search if there's even one record returned then the count will evaluate as true
     If zrs.RecordCount Then
         '
         'get an accurate record count.
         With zrs
             .MoveLast
             .MoveFirst
             If .RecordCount > 1 Then
                 '
                 'Read all of the records and return them...
                 Do Until .EOF
                     zRtrn = zRtrn & ![IA_Number_] & ","
                 Loop
                 '
                 'chop the last comma
                 zRtrn = Left(zRtrn, (Len(zRtrn) - 1))
             Else
                 '
                 'There's only on record so return the information...
                 zRtrn = ![IA_Number_]
             End If
         End With
     Else
         '
         'pass back that nothing was found
         zRtrn = "No Records"
     End If
     '
     'clean up after ourself
zcleanup:
     If Not zrs Is Nothing Then
         zrs.Close
         Set zrs = Nothing
     End If
     If Not zdb Is Nothing Then Set zdb = Nothing
     '
     'return the IA_Numbers, or no records found message
     fAppendIANum3_24AXW3F = zRtrn
     '
     'setup for error trapping
     Exit Function
zerrortrap:
         MsgBox Prompt:="ErrS: " & Err.Source & vbCrLf & _
             "ErrN: " & Err.Number & Err.Source & vbCrLf & _
             "ErrD: " & Err.Description, _
             Title:="Error in record lookup function fAppendIANum3_24AXW3F"
         zRtrn = "Error"
         Resume zcleanup
 End Function

press <ctrl><g> to open the immediate window in the VBE
In this window, enter the following

?fAppendIANum3_24AXW3F(zINDisc:="zTestz")

The code will enter the debugging state on the Stop command on line-11.

Using <F8> to step thru the code when the yellow (default is yellow :) ) execution cursor runs the Debug.Print (line-27) the resolved SQL will "print" in the immediate window.
>> Cut and paste that resolved SQL in a code block here.

Slowly <F8> thru the remaining code, keeping track of which line you are executing. This time thru the code should skip the loop and drop thru to zRtrn = "No Records" on line-60

If at anytime the execution drops to zerrortrap: (lines-76/77) make note of which line you were on and then continue to press <f8> to allow the code to popup the message box, please post this message, and which line you were on prior to this, and then <f8> to allow the function to clean-up.

Repeat the above steps...

?fAppendIANum3_24AXW3F(zINDisc:="zTestz")

However, replace "zTestz" with a value that you know will return a record from your dataset.
+Posting the SQL that resolves in the immediate window would be appreciated; however, if this contains sensitive information then we can make other arrangements.
+ once again, slowly <f8> thru the code. The loop at line-45 may take a few minutes to finish and normally I would suggest using the "run-to-cursor;" however, in this case we need to make sure that there's not a glitch in the loop.
+ Once again, report any errors and the line

++> Now let's take a look at the two SQLs that we have now generated in the immediate window.
++> From the main Access-GUI,
- Ribbon>Create>Q ueries>Query Design
- Cancel the "Show Table" dialog.
- Right click in the empty tables area and in the quick-menu that opens select "SQL View"
- Cut and paste the first SQL that was created.

hopefully the SQL resembles
SELECT Disc, IA_Number_, Status FROM TBXQuery WHERE(([Disc]='zTestz') AND ([Status]='For Signature'));

- You should be in the Ribbon>Query Tools context menu. In the "Results" group click in the Run command.
>>> this first SQL should return nothing.

++> You should be on the Ribbon>Home tab
+ In the view group, select the drop down, under the view button and select SQL view.
+ Delete the current SQL and replace with the second SQL generated.
+ Once again, you should be in the Ribbon>Query Tools context menu. In the "Results" group click in the Run command.
>>> This should return at least one record.

If either of these steps return anything unexpected then post back what has happened.

(this is basically a modification of the function discussed here: Combining Rows-Opposite of Union>post#6 and in many other threads covering implementations of the Concat() function... available in big RDMS yet we have to emulate it in AccessDB :sigh: )