Re: Very Weird Date Format Problem

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???????



.

Relevant Pages

  • Re: How to convert text to number in a query?
    ... CCurconverts the value into Currency type data. ... Queries are reallly just the engine, and not really a display interface. ... on your form/report has a Format property. ... The fields in a query do have a Format property too. ...
    (microsoft.public.access.queries)
  • Re: date format inconsistent
    ... Many thanks John ... You're spot on I had changed the code but not the query! ... My query is flipping ambiguous dates to US format. ... strsql = "SELECT Jobdata.Labname" ...
    (microsoft.public.access.queries)
  • Re: Trying to import 12 digit number into Access
    ... Create a query that deletes all records from that table. ... I won't actually be doing math on it, however, I will be searching ranges ... daily without having to go to design view each day and change the format ... you can display a date/time value ...
    (microsoft.public.access.externaldata)
  • Re: mailmerge with word 2002 and access
    ... See "Formatting Word fields with switches" on fellow MVP Graham Mayor's ... create a query in Access in which you use the Format() ... amounts display correctly in the same field. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: SQL query to format datetime
    ... The data is not stored with any particular format - the format ... If you simply want to display 1/25/2005 when retrieving the datetime ... If you want to change the actual value, and store just the date, ... >Can I ask you a simple SQL query question? ...
    (microsoft.public.sqlserver.programming)