dlookup in a form, must display a table value (1 Viewer)

[bulbisi]

dlookup in a form, must display a table value SOLVED

hello,
just joined this forum as it sounds to be the most efficient today I found a lot of solutions just surfing your posts. But this new problem is annoying me.

I do have, among others:
1 table "LastPrintDate"
where I'm looking for the value in field "LastPrint" in regard of a value in field "Query"
and 1 form where I have a field with a dlookup that is actually:

dlookup("LastPrint","LastPrintDate","query = 'Loan1FR'")

problem:
it returns a result "#Name?"

some information for you to help me:
the field into the form is a textbox
the result must be a date in the format " now() " and this result exists already
i'm using Access 2010 (thank you boss)

for the story: it is a form containing a very good working vba connected savebutton, and next to it must be displayed the last print date.

Hope somebody can help me

Chris

 

[JamesMcS]

As far as I know DLookup statements have to look like this:

dlookup("[LastPrint]","[LastPrintDate]","[query] = 'Loan1FR'")

Not sure if that will make the difference...

 

[bulbisi]

Well tried
but no
have copy-pasted your correction, and checked my links to the table but it doesn't work.

So I have tried using some vba function. And there the dLookup is working in step-by-step (name of my sub = loan1FRdate() )

Public Sub Loan1FRdate()
Dim Loan1FRdate As Date
Loan1FRdate = dlookup("[LastPrint]", "[LastPrintDate]", "[query] = 'Loan1FR'")
Debug.Print Loan1FRdate
End Sub

Then I have linked that variable from that textbox but i got the same error ( #name? )

I'm pretty new with Access & vba but keep touching a bit of everything (Excel professional = that helps)
I'm sad this is not working. If you think there is any other solution that might work, I would really appreciate it

 

[JamesMcS]

You haven't got Lastprint and Lastprintdate round the wrong way have you? At the moment it's looking up a value in the field Lastprint, in the table lastprintdate, where the field Query in lastprintdate is equal to "Loan1FR". Is that right?

Also I think that the criteria part has to result in a single value - so if there's more than one record where Query is "Loan1FR" it won't work.

Have a look at this:

http://techonthenet.com/access/functions/domain/dlookup.php

 

[bulbisi]

yes, your description is right, table, fields, etc. are well named at the right place (I do agree I would need to change the name though, but it is not the point here)

I have only one Query value "Loan1FR" so should get one and only one result in my dlookup

I'm trying another way: the FindRecord command. But considering it is a fixed criteria value, it might not work.

 

[JamesMcS]

Can you upload a copy of the DB with any sensitive data stripped out?

 

[bulbisi]

The DB weight 30Mb (compacted) so I don't think so, but here is a bunch of screenshots in attachments.
You need the vba code that I pasted in an earlier post.

Chris, burning

 

[JamesMcS]

Oh - I think it might have to be a public function if you want it to return a value:

Public Function Loan1FRdate()
Dim Loandate as date
Loandate = cdate(dlookup("[LastPrint]", "[LastPrintDate]", "[query] = 'Loan1FR'"))
Loan1FRDate=Loandate
End Function

Give that a go

 

[bulbisi]

wow
didn't think of that
i am like a blonde with these subs/functions/procedures
Looks like I have to work on it

anyway thanks a lot! It works!

A new scale for you

Chris

 

[JamesMcS]

Nice one, should have spotted that one a while back... but hey ho, as long as it works!

 

[bulbisi]

Sure!

oh
and
are you able to give me the lines to convert a Null value into a "never" print?
it should be something like
...
iif dlookup .................. is error then
else loandate = dlookup ................
end if
...
but i'm not familiar with this

 

[JamesMcS]

Yes, but you'll have to change the data type away from Date:

Public Function Loan1FRdate()

Dim Loandate
 
If not iserror dlookup("[LastPrint]", "[LastPrintDate]", "[query] = 'Loan1FR'") Then

Loandate = cdate(dlookup("[LastPrint]", "[LastPrintDate]", "[query] = 'Loan1FR'"))
Loan1FRDate=Loandate
 
Else
 
Loan1FRDate = "Never"
 
End If
End Function

 

[bulbisi]

I understand how it works
it is just that 'if not' line is in red in my visual basic window

Error message:
Compile error : expected then or goto

will give it a go for tonight, tired, cannot think anymore

 

[JamesMcS]

Idiot.... it's

If not iserror(dlookup("[LastPrint]", "[LastPrintDate]", "[query] = 'Loan1FR'")) Then

I'm getting square eyes too.... time for some liquid refreshment I think. Have a nice evening!

 

[bulbisi]

good morning Europe,
crazy man I am, just logged in even without a Hello to my colleagues because I wanted to test your new code.
Well, It looks like we have a Null problem: "Invalid use of null" highlighting the "Loandate = ... " line

Never had a problem like that before, my expectations on this file are maybe to high


EDIT:
as the LastPrintDate table structure is not moving anymore, I can write something else in the LastPrint field like " never ".
Therefore I have to go out of that Date thing
So from the previous function you gave me, i just removed the "as date" and the cdate. It works perfectly.
Up to you to try another solution but I'm happy with this

Muchas gracias and have a good day