SQL statements again!

[MarionD]

Hi All,

So sorry to be back with the same theme, but somehow I just can't get the hang of these SQL Statements! I hope the kind person who helped last time is still around!

Set Gebuehr = db.OpenRecordset("SELECT * FROM tblxx_Gebuehr " _
& "WHERE tblxx_Gebuehr.tblxx_grabart_id= " & Grabart AND " _
& "tblxx_Gebuehr.tblxx_Gebuehrkategorie_ID= " & Kategorie AND " _
& "tblxx_Gebuehr.deleted=0 AND " _
& "(tblxx_Gebuehr.tblxx_friedhof_id Is Null OR tblxx_Gebuehr.tblxx_friedhof_id = " & Friedhof &"))"

Where Grabart, Friedhof and Kategorie are variables. I think it has something to do with the way I tray and break the lines up but have tried a million different ways!

Thanks a lot
Marion

 

[branston]

Are Grabart, Friedhof and Kategorie numbers or text? If they are numbers that should work, but if they are text i think you need some extra ' s. It took me ages (and a lot of help from these forums) to get the hang of these, so i know how you feel!

If they are text then try this:

Set Gebuehr = db.OpenRecordset("SELECT * FROM tblxx_Gebuehr " _
& "WHERE tblxx_Gebuehr.tblxx_grabart_id= '" & Grabart & _
"'AND tblxx_Gebuehr.tblxx_Gebuehrkategorie_ID= '" & Kategorie & _
"'AND tblxx_Gebuehr.deleted=0 AND " _
& "(tblxx_Gebuehr.tblxx_friedhof_id Is Null OR tblxx_Gebuehr.tblxx_friedhof_id = '" & Friedhof & "'))"

Hope that helps!

 

[branston]

Oops, 1st line should be:
Set Gebuehr = db.OpenRecordset("SELECT * FROM tblxx_Gebuehr " & _
and then remove the & at the start of the 2nd line.

 

[aman]

Set Gebuehr = db.OpenRecordset("SELECT * FROM tblxx_Gebuehr " & _
 "WHERE tblxx_Gebuehr.tblxx_grabart_id= " & Grabart AND " & _
"tblxx_Gebuehr.tblxx_Gebuehrkategorie_ID= " & Kategorie AND " &  _
 "tblxx_Gebuehr.deleted=0 AND " & _
 "(tblxx_Gebuehr.tblxx_friedhof_id Is Null OR tblxx_Gebuehr.tblxx_friedhof_id = " & Friedhof &"))"

 

[MarionD]

Hi branston - Thanks a mill but it still doesn't work. It wont compile. Attached the message (sorry about the German!)

The variables are all dimmed as Long and have the values 1 , 1, and 5

 

[MarionD]

Umm.. sorry attachment - try again

 

[branston]

hum, i obviously still ahvent got the hang of it! Does Aman's work? If not ill have another look!

 

[MarionD]

Nope - sorry Aman - get the same message - won't compile
Thanks for the trouble

 

[aman]

Hi

Try the following. As the variables are numbers so add &" marked in green in the code.

Set Gebuehr = db.OpenRecordset("SELECT * FROM tblxx_Gebuehr " & _
 "WHERE tblxx_Gebuehr.tblxx_grabart_id= " & Grabart [COLOR=green]& "[/COLOR] AND " & _
"tblxx_Gebuehr.tblxx_Gebuehrkategorie_ID= " & Kategorie [COLOR=green]&" [/COLOR]AND " &  _
 "tblxx_Gebuehr.deleted=0 AND " & _
 "(tblxx_Gebuehr.tblxx_friedhof_id Is Null OR tblxx_Gebuehr.tblxx_friedhof_id = " & Friedhof &")")

 

[branston]

Ok try:

Set Gebuehr = ("SELECT * FROM tblxx_Gebuehr WHERE tblxx_Gebuehr.tblxx_grabart_id= " & Grabart & "AND tblxx_Gebuehr.tblxx_Gebuehrkategorie_ID= " & Kategorie & "AND tblxx_Gebuehr.deleted=0 AND IsNull(tblxx_Gebuehr.tblxx_friedhof_id) OR tblxx_Gebuehr.tblxx_friedhof_id = " & Friedhof & "))")

Im not too sure why you have the 2 )'s at the end. Maybe try removing them as well, so the end looks like & Friedhof)

 

[MarionD]

Thanks guys it works like this:-

Set Gebuehr = db.OpenRecordset("SELECT * FROM tblxx_Gebuehr " & _
"WHERE tblxx_Gebuehr.tblxx_grabart_id= " & Grabart & " AND " & _
"tblxx_Gebuehr.tblxx_Gebuehrkategorie_ID= " & Kategorie & " AND " & _
"tblxx_Gebuehr.deleted=0 AND " & _
"(tblxx_Gebuehr.tblxx_friedhof_id Is Null OR tblxx_Gebuehr.tblxx_friedhof_id = " & Friedhof & ")")

 

[branston]

Glad you got it sorted!

 

[tkurland]

You should get Total Visual Code Tools one of the tools allows you to copy sql from the access query builder and it automatically makes a vba variable for you. It has a bunch of other helpful tools. well worth the price.

http://fmsinc.com/MicrosoftAccess/VBACodingTools.html

 

[MarionD]

Thanks for the tip - I'll check it out!

 

[namliam]

Just another tip: Make your code readable ...

Something like this:

dim mySQL as string
mySQL = ""
mySQL = mySQL & " SELECT * "
mySQL = mySQL & " FROM  tblxx_Gebuehr "
mySQL = mySQL & " WHERE tblxx_Gebuehr.tblxx_grabart_id= " & Grabart
mySQL = mySQL & "   AND tblxx_Gebuehr.tblxx_Gebuehrkategorie_ID= " & Kategorie
mySQL = mySQL & "   AND tblxx_Gebuehr.deleted=0 "
mySQL = mySQL & "   AND (      tblxx_Gebuehr.tblxx_friedhof_id Is Null "
mySQL = mySQL & "           OR tblxx_Gebuehr.tblxx_friedhof_id = " & Friedhof & ")" 

Set Gebuehr = db.OpenRecordset(mySQL)

is a lot more readable and maintainable as now everyone can right off see whats goings ons...

 

[MarionD]

Thanks for the tip - I appreciate all helpful comments!