IsNull

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

IsNull

Post by adam »

I'm trying to use the following function in the query to get data from access table to excel even if the field is empty

Code: Select all

strSQL = "SELECT [Name],IIf(IsNull([Gender]),[Address],[DoB] FROM tblList WHERE [ID]='" & Me.txtID.Value & "'"
I'm getting the syntax error missing query. What Might I be doing wrong in here?

Any help would be kindly appreciated.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: IsNull

Post by HansV »

You forgot the closing parenthesis of IIf:

Code: Select all

strSQL = "SELECT [Name],IIf(IsNull([Gender]),[Address],[DoB]) FROM tblList WHERE [ID]='" & Me.txtID.Value & "'"
The SQL statement treats ID as a text field. Is that correct?
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: IsNull

Post by adam »

ID is a text field yeah. I'm getting wrong number of arguments used with function in query expression. What may ne the reason for this?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: IsNull

Post by HansV »

I don't see what would cause that, but does this work better?

Code: Select all

strSQL = "SELECT [Name],IIf([Gender] Is Null,[Address],[DoB]) FROM tblList WHERE [ID]='" & Me.txtID.Value & "'"
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: IsNull

Post by adam »

I now get error Item cannot be found in the collection...........highlighting the line

Me.txtGender.Value = rst![Gender]

Do I have to change this line accordingly?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: IsNull

Post by HansV »

If you need Gender in the recordset, you should add it to the list of fields in the SELECT part of the SQL. Currently, you only have an IIf expression that uses Gender, but not Gender as a separate field.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: IsNull

Post by adam »

How may I do that?
Best Regards,
Adam

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: IsNull

Post by adam »

Here's the full code I'm using. How may I do it?

Code: Select all

Private Sub txtID_AfterUpdate()
    
    Dim cnn         As ADODB.Connection
    Dim rst         As ADODB.Recordset
    Dim strSQL      As String
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    If Me.txtID.Value <> "" Then
        
        Set cnn = New ADODB.Connection
        cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
                 "Data Source=D:\VA\Database.accdb;"
        
        Set rst = New ADODB.Recordset
        
        strSQL = "SELECT [Name],IIf([Gender] Is Null,[Address],[DoB]) FROM tblList WHERE [ID]='" & Me.txtID.Value & "'"
        
        rst.Open Source:=strSQL, ActiveConnection:=cnn, Options:=adCmdText
        
        Me.txtName.Value = rst![Name]
        Me.txtGender.Value = rst![Gender]
        Me.txtAddress.Value = rst![Address]
        Me.txtDOB.Value = rst![DOB]
        
        rst.Close
        cnn.Close
        
        Set rst = Nothing
        Set cnn = Nothing
        
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: IsNull

Post by HansV »

You haven't added GENDER to the fields in the SELECT part of strSQL.
Nor have you added Address.
Nor DOB.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: IsNull

Post by HansV »

What is the purpose of IIf([Gender] Is Null,[Address],[DoB]) ? It seems rather weird to me to return either a text string or a date in the same column.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: IsNull

Post by adam »

This is the original select statement:

Code: Select all

strSQL = "SELECT [Name],[Gender],[Address],[DoB] FROM tblList WHERE [ID]='" & Me.txtID.Value & "'"
The query works fine in this way including the following.

Code: Select all

 Me.txtName.Value = rst![Name]
        Me.txtGender.Value = rst![Gender]
        Me.txtAddress.Value = rst![Address]
        Me.txtDOB.Value = rst![DOB]
All I wanted is to run the query even if the column Gender in the access table does not have any values or if it has values. I added the line

Code: Select all

IIf([Gender] Is Null
in the hope it would run the query without errors even if it did not have data.

I'm not having either a text string or a date in the same column. [Name],[Gender],[Address],[DoB] are four separate columns.

I hope I've made my question clear. Would be glad if I get any help on this.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: IsNull

Post by HansV »

The original SQL is the correct one. The later one makes no sense, and there is no need to add anything for if Gender is null.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: IsNull

Post by adam »

But if there's no value in column gender, I'm getting the error message could not set the value property. Type mismatch error?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: IsNull

Post by HansV »

Does this work?

Me.txtGender.Value = rst![Gender] & ""
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: IsNull

Post by adam »

Perfect!!! Thanks a lot Hans. It worked perfect :-)
Best Regards,
Adam