SQL Query

[rex028]

i want to use a Combo Box contain some condition like "<50" , ">100" etc...

then compare to the database

select * from table1 where Item_Amount = "& cboitemAmount.Text & "

how can i make it works ???

 

[ArizonaRedneck]

take out the "=" so it would look like the following...

select * from table1 where Item_Amount "& cboitemAmount.Text & "

 

[mzim]

take out the "=" so it would look like the following...

  select * from table1 where Item_Amount "& cboitemAmount.Text & "

does it work w/out the '=' sign?

rex028: if you want to return a result > and <
try this one:

  select * from table1 where Item_Amount > " & cboitemAmount.Text & "
  or
  select * from table1 where Item_Amount < " & cboitemAmount.Text & "

 

[Schenz]

Depending on the database you may also need to enclose the number in some sort of delimiter such as the single quote (').

 

[ArizonaRedneck]

does it work w/out the '=' sign?

rex028: if you want to return a result > and <
try this one:

select * from table1 where Item_Amount > " & cboitemAmount.Text & "
or
select * from table1 where Item_Amount < " & cboitemAmount.Text & "

I assumed that he had the ">" or "<" listed in the combo box

i want to use a Combo Box contain some condition like "<50" , ">100"

 

[rex028]

thanks !! i did it !!

 

[rex028]

i have another problem now ...
i'm doing a search Function ..
TextBox:
Item number, item name, item type, item amount and item description

i have a problem is that .. when the Item description text box is empty ...
it will display ALL record ...
how can i avoid this ???

i think my coding is just mess:

  Private Sub Getim()
		Dim fromdate As String
		Dim fromday, today As Date
		If cbSearchAll.Checked Then
			objDS.Clear()
			objDS = objUser.Search_tblUser("Select * from tbl_ITEM ")
			dgimsearch.DataSource = objDS.Tables(0)
		ElseIf cboisearchFD.Text = "" Then
			If txtItemDesc.Text = "" Then
				desc = ""
			Else
				desc = "or Item_Desc LIKE '%" & txtItemDesc.Text & "%'"
			End If
			objDS.Clear()
			objDS = objUser.Search_tblUser("Select * from tbl_ITEM where Item_No = '" & txtItemNo.Text & "' or Item_Type = '" & cboItemType.Text & "'  or Item_Amount " & cboitemAmount.Text & " or Item_Desc LIKE '%" & txtItemDesc.Text & "%'")
		  
			Try
				If objDS.Tables(0).Rows.Count <= 0 Then
					MsgBox("Record Not Found")
				Else
					dgimsearch.DataSource = objDS.Tables(0)
				End If
			Catch ex As Exception
			End Try
	
			Else
				fromday = cboisearchFD.Text & "/" & cboisearchFM.Text & "/" & cboisearchFY.Text
				today = cboisearchTD.Text & "/" & cboisearchTM.Text & "/" & cboisearchTY.Text
				objDS.Clear()
				objDS = objUser.Search_tblUser("Select * from tbl_ITEM where Expire_Day Between #" & fromday & "# and #" & today & "#")
				Try
					If objDS.Tables(0).Rows.Count <= 0 Then
						MsgBox("Record Not Found")
					Else
						dgimsearch.DataSource = objDS.Tables(0)
					End If
				Catch ex As Exception
				End Try
			End If
	End Sub

 

[rex028]

	Private Function Getim()
 
		If cbSearchAll.Checked Then
 
			objDS.Clear()
			objDS = objUser.Search_tblUser("Select * from tbl_ITEM ")
 
			Getrecord()
 
 
		ElseIf cboisearchFD.Text = "" Then
			objDS.Clear()
			objDS = objUser.Search_tblUser("Select * from tbl_ITEM where Item_No = '" & txtItemNo.Text & "' or Item_Type = '" & cboItemType.Text & "' or Item_Amount " & cboitemAmount.Text & " ")
			Getrecord()
		Else
			fromday = cboisearchFD.Text & "/" & cboisearchFM.Text & "/" & cboisearchFY.Text
			today = cboisearchTD.Text & "/" & cboisearchTM.Text & "/" & cboisearchTY.Text
 
			objDS.Clear()
			objDS = objUser.Search_tblUser("Select * from tbl_ITEM where Expire_Day Between #" & fromday & "# and #" & today & "#")
			Getrecord()
 
		End If
 
	End Function

 Private Function Getrecord()
		Try
			If objDS.Tables(0).Rows.Count <= 0 Then
				MsgBox("Record Not Found")
			Else
				dgimsearch.DataSource = objDS.Tables(0)
			End If
		Catch ex As Exception
		End Try
	End Function

Why it displays every record even there is no input ???????

 

[ArizonaRedneck]

your problem is in:

or Item_Desc LIKE '%" & txtItemDesc.Text & "%'

with the wild cards on either side, if txtItemDesc.Text is a null string then it is basically saying that it wants any records that have any text in Item_Desc
I would perform an If statement of the line before to make sure there is text in the box. If there's no text, I would leave that part of the statement out.