sql with "and" and "or"

[MarionD]

Hi all,

Hope someone can help me... I just cant get this little bit of sql to work!

sqlBerechtigter = "SELECT tbl_Berechtigter.ID, [vorname] & ' ' & [nachname] & ', ' & [strasse] & ' ' & [hausNr] & ', ' & [plz] & ' ' & [ort] AS RE FROM tbl_Berechtigter WHERE ((tbl_Berechtigter.tbl_Grab_ID)=" & GSID & ") AND ((tbl_Berechtigter.Berechtigungsende) Is Null) AND ((tbl_Berechtigter.Zahlung)=-1));" '

up to here it works... now all I want to do is also select the records where the tbl_Berechtigter.Berechtigungsende is a date later than todays date! But as soon as I bring in an "OR" it goes haywire!

(i.e. where the Date is either empty or later than todays date)

Thanks a lot for any help!
Marion

 

[ByteMyzer]

Try this for your WHERE clause:

WHERE tbl_Berechtigter.tbl_Grab_ID=" & GSID & "
AND (tbl_Berechtigter.Berechtigungsende Is Null OR tbl_Berechtigter.Berechtigungsende > Date())
AND tbl_Berechtigter.Zahlung=-1;

 

[MarionD]

Thank you sooooo much - works like a charm!!

 

[ByteMyzer]

You're welcome, I'm glad it worked for you

 

[gemma-the-husky]

you may find the nz function useful

with a date nz(anydate,0) sets the date to be 1/12/1899 or something similar

so you should be able to use this sort of thing, which is often a lot easier to construct

WHERE tbl_Berechtigter.tbl_Grab_ID=" & GSID & "
AND nz(tbl_Berechtigter.Berechtigungsende,0) > Date()
AND tbl_Berechtigter.Zahlung=-1;

 

[MarionD]

Thanks so much for taking the time to reply - it's great that I've been able to learn from both of you. Will try this option in the morning!

Marion

 

[MarionD]

Great!
I have now managed to select the "reverse" records i.e. where there is a date in the 'Berechtigungsende' but it is less than the current date.

"nz([berechtigungsende],0) > #12-30-1899# and ([berechtigungsende] < date())"

This gives me only the records with a date less than current date, but not the null records.

Thanks again!
Marion

 

[ByteMyzer]

MarionD,
Switch back to the solution I posted, and you will be fine.

 

[MarionD]

Hi there!

I tried to select the records where there is a date in the end, but it is in the past .. if I just use

where Berechtigungsende < date()

it also gives me the records where no date is entered.

I tried:

where (not isnull(berechtigungsende)) or (berechtigungsende < date())
but this seems to ignore the isnull bit and still gives me the records where no date is entered...

so what am I doing wrong??

 

[ByteMyzer]

Try:

where (berechtigungsende is not null AND berechtigungsende < date())

 

[MarionD]

Anybody ever tell you you're a Star!!!

Thanks again

Marion