DLookup

[lioninwinter]

Microsoft itself warns against the use of the Dlookup function in Access but having become accustomed to VLOOKUP in Excel, I am keen at least to try to use the similar function in Access. Can someone help with the attached example in which a simple Dlookup of "GDes" on GID loops endlessly. Any thoughts would be greatly appreciated.

 

[Gasman]

If you have a character field you need to use single quotes or triple double quotes.
I tend to use single quotes unless a single quote could be in the text. Yours is not the case.

You need

=DLookUp("[GDes]","tblGender","[GID]='" & [Forms]![frmPerson]![PG] & "'")

No idea as to why it was refreshing due to the incorrect syntax.

FWIW create a query joing by GID and use the field that way. No DLookup required.

HTH

 

[lioninwinter]

Thank you so much for your help. I have tried the "where" query method; the issue is updating the query requires opening and closing the table and form for the update to take place. Is there any easy solution except the approach of using a combo box with all values entered individually?

 

[Gasman]

I just changed the form to use the query and added a record, without any issues.?

So there is no need for the PGDes field in the Person table?

FWIW I would have had a combo for the Description and from that stored the key value in the person record.?

 

[lioninwinter]

Thank you for your help. I'm wondering if a 'where' query can be entered directly into the control source field of the form?

 

[Gasman]

Thank you for your help. I'm wondering if a 'where' query can be entered directly into the control source field of the form?

Not easily and no real need. If you were to make a combo for the description, it would just be a simple Select. The only need for a WHERE would be if you had multiple types of Lookup in that table.
This is all the query is for that form?

SELECT tblPerson.*, tblGender.GDes
FROM tblPerson INNER JOIN tblGender ON tblPerson.PG = tblGender.GID;

I just used * for a quick test, normally I would declare each field.

 

[lioninwinter]

Many thanks for your help. I am a social historian and am building a database to analyse data from historical records such as parish registers. The registers were obviously intended administrative purposes that are different from ours as historians. A typical entry is like this: "John son of James Gasman was baptised the 15th day of May 1642". I would like to start by building a query (or form) that would select the data extracted from the registers into a "Baptisms" table and populate a Persons table in an order and format along these lines:

P_ID = JamGasBap1642
P_FN: James
P_LN: Gasman
P_Gender: M

I would do the same for marriages, burials etc.

The P_ID in the Persons table I assume could be build from a calculation field/expression either in the query or in the table itself. I would like the query also to look up the gender using the first name from a lookup table containing all the possible first names (George, Gertrude, Caroline etc etc) and their genders. The lookup table is not linked by a relationship with the P_ID table and one would prefer not to enter the gender manually for every single record; there are other lookups - for example the Early Modern clerks ha no sense of spelling so George is sometimes Gorg, Gorges, Giorgius (though we had to wait for George Galloway for "gorgeous"!). It would be helpful as well to look up standard values for first names from a table containing combinations of actual first names and the standard equivalents.

I have not been able to design a query that would lookup values. Do you have any thoughts on this?