DLookup with Text

[Kirsti]

Hi,

I'm trying to use the DLookup function, and the quotes around the text are driving me crazy, I just can't get it right. If anyone could help I'd really appreciate it. What I have so far is:

If IsNull(DLookup("[Total]", "qryMyQueryName", "[Name]=" ""FirstName & ' ' & LastName"" And [Total]<>)")) Then

Me.MyGraph.ChartTitle.Text = "My Report Name - No Data to Display"
Else
Me.MyGraph.ChartTitle.Text = "My Report Name"


End If

It's failing at the DLookup, and I know my syntax is wrong - any suggestions?

Kirsti

[June7]

I find it easier to use apostrophes for text delimiters. Otherwise, must triple the quote marks. FirstName and LastName are fields of form RecordSource? I assume you want <>0. Designate empty string with quote marks. Also need concatenation of FirstName. Also assume [Name] field is a constructed field in query that concatenates FirstName and Lastname. Why do search on name and not a unique id? What if you have two John Smith?

IsNull(DLookup("[Total]", "qryMyQueryName", "[Name]='" & Me!FirstName & " " & Me!LastName & "' And [Total]<>0))

[orange]

In plain English what is the DLookup criteria ?

What is in the Name field in your table?

OOops I see June7 posted while I was typing.

[Kirsti]

Hi June, sorry, I will go with Orange's advice and explain in plain english, as FirstName and LastName are not actually fields.
And now that I think about this, perhaps DLookup is not the way to go at all.

I want the IfThenElse of the report name to be based on a qryMyQuery, where the [Total] field <>0 And the [Name] field is a particular persons name (I know that it isn't good practice to hard code a name, but there are only 4 names it could possibly be).

Does that make things any clearer?

[June7]

You want to hard code an actual name, not variables, into the DLookup?

IsNull(DLookup("[Total]", "qryMyQueryName", "[Name]='John Smith' And [Total]<>0))

Do you have this code in the report Detail format event?

Is the condition backwards? Should it be =0 so title will be 'No Data'?

[Kirsti]

Hi June, yes that's right (I want to hard code a name, and it is in the report Detail format event.

I tried your code, but got "Run-time error '2001': You cancelled the previous operation" when I tried to open the report.

My exact code is:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(DLookup("[Total]", "qryTTRS2_YTD8", "[Housing_Officer_Name]='Andrea Goodman' And [Total]<>0")) Then
Me.Graph25.ChartTitle.Text = "Reason Tenants are Vacating YTD - No Data to Display"
Else
Me.Graph25.ChartTitle.Text = "Reason Tenants are Vacating YTD"
End If
End Sub

[June7]

Chart is also in the Detail section of report? I don't know why code would fail unless it is because chart has no data. I do something very similar to set axis titles and never encountered this error.

An alternative would be to not display the chart title but to use a label and code to set caption.

[Kirsti]

Yes, it's strange. I have exactly the same code working for a number of reports, however without the hard coded name in the DLookup, so I assumed it was something to do with the syntax. I'll keep looking - thanks for your help.

[June7]

I do something similar but not with a hard-coded value. However, I don't see why that would make a difference from using a variable.