Topics Search

dlookup with a contains criteria?

dlookup with a contains criteria?
Views: 42
Can a Dlookup command search for part of a field rather than the whole thing. EX.
Dlookup is RecipientX = DLookup("RecipientID", "MasterFeedData", "[Logins]=" & Me!JID)

jID input contains CITIGRPS

Currently dlookup looks for an exact match between Logins and JID. I want to change the Dlookup to look for partial matches in the Logins field if possible.
Sponsored Links:

More topics


DLookUp formula

I am having some trouble with the DLookUp formula. It seems to be entered correctly; however I want to use either the CurrentUser() function or the WindowsNTUserID as my criteria (I.e. the third (3rd) part of the DLookUp formula.

Dlookup() for NULL

I have the below DLookup() but every time it returns null when I am sure there are records with these criteria. I have tested it without the [RecPosition] criteria and it works. What am I doing wrong here?

lngRecordID = DLookup("RecEmplID", "tblAuditTrail", "[RecEmplID] = " & lngEmplID & " AND [RecPosition

Dlookup() for NULL

I have the below DLookup() but every time it returns null when I am sure there are records with these criteria. I have tested it without the [RecPosition] criteria and it works.

lngRecordID = DLookup("RecEmplID", "tblAuditTrail", "[RecEmplID] = " & lngEmplID & " AND [RecPosition] = ''")

DLookup() with Date criteria

I'm having trouble with DLookup() using dates as part of a multiple criteria. I'm using the following;

If IsNull(DLookup("ExchRate", "TBL_DDPExchRates", "CurID = " & Me.Combo4 & " AND ExchDate = #" & Me.ShipOBDate & "#")) And Me.Combo4 2 Then

It seems that if ShipOBDate is any date between the first and tenth of the month the DLookup fails to locate the appropriate record. I can't understand why as ShipOBDate and ExchDate areboth formatted as Short Date and ExchDate is being populated via an OpenArgs which is derived form the field ShipOBDate

Is the fact the the date is getting converted from a date to string and back to a date some how upsetting things

Multiple criteria in DLookup

Are you able to have 2 criterias in a DLookup statement? Here's the vital info:
Expr: OnHandQty
table name: ICPartWarehouse
criteria: FKICPart = me.FKICPart and WarehouseNumber = 3
both FKICPart & WarehouseNumber are fields within the table

Here's what I have so far:

Dlookup multiple criteria with yes/no field

I am trying to implement a user login by using a dlookup against a table called tblUser.

The concept is that if the password entered into the text box matches the password in the table WHERE the username matches the username in the table and the active yes/no field is true then it will let the user in, if not it will throw an error and ask the user to enter again.

My problem is that I am getting a type mismatch within my dlookup. This must be due to the yes/no field I have added into the criteria as it was working before I put this in.

Here's what I have:

If Me.txtPassword.Value = DLookup("[password]", "[tblUsers]", "[active] = -1 " And "[username]= " & "'" & Me.txtUsername.Value & "'") Then . etc

Numeric Criteria Expressions

To specify numeric criteria for an operation, you supply a numeric value as part of the string expression that forms the criteria argument.
Suppose that you are performing the DLookup function on an Employees table to find the last name of a particular employee, and you want to use a value from the EmployeeID field in the function's criteria argument.

Using Dlookup to pull value from table with multiple criteria

I am trying to use the Dlookup function but am running into some issues when I try to use multiple criteria.

I have a table that has the a price for a given range of thicknesses. I want the Dlookup to pull the price from the correct range. The ranges are defined by two fields, LowThickness and HighThickness.

For example, let's say I have a different price if the thickness is 0 to 1, 1 - 2, and anything over 2. I want the user to be able to input a number, say 1.556, and have it lookup the correct cost from the 1-2 range.

Here is what I have:

MaterialCost = DLookup ( "[Cost]","[tbl_MaterialCost", "[LowThickness]< " & Thickness And "[HighThickness] >= " & Thickness & "")

Edit: If there is a better or simpler way to accomplish this, including redesiging the tables involved, I'm all ears

Dlookup and multiple criteria error

I have a problem with my dlookup statement. When I run it separately there are no errors and I get my results but when I combine them together with AND I get error runtime error ‘13’: Type mismatch.
All tables are text.

dlookup() with date

Maybe there's a small nuance I'm missing when using DLookUp with a date as the criteria.
my DMax returns correctly
I do not get an error with my DLookup
BUt the DlookUp retuns the wrong value.

vMaxDate = DMax("[Version_Date]", "tblVersionInfo")
vlblVersion_Info = DLookup("[Version_ID]", "tblVersionInfo", "'[Version_date] = " & vMaxDate & "'")
Me.lblVersion_Info.Caption = vlblVersion_Info