#Error Reading with IIF Statement 1

[awl]

I have a table consisting of the fields: [Name], [1st Visit], [2nd Visit], [3rd Visit], [4th Visit], [5th Visit], [6th Visit]. There are only 6 visits, and the visits are dates the individual visited the clinic. My goal is to list all the individuals that have visited the clinic for the month of January during any of the visits, (1st through 6th Visit). A separate report will be generated for each month.

In my "qryJanuary All Visits", I have placed in the criteria under each of the 6 Visit fields, Like "1/*/*" to obtain January’s dates, which I also want to show the individual’s other visits. So far the results show what I need for each of the 6 visits.

However, in addition, I want to display a number 1 in a new field, if the date is anytime during January for each of the 6 Visit fields. For the [1st Visit] field, I have place the IIF statement: 1stJanY: IIf([1st Visit]="1/*/*","1","0&quot. The result displays #Error. The date field in my table is formatted 1/1/2003. Can someone shed some light on what the error reading could be? Thank you….

 

[MakeItSo]

It might be qute simple:
just as strings need to be enclosed in quotes, dates need to be enclosed in hashes:
1stJanY: IIf([1st Visit]="#1/*/*#","1","0&quot.

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[awl]

Well, I tried as suggested by putting the hashes, and I still get the #Error in the results. I opened the properties for that field, entered 0 for format, and instead of #Error being left aligned, it is now right aligned. Any suggestions? thanks....

 

[MakeItSo]

Oh, I skipped one thing:

The error might somewhere else:
You said, that you have a field called [Name].
Since Name is a reserved word in Access, you'd be better off changing the field name to "Visitor" or sth like that.
Perhaps this field is causing the error.

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[jebry]

Hi!

Don't know if this will fix it or not but try it like this:

1stJanY: IIf([1st Visit] Like #1/*/*#,"1","0&quot

hth


Jeff Bridgham
bridgham@purdue.edu

 

[CajunCenturion]

Have you tried using the Month function:

1stJanY: IIf((Month([1st Visit])=1),"1","0")

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[jebry]

Hi!

Good call CajunCenturion, that one sounds like a winner.



Jeff Bridgham
bridgham@purdue.edu

 

[awl]

"CajunCenturion": your suggestion worked just fine and I appreciate the other comments provided. I can use this formula in another Database that I had questions with. Thanks very much! Curtis.

 

[gcyeung]

Hi, I have this table mytable

ID Level Amount1 Amount2 Amount3 Amount4

10 1 9 10 15 6
10 2 2 15 19 2
11 1 5 11 23 0
11 2 6 12 73 0
12 1 7 2 0 0
12 2 3 25 0 0
13 1 5 45 18 0
13 2 7 48 73 0
13 3 2 235 55 0

The prim. keys are ID and Level. For each line I would like to count the nonzeros from the amount1-4 put it in the 3rd column and take out the trailing zeros to produce a .txt file:

mytable.txt:

Non zero Counter
|
V
10, 1, 4, 9 , 10, 15 ,6
10, 2, 4, 2, 15 ,19 ,2
11, 1, 3, 5 , 1 ,23
11, 2, 3, 6 , 12 ,73
12, 1, 2, 7 , 2
12, 2, 2, 3 , 25
13, 1, 3, 5 , 45, 18
13, 2, 3, 7 , 48, 73
13, 3, 3, 2 , 235, 55

My 1st question is how can I use sql query to find the count of nonzero of amount1 - amount4 to produce the counter value for the txt file?
My 2nd question is, I have been using macro with TransferText to transfer table to .txt straight, can I still use it at this case while the number of column may varies depending on the trailing zeros to be omitted?

Thanks
gcyeung