Function DLookup

[rdw456]

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]

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]

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]

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]

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]

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. What other numeric value on the planet is expressed with the Least Significant Part in the middle?

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]

Didn't know that there were Window Gnomes...always thought that they were Window Ogres! :banghead:

 

[rdw456]

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]

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

upload

your database.

 

[rdw456]

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

 

[Beetle]

You have the table name incorrect in your DLookup. You have tbDepositInformation, should be tblDepositInformation

 

[sneuberg]

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.

 

[sneuberg]

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

?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

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

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

 

[rdw456]

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

 

[sneuberg]

now I get the date 1/14/1903 Where did that come from??

You have the format of the textbox set to short date.

 

[rdw456]

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]

You are returning a currency data type from the table. Why are you trying to use a Date format on that value?

 

[sneuberg]

Why not try a currency format as I suggested in post 13?

 

[rdw456]

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]

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.