Re: Very Weird Date Format Problem
- From: NevilleT <NevilleT@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 24 Apr 2007 19:28:01 -0700
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" wrote:
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" <NevilleT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4802F804-FA4F-4872-A3A9-2FC9B71CE3FB@xxxxxxxxxxxxxxxx
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" wrote:
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???????
- References:
- Re: Very Weird Date Format Problem
- From: John Spencer
- Re: Very Weird Date Format Problem
- Prev by Date: Re: Counting Summary in Reports/Queries
- Next by Date: Get one result from multiple memo fields
- Previous by thread: Re: Very Weird Date Format Problem
- Next by thread: Page Header Control
- Index(es):
Relevant Pages
|
