VBA SQL with SELECT Statement (1 Viewer)

[cstickman]

Good Evening,

I am trying to construct a subform on a navigation form that pulls in a criteria on an SQL SELECT Statement. I am having a hard time with pulling in "Is Null". I want the query to return based on the username and if the other column is blank (no date entered), then have it sort by another column. Here is the code thus far:

 Private Sub Form_Load()
Dim user As String
Dim Task As String
Dim userLevel As String
Dim finalDate As String
 
finalDate = Nz(finalreport, "")
user = VBA.Environ("UserName")
userLevel = DLookup("[calendarname]", "tbluser", "[login] = '" & user & "'") & ""
 Task = "Select * from tblecalendar Where (assigned = '" & userLevel & "' finalreport = finalDate)"
Me.RecordSource = Task
 End Sub

The column with the blank values is called finalreport and I would like it to sort by auditid column.

Any help or suggestions would be greatly appreciated. Thanks!!

 

[Grumm]

Not 100% sure what you try to explain with that is null. But here is what i guess :

"select * from tblecalendar where (assigned = '" & userLevel & "' and (finalreport="&finalDate&" or finalreport is Null) Order by auditid"

So what i think the problem is that you didn't put the last criteria (finalDate) between &xxx&.

Now you tried to find all the users where finalreport equals finalDate (not the variable but the word).
I hope that is clear enough

 

[cstickman]

Grumm,

Thanks for helping me out. After your suggestion it got me on the right track. Below is the code that worked:

 Private Sub Form_Load()
Dim user As String
Dim Task As String
Dim userLevel As String
 user = VBA.Environ("UserName")
userLevel = DLookup("[calendarname]", "tbluser", "[login] = '" & user & "'") & ""
 Task = "Select * from tblcalendar Where (assigned = '" & userLevel & "' AND tblcalendar.finalreport Is Null) order by tblcalendar.auditid;"
 Me.RecordSource = Task
 End Sub

Thanks for your help!!