CODEWITHSUNDEEP
sqlvbams-accessms-access-2010
EStraka
EStraka

Reputation: 373

Access VBA Test for Null in Date Field

I'm trying to write a VBA script to restrict what is displayed in a list box on a form in Access 2010.

I've boiled down the situation to reduce the number of opportunities for problems so this is a simplified version of what I'm doing

 strMedicaid = "[MCO] = 'Amerigroup' AND [DateAssigned] Is Null"


 Me.lstMedicaid.RowSource = "SELECT DISTINCT tblSamples.MedicaidNumber " & _
                        "FROM tblSamples " & _
                        "WHERE " & strMedicaid & _
                        "ORDER BY tblSamples.MedicaidNumber;"

DateAssigned is a Date Field. The code returns nothing. No results.

If I do just "[MCO] = 'Amerigroup'" I get the expected results so that's not the problem.

I've tried 'Null'. I've tried 'Is Null'. No difference.

If I go into the Access Query builder and build the SQL below:

 SELECT tblSamples.MCO, tblSamples.DateAssigned
 FROM tblSamples
 WHERE (((tblSamples.MCO)="Amerigroup") AND ((tblSamples.DateAssigned) Is Null));

It works. I get the results I want/expect.

Please help me out with getting the VBA to return the same results I get through the query builder. Thanks in advance.

Upvotes: 0

Views: 2179

Answers (2)

mwolfe02
mwolfe02

Reputation: 24227

As written, your code will produce the following string:

...WHERE [MCO] = 'Amerigroup' AND [DateAssigned] Is NullORDER BY...

Notice there is no space between the word Null and ORDER BY. This is an easy mistake to make when concatenating strings and using line continuations. One way to help avoid the mistake is to put a leading space at the beginning of each line:

Me.lstMedicaid.RowSource = " SELECT DISTINCT tblSamples.MedicaidNumber " & _
                           " FROM tblSamples " & _
                           " WHERE " & strMedicaid & _
                           " ORDER BY tblSamples.MedicaidNumber;"

Upvotes: 2

Tyler
Tyler

Reputation: 750

I think this will work.

strMedicaid = "[MCO] = 'Amerigroup' AND [DateAssigned] <> ''"

Upvotes: 0

Related Questions