Problem with DLookup (1 Viewer)

[keving]

I am trying to use Dlookup in a report page footer to populate a text box. The Dlookup statment works great when the criteria is hard coded as the following examples demonstrates.

=DLookUp("[Points]","[Winning Teams Table]"," [Date]=#2002/08/15# and [id] =4")

When the hard coded date is replace with a variable the statement returns the first occurance of id in the table.

=DLookUp("[Points]","[Winning Teams Table]"," [Date]=datevar and [id] =4")

Does anyone have any ideas why this might be happening. I have check the variable datevar and it does contain the value required?

Thanks for your help.

 

[yippie_ky_yay]

Hey KevinG,

try this:
=DLookUp("[Points]","[Winning Teams Table]", "[Date]=" & datevar & "and [id] =4")

It should be noted though that using DLookup is never the recommended solution (I've been guilty of this myself!). Try adding the date to your query and placing the new field in the footer instead.

Let me know if it works out for you!

-Sean
also from Ottawa

 

[keving]

Thanks, for the help. I have tried to split the statement as you suggest but I have not been successful yet. I am also trying other approaches. Everyone appears to have a low opinion of the Dlookup statement? Thanks again.

Kevin ....

 

[yippie_ky_yay]

Hey Kevin,

the reason for their low opinion is because it is kinda like cheating - everything you need for your report you should be able to provide in the record source. There are cases where I do use it because I need one little bit of info that has nothing to do with the report.

Anyways - we'll be able to get this to work! I'm not sure how you created the rest of your report, so we'll start from scratch. Create a new query, something like:
Select * FROM [Winning Teams Table]
WHERE DATE = [Please enter Date] AND ID=[ID Number];

When you run this, you'll notice that access cannot find the two fields enclosed in brackets, so it will prompt you for them (the message in the prompt will be what is written in them). (If the date one doesn't work then surround your date when you enter it by pound signs - #)

Now create a report and use your new query as the Record source. Now when you run the report, it will prompt you for those two fields.

When you get that working, you can start to think of ways to pass values to that form (ie by pressing a button on a form that will pass the two needed values and open the report).

Good luck!

-Sean

 

[keving]

I finally got it to work. I added the # sign to the Dlookup statement as the following example shows.

=DLookUp("[Points]","[Winning Teams Table]", "[Date]=#" & datevar & "# and [id] =4")

It works great. I really appreciate your help. The # signs were a great clue.

Like you, I am using the Dlookup to get a single piece of unrelated data.

Thanks again for your help.

Maybe someday our paths will cross at the Prescott, over a cold one.

Kevin .......