Whats wrong with this SQL query?

This query doesen't work. Please, help me...

VB Code:

Dim db As Database
Dim rs As Recordset
Dim SQL, SQLWhere, Number1, Number2
 
If Len(Me![Text0]) > 0 Then
Number1 = "=""" & Me![Text0] & """"
Else
Number1 = "Is Null"
End If
 
If Len(Me![Text1]) > 0 Then
Number2 = "="""  & Me![Text1] & """"
Else
Number2 = "Is Null"
End If
 
SQL = "SELECT * FROM tbltable1 "
 
SQLWhere = "WHERE tbltable1.[Field1] " & "" & Number1 & " " & _
"AND tbltable1.[Field2] " & "" & Number2 & ";"
 
Set db = CurrentDb
Set rs = db.OpenRecordset(SQL & SQLWhere)

Try this :

Debug.Print SQL & SQLWhere


And post the result here

To make it easier for us just post the finished query once it has been built up..

Also - you can use ' instead of " inside queries.. eg;

SQL = "SELECT * FROM tblTable1 WHERE Field1=' "+this+" ' "

this will stop you going mad with the multiple " required.

think you are missing =


SQLWhere = "WHERE tbltable1.[Field1] " & "" & Number1 & " " & _
"AND tbltable1.[Field2] " & "" & Number2 & ";"

should be

SQLWhere = "WHERE tbltable1.[Field1] =" & "" & Number1 & " " & _
"AND tbltable1.[Field2] =" & "" & Number2 & ";"

assuming field1 and 2 are text fields and not numeric...

if numeric you also have to loose the "" :)

Peet,

I "fix" = in this part of the code...

VB Code:

If Len(Me![Text0]) > 0 Then
Number1 = "=""" & Me![Text0] & """"
Else
Number1 = "Is Null"
End If

Or...:eek:

if your field is text field try this:

VB Code:

If Len(Me![Text0]) > 0 Then
Number1 = "='" & Me![Text0] & "'"
Else
Number1 = "Is Null"
End If
 
If Len(Me![Text1]) > 0 Then
Number2 = "='"  & Me![Text1] & "'"
Else
Number2 = "Is Null"
End If
 
SQL = "SELECT * FROM tbltable1 "
 
SQLWhere = "WHERE tbltable1.[Field1] " & "" & Number1 & " " & _
"AND tbltable1.[Field2] " & "" & Number2 & ";"
 
Set db = CurrentDb
Set rs = db.OpenRecordset(SQL & SQLWhere)

but if your fileds are numeric then u must loose the "'"

oh.. sorry Pirre :)

u sure did :)

now do as plenderj said :)

Ok, peet. I do that..:)

E-Link,

I shuld try your example also...

This is exactly what I get with Debug.print.

VB Code:

SELECT * FROM tbltable1 WHERE tbltable1.[Field1] Is Null AND tbltable1.[Field2] ="D=8
L=30
U=37VAC
R=160W";

Why can´t I use BOF and EOF whith the code? Now I always get True on both EOF and BOF.

VB Code:

If Len(Me![Text0]) > 0 Then
Number1 = "='" & Me![Text0] & "'"
Else
Number1 = "Is Null"
End If
 
If Len(Me![Text1]) > 0 Then
Number2 = "='" & Me![Text1] & "'"
Else
Number2 = "Is Null"
End If
 
SQL = "SELECT * FROM tbltable1 "
 
SQLWhere = "WHERE tbltable1.[Field1] " & "" & Number1 & " " & _
"AND tbltable1.[Field2] " & "" & Number2 & ";"
Set db = CurrentDb
Set rs = db.OpenRecordset(SQL & SQLWhere)
 
If rs.BOF = False And rs.EOF = False Then
Msgbox "Test"
End if

Quote:

---

If If rs.BOF = False And rs.EOF = False Then
Msgbox "Test"
End if

---

Did u use 2 If sentences ???

try to omit one.

Btw, what kind of cursor type do u use to open ur recordset ? if u're using adOpenKeyset, u can use

Code:

---

    If rs.RecordCount > 0 Then
          MsgBox "Test"
    End If

---

The only I wanna know...if I get any result of my query or not.