Can someone check Dlookup Code 4 me!!

[DaniBoy]

Hello,

I have a problem with Dlookup, I am triying to bring in a value to my Clients form from a table called "Abonos", the field that I need is "AboCantidad" and I need it to be the same "ClienteID" that I am viewing on the form. I have tried all kinds of ways to do it but it does not work with the criteria. It works fine without the criteria.

=DLookUp("[AboCantidad]","Abonos","[ClienteID]"="&[ClienteID]&")

Also can I bring a value from a queryto a form with dlookup. I try and try, cut and paste and nothing. The book does not talk about qrys onle tables.

Another thing, do the tables have to be related to get the info?

Bradd helped me with it but I just cant get it to work.

Thanks
DaniBoy

 

[bradcccs]

Alright, I will have another bash

Alter your criteria segment to: "ClienteID =" & ClienteID

Thus:

=DLookUp("[AboCantidad]","Abonos","[ClienteID] =" & [ClienteID])

The second reference to ClienteID in the criteria statement should refer to the Text box that contains the required ClienteID.

You can lookup values from a query. It is not possible to have a table and query name as the same, so your Domain statement will be exclusively a table or query.


Brad.

 

[rich.barry]

=DLookUp("[AboCantidad]","Abonos","[ClienteID]=" & forms!formabc!ClienteID)

may be the way to go.

Queries should work just the same as a recordsource for a DLookup as a Table

Regards

Richard

 

[bradcccs]

For my own benefit:

DaniBoy's original message to me was:
****
I have a client form where I want to have an unbound field to bring a value from a query:

DLookup([Total],"Total Qry")

Well I cant get anything with or without a simple cirteria.

But this is the criteria that I want, for it to have the same clientId as the form as it is at the query at the time of viewing the record. If there is no record on the query to make this value on the form "$0.00"
****

My solution was presented as:

=IIf(IsNull(DLookUp("Total","Total Qry","ClientID=" & [RecIDWanted])),0,DLookUp("Total","Total Qry","ClientID=" & [RecIDWanted]))

Where "RecIDWanted" is the field name of the clientID that you wish to lookup


This principle works for me. Is there a better (more correct / efficient) way to handle null values in a dlookup?

Sorry to tag onto your post DaniBoy.

Brad.

 

[DaniBoy]

No appolagies

No need to appolagize, I welcome every help I can get!! Maybe someday I'll me the one helping people just like you guys do!!! Its tough enough to have to deal with a frustrated individual like me!!jeje You guys are the best of the best!!!

yyyeeehhhaaaaa!!!! it works Braddd!!! jeje god am so happy you got it to work, now am going to step two, jejej the null item. Will most likely be back with my tail between my legs!!! But am gonna go fight it!!

You know, this books they sell for access are crap, the book tells you to do it the wrong way!!! You all you get together and write a book!!!! I pay in advance!!!

this is what they have:

DLookUp("[Field to display]", "[Lookup Table]", "<Criteria for Search>")

"[Field to display]" in quotation marks is the field in the lookup table you want to find.

"[Lookup Table]" in quotation marks is the table containing the field you want to display.

"<Criteria for Search>" in quotation marks signifies criteria used by the lookup function.

Access suggests that Criteria for Search is not necessary, but if you want to use a different criterion for each record, it is essential. When you use DLookUp( ), the format of your criteria is critical. The syntax of Criteria for Search is as follows:

"[Field in Lookup Table] = '<Example Data>' "

You can replace the equal operator with any valid Access operator.

'<Example Data>' in single quotation marks is usually a literal, such as 'DOG' or 'AC001'. If the data is a field in the current table, you must use the following syntax:

" & [Field in This Table] & "

Notice that the field is surrounded with double quotation marks (") and ampersands (&).


I followed this to the piont and never worked!!! From looking at the code above there is not the same way as you guys did it.

Their way: DLookUp("[AboCantidad]", "[Abonos]", "[ClienteID]="&[ClienteID]&")

The right way YOURS: =DLookUp("[AboCantidad]","Abonos","[ClienteID] =" & [ClienteID])


DaniBoy