Topics Search

dlookup with a contains criteria?

dlookup with a contains criteria?
Views: 52
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)

Logins field contains CITIGRTS,CITIGRPS,GTORTTRO
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:

Similar posts...

 

DLookUp formula

DLookUp formula Icon
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() with Date criteria

DLookup() with Date criteria Icon
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
 

Numeric Criteria Expressions

Numeric Criteria Expressions Icon
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.
 

Dlookup and multiple criteria error

Dlookup and multiple criteria error Icon
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

dlookup() with date Icon
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
 

Is there an 'Also' equivalent to 'And' for the DLookup function?

Is there an 'Also' equivalent to 'And' for the DLookup function? Icon
But forgot it all, and I've entirely retaught myself vba in the last three days while trying to programme a database), and I'm trying to programme a fairly complex changelog database.

Anyway, when trying to create an issues registry I discovered a fairly major flaw in my programming in that a DLookup function was returning stuff for any record that fulfilled any one of the criteria, rather than just the one that fulfilled all of them.

Is there a way of doing this with the DLookup command, or do Ineed to try something else entirely?
 

Is the ELookUp or DLookUp function can be retrieve all the rows

Is the ELookUp or DLookUp function can be retrieve all the rows Icon
Is the ELookUp or DLookUp function can be retrieve all the value Equals with Criteria?
 

DLookup using numeric values

DLookup using numeric values Icon
I have a form that I am trying to use a DLookup on to find a record based on the value of two combo boxes. Here is what my DLookup looks like:[CODE]

I am not sure if the syntax is right it gives me an "#Error" in the Account Description field.
 

Dlookup results in Text when data type is number

Dlookup results in Text when data type is number Icon
I am using a dlookup to find a numeric value from a table, when I run the query I get the correct result but the format is text. The dlookup is:

Month: DLookUp("[Dim_Month]![Next_Month]","Dim_Month",[tbl_Data]![Dim_month]=[Dim_Month]![ID])
 

DLookup with Null criteria

DLookup with Null criteria Icon
But I'm running into this issue. I have a textbox with a control source as a DLookup. The recordset contains IDs from different tables, and I want the customer name to display in a textbox related to the customer id. but this is what I have/get

textbox = #Name? when CustID is null, using Nz and setting the textbox to an empty string if there was no Customer id

=IIF(IsNull(CustID).) and =IIF(Not IsNull(CustID). And still the textbox always turns up as #Name?

I tried to evaluate the code in vba and even when CustID is null, it still attempts to evaluate the DLookup instead of just giving me the appropriate value.

I realize that I can set the value of this textbox on Current event, or through VBA using simple If statement, but is there a solution where I can just set the controlsource in design to evaluate it?