Whats wrong with this SQL query?

[Pirre001]

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

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)

[plenderj]

Try this :

Debug.Print SQL & SQLWhere


And post the result here

[Buzby]

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.

[peet]

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 ""

[Pirre001]

Peet,

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

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

Or...

[E-Link]

if your field is text field try this:

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 "'"

[peet]

oh.. sorry Pirre

u sure did

now do as plenderj said

[Pirre001]

Ok, peet. I do that..

[Pirre001]

E-Link,

I shuld try your example also...

[Pirre001]

This is exactly what I get with Debug.print.

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.

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

[Wen Lie]

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

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

[Pirre001]

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