Examining dates

G

Guest

I have an Attendance table that has 1 entry for every time a person in late
or absent. I have a query in design mode that takes the date of the
occurrance and formats an interval week so I can do grouping much later down
stream. My problem is that mgmt decided that all of our reporting should end
(this year only) on 10/31/07. This is a wednesday and in the middle of the
week of 0744. They want 11/01/07, 11/02, and 11/03 to be included with 0745.
I tried the following:
AttendWeek & Year:
IIf([Attend-date]>'10/31/07'<'11/04/07','0745',[AttendWeek & Year1]) where
AttendWeek & Year1 is the Interval week. This gives me error 3122.
Ideas?
 
P

Pieter Wijnen

Horrific Field names <g>'

Try

IIf([Attend-date]>#10/31/07# AND [Attend-date]<#11/04/07#,'0745',[AttendWeek
& Year1])

HtH

Pieter
 
G

Guest

I still get the 3122. And what is the matter with the field name? Too long?

Pieter Wijnen said:
Horrific Field names <g>'

Try

IIf([Attend-date]>#10/31/07# AND [Attend-date]<#11/04/07#,'0745',[AttendWeek
& Year1])

HtH

Pieter

Bunky said:
I have an Attendance table that has 1 entry for every time a person in late
or absent. I have a query in design mode that takes the date of the
occurrance and formats an interval week so I can do grouping much later
down
stream. My problem is that mgmt decided that all of our reporting should
end
(this year only) on 10/31/07. This is a wednesday and in the middle of
the
week of 0744. They want 11/01/07, 11/02, and 11/03 to be included with
0745.
I tried the following:
AttendWeek & Year:
IIf([Attend-date]>'10/31/07'<'11/04/07','0745',[AttendWeek & Year1]) where
AttendWeek & Year1 is the Interval week. This gives me error 3122.
Ideas?
 
P

Pieter Wijnen

I don't like fields with special characters, that's all - I use the caption
property for that sort of thing - not in the least since [] are hard to get
to on a norwegian keyboard.

might be something wrong elsewhere in the query.
The iif looks ok to me if [AttendWeek & Year1] is a text field at least. if
not try adding
Format([AttendWeek & Year1],'0000')

Pieter

Bunky said:
I still get the 3122. And what is the matter with the field name? Too
long?

Pieter Wijnen said:
Horrific Field names <g>'

Try

IIf([Attend-date]>#10/31/07# AND
[Attend-date]<#11/04/07#,'0745',[AttendWeek
& Year1])

HtH

Pieter

Bunky said:
I have an Attendance table that has 1 entry for every time a person in
late
or absent. I have a query in design mode that takes the date of the
occurrance and formats an interval week so I can do grouping much later
down
stream. My problem is that mgmt decided that all of our reporting
should
end
(this year only) on 10/31/07. This is a wednesday and in the middle of
the
week of 0744. They want 11/01/07, 11/02, and 11/03 to be included with
0745.
I tried the following:
AttendWeek & Year:
IIf([Attend-date]>'10/31/07'<'11/04/07','0745',[AttendWeek & Year1])
where
AttendWeek & Year1 is the Interval week. This gives me error 3122.
Ideas?
 
G

Guest

Pieter,

I got it too work and thanks a bunch!

Basically, I was trying to do too much within 1 query. When I created a
sub-query, and added the expression on the top query, it worked perfectly.

Never thought of that drawback on spl char. I'm sure that would change my
mind in a hurry!

Thanks again.

Pieter Wijnen said:
I don't like fields with special characters, that's all - I use the caption
property for that sort of thing - not in the least since [] are hard to get
to on a norwegian keyboard.

might be something wrong elsewhere in the query.
The iif looks ok to me if [AttendWeek & Year1] is a text field at least. if
not try adding
Format([AttendWeek & Year1],'0000')

Pieter

Bunky said:
I still get the 3122. And what is the matter with the field name? Too
long?

Pieter Wijnen said:
Horrific Field names <g>'

Try

IIf([Attend-date]>#10/31/07# AND
[Attend-date]<#11/04/07#,'0745',[AttendWeek
& Year1])

HtH

Pieter

I have an Attendance table that has 1 entry for every time a person in
late
or absent. I have a query in design mode that takes the date of the
occurrance and formats an interval week so I can do grouping much later
down
stream. My problem is that mgmt decided that all of our reporting
should
end
(this year only) on 10/31/07. This is a wednesday and in the middle of
the
week of 0744. They want 11/01/07, 11/02, and 11/03 to be included with
0745.
I tried the following:
AttendWeek & Year:
IIf([Attend-date]>'10/31/07'<'11/04/07','0745',[AttendWeek & Year1])
where
AttendWeek & Year1 is the Interval week. This gives me error 3122.
Ideas?
 
P

Pieter Wijnen

thought you would

P.

Bunky said:
Pieter,

I got it too work and thanks a bunch!

Basically, I was trying to do too much within 1 query. When I created a
sub-query, and added the expression on the top query, it worked perfectly.

Never thought of that drawback on spl char. I'm sure that would change my
mind in a hurry!

Thanks again.

Pieter Wijnen said:
I don't like fields with special characters, that's all - I use the
caption
property for that sort of thing - not in the least since [] are hard to
get
to on a norwegian keyboard.

might be something wrong elsewhere in the query.
The iif looks ok to me if [AttendWeek & Year1] is a text field at least.
if
not try adding
Format([AttendWeek & Year1],'0000')

Pieter

Bunky said:
I still get the 3122. And what is the matter with the field name? Too
long?

:

Horrific Field names <g>'

Try

IIf([Attend-date]>#10/31/07# AND
[Attend-date]<#11/04/07#,'0745',[AttendWeek
& Year1])

HtH

Pieter

I have an Attendance table that has 1 entry for every time a person
in
late
or absent. I have a query in design mode that takes the date of the
occurrance and formats an interval week so I can do grouping much
later
down
stream. My problem is that mgmt decided that all of our reporting
should
end
(this year only) on 10/31/07. This is a wednesday and in the middle
of
the
week of 0744. They want 11/01/07, 11/02, and 11/03 to be included
with
0745.
I tried the following:
AttendWeek & Year:
IIf([Attend-date]>'10/31/07'<'11/04/07','0745',[AttendWeek & Year1])
where
AttendWeek & Year1 is the Interval week. This gives me error 3122.
Ideas?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top