Criteria Search - Confused me!

[Wolff]

Hi All,

I'm creating a small database at work (Access 2000) and want to provide a report function with the option of chooosing multiple criteria. Now, i've done this before on a much larger database, and had absolutely zero problems, but on this database there is one criteria selection which causes zero records to be returned

So, the data all comes from 1 table, tbl_Servers. There are 5 fields -
Server_name (text)
Start_time (short time)
End_Time (short time)
Reboot_Method (text)
Day (text)

I can get the query to work for Server_Name, Start_Time, End_Time and Day, or any combination thereof, but as soon as I enter a Reboot_Method, I get zero records returned.

The options for Reboot_Method are all taken from the table, using a Select Distinct statement, and are as follows:
Auto
DailyScheduledReboot
Manual

The code I am using is as follows, but can anyone explain why the Reboot_Method part isn't working??

    Dim strWhere As String
    Dim stDocName As String
    Dim blnTrim As Boolean
     
    'if Owner
    If Not IsNull(Me.Combo15) Then
        strWhere = strWhere & "tbl_servers.[owner]= '" & Me.Combo15 & "' And "
        blnTrim = True
    End If
    
    'If Start Time
    If Not IsNull(Me.Combo5) Then
        strWhere = strWhere & "tbl_servers.[start_time]= #" & Me.Combo5 & "# And "
        blnTrim = True
    End If
    
    'If End Time
    If Not IsNull(Me.Combo7) Then
        strWhere = strWhere & "tbl_servers.[End_time]= #" & Me.Combo7 & "# And "
        blnTrim = True
    End If
    
    'if Reboot Method
    If Not IsNull(Me.Combo11) Then
        strWhere = strWhere & "tbl_servers.[reboot_method]= '" & Me.Combo11 & "' And "
        blnTrim = True
    End If
    
    'If Day
    If Not IsNull(Me.Combo13) Then
        strWhere = strWhere & "tbl_servers.[Day]= '" & Me.Combo13 & "' And "
        blnTrim = True
    End If
        
    If blnTrim Then
        strWhere = Left(strWhere, Len(strWhere) - 5)
    End If
    
    stDocName = "rpt_server_details"
    DoCmd.OpenReport stDocName, acPreview, , strWhere
     
    Exit Sub

And yes, I know I should change the names of the combo boxes to something more meaningful, but at the moment i'm just trying to get the above to work for Reboot_method!!

Many thanks in advance!
Chris

 

[Galaxiom]

Are you sure the table doesn't have a lookup for that field?
You would need to enter the bound value in the SQL not the displayed string.

The values certainly should be held as numbers and looked up for display on the report.

 

[Wolff]

No, no Lookup.... its setup exactly the same way as the Day field and Owner field (which work)

 

[Wolff]

Sussed it... something you said there made me wonder if the bound column was correct - it was set to 0, changed it to 1 and everything was fixed.

I thought column counts started at 0 for drop down boxes? And I am only returning one columns worth of data.... ah well! Must have been the haircut at the weekend letting the brain cells escape (or the copious amounts of alochol!!!)

Thanks for response!!

Cheers
Chris

 

[Galaxiom]

Combo11 could contain a Null String. This would cause ="" to be included in the where clause and return no records.

You should test for both Null and Null String all text controls and fields.

If [textCombo] & "" <> "" Then

 

[Galaxiom]

I thought column counts started at 0 for drop down boxes? And I am only returning one columns worth of data
Chris

They do start counting at zero.
You should also be using the numeric column in your search criteria because it is much, much faster than searching with a string.

 

[Wolff]

They do start counting at zero.
You should also be using the numeric column in your search criteria because it is much, much faster than searching with a string.

I understand what you are saying, but the table that we are querying only stores the string - as I said, its a very quick very small (300 rows of data which is unlikely to grow) and very simple database. If I get the time i'll get it setup correctly, but its kind of a rush job at the moment! I'm sure there's plenty of other improvements that could and will be made, and no-doubt it will grow to include a lot more functionality!!

Thanks for your help and suggestions

Cheers
Chris

 

[Galaxiom]

The table should be storing a numeric code while reports and forms should have a lookup.

I would strongly encourage you to put this right before you continue. It will only become more problematic the longer you leave it. These are the foundations of the data structure.