#Error in query

[lmcc007]

#Error

Query gets #Error when I hit Run!

I am trying to say:

1) Check to see if Documenter.FileName is not empty

2) If it is not empty, look for DocumentTypeID 10

3) Display image icon
​

Below is the code I'm using:

EventJobListing: IIf(DLookUp("Documenter.FileName","Documenter","EventID = " & [EventID] & " AND DocumentTypeID = 10") Is Not Null,"C:\Users\LMCC007\Pictures\paperclip -- tiny.bmp",Null)

 

[jzwp22]

I believe that "IS NOT NULL" is SQL syntax and as such can only be used in the WHERE clause of a query. I would recommend using the IsNull() function

EventJobListing: IIf(IsNull(DLookUp("Documenter.FileName","Documenter","EventID = " & [EventID] & " AND DocumentTypeID = 10")),Null, "C:\Users\LMCC007\Pictures\paperclip -- tiny.bmp")

It does not appear that you are using the actual value returned by the DLookup() function or did you intend to substitute the DLookup for the path you have C:\Users....?

Alternatively, you could use the DCount() function to check if a record exists and if it does (Dcount()<>0), return the file name & if not (DCount()=0, return Null

EventJobListing: IIf(DCount("*","Documenter","EventID = " & [EventID] & " AND DocumentTypeID = 10")=0,Null, DLookUp("Documenter.FileName","Documenter","EventID = " & [EventID] & " AND DocumentTypeID = 10"))

 

[lmcc007]

I get the #Error with both.

I change the last to:

EventJobListing: IIf(DCount("*","Documenter","EventID = " & [EventID] & " AND DocumentTypeID = 10")=0,Null,"lovely")

and it seems to be working.

You know the code I first posted is being used in another query and it is working fine. That's the most frustrating because I can't tell what I am doing wrong especially when it works in one db and not in the other.



So, let me see if I understand the above code correctly.

1. * means to count all the records in Documenter based on the EventID you are on if the DocumentType is 10 and the field is not empty
2. If empty put Null
3. If not put “lovely”

 

[jzwp22]

Since the DCount() and DLookup() are essentially the same structure with the exception of the first term (the field designation), and the DCount() itself works fine, I have to wonder if something is still wrong with the DLookup(). So do you have the field name referenced correctly in the DLooup? Technically speaking you do not need the Is not null or IsNull or even the DCount() since the DLookup() function should return the value or Null depending on whether the criteria of the DLookup is/are satisfied or not, respectively.

So will this expression work?

EventJobListing: DLookUp("Documenter.FileName","Documenter","EventID = " & [EventID] & " AND DocumentTypeID = 10")

Can you post the entire SQL text for the query? I would like to see from where all of the fields are coming.

 

[boblarson]

In the DLookup you don't include the table name in the first argument. You only use the field name. The table is already included in the second argument. So this:

DLookUp("Documenter.FileName","Documenter",...etc.

Should be this:

DLookUp("FileName","Documenter",...etc.

 

[lmcc007]

Since the DCount() and DLookup() are essentially the same structure with the exception of the first term (the field designation), and the DCount() itself works fine, I have to wonder if something is still wrong with the DLookup(). So do you have the field name referenced correctly in the DLooup? Technically speaking you do not need the Is not null or IsNull or even the DCount() since the DLookup() function should return the value or Null depending on whether the criteria of the DLookup is/are satisfied or not, respectively.

So will this expression work?

EventJobListing: DLookUp("Documenter.FileName","Documenter","EventID = " & [EventID] & " AND DocumentTypeID = 10")

Can you post the entire SQL text for the query? I would like to see from where all of the fields are coming.

I get #Error.

Here is the SQL text:

SELECT Event.EventID, Company.CompanyID, IIf([Company]![CompanyName]="","Company ID " & [Company]![CompanyID],[Company]![CompanyName]) AS Company, DLookUp("Documenter.FileName","Documenter","EventID = " & [EventID] & " AND DocumentTypeID = 10") AS EventJobListing
FROM tlkpActivity INNER JOIN (Company INNER JOIN (qryF_EventDateMax RIGHT JOIN Event ON qryF_EventDateMax.CompanyID = Event.CompanyID) ON Company.CompanyID = Event.CompanyID) ON tlkpActivity.ActivityID = Event.ActivityID
ORDER BY Event.EventID;

 

[boblarson]

Try removing the table reference from the first argument of the DLookup:

SELECT Event.EventID, Company.CompanyID, IIf([Company]![CompanyName]="","Company ID " & [Company]![CompanyID],[Company]![CompanyName]) AS Company, DLookUp([B][COLOR=red]"FileName"[/COLOR][/B],"Documenter","EventID = " & [EventID] & " AND DocumentTypeID = 10") AS EventJobListing
FROM tlkpActivity INNER JOIN (Company INNER JOIN (qryF_EventDateMax RIGHT JOIN Event ON qryF_EventDateMax.CompanyID = Event.CompanyID) ON Company.CompanyID = Event.CompanyID) ON tlkpActivity.ActivityID = Event.ActivityID
ORDER BY Event.EventID;

 

[lmcc007]

Try removing the table reference from the first argument of the DLookup:

SELECT Event.EventID, Company.CompanyID, IIf([Company]![CompanyName]="","Company ID " & [Company]![CompanyID],[Company]![CompanyName]) AS Company, DLookUp([B][COLOR=red]"FileName"[/COLOR][/B],"Documenter","EventID = " & [EventID] & " AND DocumentTypeID = 10") AS EventJobListing
FROM tlkpActivity INNER JOIN (Company INNER JOIN (qryF_EventDateMax RIGHT JOIN Event ON qryF_EventDateMax.CompanyID = Event.CompanyID) ON Company.CompanyID = Event.CompanyID) ON tlkpActivity.ActivityID = Event.ActivityID
ORDER BY Event.EventID;

Still get Error#.

Here is my code from another table that is working:

EventNote: IIf(DLookUp("EventNoteID","EventNote","EventID = " & [EventID]) Is Not Null,"C:\Users\LMCC007\Pictures\Note.bmp",Null)

Why does this code works and not the other?

 

[jzwp22]

I don't see anything wrong with the way your query is structured. I did some testing here per Bob's suggestion of removing the table reference in documenter.filename, but it did not make a difference in my sample database. But at this point it might be worth a shot. The other thing, I don't know if the word filename is a reserved word in Access, so you might try square brackets around it (probably a long shot). The only other thing is to fully reference the eventID field as Event.EventID:

SELECT Event.EventID, Company.CompanyID, IIf([Company]![CompanyName]="","Company ID " & [Company]![CompanyID],[Company]![CompanyName]) AS Company, DLookUp([COLOR="red"]"[FileName]"[/COLOR],"Documenter","EventID = " & [COLOR="Red"]Event.EventID [/COLOR]& " AND DocumentTypeID = 10") AS EventJobListing
FROM tlkpActivity INNER JOIN (Company INNER JOIN (qryF_EventDateMax RIGHT JOIN Event ON qryF_EventDateMax.CompanyID = Event.CompanyID) ON Company.CompanyID = Event.CompanyID) ON tlkpActivity.ActivityID = Event.ActivityID
ORDER BY Event.EventID;

 

[boblarson]

Also, is DocumentID really numeric in the table? Or, also - you aren't using lookups at table level there are you? (just throwing out ideas here).

 

[lmcc007]

Also, is DocumentID really numeric in the table? Or, also - you aren't using lookups at table level there are you? (just throwing out ideas here).

Yes, it's numeric.

No lookups in table. You guys have hit me over the head about that even though all the classes and books teach it that way--I no longer use lookups at table level; they are created in the form. I even removed the multi-fields in the table.

 

[boblarson]

Well, I guess you know what this means then...



I know we've said this too many times to you already but... here goes....






Can you upload a copy of your database?

 

[lmcc007]

Well, the below seems to be working:

EventJobPosting: IIf(DLookUp("DocumenterAttachment","Documenter","EventID = " & [EventID] & " AND [DocumentTypeID]=10") Is Not Null,"C:\Users\LMCC007\Pictures\paperclip -- tiny.bmp",Null)

Do you'll see anything different with the above code that I don't see? I'm trying to figure out why this code works and others I posted didn't.

I have been studying DLookup Usage Samples (http://www.mvps.org/access/general/gen0018.htm)
back and forward. It's frustrating to think something so simple is giving me this much trouble.

Is my sight failing me--did I miss something?

 

[jzwp22]

The field name in your original DLookup() was: "Documenter.FileName"
Now it is this: "DocumenterAttachment"

 

[lmcc007]

The field name in your original DLookup() was: "Documenter.FileName"
Now it is this: "DocumenterAttachment"

Ok, thanks!