Very Weird Date Format Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

So far I have wasted over 3 hours on this silly problem. I have a report
that is a monthly calendar display - a box for each day. To determine which
day date to put in which box I find out the weekday of the first of the
month. If it is Sunday (weekday 1) I put "1" in the first box (Weeks are
displayed as Sunday to Saturday). I have a format value of d in each text
box.

The query uses a stored field which is the first of the month. That field
is called StartDate.

The query has a field called DayStart which is

"DayStart: Weekday([StartDate])"

This tells me the first day of the month (somewhere between 1 and 7) 1 being
Sunday and 7 Saturday. Looking at April 2007 where the fist was Sunday, the
value of this field is 1.

I have another field in the query called "L1" which is used as the
controlsource in the first text box. In the query

L1: IIf([DayStart]=1,[StartDate],"")

So if [DayStart] = 1 then L1 = First of the month - in my case 1st April.
The text box with the controlsource L1 has format of d to display only days.
I would expect to see "1" displayed. What I actually get is 1/4/2007

Just to confuse the situation, the second box (for Monday) has L2 for a
controlsource. L2 is

L2: IIf([DayStart]=2,[StartDate],IIf([DayStart]<2,DateAdd("d",1,[L1])))

Sooo if the weekday of 1 April were Monday it would display the start date
but since it isn't it displays L1 + 1 day. The text box has the same format
(d) and displays "2" which is what I want it to do. My question is why the
first date will now display as a day???????
 
Here is how to reproduce the problem.

Create a table called tblTest. No need for a primary key
Fields: Month (text)
FirstOfMonth(date)
Create one record for Month= April, FirstOfMonth= 1 April 2007

Create a query called qryTest using tblTest
Fields: Month - tblText
FirstOfMonth - tblText
DayStart: Weekday([FirstOfMonth])
L1: IIf([DayStart]=1,[FirstOfMonth],"")
L2:
IIf([DayStart]=2,[FirstOfMonth],IIf([DayStart]<2,DateAdd("d",1,[L1])))

Create a report based on qryTest. Put all the fields on the report. For
the date fields set format to d All but L1 display the day. L1 ignores
formatting and displays the full date.
 
Your IIF statement for L1 is returning a string which doesn't respond to
date formatting. Try changing L1 to
IIf([DayStart]=1,[FirstOfMonth],Null)

Access uses both return arguments to decide on the type of data. When you
have a string as one of the arguments, then it will convert the other
argument's result to a string also. Since a Null value doesn't have a data
type, the IIF statement will not do a conversion on the value of the other
argument.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

NevilleT said:
Here is how to reproduce the problem.

Create a table called tblTest. No need for a primary key
Fields: Month (text)
FirstOfMonth(date)
Create one record for Month= April, FirstOfMonth= 1 April 2007

Create a query called qryTest using tblTest
Fields: Month - tblText
FirstOfMonth - tblText
DayStart: Weekday([FirstOfMonth])
L1: IIf([DayStart]=1,[FirstOfMonth],"")
L2:
IIf([DayStart]=2,[FirstOfMonth],IIf([DayStart]<2,DateAdd("d",1,[L1])))

Create a report based on qryTest. Put all the fields on the report. For
the date fields set format to d All but L1 display the day. L1 ignores
formatting and displays the full date.

NevilleT said:
So far I have wasted over 3 hours on this silly problem. I have a report
that is a monthly calendar display - a box for each day. To determine
which
day date to put in which box I find out the weekday of the first of the
month. If it is Sunday (weekday 1) I put "1" in the first box (Weeks are
displayed as Sunday to Saturday). I have a format value of d in each
text
box.

The query uses a stored field which is the first of the month. That
field
is called StartDate.

The query has a field called DayStart which is

"DayStart: Weekday([StartDate])"

This tells me the first day of the month (somewhere between 1 and 7) 1
being
Sunday and 7 Saturday. Looking at April 2007 where the fist was Sunday,
the
value of this field is 1.

I have another field in the query called "L1" which is used as the
controlsource in the first text box. In the query

L1: IIf([DayStart]=1,[StartDate],"")

So if [DayStart] = 1 then L1 = First of the month - in my case 1st April.
The text box with the controlsource L1 has format of d to display only
days.
I would expect to see "1" displayed. What I actually get is 1/4/2007

Just to confuse the situation, the second box (for Monday) has L2 for a
controlsource. L2 is

L2: IIf([DayStart]=2,[StartDate],IIf([DayStart]<2,DateAdd("d",1,[L1])))

Sooo if the weekday of 1 April were Monday it would display the start
date
but since it isn't it displays L1 + 1 day. The text box has the same
format
(d) and displays "2" which is what I want it to do. My question is why
the
first date will now display as a day???????
Click to expand...
Click to expand...
 
Many, many, many thanks John. It worked. I am sure if it were not for your
insight I may have spent another 3 hours looking for the elusive answer. It
makes sense, and is probably buried somewhere in the help files or on Google
but I doubt I would ever have thought of the data type being set to string.

John Spencer said:
Your IIF statement for L1 is returning a string which doesn't respond to
date formatting. Try changing L1 to
IIf([DayStart]=1,[FirstOfMonth],Null)

Access uses both return arguments to decide on the type of data. When you
have a string as one of the arguments, then it will convert the other
argument's result to a string also. Since a Null value doesn't have a data
type, the IIF statement will not do a conversion on the value of the other
argument.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

NevilleT said:
Here is how to reproduce the problem.

Create a table called tblTest. No need for a primary key
Fields: Month (text)
FirstOfMonth(date)
Create one record for Month= April, FirstOfMonth= 1 April 2007

Create a query called qryTest using tblTest
Fields: Month - tblText
FirstOfMonth - tblText
DayStart: Weekday([FirstOfMonth])
L1: IIf([DayStart]=1,[FirstOfMonth],"")
L2:
IIf([DayStart]=2,[FirstOfMonth],IIf([DayStart]<2,DateAdd("d",1,[L1])))

Create a report based on qryTest. Put all the fields on the report. For
the date fields set format to d All but L1 display the day. L1 ignores
formatting and displays the full date.

NevilleT said:
So far I have wasted over 3 hours on this silly problem. I have a report
that is a monthly calendar display - a box for each day. To determine
which
day date to put in which box I find out the weekday of the first of the
month. If it is Sunday (weekday 1) I put "1" in the first box (Weeks are
displayed as Sunday to Saturday). I have a format value of d in each
text
box.

The query uses a stored field which is the first of the month. That
field
is called StartDate.

The query has a field called DayStart which is

"DayStart: Weekday([StartDate])"

This tells me the first day of the month (somewhere between 1 and 7) 1
being
Sunday and 7 Saturday. Looking at April 2007 where the fist was Sunday,
the
value of this field is 1.

I have another field in the query called "L1" which is used as the
controlsource in the first text box. In the query

L1: IIf([DayStart]=1,[StartDate],"")

So if [DayStart] = 1 then L1 = First of the month - in my case 1st April.
The text box with the controlsource L1 has format of d to display only
days.
I would expect to see "1" displayed. What I actually get is 1/4/2007

Just to confuse the situation, the second box (for Monday) has L2 for a
controlsource. L2 is

L2: IIf([DayStart]=2,[StartDate],IIf([DayStart]<2,DateAdd("d",1,[L1])))

Sooo if the weekday of 1 April were Monday it would display the start
date
but since it isn't it displays L1 + 1 day. The text box has the same
format
(d) and displays "2" which is what I want it to do. My question is why
the
first date will now display as a day???????
Click to expand...
Click to expand...
Click to expand...
 
Back
Top