translate access iif statment to sql statment

[Appienator]

IIf(Time()>=TimeValue("00:00:00");IIf(Time()<=TimeValue("07:00:00");Format(Now()-1;"dd mm yyyy");Format(Now();"dd mm yyyy")))

[BitBlt]

So if I understand this, you want any date/time after midnight and before 7am to report as the previous day, anything after 7am until midnight to show as current day.
Sleeves rolled up...
You aren't going to like this...it's ugly but it will work on SQL2000 - 2008.

select
datediff(hh, cast(convert(varchar(10), getdate(), 101) as datetime), getdate()) as diff,
case 
when datediff(hh, cast(convert(varchar(10), getdate(), 101) as datetime), getdate()) <= 7 then convert(varchar(10), dateadd(d, -1, cast(convert(varchar(10), getdate(), 101) as datetime)), 103)
when datediff(hh, cast(convert(varchar(10), getdate(), 101) as datetime), getdate()) > 7 then convert(varchar(10), cast(convert(varchar(10), getdate(), 101) as datetime), 103)
end as MyDate

If you wanted it prettier, you could try something using variables and such.

[debasisdas]

It does not matter how the code looks or anyone likes it or not CASE is the solution.

And CASE works really faster.

[Appienator]

How do I put it into the sql query??

SELECT Groep, Afdeling, Dienst, Datum,
rapportage_klaar_J_N
FROM dbo.tbl____Nacht_rapportage
WHERE (rapportage_klaar_J_N = 1)

[debasisdas]

why not post your original access query , we can fix that .

[Appienator]

SELECT tbl____Nacht_rapportage.Groep, tbl____Nacht_rapportage.Afdeling, tbl____Nacht_rapportage.Dienst, Format([Datum],"dd mm yyyy") AS d, tbl____Nacht_rapportage.rapportage_klaar_J_N
FROM tbl____Nacht_rapportage
GROUP BY tbl____Nacht_rapportage.Groep, tbl____Nacht_rapportage.Afdeling, tbl____Nacht_rapportage.Dienst, Format([Datum],"dd mm yyyy"), tbl____Nacht_rapportage.rapportage_klaar_J_N
HAVING (((Format([Datum],"dd mm yyyy"))=IIf(Time()>=TimeValue("00:00:00"),IIf(Time()<=TimeValue("07:00:00"),Format(Now()-1,"dd mm yyyy"),Format(Now(),"dd mm yyyy")))) AND ((tbl____Nacht_rapportage.rapportage_klaar_J_N)=True));

[BitBlt]

First, let me just say that Access SQL is an abomination. Not your fault, just my opinion.
Second, I don't speak German, so I have to make some assumptions about what all your columns are, at least as far as datatypes. Here is the table I created, with a test data load.

create table dbo.tbl____Nacht_rapportage
(Groep int,
Afdeling varchar(10),
Dienst varchar(10),
[Datum] datetime,
rapportage_klaar_J_N tinyint
)
go
insert into dbo.tbl____Nacht_rapportage
(groep, afdeling, dienst, datum, rapportage_klaar_j_n)
values
(1, 'Afdeling1', 'Dienst 1', getdate(), 1)
insert into dbo.tbl____Nacht_rapportage
(groep, afdeling, dienst, datum, rapportage_klaar_j_n)
values
(2, 'Afdeling2', 'Dienst 2', getdate(), 1)
insert into dbo.tbl____Nacht_rapportage
(groep, afdeling, dienst, datum, rapportage_klaar_j_n)
values
(3, 'Afdeling3', 'Dienst 3', getdate(), 0)
insert into dbo.tbl____Nacht_rapportage
(groep, afdeling, dienst, datum, rapportage_klaar_j_n)
values
(4, 'Afdeling4', 'Dienst 4', dateadd(d, 1, getdate()), 1)
insert into dbo.tbl____Nacht_rapportage
(groep, afdeling, dienst, datum, rapportage_klaar_j_n)
values
(5, 'Afdeling5', 'Dienst 5', dateadd(d, -1, getdate()), 1)
insert into dbo.tbl____Nacht_rapportage
(groep, afdeling, dienst, datum, rapportage_klaar_j_n)
values
(6, 'Afdeling6', 'Dienst 6', dateadd(hh, -12, getdate()), 1)
insert into dbo.tbl____Nacht_rapportage
(groep, afdeling, dienst, datum, rapportage_klaar_j_n)
values
(7, 'Afdeling7', 'Dienst 7', dateadd(hh, 12, getdate()), 1)
go

Now, finally, here is the query I came up with. It assumes that [Datum] is a datetime datatype.

SELECT T.Groep, 
T.Afdeling, 
T.Dienst, 
convert(varchar(10), T.[Datum], 103) AS d, -- Assumes [Datum] is datatype DATETIME
T.rapportage_klaar_J_N
FROM tbl____Nacht_rapportage T
GROUP BY T.Groep, 
T.Afdeling, 
T.Dienst, 
convert(varchar(10), [Datum], 103), 
T.rapportage_klaar_J_N
HAVING     
convert(varchar(10), [Datum], 103) 
    = case 
        when datediff(hh, cast(convert(varchar(10), getdate(), 101) as datetime), getdate()) <= 7 then convert(varchar(10), dateadd(d, -1, cast(convert(varchar(10), getdate(), 101) as datetime)), 103)
        when datediff(hh, cast(convert(varchar(10), getdate(), 101) as datetime), getdate()) > 7 then convert(varchar(10), cast(convert(varchar(10), getdate(), 101) as datetime), 103)
       end
AND 
    T.rapportage_klaar_J_N = 1   --assumes non-zero is "true"

If [Datum] is NOT a datetime datatype, you'll have to use the whole cast/convert trick to beat it into shape. Good luck!

Disclaimer: I didn't have a good sample data set, so I can't truly say that the query will give you precisely what you're looking for. However, it does execute and it is a good illustration of how to use the techniques.

[Appienator]

HAVING CONVERT(varchar(10), [d], 103) = CASE gifs an error and tels me that
CASE is not compartable with the ms sql server where i'm work on
is there an other way to do this
I try to chanse an ms access mdb to an ms access prodject and make the query's also into ms access prodject.

[BitBlt]

You can't use [d] in your convert statement, you have to use the syntax I gave you.

I tried this query on SQL2000, SQL2005 sp3, SQL2008 sp1. What version of SQL Server are you using?

And, if you try to use the "upsizing wizard" it will just tell you that there are some things that can't be translated. Using functions like IIF is one case of that.

[Appienator]

The query example works great into ms sql only I have a problem with ms access 2000 where I create the query. The access project is connected to the ms sql server.
It can't translate the case statement, is there something I can do to fix that problem?

[Appienator]

if I place it into a function module is this code then correct??

Function fShifttijd(strShift As Variant) As String
Dim Shifttijd As String
Select Case strShift

convert(varchar(10), [Datum], 103)
= case
when datediff(hh, cast(convert(varchar(10), getdate(), 101) AS datetime), getdate()) <= 7 then shifttijd = convert(varchar(10), dateadd(d, -1, cast(convert(varchar(10), getdate(), 101) AS datetime)), 103)
when datediff(hh, cast(convert(varchar(10), getdate(), 101) AS datetime), getdate()) > 7 then Schifttijd = convert(varchar(10), cast(convert(varchar(10), getdate(), 101) AS datetime), 103)

End Select
fShifttijd = Shifttijd
End Function

[BitBlt]

You are mixing languages. SQL statements are SQL, VB statements are VB. Read the book or help file for how to execute SQL statements from within VB.

[debasisdas]

The query example works great into ms sql only I have a problem with ms access 2000 where I create the query. The access project is connected to the ms sql server.

I really do not understand where are you executing the SQL , in MS SQL or MS Access ?

[Appienator]

I executing the SQL in MS Access