Like and Dates

[CanWest]

I have a report that has a couple of fields that I wish to use criteria to pull from a text box on a form

The first field is called EnteredBy and has the following text in the criteria line of the query that loads the report

Like "*" & [Forms]![frm_MainMenu2]![frm_Reports].[Form]![cboCdc] & "*"

This works perfectly

The next field is called DateEntered and it has the following text in the criteria line

Between [Forms]![frm_MainMenu2]![frm_Reports].[Form]![txtCDCContactNotesStartDate]_
 And [Forms]![frm_MainMenu2]![frm_Reports].[Form]![txtCDCContactNotesEndDate]

This also works perfect IF there are dates in both fields.

I want to be able to have the user leave one or the other or both of the date fields blank similar to the Like in the Entered by field.

Is this possible?

 

[namliam]

It is possible, however when you are setting up more and more of these kinds of filters it is going to be far more efficient (and it is also going to prevent a lot of issues) to "Filter by form" and inside the form have some code to build your where clause dynamicaly ...

Research it a bit and come back with any questions you run into.

 

[CanWest]

I am afraid I have no idea what you are talking about. You mention filter by form when I am trying to open a report. I do not see how one has anything to do with the other. I am pulling the criteria from a form and it is the criteria I am having trouble with. If I am missing something here please enlighten me on what to research.

 

[namliam]

llike I suggested, research "Filter by form" your form can (when you click a button for example) use the docmd.OpenReport and pass a Where clause to your report that is custom build using some VBA.

When you want to be able to use flexible input from your form into your report this is the best way of doing it... Sure "your way" will work to a point but will cause lots of grief and headaches while the filter by form method is way more flexible and easier once you get going.

Loads of samples can be found on bob's website: http://www.btabdevelopment.com/ts/samples

This one may be of particular intrest:
http://downloads.btabdevelopment.com/Samples/combos/FormSampleFromMultipleCriteria.zip

Let me know if you have any (additional) questions.

 

[CanWest]

I am so far lost I think I am ahead. I have looked at this example and can not even get the first part to work. That is where the stWhere should be using cboCdc for part of the filter. I am taking baby steps with this to get one thing working at a time.

Here is my code so far

    Dim stDocName As String
    Dim stWhere As String
    Dim stDates As String
    Dim blnTrim As Boolean
    
    stDocName = "rpt_CDCContactNotesActivity"
            
        If Not IsNull(Me.cboCdc) Then
            
            stWhere = [EnteredBy] Like Me.cboCdc
            
        End If
  
    DoCmd.OpenReport stDocName, acPreview, , stWhere

This works if there is nothing in cbcCdc but if a name is selected I get this error

can't find the field '|1' referred to in your expression. For information sake this is a text field. There is only one column in cboCdc

Next I Tried

    Dim stDocName As String
    Dim stWhere As String
    Dim stDates As String
    Dim blnTrim As Boolean
    
    stDocName = "rpt_CDCContactNotesActivity"
            
        If Not IsNull(Me.cboCdc) Then
            stWhere = "[EnteredBy] =" & """ & Me.cboCdc & """
            
        End If
  
    DoCmd.OpenReport stDocName, acPreview, , stWhere

The " inside 2 " is because the string in cboCdc contains a comma ie Smith, John. This too worked if the cboCdc was blank but it a person was selected I was asked for a parameter Value. For fun I entered a valid value for cbdCdc and no records were returned.

 

[namliam]

Close but yeah, you need to know that you need to enclose both date and text fields, leave number fields untouched....
So assuming EnteredBy is a

number field:     stWhere = "[EnteredBy] =" & Me.cboCdc 
Text field (m) :  stWhere = "[EnteredBy] =" & """" & Me.cboCdc & """"
Text field (alt): stWhere = "[EnteredBy] =" & "'" & Me.cboCdc & "'"
Date field:       stWhere = "[EnteredBy] =" & "#" & Me.cboCdc & "#"

Inside the double quotes you need to "escape" the double quotes which is why you need them 2 times making a total of four
It is also (offcourse) possible to do it like:

Text field (m) :  stWhere = "[EnteredBy] =""" & Me.cboCdc & """"

Edit: Pft the sample DB doesnt even show this and is even bugged when working with dates....

 

[CanWest]

I have tried

stWhere = "[EnteredBy] =" & """" & Me.cboCdc & """"

and
stWhere = "[EnteredBy] =" & "'" & Me.cboCdc & "'"

and
stWhere = "[EnteredBy] =" & """ & Me.cboCdc & """

All three work when cboCdc is blank. But when a name was selected I got a parameter prompt for EnteredB. Then a light bulb lit up and burnt the inside of my skull so I took a look at the report itself and lo and behold the field is named CreatedBy not EnteredBy. With this fixed ....

stWhere = "[EnteredBy] =" & "'" & Me.cboCdc & "'"

and
stWhere = "[EnteredBy] =" & """ & Me.cboCdc & """

Worked but ...

stWhere = "[EnteredBy] =" & """" & Me.cboCdc & """"

did not.

Thank you so very much. Can you tell what the difference between Text field (m) and Text Field (Alt) is?

Now on to the date fields.....

 

[namliam]

VERY curious, Where = "[EnteredBy] =" & """ & Me.cboCdc & """, should NOT work at all (not properly anyways)

The difference basicaly is the use of " vs that of ', which in a large part has to do with what you see in code.... But also with your where clause and your table content.

Any column that can contain a ', you shouldnt wrap in 'Column' because it might become 'Col'umn' which is going to cause obvious issues. Same is true for " but reversed, though the """" & YourColumn & """"" looks confusing its usually the way to go....

 

[CanWest]

I'm Back.

So I am now tackling the two txt controls that are used to specify a from date, a to date or a between two dates.

The between two dates works perfectly but the from a date or to a date returns all records regardless of the dates.

I must add that if I incorporate cboCDC in the mix it has no bearing as to wether or not the rest works.

Here is the current code

    Dim stDocName As String
    Dim stWhere As String
    Dim stDates As String
    Dim blnTrim As Boolean
    
      [COLOR="Red"] 'This seems to work perfectly in all combinations[/COLOR]
        If Not IsNull(Me.cboCdc) Then
            stWhere = "[CreatedBy] =" & "'" & Me.cboCdc & "'" & " And "
            blnTrim = True
        End If

        [COLOR="red"]'This on the other hand does not seem to have any effect at all[/COLOR]
        If IsNull(Me.txtCDCContactNotesStartDate) And Me.txtCDCContactNotesStartDate = "" Then
            If Not IsNull(Me.txtCDCContactNotesEndDate) And Me.txtCDCContactNotesEndDate <> "" Then
                stWhere = stWhere & "[DateEntered]  <=" & Me.txtCDCContactNotesEndDate & "#"
                blnTrim = False
            End If
        Else
            If IsNull(Me.txtCDCContactNotesEndDate) And Me.txtCDCContactNotesEndDate = "" Then
                If Not IsNull(Me.txtCDCContactNotesStartDate) And Me.txtCDCContactNotesStartDate <> "" Then
                    stWhere = stWhere & "[DateEntered]>=" & Me.txtCDCContactNotesStartDate
                    blnTrim = False
                End If
            Else
     
                [COLOR="red"]' And this too seems to work in all combinations[/COLOR]
                If (Not IsNull(Me.txtCDCContactNotesStartDate) And Me.txtCDCContactNotesStartDate <> "") And (Not IsNull(Me.txtCDCContactNotesEndDate) Or Me.txtCDCContactNotesEndDate <> "") Then
                stWhere = stWhere & "[DateEntered] Between #" & Me.txtCDCContactNotesStartDate & "# And #" & Me.txtCDCContactNotesEndDate & "#"
                blnTrim = False
                End If
            End If
        End If

        If blnTrim Then
            stWhere = Left(stWhere, Len(stWhere) - 5)
        End If
    
    stDocName = "rpt_CDCContactNotesActivity"
    DoCmd.OpenReport stDocName, acPreview, , stWhere

I apologize for the long names of some of my controls but I need to be able to identify them by their name.

 

[CanWest]

VERY curious, Where = "[EnteredBy] =" & """ & Me.cboCdc & """, should NOT work at all (not properly anyways)

You are correct ..
Where = "[EnteredBy] =" & """ & Me.cboCdc & """ did not work

but

Where = "[EnteredBy] =" & "'" & Me.cboCdc & "'" did work as expected

 

[namliam]

Carefull...

stWhere = stWhere & "[DateEntered]>=" & Me.txtCDCContactNotesStartDate

is not using a proper date anotation, it may be working but is depeding on access "smarts" to do so.

 

[CanWest]

Carefull...

stWhere = stWhere & "[DateEntered]>=" & Me.txtCDCContactNotesStartDate

is not using a proper date anotation, it may be working but is depeding on access "smarts" to do so.

Changed it to

stWhere = stWhere & "[DateEntered]>=" & Me.txtCDCContactNotesStartDate & "#"

But that did not fix anything. You mentioned earlier that the date section of Bob's sample was flawed. Remembering that I am a work in progress I am having trouble understanding the logic behind the Only start date or only end date section. Any guidance would be greatly appreciated.

 

[namliam]

Yes I did mention the sample is bugged... but also told you how any dates should work.

Close but yeah, you need to know that you need to enclose both date and text fields, leave number fields untouched....
So assuming EnteredBy is a

number field:     stWhere = "[EnteredBy] =" & Me.cboCdc 
Text field (m) :  stWhere = "[EnteredBy] =" & """" & Me.cboCdc & """"
Text field (alt): stWhere = "[EnteredBy] =" & "'" & Me.cboCdc & "'"
Date field:       stWhere = "[EnteredBy] =" & "#" & Me.cboCdc & "#"

Much like text fields always have 'SomeText' around them, dates always should have #10/17/2013# around them... Also note that the date is in US format MM/DD/YYYY

 

[CanWest]

I see that mistake and have changed the three where statements to

stWhere = stWhere & "[DateEntered]  <=" & "#" & Me.txtCDCContactNotesEndDate & "#"

stWhere = stWhere & "[DateEntered]>=" & "#" & Me.txtCDCContactNotesStartDate & "#"

stWhere = stWhere & "[DateEntered] Between #" & "#" & Me.txtCDCContactNotesStartDate & "#" & "# And #" & "#" & Me.txtCDCContactNotesEndDate & "#"

But still no joy. The sections of the code that deal with dates only one works. The between. The <= and the >= have no effect what so ever.

 

[namliam]

stWhere = stWhere & "[DateEntered] Between #" & "#" & Me.txtCDCContactNotesStartDate & "#" & "# And #" & "#" & Me.txtCDCContactNotesEndDate & "#"
Is broken, can you tell me whats wrong?

How are you entering your dates in the txt fields on your form?

 

[CJ_London]

@Namliam - do you mean a double #?

Between #" & "#" &

 

[CanWest]

stWhere = stWhere & "[DateEntered] Between #" & "#" & Me.txtCDCContactNotesStartDate & "#" & "# And #" & "#" & Me.txtCDCContactNotesEndDate & "#"
Is broken, can you tell me whats wrong?

How are you entering your dates in the txt fields on your form?

It should be ...

stWhere = stWhere & "[DateEntered] Between #" & Me.txtCDCContactNotesStartDate & "# And #" & Me.txtCDCContactNotesEndDate & "#"

because there already is a # inside the

stWhere = stWhere & "[DateEntered] Between #" ......

I enter the dates in mm/dd/yyyy format ie: 10/17/2013

 

[CanWest]

So now the between is working again but the one that uses only the start date and the one that uses only the end date still do not. They act as if no dates had been entered at all

 

[namliam]

:banghead:
Not sure what goes wrong here... any chance of posting a mockup db? or your db?

Last ditch resort....
stDocName = "rpt_CDCContactNotesActivity"
debug.print stWhere 'Insert this line
DoCmd.OpenReport stDocName, acPreview, , stWhere

Find the line printed in your immediate window and post it here.

My best guess right now is that your If's are causing issues... Yeah defonately
If IsNull(Me.txtCDCContactNotesStartDate) And Me.txtCDCContactNotesStartDate = ""

You cannot do AND here because either of the statements will be false.... Change the AND to OR ....
or change your if to....
If NZ(Me.txtCDCContactNotesStartDate,"") = ""

Curiously enough you do have the OR on the Between .... where you need need it for the enddate

 

[CanWest]

Alas it is working. Changing the and to or did the trick. I was wondering about that because the if statement seemed to be asking for the date field to be in two different states at the same time. Here is the complete code fully functional and working.

    Dim stWhere As String
    Dim stDates As String
    Dim blnTrim As Boolean
    
       'Detrminis if a CDC has been selected and if so only shows records
       'for that CDC
       If Not IsNull(Me.cboCdc) Then
            stWhere = "[CreatedBy] =" & "'" & Me.cboCdc & "'" & " And "
            blnTrim = True
        End If
  
        'Check to see if only an Endate has been entered and if so shows
        'only records before that end date
        If IsNull(Me.txtCDCContactNotesStartDate) Or Me.txtCDCContactNotesStartDate = "" Then
            If Not IsNull(Me.txtCDCContactNotesEndDate) Or Me.txtCDCContactNotesEndDate <> "" Then
                stWhere = stWhere & "[DateEntered]  <=" & "#" & Me.txtCDCContactNotesEndDate & "#"
                blnTrim = False
            End If
        Else
            'Checks to see if only a start date has been entered and if so
            'shows records after that start date
            If IsNull(Me.txtCDCContactNotesEndDate) Or Me.txtCDCContactNotesEndDate = "" Then
                If Not IsNull(Me.txtCDCContactNotesStartDate) And Me.txtCDCContactNotesStartDate <> "" Then
                    stWhere = stWhere & "[DateEntered]>=" & "#" & Me.txtCDCContactNotesStartDate & "#"
                    blnTrim = False
                End If
            Else
                'Checks to see if both an end date and a start date have been
                'entered and if so shows records between those dates
                If (Not IsNull(Me.txtCDCContactNotesStartDate) And Me.txtCDCContactNotesStartDate <> "") And (Not IsNull(Me.txtCDCContactNotesEndDate) Or Me.txtCDCContactNotesEndDate <> "") Then
                stWhere = stWhere & "[DateEntered] Between #" & Me.txtCDCContactNotesStartDate & "# And #" & Me.txtCDCContactNotesEndDate & "#"
                blnTrim = False
                End If
            End If
        End If

        If blnTrim Then
            stWhere = Left(stWhere, Len(stWhere) - 5)
        End If
    
    stDocName = "rpt_CDCContactNotesActivity"
    DoCmd.OpenReport stDocName, acPreview, , stWhere