Filter report by fields with Null value (1 Viewer)

I mentioned you should pay attention to spaces and concatenation. Take a look at the red part of the code I posted and take your time to work it out.
 
I mentioned you should pay attention to spaces and concatenation. Take a look at the red part of the code I posted and take your time to work it out.


i have tried everything i can think of, please tell me :)
 
I've fixed the AND part. So this is what I wrote in my other post which points out the problem:
Code:
         strFilter = "[Total Hours Worked] Is Null[COLOR=Red]" And "[/COLOR] & _
                        "[Engineer 1] = '" & Me!cbrEngineer & "[COLOR=Red]'Or "[/COLOR] & _
And here's the fixed AND part:
Code:
         strFilter = "[Total Hours Worked] Is Null [COLOR=Blue]And "[/COLOR] & _
                        "[Engineer 1] = '" & Me!cbrEngineer & "[COLOR=Red]'Or "[/COLOR] & _
Can you see what was done? Can you see there was a space and concatenation issue? Now I want you to fix the OR issue.
 
There's such a thing as trial and error. There needs to be a space somewhere. Put some effort into it. ;)
 
I'm doing it because it's xmas ;)
Code:
         strFilter = "[Total Hours Worked] Is Null [COLOR=Blue]AND "[/COLOR] & _
                        "[Engineer 1] = '" & Me!cbrEngineer & "[COLOR=Blue]' OR "[/COLOR] & _
Can you see it now?
 
I'm doing it because it's xmas ;)
Code:
         strFilter = "[Total Hours Worked] Is Null [COLOR=blue]AND "[/COLOR] & _
                        "[Engineer 1] = '" & Me!cbrEngineer & "[COLOR=blue]' OR "[/COLOR] & _
Can you see it now?

Ive tried that... it doesnt work
 
You still have problems with your other code lines. You will need to fix those too.
 
You still have problems with your other code lines. You will need to fix those too.

I have
Code:
 ElseIf Me.chkIncompleted = True And Me.cbrEngineer & "" <> "" Then
            strFilter = "[Total Hours Worked] Is Null And " & _
                        "[Engineer 1] = '" & Me!cbrEngineer & "' OR " & _
                        "[Total Hours Worked] Is Null" And " & _
                        "[Engineer 2] = '"  Me!cbrEngineer & "' OR " & _
                        "[Total Hours Worked] Is Null And " & _
                        "[Engineer 3] = '" & Me!cbrEngineer & "'"
                DoCmd.Close
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
 
Seriously lovett, we're spending too long on this. Look at your code again, you haven't fixed it all.
 
Code:
 ElseIf Me.chkIncompleted = True And Me.cbrEngineer & "" <> "" Then
            strFilter = "[Total Hours Worked] Is Null And " & _
                        "[Engineer 1] = '" & Me!cbrEngineer & "' OR " & _
                        "[Total Hours Worked] Is Null And " & _
                        "[Engineer 2] = '"  Me!cbrEngineer & "' OR " & _
                        "[Total Hours Worked] Is Null And " & _
                        "[Engineer 3] = '" & Me!cbrEngineer & "'"
                DoCmd.Close
        DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
 
You just posted code without explaining whether it's working or not. We're not psychic you know (even if we try to be) ;)

Why is this repeated three times?

"[Total Hours Worked] Is Null And "
 
You just posted code without explaining whether it's working or not. We're not psychic you know (even if we try to be) ;)

Why is this repeated three times?

"[Total Hours Worked] Is Null And "

if it worked i wouldnt post it ;) and because otherwise it does

hours worked and engineer1
Or
Engineer 2
Or
Engineer3
 
We've seen otherwise from other posters.

Alright, I get you:
Code:
            strFilter = "[Total Hours Worked] Is Null And (" & _
                        "[Engineer 1] = '" & Me!cbrEngineer & "' OR " & _
                        "[Engineer 2] = '"  Me!cbrEngineer & "' OR " & _
                        "[Engineer 3] = '" & Me!cbrEngineer & "')"
If that doesn't work then cbrEngineer is returning Null or zero-length string or there's some other code above that's causing the syntax error.
 
We've seen otherwise from other posters.

Alright, I get you:
Code:
            strFilter = "[Total Hours Worked] Is Null And (" & _
                        "[Engineer 1] = '" & Me!cbrEngineer & "' OR " & _
                        "[Engineer 2] = '"  Me!cbrEngineer & "' OR " & _
                        "[Engineer 3] = '" & Me!cbrEngineer & "')"
If that doesn't work then cbrEngineer is returning Null or zero-length string or there's some other code above that's causing the syntax error.

it gives syntax error still, and highlights that code, and when i take the code out the rest of the code runs fine
 
Re-read the last sentence in my last post I mentioned two cases.
 

Users who are viewing this thread

Back
Top Bottom