Help with this logic - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with this logic

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-05-14 : 09:05:58

I have a table with two fields called PropAddress and HomeAddress. I have a form with two labels lblPAdd and lblHAdd. There may or may not be a value in either or both of these labels. On a button click I want to run a sql statement to see if any value in lblPadd or lblHAdd matches either of the values in the fields in my table. Sounds simple but I am having problems when there is no value in lblPAdd or lblHAdd. Here's what I have now an dit is returning values where there is nothing in both.

strSQL2 = "Select * from Register where HomeAddress = '" & Me.lblPAdd.Text & "'" & " or HomeAddress = '" & Me.lblHAdd.Text & "'" _
'& " or PropAddress = '" & lblPAdd.Text & "'" & " or PropAddress = '" & lblHAdd.Text & "'"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-14 : 09:19:50

Use Like operator

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-05-14 : 09:47:29

I am now getting every record in the table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-14 : 09:49:23

Ok. What do you want to get if labels are empty?

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-05-14 : 09:55:32

If there is a value in either (or both) lblPAdd or lblHAdd I want to return a record if either matches PropAddress or HomeAddress

The value is a number (id of a property) which may ,make it easier. It is also a text field (not our choice))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-14 : 10:07:35

Try

strSQL2 = "Select * from Register where (HomeAddress = '" & Me.lblPAdd.Text & "'" & " or HomeAddress = '" & Me.lblHAdd.Text & "'" _
'& " or PropAddress = '" & lblPAdd.Text & "'" & " or PropAddress = '" & lblHAdd.Text & "') and
'" & length(Me.lblPAdd.Text) & "'>0 or '" & length(Me.lblPAdd.Text) & "'>0"

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-05-14 : 11:01:57

Thanks - I got it to work finally without this bit and tweaking the syntax

" & length(Me.lblPAdd.Text) & "'>0 or '" & length(Me.lblPAdd.Text) & "'>0"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-15 : 01:04:26

quote:
Originally posted by Pinto

Thanks - I got it to work finally without this bit and tweaking the syntax

" & length(Me.lblPAdd.Text) & "'>0 or '" & length(Me.lblPAdd.Text) & "'>0"

Ok. Post the workable solution

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-05-22 : 04:59:44

Just found out it still doesn't work.....problem is it matches two nulls.

strSQL2 = "Select * from tblPHCR_Register where Reg_HomeLLPG = '" & Me.lblCallerUPRN.Text & "'" & " or Reg_HomeLLPG = '" & Me.lblPremisesUPRN.Text & "'" _
& " or Reg_PropLLPG = '" & Me.lblCallerUPRN.Text & "'" & " or Reg_PropLLPG = '" & Me.lblPremisesUPRN.Text & "'"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources