If a textbox value = null then

[spnz]

Hi there

I am in need of some help with an SQL VBA string

I have created a search form and I am now trying to make a search feature using dates.

I have 2 textboxes txtSearch1 & txtSearch2

what I am trying to do is use both the text boxes togeather to achieve my result.

I would like if only txtSearch1 contained information for the sql statement to only use the criteria in that box but if there was another criteria in txtSearch2 then for both the txtboxes to work togeather.


e.g
if txtSearch1 had the date 01/08/05 then the search would only contain that date but if
txtSearch1 had the date 01/05/05 & txtSearch2 had the date 01/08/05 then the search would find everything in between.

This is what I am trying to work on at the moment.......Please don't laugh to much

Private Sub SearchStartDate()

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim strFilterSQL As String
    Dim txtSearch1 As String
    Dim txtSearch2 As String

    txtSearch1 = Me.txtSearch1.Value
    txtSearch2 = Me.txtSearch2.Value
       
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qrySearch")
     
     
     If txtSearch2.Value Is Null Then txtSearch2 = txtSearch1
    
    strSQL = "SELECT tblMain.[bkg no],tblMain.[Surname],tblMain.[TempName],tblMain.[Department],tblMain.[Taken By],tblMain.[Reporting To],tblMain.[Start Date],tblMain.[End Date] " & _
                "FROM tblMain " & _
                "WHERE tblMain.[Start Date] Between [Forms]![frmSearch]![txtSearch1] And [Forms]![frmSearch]![txtSearch2]" & _
                "ORDER BY tblMain.[Start Date]"
           
           
    
             
            
             
    qdf.SQL = strSQL
    Me.lstSearchResults.RowSource = "qrysearch"
    
    Set qdf = Nothing
    Set db = Nothing

End Sub

As you see help would be of great help!!!

TIA

 

[Liv Manto]

if IsNull (txtSearch2.Value ) = true then


---------------
Try to dispel spaces in your fieldnames if its not too late.



strSQL = "SELECT tblMain.bkg no,tblMain.Surname,tblMain.TempName, tblMain.Department, tblMain.Taken By, tblMain.Reporting_To,tblMain.Start_Date,tblMain.End_Date "

strSQL = strSQL & " FROM tblMain WHERE tblMain.Start_Date "

strSQL = strSQL & "Between #" & txtSearch1 & "# And #" & txtSearch1 & "#" & ORDER BY tblMain.Start_Date"


----------------------


(Try this in Northwind:
SELECT Orders.OrderDate INTO fff FROM Orders WHERE Orders.OrderDate Between #" & date1 & "# And #" & date2 & "#" )

 

[workmad3]

if IsNull (txtSearch2.Value ) = true then

Just use If IsNull(txtSearch2) Then

saves a comparison, and makes a bit more sense in english terms(and a bit neater).

Theres never really any point comparing a function that returns true or false to true.

 

[Liv Manto]

I assure you that I know that. I spelled it out so if in the future he would want to use "false", he would have an idea. Thats how you teach, in details so next time they dont come back again for another question that should have been answered in the first response.

Instead of correcting imagined errors, why dont you answer someone else's unanswered problem? All you are doing is annoying people who tries to help.

 

[modest]

Both of you are right, but if we are really going to "teach" let's explain why.

IsNull() returns a True/False boolean value. So if the parameter is Null, it returns true. If the parameter is not null, it returns false.

Therefore both of the following are the same when testing for a Null value:

If IsNull(txtSearch2.Value) = True Then
If IsNull(txtSearch2.Value) Then

And both the following are the same when testing for non-Null value:

If Not IsNull(txtSearch2.Value) Then
If IsNull(txtSearch2.Value) = False Then

One important thing to note is that textboxes sometimes have empty strings instead of null values, so if you want to test if the textbox is filled in or not you should do:

If txtSearch2.Value & "" = "" Then

 

[Liv Manto]

If txtSearch2.Value = "" Then

or

If NZ(txtSearch2.Value , "NULL") = "NULL" or txtSearch2.Value = "" then


I like using the one with the "NULL".... when I reread the code, its easier for me for me to remember what I was trying to do. I had moments of when I am looking back at my code, I cant even remember what I was trying to do. or how I came up with it. I try not to document my code for job security.

LOL.

 

[ghudson]

I try not to document my code for job security.

Now thats a new twist.

If IsNull(txtSearch2) Or txtSearch2 = "" Then

The .Value is not needed. The above will test if the txtSearch2 is Null or an empty string [like when the user types something in the txtSearch2 field and then deletes it which makes it look empty but it is now an empty string].

 

[Liv Manto]

If you have more comments or questions on why I try to complete code to the last detail even if I dont have too, please refer to post #4.

If you have more nasty, sarcastic comments, really guys, dont you have anything else to do? Or is this a board affected by what's going in on London these days?

Hey, we have terrorist problems too in south of the Philippines, so lets be nice to each other, ok?

 

[RV]

No need to be offended.
What ghudson seems to be saying in his own personal wording (everybody's different and therefore uses his or hers own wording) is that it's common practice to document your coding.

You seem to be implying that you don't:

I try not to document my code for job security

That does sound rather weird.
Personally, I would fire ANY programmer that I would caught NOT documenting code on the spot, no exceptions

RV

 

[ChrisO]

Documenting your code gone mad…

If Len(Trim(txtSearch2)) Then

Start documentation…

If txtSearch2 only contains spaces then they will be removed.
If txtSearch2 is Null then the Trim function returns Null.
The result of Trim is either a ZLS, is Null or has some characters.
Len(ZLS) = 0 and is equal to False.
Len(Null) returns Null and it will be regarded as False.
Len(some characters) will return > 0 and it will be regarded as True.
However, Len(some characters) is not equal to True.
Don’t try inverting the logic, when the result is Null the Not of Null is still Null.
Null does not play logical comparison games.
Null equates to the unknown.
Unknown1 = Unknown2 is False as also is Unknown1 <> Unknown2.
This occurs simply because one, or the other or both are unknown.

End Documentation

I would prefer not to have to do that for every line of code simply because it takes time and time is money.
I’m self-employed, so if I don’t document my code, should I sack myself?

Regards,
Chris.

 

[workmad3]

I assure you that I know that. I spelled it out so if in the future he would want to use "false", he would have an idea. Thats how you teach, in details so next time they dont come back again for another question that should have been answered in the first response.

Instead of correcting imagined errors, why dont you answer someone else's unanswered problem? All you are doing is annoying people who tries to help.

Im sorry. I didnt mean to attack you or annoy you. I personnally prefer to try and work things out on my own and hence prefer to put up lines that are more to my style of coding. I also prefer to give the person a chance to work things out for themselves rather than telling them every little detail as my personal opinion(and im aware that ppl will disagree, but its my opinion and not looking to change it) is that if you tell people every little detail they wont try for themselves and learn. A mistake is worth more than a correct solution in terms of learning.

So, this is my apology and explanation as to why i replied in the first place.

 

[misscrf]

I hope that I am not opening a can of worms, but I have code with text boxes that MAY require a date, and I can't seem to get them to work. Can someone please help?

Here is the form that I have for users to choose a report:

Here is the code that is behind the onclick of the command button:

Private Sub cmdOpenReport_Click()
On Error GoTo ErrorHandler
Dim Msg, Style, Title
Dim gstrReportName As String
Dim gstrWhere As String

Select Case Forms![frmReports]![GrpReportType]
Case 1
gstrReportName = "rptExclusions"

Case 2
gstrReportName = "rptObjections"

Case 3
gstrReportName = "rptNoForwardingAddress"

Case 4
gstrReportName = "rptUpdatedAddress"

Case 5
gstrReportName = "rptCommentsQuestions"

End Select

Select Case Forms![frmReports]![GrpReportDate]

   Case 1 'today
       gstrWhere = "ActivityDate = #" & Date & "#"   'SQL wants hashes around dates
       
   Case 2  'a particular date
       gstrWhere = "ActivityDate =#" & Me.txtFromChoose & "#"
       
   Case 3 ' date range
       gstrWhere = "ActivityDate between #" & Me.txtFromChoose & "# and #" & Me.txtTo & "#"
       
   Case 4 'all dates
      gstrWhere = ""                            'we are selecting all records so no filter needed"

End Select

DoCmd.SetWarnings False
[COLOR=Red]If (Forms![frmReports]![GrpReportDate] = 2 And Me.txtFromChoose.Value & "" = "") Then[/COLOR]Msg = "You must enter a date in the Choose Date field!"
    Style = vbOKOnly
    Title = "Date Must be Entered!"
[COLOR=Red]ElseIf (Forms![frmReports]![GrpReportDate] = 3 And (Me.txtFromChoose.Value & "" = "" Or Me.txtTo.Value & "" = "")) Then[/color]
Msg = "You must enter a start AND end date!"
    Style = vbOKOnly
    Title = "Dates Must be Entered!"
Else

DoCmd.OpenReport gstrReportName, acViewPreview, , gstrWhere

End If
DoCmd.SetWarnings True
ExitHandler:
    Exit Sub

ErrorHandler:
If Err = 2501 Then
    Resume ExitHandler
    
    Else
        MsgBox Err.Description
        Resume ExitHandler
        
        End If
'    msgbox Err.Description
'    Resume Exit_cmdPreviewRpt_Click
End Sub

As you can see, the red is where I am having the problem. With this code, if I choose any option, it works. If I choose option 2 or 3 and enter in a date(s) that has data, it works. If I enter in a date(s) that does not have data I get the appropriate nodata message. If I leave the text blank, nothing happens. I can click and click and click the button. No message, no report, nothing.

Please help!

 

[RV]

For Case 4, try

gstrWhere Is Null

RV

 

[misscrf]

oddly enough, it wasn't even the empty string.

It was in the message:

Msg = "You must enter a date in the Choose Date field!"
    Style = vbOKOnly
    Title = "Date Must be Entered!"

I forgot to put the
Msgbox msg,,Title

That did it.

Thanks for all the help!