IIf question (1 Viewer)

[danbl]

I have a report with a "Name" field. On the report I want to reference a constant value in a table. I am using the IIf function to try to pull this on to the report as there are number of Names to refernce. I have tried the following but notthing happens.

IIf(reportname = tablename, table name "value", " ")

Can someone tell me the problem?

 

[pr2-eugin]

Can you explain what you are trying to do, maybe by giving an example? IIF works as such..

[B]IIF([I] condition_to_check[/I] , [I]value_If_Condition_TRUE , value_If_Condition_FALSE[/I] )[/B]

Your True Part is wrong..

 

[danbl]

OK .. in a table I have a constant value for each persons name in a table. I have a report that sums data by each persons name. I want to pull the constant value from the table onto the report by each persons name in the name footer section.

Does this help?

 

[pr2-eugin]

I am no expert on Reports, I normally use Queries.. But why did you go for IIF? How would you find the 'Value' from the 'Table'? Should you not be using DLookUp?

 

[danbl]

I am not really familar with DLookup???

Can you explain?

 

[Beetle]

Paul is right. You need to use DLookup. Or, you could possibly Join this other table in a query that is the Record Source of the report and return the value that way.

Post back if you have questions about either of those methods.

 

[danbl]

Would like to see how Dlookup works.

Need to learn more about Access !!

 

[pr2-eugin]

DLookUp Works as a simplified SELECT Statement.. It is simplified because it returns only one column's value and only one row.. So the syntax goes this way..

someVariable = DLookUp ( [COLOR=Red][B]"[/B][/COLOR][I][B]column_that_has_the_Value[/B][/I][COLOR=Red][B]"[/B][/COLOR] , [COLOR=Red][B]"[/B][/COLOR][I][B]tableName[/B][/I][COLOR=Red][B]"[/B][/COLOR] , [COLOR=Red][B]"[/B][/COLOR][I][B]Condition_That_Filters_Your_Result[/B][/I][COLOR=Red][B]"[/B][/COLOR] )

Example: If your table is called 'nameValue' column that has the values is called 'valueList' and the way to match the name to the value is with an ID called 'valueID' then your DLookUp will look like,

Me.footerLbl.Caption = DLookUp("valueList", "nameValue", "valueID=" & Me.ID_Txt)

Hope this helps.

 

[Beetle]

I am not really familar with DLookup???

Can you explain?

As far as the DLookup option, we would need more details about your tables to be specific, but the basic syntax would look something like;

DLookup("[TheValue]", "[YourTable]", "[PersonName]=""" & [PersonNameFieldFromReport] & """")

This could be used as the Control Source of, for example, an unbound text box on your report. You can find more info on DLookup in Access help or online.

 

[danbl]

below is the IIf Statement I was trying.

=IIf([Physician]=t_Physician!Physician,t_Physician!time," ")

Would Dlookup be?

Dlookup("[Time]","[t-physician]","[Physician]="""&[Physician]&"""

I am trying to use this in a footer on the report.

 

[pr2-eugin]

Best answer would be, just try.. Looks fine.. Although.. this would be more pleasant..

controlName = Dlookup("Time","t-physician","Physician= '" & [Physician] & "'")

 

[danbl]

I tried both; mine says syntax error and the one you sent give #Error on the report.

Thoughts?

 

[Sketchin]

Check spelling

 

[Sketchin]

controlName = Dlookup("Time","t-physician","Physician= '" & [Physician] & "'")

Try:

controlName = Dlookup("Time","t-physician","Physician= "' & [Physician] & "'")

 

[danbl]

here is what I have tried

=DLookUp("[time]","[t_Physician]","[t_Physician].[Physician]='" &[r_Utilization].[Physician]& "'")
this latest came from an access 2003 ref book. keeps asking for a perameter for r_Utilization and then is #Error on the report.

 

[danbl]

Okay I have gotten past the error message but nothing displays.

That issue is the Report adds up time in the "00"00" format but the value in the lookup table is to large to but in the same format. An individual can have more that 24:00 hours availabe., Ex 76:00. How can i change the formats so both match? I believe that the lookup will work once I solve this. The individual and total time is calculated on the form.

Thanks for all the help

 

[danbl]

Changed the time to a number field to see if the lookup worked and it is still blank.

 

[Beetle]

Maybe we should take a step back here. Can you provide some more details about what values you are trying to compare, where and how those values are stored or calculated, the data types of the relevant fields, and how they differ?

 

[danbl]

here is the test database.

Look at the Physician table, query Utlz, and report Utilization.

Appreciate any and all help

 

[Beetle]

OK, I see the problem. You're using a Lookup field in qryUtlz for the Physician. One of the problems with using Lookup fields in tables or queries is that it disguises the value that is actually being stored in the table. That field displays the Physician Name, but it actually stores the ID value (which is named DrID in your t_Physician table), so any criteria applied against this field must reference the ID value, not the name.

So the DLookup on your report needs to be adjusted to reference the DrID field, and since that field is a number data type - not text - the quote delimiters need to be removed;

=DLookUp("Time","t_physician","[DrID]= " & [Physician])

See this link for more on the flaws of Lookup fields.

On another note, I don't understand what you are trying to accomplish with the [time] field in the t_Physicians table. This is a number (Long Integer) field and you have (as a test value I guess) the number 90 in one of the records (the rest of the records are null). You are then trying to format this number as Short Time on your report, which isn't going to work. Date/Time values in Access are actually stored as a floating point number where the integer portion represents the number of days since midnight 12/30/1899 and the decimal portion represents the time portion of each day. For example;

September 19 2012 2:32 pm

is stored as;

41171.606087963

So the number 90, when converted to a Date/Time value equates to March 30, 1900. There is no decimal portion to the number so the implied time is midnight (00:00). Therefore, any attempt to format this number (or any whole integer) as Short Time will result in "00:00".