Query with a Null value isnt working (1 Viewer)

[Prysson]

I am trying to build an ADODB recordset.

Here is my code

Set Recordset = New ADODB.Recordset

With Recordset
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseServer
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "Select * From tblContact Where tblContact.FirstName = " & Chr(34) & Me!txtFirstName & Chr(34) & " AND tblContact.LastName = " & Chr(34) & Me!txtLastName & Chr(34) & " AND tblContact.MiddleInitial = " & Chr(34) & Me!txtMiddleInitial & Chr(34) & ";"

End With

Now the txtfields on the form require a value for First Name and Last Name

But Not for Middle Initial

(this is handle in the form code)

So when this code is run to build a recordset the value for txtMiddleInitial MIGHT be null.

If it isnt...the code works fine.

For example

If I have a record in the contact table whose first name is Joe
Last name is Somebody
and Middle initial is Q

and the txt fields were filled out with those values it would successfully return the record.

BUT if I have a record for Joe Sombody with NO middle initial value and the txtMiddleInitial field is empty...

That Should return the correct record.

Instead it returns an EOF which is NOT the case.

Any ideas why this isnt working.

I am stumped.

 

[pdx_man]

Try having the middle initial clause added conditionally:


MidInit = IIf(IsNull(Me!txtMiddleInitial),""," AND tblContact.MiddleInitial = " & Chr(34) & Me!txtMiddleInitial & Chr(34) )
.Open "Select * From tblContact Where tblContact.FirstName = " & Chr(34) & Me!txtFirstName & Chr(34) & " AND tblContact.LastName = " & Chr(34) & Me!txtLastName & Chr(34) & MidInit

 

[Prysson]

Sort of works but not quite.

Thanks for the reply..I appreciate the time.

The solution you gave almost works but not quite.


What occurs with the code you gave me is that If the Middle Initial text box is Null the it basically pulls up any values that have the same last name and first name..

Thus is the there is a record with a first name X and last name Z and a middle initial of Y then and the user only enters in First Name X and Last Name Z the query would pull up X Y Z as a valid record...but what it should do is accept only a record of X Z with no middle inital as a valid record.

The logic may seem strange and it may be difficult to imagine a senario that needs this...but one thing to consider is that the purpose of the query is not simply to look up the record...but instead is to look up the record to determine if the data being entered is duplicated.

The irony is that if the value of the record field in the table is Null you would think that doing a query on the table for a Null value would work...but it just Nulls out the whole value and returns no records.

 

[pdx_man]

Modify the first part:

MidInit = IIf(IsNull(Me!txtMiddleInitial),""," AND tblContact.MiddleInitial = " & Chr(34) & Me!txtMiddleInitial & Chr(34) )


MidInit = IIf(IsNull(Me!txtMiddleInitial)," AND tblContact.MiddleInitial IS NULL"," AND tblContact.MiddleInitial = " & Chr(34) & Me!txtMiddleInitial & Chr(34) )

 

[Prysson]

Excellent!!

It worked...Thank you. Thank you. Thank you.

I have been banging my head on the wall over that for two days now.

Much appreciation!!

 

[pdx_man]

Happy to help.

 

[Prysson]

Ran into a problem trying to utilize the same concept

The real change comes in that the string I build off of the Iff statement does NOT have any preceding Where conditions in the SQL statement...


.Open "Select * From tblContactCompany Where & CompVal"

Is the sql statement

I set the value of the CompVal with this just prior

CompVal = IIf(IsNull(Me!txtCompany), " tblContactCompany.Company IS NULL", " tblContactCompany.Company = " & Chr(34) & Me!txtCompany & Chr(34))


But for some reason it doesnt like the syntax of the sql statement...coudl you help me identify where my statement is wrong.

 

[pdx_man]

Try:

.Open "Select * From tblContactCompany Where & CompVal"

.Open "Select * From tblContactCompany Where " & CompVal

Now, if you don't have any criteria and want to return all of them, you will have to include the Where clause in your IIF statement.

CompVal = IIf(IsNull(Me!txtCompany), "", " WHERE tblContactCompany.Company = " & Chr(34) & Me!txtCompany & Chr(34))

.Open "Select * From tblContactCompany" & CompVal

 

[Prysson]

Cool

Worked great...just needed to move that " to after the Where.

Thank a bundle again!!!