I keep getting a "#Error"

[Guest]

I can't figure out why I am getting the "#Error" result in my query. This is
the source of that field:
Problem?: IIf([Daily_Tracking.Day_Worked]<=[CPR_Tracking.Week_Ending] And
[Daily_Tracking.Day_Worked]>=[CPR_Tracking.Week_Ending]-7 AND
[CPR_Tracking.Week_Ending]<> "NP","No","Yes")

It returns the "Yes" value fine, but it won't return a "no" value, instead
of "no" I get the "#Error". Please help. Thanks.

 

[Guest]

Sorry...I managed to figure this one out myself. Thanks anyways.

 

[Guest]

What did you do to get it to work? I am having same difficulty. Thanks for
your time, Ryan

Sorry...I managed to figure this one out myself. Thanks anyways.

I can't figure out why I am getting the "#Error" result in my query. This is
the source of that field:
Problem?: IIf([Daily_Tracking.Day_Worked]<=[CPR_Tracking.Week_Ending] And
[Daily_Tracking.Day_Worked]>=[CPR_Tracking.Week_Ending]-7 AND
[CPR_Tracking.Week_Ending]<> "NP","No","Yes")

It returns the "Yes" value fine, but it won't return a "no" value, instead
of "no" I get the "#Error". Please help. Thanks.

 

[Sylvain Lafontaine]

You are using [CPR_Tracking.Week_Ending] both as a numeric value (by
substracting 7) and a string value (by comparing to "NP"). Things to
verify:

1- Make sure that [CPR_Tracking.Week_Ending] is a numeric value. Set an
explicit cast with CInt() or CLng() is necessary.

2- Make sure that none of these fields is null. Use Nz() if necessary.

3- Add parenthesis around « CPR_Tracking.Week_Ending] - 7 » (Not sure of
this last one but I don't have the time to verify the order of precedence
for the mathematical/logical operators under VBA.)

S. L.

What did you do to get it to work? I am having same difficulty. Thanks for
your time, Ryan

Sorry...I managed to figure this one out myself. Thanks anyways.

I can't figure out why I am getting the "#Error" result in my query.
This is
the source of that field:
Problem?: IIf([Daily_Tracking.Day_Worked]<=[CPR_Tracking.Week_Ending]
And
[Daily_Tracking.Day_Worked]>=[CPR_Tracking.Week_Ending]-7 AND
[CPR_Tracking.Week_Ending]<> "NP","No","Yes")

It returns the "Yes" value fine, but it won't return a "no" value,
instead
of "no" I get the "#Error". Please help. Thanks.

 

[Guest]

Sylvain, I should of been more clearer....this actually is my questions:

This is what I am trying to accomplish:
I am tracking patients on a once every 3 Month basis after an initial
assessment. If they "Pass" two visits in a row they will no longer be
tracked. But I want to capture the patients who have not "Passed" until they
do "Pass" (again they have to pass two visits in a row.)


I am not sure if my tables are set up correctly, but this is what I have:
I have two tables; First table tblInitialSettings: InitialDate (Date field),
InitialResult(pass/fail). Second table tblFollowUp: FollowUpDate (Date
Field), FollowUpType (cboBox w/"3 Month", "6 Month", "9 Month",.......,"4
Years", FollowUpResult (Pass/Fail).

I have been trying to write IIF Statements but I get no where. For example:

IIF(IIF([InitialResult]="Pass" and IIF([FollowUpType] = "3 Month" and
IIF([FollowUpResult]="Pass")))........

I am confused. Again, I may have set up my tables wrong. Short of just
putting check boxes on my forms that state "3 Month Follow Up Pass"/"3 Month
Follow Up Fail" I am at a loss

Thanks for your time, Ryan

You are using [CPR_Tracking.Week_Ending] both as a numeric value (by
substracting 7) and a string value (by comparing to "NP"). Things to
verify:

1- Make sure that [CPR_Tracking.Week_Ending] is a numeric value. Set an
explicit cast with CInt() or CLng() is necessary.

2- Make sure that none of these fields is null. Use Nz() if necessary.

3- Add parenthesis around « CPR_Tracking.Week_Ending] - 7 » (Not sure of
this last one but I don't have the time to verify the order of precedence
for the mathematical/logical operators under VBA.)

S. L.

What did you do to get it to work? I am having same difficulty. Thanks for
your time, Ryan

Sorry...I managed to figure this one out myself. Thanks anyways.

:

I can't figure out why I am getting the "#Error" result in my query.
This is
the source of that field:
Problem?: IIf([Daily_Tracking.Day_Worked]<=[CPR_Tracking.Week_Ending]
And
[Daily_Tracking.Day_Worked]>=[CPR_Tracking.Week_Ending]-7 AND
[CPR_Tracking.Week_Ending]<> "NP","No","Yes")

It returns the "Yes" value fine, but it won't return a "no" value,
instead
of "no" I get the "#Error". Please help. Thanks.

 

[Sylvain Lafontaine]

With this kind of design, you will probably get nowhere because it's very
difficult to make such a query. In fact, the best way to resolve this
probably involve the use of subqueries and of the clauses Exists (...), Not
Exists (...) and IN (...).

A better (an easier) way for you would be to store in the database the next
tracking date for each patient by adding 3 months to the last rendez-vous or
the last attempt; with a number telling if it's the first or the second
attempt. If a second attempts fail, then you do nothing and all tracking
for this customer will cease in the futur.

If you really want, you can build a query that will return the required
records but it's probably difficult to write; especially for a beginner.

Also, instead of writing things like "3 months", you should store the number
instead: 3 for 3 months, 12 (months) for one year, etc. You can still
display "3 months" in your combobox for an easier pick. Storing the number
have the avantage that it can be directly used in a calculation on dates.
Take a look at any good book on Access to know how to do this.

Finally, when you want to ask a question, it's always better to not mix it
up with the question of someone else; especially if they don't have a clear
and direct link or some very precise and limited common ground.

S. L.

Sylvain, I should of been more clearer....this actually is my questions:

This is what I am trying to accomplish:
I am tracking patients on a once every 3 Month basis after an initial
assessment. If they "Pass" two visits in a row they will no longer be
tracked. But I want to capture the patients who have not "Passed" until
they
do "Pass" (again they have to pass two visits in a row.)


I am not sure if my tables are set up correctly, but this is what I have:
I have two tables; First table tblInitialSettings: InitialDate (Date
field),
InitialResult(pass/fail). Second table tblFollowUp: FollowUpDate (Date
Field), FollowUpType (cboBox w/"3 Month", "6 Month", "9 Month",.......,"4
Years", FollowUpResult (Pass/Fail).

I have been trying to write IIF Statements but I get no where. For
example:

IIF(IIF([InitialResult]="Pass" and IIF([FollowUpType] = "3 Month" and
IIF([FollowUpResult]="Pass")))........

I am confused. Again, I may have set up my tables wrong. Short of just
putting check boxes on my forms that state "3 Month Follow Up Pass"/"3
Month
Follow Up Fail" I am at a loss

Thanks for your time, Ryan

You are using [CPR_Tracking.Week_Ending] both as a numeric value (by
substracting 7) and a string value (by comparing to "NP"). Things to
verify:

1- Make sure that [CPR_Tracking.Week_Ending] is a numeric value. Set an
explicit cast with CInt() or CLng() is necessary.

2- Make sure that none of these fields is null. Use Nz() if necessary.

3- Add parenthesis around « CPR_Tracking.Week_Ending] - 7 » (Not sure of
this last one but I don't have the time to verify the order of precedence
for the mathematical/logical operators under VBA.)

S. L.

What did you do to get it to work? I am having same difficulty. Thanks
for
your time, Ryan

:

Sorry...I managed to figure this one out myself. Thanks anyways.

:

I can't figure out why I am getting the "#Error" result in my query.
This is
the source of that field:
Problem?:
IIf([Daily_Tracking.Day_Worked]<=[CPR_Tracking.Week_Ending]
And
[Daily_Tracking.Day_Worked]>=[CPR_Tracking.Week_Ending]-7 AND
[CPR_Tracking.Week_Ending]<> "NP","No","Yes")

It returns the "Yes" value fine, but it won't return a "no" value,
instead
of "no" I get the "#Error". Please help. Thanks.