date selection in vba vs query designer

[Kamil]

Hi.
I have 2 queries:
VBA:
"SELECT Pracownicy.ID, Pracownicy.Nazwisko, Pracownicy.Imie,
Dzialy.Pion, Dzialy.Dzial, & _
"FROM Pracownicy RIGHT JOIN (Dzialy RIGHT JOIN Zatrudnienie ON
Dzialy.ID = Zatrudnienie.ID_Dzialu) ON Pracownicy.ID =
Zatrudnienie.ID_Prac " & _
"WHERE (IIf(zatrudnienie.datakoniec Is Null," & Date + 1 &
",zatrudnienie.datakoniec)>" & Date & ") " & _
"ORDER BY Pracownicy.Nazwisko, Pracownicy.Imie;"

Query built in Access interface:
SELECT Pracownicy.ID, Pracownicy.Nazwisko, Pracownicy.Imie,
Dzialy.Pion, Dzialy.Dzial
FROM Pracownicy RIGHT JOIN (Dzialy RIGHT JOIN Zatrudnienie ON
Dzialy.ID = Zatrudnienie.ID_Dzialu) ON Pracownicy.ID =
Zatrudnienie.ID_Prac
WHERE (((IIf([datakoniec] Is Null,Date()+1,[datakoniec]))>Date()))
ORDER BY Pracownicy.Nazwisko, Pracownicy.Imie;

datakoniec field can be filled or not. not filled=null.
I thought both queries should return the same results, but the VBA one
is only returning values where datakoniec is not null.

I've tried with modified WHERE section for VBA:
WHERE ((zatrudnienie.datakoniec is null) or (zatrudnienie.datakoniec >
Date))
and it works the same (returns not null in fact).

datakoniec field is of Date/Time type, with Short Date format.

What could be wrong?
Best regards,
Kamil

 

[Kamil]

Hi.
I have 2 queries:
VBA:
"SELECT Pracownicy.ID, Pracownicy.Nazwisko, Pracownicy.Imie,
Dzialy.Pion, Dzialy.Dzial,  & _
"FROM Pracownicy RIGHT JOIN (Dzialy RIGHT JOIN Zatrudnienie ON
Dzialy.ID = Zatrudnienie.ID_Dzialu) ON Pracownicy.ID =
Zatrudnienie.ID_Prac " & _
"WHERE (IIf(zatrudnienie.datakoniec Is Null," & Date + 1 &
",zatrudnienie.datakoniec)>" & Date & ") " & _
"ORDER BY Pracownicy.Nazwisko, Pracownicy.Imie;"

Query built in Access interface:
SELECT Pracownicy.ID, Pracownicy.Nazwisko, Pracownicy.Imie,
Dzialy.Pion, Dzialy.Dzial
FROM Pracownicy RIGHT JOIN (Dzialy RIGHT JOIN Zatrudnienie ON
Dzialy.ID = Zatrudnienie.ID_Dzialu) ON Pracownicy.ID =
Zatrudnienie.ID_Prac
WHERE (((IIf([datakoniec] Is Null,Date()+1,[datakoniec]))>Date()))
ORDER BY Pracownicy.Nazwisko, Pracownicy.Imie;

datakoniec field can be filled or not. not filled=null.
I thought both queries should return the same results, but the VBA one
is only returning values where datakoniec is not null.

I've tried with modified WHERE section for VBA:
WHERE ((zatrudnienie.datakoniec is null) or (zatrudnienie.datakoniec >
Date))
and it works the same (returns not null in fact).

datakoniec field is of Date/Time type, with Short Date format.

What could be wrong?
Best regards,
Kamil

one more test:
Debug.Print rst!datakoniec > Date
for the first selected record returns false.

 

[Stefan Hoffmann]

hi Kamil,

I've tried with modified WHERE section for VBA:
WHERE ((zatrudnienie.datakoniec is null) or (zatrudnienie.datakoniec >
Date)) and it works the same (returns not null in fact).
What could be wrong?

When using fields from the left/right side from a corresponding JOIN any
operation with them forces Jet to reduce the result set by removing the
rows containing NULL in the joined table fields.

To avoid this reduction, you normally move the condition from the WHERE
clause to the correct ON clause.

If I have read your VBA query correctly, then you just need to replace
the WHERE keyword with an AND.


mfG
--> stefan <--

 

[Douglas J. Steele]

Because you have the Date and Date + 1 outside of the quotes in your VBA,
you're getting the actual value there. You need to delimit date values with
# characters and, regardless of what your Regional Settings have your Short
Date format set to, you must use a date format that Access will correctly
recognize.

However, neither of your Where clauses are valid.

"SELECT Pracownicy.ID, Pracownicy.Nazwisko, Pracownicy.Imie,
Dzialy.Pion, Dzialy.Dzial, & _
"FROM Pracownicy RIGHT JOIN (Dzialy RIGHT JOIN Zatrudnienie ON
Dzialy.ID = Zatrudnienie.ID_Dzialu) ON Pracownicy.ID =
Zatrudnienie.ID_Prac " & _
"WHERE (zatrudnienie.datakoniec Is Null) OR (zatrudnienie.datakoniec>" & _
Format(Date, "\#yyyy\-mm\-dd\#") & ") " & _
"ORDER BY Pracownicy.Nazwisko, Pracownicy.Imie;"

Another option for your Where clause would be:

"WHERE Nz(zatrudnienie.datakoniec, Date + 1) > Date "

 

[Kamil]

Because you have the Date and Date + 1 outside of the quotes in your VBA,
you're getting the actual value there. You need to delimit date values with
# characters and, regardless of what your Regional Settings have your Short
Date format set to, you must use a date format that Access will correctly
recognize.

However, neither of your Where clauses are valid.

"SELECT Pracownicy.ID, Pracownicy.Nazwisko, Pracownicy.Imie,
Dzialy.Pion, Dzialy.Dzial,  & _
"FROM Pracownicy RIGHT JOIN (Dzialy RIGHT JOIN Zatrudnienie ON
Dzialy.ID = Zatrudnienie.ID_Dzialu) ON Pracownicy.ID =
Zatrudnienie.ID_Prac " & _
"WHERE (zatrudnienie.datakoniec Is Null) OR (zatrudnienie.datakoniec>" & _
  Format(Date, "\#yyyy\-mm\-dd\#") & ") " & _
"ORDER BY Pracownicy.Nazwisko, Pracownicy.Imie;"

Another option for your Where clause would be:

"WHERE Nz(zatrudnienie.datakoniec, Date + 1) > Date "

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Hi.
I have 2 queries:
VBA:
"SELECT Pracownicy.ID, Pracownicy.Nazwisko, Pracownicy.Imie,
Dzialy.Pion, Dzialy.Dzial,  & _
"FROM Pracownicy RIGHT JOIN (Dzialy RIGHT JOIN Zatrudnienie ON
Dzialy.ID = Zatrudnienie.ID_Dzialu) ON Pracownicy.ID =
Zatrudnienie.ID_Prac " & _
"WHERE (IIf(zatrudnienie.datakoniec Is Null," & Date + 1 &
",zatrudnienie.datakoniec)>" & Date & ") " & _
"ORDER BY Pracownicy.Nazwisko, Pracownicy.Imie;"

Query built in Access interface:
SELECT Pracownicy.ID, Pracownicy.Nazwisko, Pracownicy.Imie,
Dzialy.Pion, Dzialy.Dzial
FROM Pracownicy RIGHT JOIN (Dzialy RIGHT JOIN Zatrudnienie ON
Dzialy.ID = Zatrudnienie.ID_Dzialu) ON Pracownicy.ID =
Zatrudnienie.ID_Prac
WHERE (((IIf([datakoniec] Is Null,Date()+1,[datakoniec]))>Date()))
ORDER BY Pracownicy.Nazwisko, Pracownicy.Imie;

datakoniec field can be filled or not. not filled=null.
I thought both queries should return the same results, but the VBA one
is only returning values where datakoniec is not null.

I've tried with modified WHERE section for VBA:
WHERE ((zatrudnienie.datakoniec is null) or (zatrudnienie.datakoniec >
Date))
and it works the same (returns not null in fact).

datakoniec field is of Date/Time type, with Short Date format.

What could be wrong?
Best regards,
Kamil

Thanks for help!
What if user will have different regional settings?

 

[Douglas J. Steele]

That's the reason for using the Format function. Access is very particular
about dates: it doesn't respect the user's Regional Settings in SQL
statements. You must use mm/dd/yyyy or an unambiguous format such as
yyyy-mm-dd or dd mmm yyyy.

For more details, see Allen Browne's "International Dates in Access" at
http://allenbrowne.com/ser-36.html or what I had in my September 2003 Access
Answers column for Pinnacle Publication's "Smart Access" newsletter. (The
column and accompanying database can be downloaded for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Because you have the Date and Date + 1 outside of the quotes in your VBA,
you're getting the actual value there. You need to delimit date values
with
# characters and, regardless of what your Regional Settings have your
Short
Date format set to, you must use a date format that Access will correctly
recognize.

However, neither of your Where clauses are valid.

"SELECT Pracownicy.ID, Pracownicy.Nazwisko, Pracownicy.Imie,
Dzialy.Pion, Dzialy.Dzial, & _
"FROM Pracownicy RIGHT JOIN (Dzialy RIGHT JOIN Zatrudnienie ON
Dzialy.ID = Zatrudnienie.ID_Dzialu) ON Pracownicy.ID =
Zatrudnienie.ID_Prac " & _
"WHERE (zatrudnienie.datakoniec Is Null) OR (zatrudnienie.datakoniec>" & _
Format(Date, "\#yyyy\-mm\-dd\#") & ") " & _
"ORDER BY Pracownicy.Nazwisko, Pracownicy.Imie;"

Another option for your Where clause would be:

"WHERE Nz(zatrudnienie.datakoniec, Date + 1) > Date "

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Hi.
I have 2 queries:
VBA:
"SELECT Pracownicy.ID, Pracownicy.Nazwisko, Pracownicy.Imie,
Dzialy.Pion, Dzialy.Dzial, & _
"FROM Pracownicy RIGHT JOIN (Dzialy RIGHT JOIN Zatrudnienie ON
Dzialy.ID = Zatrudnienie.ID_Dzialu) ON Pracownicy.ID =
Zatrudnienie.ID_Prac " & _
"WHERE (IIf(zatrudnienie.datakoniec Is Null," & Date + 1 &
",zatrudnienie.datakoniec)>" & Date & ") " & _
"ORDER BY Pracownicy.Nazwisko, Pracownicy.Imie;"

Query built in Access interface:
SELECT Pracownicy.ID, Pracownicy.Nazwisko, Pracownicy.Imie,
Dzialy.Pion, Dzialy.Dzial
FROM Pracownicy RIGHT JOIN (Dzialy RIGHT JOIN Zatrudnienie ON
Dzialy.ID = Zatrudnienie.ID_Dzialu) ON Pracownicy.ID =
Zatrudnienie.ID_Prac
WHERE (((IIf([datakoniec] Is Null,Date()+1,[datakoniec]))>Date()))
ORDER BY Pracownicy.Nazwisko, Pracownicy.Imie;

datakoniec field can be filled or not. not filled=null.
I thought both queries should return the same results, but the VBA one
is only returning values where datakoniec is not null.

I've tried with modified WHERE section for VBA:
WHERE ((zatrudnienie.datakoniec is null) or (zatrudnienie.datakoniec >
Date))
and it works the same (returns not null in fact).

datakoniec field is of Date/Time type, with Short Date format.

What could be wrong?
Best regards,
Kamil

Thanks for help!
What if user will have different regional settings?