trying to detect multiple null or zero values

[Damob9K]

Hi all,

Hope there is not a maximum amount of questions I can ask on my first day !!

Also sorry if this question is not in the correct sub forum.

Right what I am trying to achieve is this:

I have a search form that has 8 criteria fields, of which the user can fill (from combo boxes and txt boxes) any number of them to narrow or widen the search.
This by the way works perfectly, with a bit of help from a few sources.

What I want to have is a message box pop up if the user does not enter any criteria and click search.

I tried stringing multiple if isnull statements together but realised that because I have an onload function that sets all fields to "" the isnull function won't work, so have changed this to the Len function.

But when I run it, it just skips straight past the len line and opens the search detail form regardless.

I suspect I have the order of things wrong, as it does not throw up any errors when run.
Could someone look at the code and tell me what I have got wrong please ?

Code:

Private Sub Form_Load()
Me.PCNumberIn.Value = ""
Me.SerialNumberIn.Value = ""
Me.DeviceTypeIn.Value = ""
Me.DeviceModelIn.Value = ""
Me.StatusIn.Value = ""
Me.UserIn.Value = ""
Me.CustomerNameIn = ""
Me.LocationIn.Value = ""
End Sub

Private Sub Search_Click()
Dim sqlStatement As String

If Len(Me![PCNumberIn] & vbNullString) = 0 And Len(Me![SerialNumberIn] & vbNullString) And Len(Me![DeviceTypeIn] & vbNullString) And Len(Me![DeviceModelIn] & vbNullString) And Len(Me![StatusIn] & vbNullString) And Len(Me![CustomerNameIn] & vbNullString) And Len(Me![LocationIn]) And Len(Me![UserIn] & vbNullString) Then
MsgBox "You Must enter at least one search criteria", vbOKOnly, "Advanced Search"

Else
sqlStatement = "TRUE "
If Nz(Me.PCNumberIn) <> "" Then
sqlStatement = sqlStatement & " AND PCNumber Like '*" & Me.PCNumberIn & "*'"
End If
If Nz(Me.SerialNumberIn) <> "" Then
sqlStatement = sqlStatement & " AND SerialNumber Like '*" & Me.SerialNumberIn & "*'"
End If
If Nz(Me.DeviceTypeIn) <> "" Then
sqlStatement = sqlStatement & " AND DeviceType = '" & Me.DeviceTypeIn & "'"
End If
If Nz(Me.DeviceModelIn) <> "" Then
sqlStatement = sqlStatement & " AND DeviceModel = '" & Me.DeviceModelIn & "'"
End If
If Nz(Me.StatusIn) <> "" Then
sqlStatement = sqlStatement & " AND Status = '" & Me.StatusIn & "'"
End If
If Nz(Me.CustomerNameIn) <> "" Then
sqlStatement = sqlStatement & " AND CustomerName Like '*" & Me.CustomerNameIn & "*'"
End If
If Nz(Me.LocationIn) <> "" Then
sqlStatement = sqlStatement & " AND Location Like '*" & Me.LocationIn & "*'"
End If
If Nz(Me.UserIn) <> "" Then
sqlStatement = sqlStatement & " AND User Like '*" & Me.UserIn & "*'"
End If
DoCmd.OpenForm "SearchDetailForm", , , sqlStatement

DoCmd.Close acForm, "AdvancedSearchForm"
End If



End Sub


Many thanks

Damian

 

[pbaldy]

For starters, I'd make your tests consistent. You don't always include the =0, and don't always add the vbNullString part.

 

[spikepl]

Slow down! If something composed of many bits does not work then check each bit on its own!

Learn how to debug your stuff, like here http://www.baldyweb.com/ImmediateWindow.htm or like here: http://www.access-programmers.co.uk/forums/showthread.php?t=149429 (scroll down to Debugging tips). Use the forum as a source of enlightenment rather than as a crutch

 

[Damob9K]

Oh poop, I hadn't realised that I had missed all those & vbNullString) = 0 I got distracted half way through and thought I had done them all correctly.

Sorry guys less haste and more speed and all that !

Thanks for the links spikepl, a lot of stuff there that I didn't know about.

I think I got a bit over excited as this is the last real hurdle to overcome - apart from working out how to import all the data from multiple un nomalised spreadsheets :banghead:

Cheers

I'll report back once I have made the changes.

D

 

[Damob9K]

OK solved yay.

After playing with the breakpoint and then seeing what values were being assigned, I discovered that vbNullString was always returning a value of "" even when I actually put something in that corresponding input box.
Which led me to believe that it wasn't needed, so now I have removed it and have: If Len(Me![PCNumberIn]) = 0 And Len(Me![SerialNumberIn]) = 0 etc etc
And that works a treat.

Thank again for you words of wisdom.

D

Just one last little ism to solve !

 

[pbaldy]

That's what it's supposed to do, but glad you have it working.