Function DLookup (1 Viewer)

rdw456

Novice
Local time
Today, 00:48
Joined
Jul 20, 2012
Messages
43
I have a form with a text box and have the control source set to the function listed below. Payin is currency and end is a date. I get the following error (#error or name ) . I have tried many different configurations to no avail any help would be appreciated.

Thanks Bob

=DLookUp("payIn","tblDepositInformation","[End]= #1/7/2017# ")
 

Minty

AWF VIP
Local time
Today, 07:48
Joined
Jul 26, 2013
Messages
10,355
You'll need to ensure the date is formatted mm/dd/yyyy style to make it work. See the links in my signature for sensible explanations.
 

sneuberg

AWF VIP
Local time
Today, 00:48
Joined
Oct 17, 2014
Messages
3,506
I suggest testing it in the Immediate Window, i.e., paste in

?DLookUp("payIn","tblDepositInformation","[End]= #1/7/2017# ")

and hit return It might give you more information about what's wrong.
 

sneuberg

AWF VIP
Local time
Today, 00:48
Joined
Oct 17, 2014
Messages
3,506
In case you don't know how to get to the Immediate Window
  1. Go to the VBE Editor. Click Visual Basic in the CREATE tab Macro & Code group or in some versions Alt F11 will get you there
  2. In the top Menu click on View and then Immediate Window or press Ctrl G
 

missinglinq

AWF VIP
Local time
Today, 03:48
Joined
Jun 20, 2003
Messages
6,423
Since you're in one of those Commonwealth Nations that use that funny dd/mm/yyyy formatting for Dates, and going along with Minty's suggestion, it's probably a good idea if you look at Allen Browne's article on Access and non-American formatted Dates:

International Dates in Access

But while 1/7/2017 might give not give you the results you expect, it is a valid Date, from the viewpoint of the Access Gnomes, and that, together with the fact that your syntax is correct, for using an explicit Date in this function, I suspect that the problem here is something else.

My guess would be that you're confusing the aforementioned Gnomes, no end, by using a Reserved Word as the name of a Field! Try changing the Field name from End to something else, and see what happens.

Linq ;0)>
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:48
Joined
Jan 20, 2009
Messages
12,849
Since you're in one of those Commonwealth Nations that use that funny dd/mm/yyyy formatting for Dates,

It is the US date format that is funny. :p What other numeric value on the planet is expressed with the Least Significant Part in the middle?:confused:

But while 1/7/2017 might give not give you the results you expect, it is a valid Date, from the viewpoint of the Access Gnomes,

It isn't actually the Access Gnomes but the Windows Gnomes that do this. Access just wraps the Windows function. My favourite is how they will accept date strings like "29/2/17" as valid and put them somewhere most users wouldn't think to look when ordered or filtered.
 

missinglinq

AWF VIP
Local time
Today, 03:48
Joined
Jun 20, 2003
Messages
6,423
Didn't know that there were Window Gnomes...always thought that they were Window Ogres! :banghead:
 

rdw456

Novice
Local time
Today, 00:48
Joined
Jul 20, 2012
Messages
43
Hi All still :banghead: I have created a blank form ( Form1) placed a single text box on the form and tried all the variations suggested still receive error when opening form. not sure where to go from here. It seems a lot of people have this problem and no solutions

Bob

PS surely someone has a solution???????
 

sneuberg

AWF VIP
Local time
Today, 00:48
Joined
Oct 17, 2014
Messages
3,506
What errors did you get when you pasted it into the Immediate Window as I suggested in post #3? That should give you specific information something like



Let us know what you got or better yet
Code:
upload
your database.
 

Attachments

  • ImmediateWindow.jpg
    ImmediateWindow.jpg
    61.9 KB · Views: 481

rdw456

Novice
Local time
Today, 00:48
Joined
Jul 20, 2012
Messages
43
Thanks sneuberg for your reply. I tried what you suggested in the immediate window but it just returned what i typed in minus the question mark so not sure what to do with that. i am still a novice so not very good on some of the technical stuff. I have a make table query that does sum and group stuff so I had to have a table to be able to use the results. I did change the field name from End which someone mentioned was a reserved word so it is WkEnding.

The outcome that I am trying to achieve is a form that uses a table as its source.
On the form I have an unbound txtbox wich I enter a date this in turn is referenced to another txtbox that has the DLookup function to provide the user information from another table for information purposes.

Thanks Bob
 

Attachments

  • NewZipDB4.zip
    11.2 KB · Views: 64

Beetle

Duly Registered Boozer
Local time
Today, 01:48
Joined
Apr 30, 2011
Messages
1,808
You have the table name incorrect in your DLookup. You have tbDepositInformation, should be tblDepositInformation
 

sneuberg

AWF VIP
Local time
Today, 00:48
Joined
Oct 17, 2014
Messages
3,506
Edit: You can ignore this. See other posts.

You need to type it (or copy and paste it) in precedented by the question mark just like in the screen shot it post #9. You can try this is the attached database where it works and produces the answer 10000000. You can also see the DLookUp work in the form. There is nothing wrong with the DLookup. Either payIn, tblDepositInformation, or End are misspelled, don't exist or if they do I would suspect your database or Access is corrupt.

Something strange is going on. If you just typed the DLookup in without the question mark it should have giving you an error not just repeated what you typed.
 

Attachments

  • DLookUpTest.accdb
    368 KB · Views: 52
Last edited:

sneuberg

AWF VIP
Local time
Today, 00:48
Joined
Oct 17, 2014
Messages
3,506
Sorry didn't notice the upload. Beetle is right. The table is incorrectly named. The other thing I noticed is that you have the textbox format set to short date. I think you want this to be currency.

Edit: But for practice I suggest you copy and paste
Code:
?DLookUp("PayIns","tblDepositInformation"," WkEnding = #1/7/2017# ")

into the Immediate Window and see the result which should be 1495.65
and with the misspelled table name

Code:
?DLookUp("PayIns","tbDepositInformation"," WkEnding = #1/7/2017# ")

it should tell you it can't find the table or query "tbDepositInformation"
 
Last edited:

rdw456

Novice
Local time
Today, 00:48
Joined
Jul 20, 2012
Messages
43
Hi all
Beetle picked up on the table name which was misspelled was tbDepositInformation and should be tblDepositInformation when i put this in the intermediate window it returns 1495.65 when I open the form now I get the date 1/14/1903 Where did that come from??

Bob

Ps running access 2003 so cant open access 2007
 

rdw456

Novice
Local time
Today, 00:48
Joined
Jul 20, 2012
Messages
43
Hi Steve,

I have tried all formats

no format result 1110.2

General Date result 1/14/1903 4:48:00 am

Long Date result Wednesday January 14 1903

medium Date 14-Jan-1903

Short Date 1/14/1903

so that does not seem to make any difference



BOB
 

Beetle

Duly Registered Boozer
Local time
Today, 01:48
Joined
Apr 30, 2011
Messages
1,808
You are returning a currency data type from the table. Why are you trying to use a Date format on that value?
 

sneuberg

AWF VIP
Local time
Today, 00:48
Joined
Oct 17, 2014
Messages
3,506
Why not try a currency format as I suggested in post 13?
 

rdw456

Novice
Local time
Today, 00:48
Joined
Jul 20, 2012
Messages
43
Hi the make table query sums and groups records based on the week. Each day is a record and the WkEnding field is the ending date for each entry so I want the sum of the weeks payins the WkEnding is the selection criteria

so I want a dollar value for a series of dates

Bob
 

jdraw

Super Moderator
Staff member
Local time
Today, 03:48
Joined
Jan 23, 2006
Messages
15,364
PMFJI,

I changed the format of the text box to currency, adjusted the table name to include the "L"
I made a label on the form and set it to the Controlsource to show in the result
attached as jpg.

Good luck.
 

Attachments

  • DLookupIssue.jpg
    DLookupIssue.jpg
    96.8 KB · Views: 72

Users who are viewing this thread

Top Bottom