Help with working, but buggy code

[lovett10]

Here's some code to get you started:

    Dim strWhere As String
 
    ' Quote Number
    If Len(Me.QuoteNumber & vbNullString) <> 0 Then
        strWhere = "[QuoteNumber] = " & Chr(34) & Me.QuoteNumber & Chr(34)
    End If
 
    ' Engineer Name
    If Len(Me.EngineerName & vbNullString) <> 0 Then
        strWhere = "[EngineerName] = " & Chr(34) & Me.EngineerName & Chr(34)
    Else
        If Len(strWhere) <> 0 Then
            strWhere = strWhere & " AND " & "[EngineerName] = " & Chr(34) & Me.EngineerName & Chr(34)
        Else
            strWhere = "[EngineerName] = " & Chr(34) & Me.EngineerName & Chr(34)
        End If
    End If

That looks better, although how wouldi link two together like for example if they are searching for quote number between date 01/12/2011 and 05/12/2011 (UK timezone)

 

[vbaInet]

* If both dates were entered, it should find BETWEEN the dates.
* If Start Date has a date and End Date is empty it should find dates >= Start Date
* If End Date has a date and Start Date is empty it should find dates <= End Date
* If both are empty it should ignore

Something like the above?

 

[lovett10]

* If both dates were entered, it should find BETWEEN the dates.
* If Start Date has a date and End Date is empty it should find dates >= Start Date
* If End Date has a date and Start Date is empty it should find dates <= End Date
* If both are empty it should ignore

Something like the above?

Yes but aswell as that it needs to use multiple search criteria.

for example they may want to find the dates Engineer X was working in december.

So they would select Engineer X and then the dates and hit search.
How would i represent this in my code and make it work?

Much appreciated

 

[vbaInet]

By the way, you don't need to quote my posts all the time. It's making the thread pretty long

Yes I know that it will include other search criteria but I was clarifying the situation for the date field alone. Is that how it should behave for the date field?

 

[lovett10]

Haha ok sorry

And yes that is exactly how i would like the date search part to work.

 

[vbaInet]

Amended code:

    Dim strWhere As String
    Dim strDates As String
    
    ' Quote Number
    If Len(Me.QuoteNumber & vbNullString) <> 0 Then
        strWhere = "[QuoteNumber] = " & Chr(34) & Me.QuoteNumber & Chr(34)
    End If
    
    ' Engineer Name
    If Len(Me.EngineerName & vbNullString) <> 0 Then
        If Len(strWhere) <> 0 Then
            strWhere = strWhere & " AND " & "[EngineerName] = " & Chr(34) & Me.EngineerName & Chr(34)
        Else
            strWhere = "[EngineerName] = " & Chr(34) & Me.EngineerName & Chr(34)
        End If
    End If
    
    ' Validate date textboxes
    If IsDate(Me.StartDate) Then
        strDates = Me.StartDate
    End If
    
    If IsDate(Me.EndDate) Then
        If Len(strDates) <> 0 Then
            strDates = "BETWEEN " & strDates & " AND " & Me.EndDate
        Else
            strDates = "<= " & Me.EndDate
        End If
    Else
        If Len(strDates) <> 0 Then
            strDates = ">= " & strDates
        End If
    End If
    
    If Len(strDates) <> 0 Then
        If Len(strWhere) <> 0 Then
            strWhere = strWhere & " AND " & "[DateField] " & strDates
        Else
            strWhere = "[DateField] " & strDates
        End If
    End If

strWhere will contain the built-up criteria. You may need to format the dates but that can be done later.

Also, if you know how to call code once, e.g. in the Load event of the form, then you can shorten the Dates validation routine.

Oh, I amended the Engineer Name part as well because I noticed one part of it wasn't necessary. It's all aircode.

 

[lovett10]

=strWhere will contain the built-up criteria. You may need to format the dates but that can be done later.

Also, if you know how to call code once, e.g. in the Load event of the form, then you can shorten the Dates validation routine.

Oh, I amended the Engineer Name part as well because I noticed one part of it wasn't necessary. It's all aircode.

Im sorry but im not really particularly good wth code. whats aircode, and ive heard of it but wouldnt know how to call code once

sorry for being a noob and appreciate the help

 

[vbaInet]

Aircode is code that hasn't been tested and was written to convey logic.

Do you not understand the parts you need to replace?

 

[lovett10]

Aircode is code that hasn't been tested and was written to convey logic.

Do you not understand the parts you need to replace?

Im afraid not, ive been trying to learn as i go along. I think i should watch a few tutorials.

Do you know of any good ones? Do you know if the lynda.com one is any good?

 

[vbaInet]

I thought you wrote the code in your first post?

 

[lovett10]

I thought you wrote the code in your first post?

I did but that was from mostly trial and error and putting together other code on the internet

 

[vbaInet]

I gave you three blocks of code. All you need to do was replace the field names and control names to match what you have in your db. Everything else stays the same.

 

[lovett10]

I gave you three blocks of code. All you need to do was replace the field names and control names to match what you have in your db. Everything else stays the same.

Yea thats great and thanks for that but i want to learn so in the future i dont have to annoy people like you by being dumb

 

[vbaInet]

Well, get it working first then step through the code to understand it. Once you've done that you can ask for clarification on parts of the code you don't understand.

 

[lovett10]

Ok thanks very much for your help ill look into the code. It will be atleast tomorrow before i speak again as i leave off soon

Thanks again