Problem with "IN" statement 1

[policechiefwiggum]

Hi All,

I've started to get an issue with an IN statement.
Code below

Dim ProjectRowSource As String
If Me.select_tester.Value > "" Then
Me.select_proj.Value = ""
Me.ViewProjectIndex.Value = ""

ProjectRowSource = "SELECT [ProjectTitle] FROM [projects] WHERE [UATTester] = '" & Me.select_tester.Value & "' AND [Status] IN ('Active','Early Engagement','On Hold')ORDER BY [projects].[ProjectTitle]"
Me.select_proj.Value = "Please Select a Project.."
Else
ProjectRowSource = ""
Me.select_proj.Value = "Please Select a Project.."
End If

Me.select_proj.RowSource = ProjectRowSource

This has been working fine, but now its started to stop filtering out projects that have a status of "closed".

I only have 4 possible status's (Active, Early Engaement, On Hold & Closed) so is there anyway i can code it to look for Closed projects and affectivly ignore them?

 

[SkipVought]

Put a SPACE between the ) and ORDER BY

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TheAceMan1]

How are ya policechiefwiggum . . .

[blue][tt]Change: If Me.select_tester > "" Then

To    : [purple][b]If Trim(Me.select_tester & "") <> "" Then[/b][/purple][/tt][/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[policechiefwiggum]

Thanks for your suggestions guys, still no luck!

Me.select_proj.Value = ""
Me.ViewProjectIndex.Value = ""

Dim ProjectRowSource As String
Dim user As String

If Trim(Me.select_tester & "") <> "" Then

ProjectRowSource = "SELECT [ProjectTitle] FROM [projects] WHERE [UATTester] = '" & Me.select_tester.Value & "' AND [Status] IN ('Active','Early Engagement','On Hold') ORDER BY [projects].[ProjectTitle]"
Me.select_proj.Value = "Please Select Project..."
Else
ProjectRowSource = ""
Me.select_proj.Value = "ERROR: No User Defined"
End If

Me.select_proj.RowSource = ProjectRowSource

i have a project that shows as having a status of "Closed" in the database, dispite having this code in place it still shows the project.

as i have multiple users that login, and they can select other users projects i have the above code for both the OnLoad and OnChange for the dropdown box. is this likely to be causing a conflict?

Thanks Again.

 

[SkipVought]

Is there ONE and ONLY ONE row per Project in Projects?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TheAceMan1]

policechiefwiggum . . .

I've added parenthese's in [red]red[/red] and trimmed the code for better viewing (just copy & paste over). Give it a shot.

[blue]   Dim SQL As String
   
   If Trim(Me.select_tester & "") <> "" Then
      Me.select_proj = ""
      Me.ViewProjectIndex = ""
      
      SQL = "SELECT [ProjectTitle] " & _
            "FROM [projects] " & _
            "WHERE [red][b]([/b][/red][UATTester] = '" & Me.select_tester & "'[red][b])[/b][/red] AND " & _
                  "[red][b]([/b][/red][Status] IN ('Active','Early Engagement','On Hold')[red][b])[/b][/red] " & _
            "ORDER BY [ProjectTitle]"
      Me.select_proj = "Please Select a Project.."
   Else
      SQL = ""
      Me.select_proj = "Please Select a Project.."
   End If
   
   Me.select_proj.RowSource = SQL[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[policechiefwiggum]

Thanks everyone for your suggestions.

I'm going to go and sit in the corner with the dunce hat on!!

SkipVought you were right! someone had added another project with the same name in to the DB, and obviously the way i've coded it, hasn't allowed for that to happen! Deleted the 2nd entry and everything works perfectly!

What a muppett i am! Oh well you live and learn - i've learn't not to let idiots loose on my app!!!! ;o)

 

[SkipVought]

Hey, Chief; maybe could you give me one of those magic stickers for my windshield? ;-)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]