Hi!

It's been a too long day I guess, because this shouldn't be such a problem...

The problem is the bold part. Both TblLogboek as the current report has the same field, and I want to have it look up the record with the matching ID

But how do I make it clear that one of them is the report field, and the other one is from the table?

Format(CDate(DLast("[Tijd]";"TblLogboek"; <b>[MAINID] = [MAINID]</b> And ([Status]="1" Or [Status]="VK")));"dddd d mmmm")

Thx for the help!

The criteria argument (3rd argument) of DLast and other Domain Aggregate functions must be a String.

Try:


assuming that [MainID] is a Numeric Field and [Status] is a Text Field.

Hi MrSiezen,

Where is this code? In the report or a ControlSource or in a query?

Maybe:

Format(CDate(DLast("[Tijd]";"TblLogboek"; [MAINID] = " & Me.[MAINID] & " And ([Status]="1" Or [Status]="VK")));"dddd d mmmm")

You may have to change the reference to the report as appropriate

hth,

d

Hmmm,

Think I understand what you mean, but you forgot one " in your code. Tried to fix it myself:



I tried removing the first " before AND, and put it behind = "VK"))). But when I open it, it doesn't work. If I go back to design mode, it's back where it is now....

Oh sorry it's in a control source!

Hmm

your solution seems logical to me CheekyBuddha, but for some reason it doesn't work either...

Make sure you include the equal sign in the ControlSource. Try:



Note that I used single-quotes around 1 and VK. You can either use [MAINID] or Report![MAINID]

This works for me!

[MAINID] = reports![Bevestigings brief]![MAINID]

Hmm well I thought it worked because it didn't give an error anymore, but now it doesn't find any matches...

Both fields are long integers, so that couldn't be the problem.

Oh I changed what you said Dinh. Didn't make a difference, but it's better to work like that indeed.

Strange...


If I add the field MAINID form the field list to the report, access can't find it's value... How can that be possible. I'm sure there is a value...

Slow down, think and try 3 times before replying with your results. Also re-read your reply (before posting) and see if the reply makes sense to others who can't see your database or the expressions you used and depend on your description to work out the problem, the cause and the possible solutions.

You tried different expressions and I don't know the expressions you tried or which one didn't work. Post clearly the expression(s) you tried and the result you got for each expression.

In one reply, you mentioned "both Fields are long integers" which could be significant ... In one of my earlier replies, the way you structured your expression indicated that [Status] is a Text Field. If [Status] is a Long Field, you need to re-structure the criteria ... but then what is [VK]?

I'm very sorry for the rushing, but that report was very needed for a mailing that had to be done that day. Couldn't fix it so was forced to use an old layout... Well, that was then.

Now let's relax indeed and will look at it at ease.... No the Status field isn't a integer field. With both fields I ment both MAINID fields (the field in TblLogboek and the one on the report).

To make it a bit easier to fix, I extracted the problem part (the rest works):

=DLast("[Tijd]";"TblLogboek";"[MAINID] = reports![Bevestigings brief]![MAINID]")

But I'm pretty sure that this syntax is ok, because I also use an almost similar DLookup in another report. Tried many (of your) variations, but nothing worked.

This is wanted to say in the last post:

I select MAINID from the field list of the form, and drag it into the report which is obviously in design mode. So no coding or renaming, just using the design interface. If I try to open the report, I get this error message:

Microsoft Office Access can't find the field 'MAINID' referred to in your expression.
You may have misspelled the field name, or the field may have been renamed or deleted.

Which is weird, since it came directly from the field list... I think this error is also the cause for my problem.

Can you explain this?

You're putting together a string - and in that string, you have

"[MAINID] = reports![Bevestigings brief]![MAINID]"

Access does not interpret this as being a value from a report, so you're looking for a MAINID that has a current value of "reports![Bevestigings brief]![MAINID]" - not whatever value is in that text box on the report.

You need

"([MAINID]= " & reports![Bevestigings brief]![MAINID] & ")"

Thx for your reply, but your suggestions results in #error. Here's how I inserted your suggestion.

=DLast("[Tijd]";"TblLogboek";"([MAINID]= " & [Reports]![Bevestigings brief]![MAINID] & ")")


Here's the similar code I mentioned earlier, with the same syntax I have now:

=IIf(NZ(DLookUp("[Vervaldatum lidmaatschap]";"TblCRM";"[CRMID] = reports![Deelnemerslijst]![MAINID]"))="";Null;CDate(NZ(DLookUp("[Vervaldatum lidmaatschap]";"TblCRM";"[CRMID] = reports![Deelnemerslijst]![MAINID]"))))

This code works perfectly on another form.


So I'm still kinda puzzled what it could be...

To be honest, I don't see how that code can work perfectly anywhere as the criteria for the DLookups have the textbox values inside the quotes.

I'm now confused as to which bit you want help with, so I'm sticking with the original.

Try this - copy and paste it in so it's exact:

=DLast("[Tijd]","TblLogboek","[MAINID]= " & Reports![Bevestigings brief].[MAINID] )

Hmmm well I pasted it, but that generates a syntax error...But I don't see it.

Can you post the database - and tell me where to look.

Ah while making the dummy database I found the problem with your syntax. I replaced , with ;

But as it still can't find anything, I attached a sample database. Couldn't post the original because some restrictions here.

There's only one report, and two tables, so you can't mis it. For some reason Access removes the last brackets from the code, don't know why...

The , and ; must be a language thing.

This report works fine for me when I change the report name in the DLast to testreport ('cos that's what it's called).

=DLast("[Tijd]","TblLogboek","[MAINID]= " & Reports!testreport.MAINID)

Ah!

So it were the brackets... Still I must use these ; instead of comma's, but at least it works

=DLast("[Tijd]";"TblLogboek";"[MAINID]= " & Reports!testreport!MAINID)

Well it seems we finally found it...Thank you very very much!

Edited by: MrSiezen on Mon Sep 5 10:03:21 EDT 2005.