Modules & VBA :: Multi Criteria DLookup - Return ID Number Of Entry

Dec 4, 2013

I am trying to make a DLookup function to return the ID number of an entry that matches 2 or 3 criteria but I am struggling to get the syntax correct for the second and third criteria.

Here is what I have so far:

1 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] =" & Forms![Mainform]![p11] )

2 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " And [timedata]![BGSnum] = 1001" )

BGSnum is a numerical value but it changes for each form I load, so what I want to do is use the form location value as the criteria.

E.g.

=DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " AND [BGSnum] = ' " & Forms![Mainform]![BGS] & "' ')

and possibly a third criteria too. This is where I am having problems and the syntax is wrong so MS Access wont let me save the macro.

View Replies


ADVERTISEMENT

Modules & VBA :: Multiple Criteria To Return Value - DLookup Error

Feb 11, 2014

I have been trying to find a solution to why I can't get a Dlookup with multiple criteria to return the value I need.

Essentially I am trying to use an Order Number to find the item number which is contained within another table. However the order number has multiple lines (suffixes) which alter the item number. Therefore I am trying to have the item number be populated by the correct 'combination' of Order Number and line ("suffix").

I have managed to use the Dlookup in the after update of each box of the form separately and they retrieve values in the table correctly:

Afterupdate of main order number:

Code:

Private Sub OrderNumbertxt_AfterUpdate()
ItemNumbertxt = DLookup("item", "dbo_job", "[job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'")
End Sub

Afterupdate of suffix:

Code:

Private Sub SuffixTxt_AfterUpdate()
ItemNumbertxt = DLookup("item", "dbo_job", "[suffix] = Forms![**INPUT]![SuffixTxt]")
End Sub

However when I combine them as follows in the afterupdate of the Suffix box I receive a "Run-time error '13': Type mismatch"

Code:

Private Sub SuffixTxt_AfterUpdate()
ItemNumbertxt = DLookup("item", "dbo_job", ("[suffix] = Forms![**INPUT]![SuffixTxt]") And ("[job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'"))
End Sub

I think the reason is something to do with some being numbers and some being a combination of text and numbers (based on the replies of other topics), but have been trying to modify these slightly and can't get it to work still.

Also the Order Number is a combination of letters and numbers (normally in the form of AB12345678), the suffix is just a number between 0-9 and the Item number it finds is a combination of numbers and letters.

View 3 Replies View Related

How To Return A Value On Multi Criteria

Nov 20, 2012

On a report I want to Dlook up using if a name and a date match on a query line it will return the total that is associated in that same line. What do i need to add to the below is if

=DLookUp("[Total]","qryaQFFSumOfTotal10day","[AssociateName]=Reports!rprtQFF10day![AssociateName]") & qryaQFFSumOfTotal10day","[ErrorDate]=Reports!rprtQFF10day![Date1]") if null = 0?

View 5 Replies View Related

Modules & VBA :: DLookup Doesn't Return Correct Value

Aug 3, 2015

I'm trying to use a DLookup to get a specific value from a field in a table.

This is what my code currently looks like;

Code:
JOBID = DLookup("[ID]", "MASTER PLANNER", "[JOB NUMBER] = '" & JOB_NUMBER & "'")

Basically I want to get the ID (a number) from a specific record where the JOB NUMBER equals the string I have typed in to a field on the form, also called JOB NUMBER.

However, my problem is that it doesn't navigate to the record where the criteria matches, it just chooses the ID from the very first record of the table.

what I'm doing wrong?

View 9 Replies View Related

Modules & VBA :: Use DLookup To Return A Value From Relevant Field

Nov 14, 2014

I have a small table, that only contains one record (and should only ever contain one record, which simply gets edited to suit once in a while).The table is called OtherRates, and the fields are as follows:

WRD_Sat/Sun/BH
WRD_Mon-Fri
AHDifferentia
SKDifferentia
Sat/Sun/BH_OTRate
Mon-Fri_OTRate

I am trying to use DLookup to return a value from the relevant field in this table, however the field to look in is a variable.The code (below) that I have tried returns an error and highlights the DLookup line,.

Code:
Dim WRD
Dim DayType

[code]....

View 3 Replies View Related

Trying To Create A Multi-criteria Incrementing Number

Jul 3, 2006

Is there any way it would be possible to have a number automatically created using NZ() or DMax function; based on certain criteria and also when creating the number preventing a duplicate number creation in the even of a simultaneous record creation?

Here's my example:

I have a CCN Number (Currently the employee manually types it in; and it is the primary key. What I would like is to take out the human element (error possibilities) and let access create it for the employee.

Here is how the CCN looks and the breakdown of each section (the important part's will be the first 3 sets of numbers):

96-06-184-100-000

96 = Region Code
06 = Current Year
184 = Julian Calendar Date (based on the receipt date field)
100 = Filler number based on day of the week (Eg. Monday = 100, Tuesday =
200, etc.)
000 = Filler numbers (Should always be Zero's (000)

I need to find away to pull this information from a table of some sort and combine it to create the CCN and to then add 1 (incriment) to the 4th set of numbers. Example 100 (1st record of the day) 101, 102, 103, etc.

Any help or ideas would be greatly appreciated.

View 4 Replies View Related

Queries :: DLookup With 2 Criteria - One Number And One Date

Jan 26, 2014

I have been working on a query in my database and I would like to look up a Number Value from another table based on a Number ID field and the Number Date in the Number Values table, where the Number ID field in the table agrees to the Number ID Index field in the query, and the Number Date field in the table agrees with the Initial Date field in the query. The Dlookup syntax I currently have is as follows:

DLookUp("[Number Value]","Number Values","[Number ID]=" & [Number ID Index] & "And [Number Date]= [Initial Date]")

The Dlookup shown above does not return an error, but it returns a blank field. I know that individually, the Dlookup for the Number ID works, but when I add the And for the Number Date the Dlookup returns a blank. Also, I have verified that there are existing records where the Number Date and Initial Date fields agree.

Also, I changed the format of the date fields in the tables to be ShortDate, thinking that the Timestamp was causing the Dlookup to not find a match. I also tried to use the DateValue() function within a query to reformat with not luck.

Why my Dlookup is returning a blank?

View 3 Replies View Related

Modules & VBA :: DLookup To Return A Value Starting With Specific Letter

Feb 25, 2014

I am trying to use a DLookup to search to find what block goes into the production of an item.

In a table called "dbo_vw_MCE_job_with_materials", each item contains a list of the parts which go into its production in a column titled "Expr1". However there are always more than one parts to this, with varying names.

What I am trying to do is to use a Dlookup to only return the first value which starts with B (as the block number is always the only part on the list which begins with B).

At the moment my DLookup would look ike this:

Code:
MixTypeTxt = DLookup("Expr1", "dbo_vw_MCE_job_with_materials", "[item] ='" & Forms![Theta Input]![ItemNumbertxt] & "'")

However I think as the item column contains the same item number in 8 columns, with different values in the Expr1 column, Dlookup only returns the top value "A-CF0057" (which is irrelevant as far as I am concered) - whereas I am hoping it will be able to pull the "B1499" value from the column as it is the only one starting with a B

Is there anyway to specify which value the dlookup function would return, or is there any way to apply some sort of permanent filter on my table?

View 1 Replies View Related

Modules & VBA :: Return Single Value From Table And Assign It To String - DLookup Not Working

Sep 3, 2014

I am trying to return a single value from a table and assign it to a string to be used later but Dlookup isnt working at all. below is the code im using and the error message im recieving is "wrong number of arguements or invalid property assignment"

Code:
Sub boo()
Dim result As Integer
result = dlookup("Definition", "Config", "Parameter = 'Mail Folder'")
End Sub

View 13 Replies View Related

Modules & VBA :: DLookUp With 2 Criteria

Aug 4, 2015

I have a form that has a field that needs to get its data from a query based on two fields in the form. The following is my DLookUp statement:

DLookup("[COMEX]", "OptionMetalsListQ", "[Metals] = '" & [cboMetals].[Column](1) & "'" And "[DateOfPrice] = " & Me.txtDateOfPrice)

Comex is a metal market. the bound column in cboMetals is text and The date is a date.I am getting the error "Run-time error '13': Type Mismatch.

View 3 Replies View Related

Modules & VBA :: Str Variable In DLookup Criteria

Aug 18, 2013

Why does this work when the text box is used and not when the variable is used directly?

Code:

Private Sub Command61_Click()
Dim strDBName As String
strDBName = getDBName()
Me.Text59 = strDBName
Me.Text62 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = text59")
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = " & strDBName)
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = strDBName")
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName]" = strDBName)
End Sub

Text62 returns the correct value
Text64 failes on everyone of the examples

View 7 Replies View Related

Modules & VBA :: Multi Criteria - Checking For A Value To Be Null

Oct 16, 2014

Me.Text11 = Nz(DAvg("[final whse-in diff]", "dbo_inventory", "[CAFETYPE]=" & Me.Text7 And "isnull(me.[DATE FIXED])=" & True And "isnull(me.DATE_IN)=" & True), 0)

I am getting a type mismatch error with this.

My question is:
1. is the syntax correct....
2. Is my way of checking for a value to be null correct....

View 4 Replies View Related

Modules & VBA :: Incremental Invoice Number Using DLookup

Jun 8, 2015

Part of my access application does invoicing. I have an invoice number table that wholes one field "InvoiceNum". On my invoice report i have the following code:

Code:
Private Sub Report_Open(Cancel As Integer)
'// lookup invoice number when invoice opens
intInvoiceNum = Nz(DLookup("InvoiceNum", "tblInvoiceNum"), 0)
End Sub

[Code] .....

Problem: My report open with groupby ClientID and ProjectNum. Multiple invoices print on the same report. If any invoice spills over to second page the Format Code runs again inserting an incremental invoice number to the second page. How can i prevent this from happening?

View 8 Replies View Related

Modules & VBA :: DLookup - Check 2 Criteria In Two Different Columns In Same Table

Dec 2, 2013

I would like to make a DLookup that check 2 criteria in two different columns in the same table. The reason to use a DLookup is that I would like to check if two parts already have been linked together. The user therefore selects two values in to different comboboxes (lstLinkPart and lstLinkToPart). In this case the value of the first combobox is column: ComponentPN in tblProductLinkComponent. For the second (lstLinkToPart) column ProductPN, in the same table, has to be checked.

If both values from the comboboxes match the values in both (and only) columns I don't want to continue and made a code to be executed.

I found multiple examples on the internet for using multiple criteria but something similar to what I require.

View 6 Replies View Related

Modules & VBA :: DLookup Error - Wrong Number Of Arguments

Jul 1, 2013

Access 2010

I am trying to check for when a user trys to enter a duplicate number.

The control that I am checking is in a subform on the main form:

Main: frmCandidateInfo
Sub: frmTestInfo

Control on the subform is: RankOrder

I am trying to check the control entry against the table entry:

Table: tblTestEvents
Field: RankOrder

Here is what I have:

Code:
Private Sub RankOrder_BeforeUpdate(Cancel As Integer)
Dim lngRankDup As Long
lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & Forms!frmCandidateInfo!sfTestInfo!Form!RankOrder, 0))
If lngRankDup <> 0 Then
MsgBox TestEventID & " already exists in the database"
End If
End Sub

I know that the error is due to the argument not being correct, but I am not sure how to fix it.

I have tried:

Code:
lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & Me.RankOrder, 0))

Code:
lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & [RankOrder], 0))

Code:
lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & Forms!sfTestInfo!Form!RankOrder, 0))

But nothing seems to work.

View 3 Replies View Related

Modules & VBA :: Form With Multi-criteria Searches / Uses Strings And Filters

Jan 23, 2014

I have a search form with blank fields tied to a table, four criteria search boxes, and a button to take the input from the search boxes, search the table, and populate the results on the form's blank fields. As of now, it works as long as all four criteria boxes aren't null.I used filters to achieve this, and here's the code that works as long as all four boxes are not empty. (My criteria boxes are as follows: a textbox called "Keyword" and three combo boxes called HRCombo, BuildingCombo, and RoomCombo, and the fields they're tied to are as follows: "Item Description" "HR Holder" "Building" "Room") My first line "Me.Filter = ..." was broken up to make it easier to view.

Code:

Me.Filter = "[Item Description] Like " & Chr(34) & Me.Keyword & "*" & Chr(34) & "
AND [HR Holder] = '" & Me.HRCombo & "'" & " AND [Building] = '" & Me.BuildingCombo
& "'" & " AND [Room] = '" & Me.RoomCombo & "'"
Me.FilterOn = True
Me.Requery

I need it to be able to do the search no matter which combination of criteria boxes have input. Someone recommended using if statements to do the following: Create four strings, one for each criteria box. Use 4 if statements to check if the box is null - if it is null, assign an asterisk to its string, and if its not null, assign the value I used for the above Me.Filter statement to each box's string. Then, use Me.Filter and concatenate the four strings at the end. Here's the code I used for this, and, with my limited knowledge, I can't get it to work.

Code:

Dim StrA as String, StrB as String, StrC as String, StrD as String
If Me.Keyword is null then
StrA = "*"
else
StrA = [Item Description] Like " & Chr(34) & Me.Keyword & "*" & Chr(34)
End If

[code]....

View 14 Replies View Related

Modules & VBA :: Dlookup With Multiple Criteria - Count Of Callbacks For Day And Time

May 2, 2014

I have this

Code:
If Not IsNull(strCount = DLookup("[Number_Of_Records]", "All_Booked_Callbacks ", "[CallBack_Date] =#" & Me.CB_DAte.Value & "#" _
& " And [CallBack_Time] = #" & Me.CB_Time.Value & "#")) Then strCount = DLookup("[Number_Of_Records]", "All_Booked_Callbacks ", "[CallBack_Date] =#" & Me.CB_DAte.Value & "#" _
& " And [CallBack_Time] = #" & Me.CB_Time.Value & "#") Else strCount = "0"

All_Booked_Callbacks is a query which has a date and time columns and count of callbacks for that day and time, this always returns 0

View 7 Replies View Related

Modules & VBA :: Criteria On MS Access Query Not Working With Multi-Select Listbox

Aug 22, 2014

I am attempting to filter records using a multi-select listbox, but all records are being returned. Here is my code.

Private Sub btnKeyboxCount_Click()
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

[Code] ....

View 1 Replies View Related

Modules & VBA :: DLookup Multiple Criteria Not Working - Column Is A Numeric Field

Feb 25, 2015

I have a small problem with dlookup multiple criteria. Vba code looks like this:

Code:

Label34.Caption = DLookup("[Spent_Hours]", "249_1_CHours", "[Date_Added]= " & Me.Text27 & " And [Shift] = '" & Me.Text29 & "'")

This gives following error:

Syntax error in number in query expression '[Date_Added]=4.02.2015 And [Shift] = '2'.

[Shift] column is a numeric field.

View 14 Replies View Related

Multi-table, Multi-criteria: Avoid Repeating Records

Apr 10, 2008

Hi everyone. Apologies if this has come up before, but the search terms I've tried here and on google keep turning up the wrong information.

At work I manage a large database with many tables. It stores data for participants in a research study. Each table stores the data for a different test, so one participant may have multiple records. Primary keys for these tables are defined by a combination of the participant and date of test fields. (Everything is dependent upon a table that stores the static info for participants, so the database is normalized.)

I want to be able to make a table that lists target participants and dates, and then create a query that looks at this table and pulls all the available data from various tables for those individuals that was recorded within one year of the target dates.

I've successfully made queries that meet these criteria while pulling data from only one table. The problem I'm having is that when I try to pull from multiple tables, each with it's own date field that needs to be used as a criterion, I end up excluding almost all the data, because most of the target participants do not have all the requested data within the target dates.

I've tried being inclusive with my criteria (using ORs), but then I end up with tons of data that I don't want and I need to filter through it, which defeats the purpose of the query.

Any advice on handling this issue, or do I basically just need to create a separate query for each table?

I'm sorry if this is too vague, but it's illegal for me to upload any of my own dataset. I could probably come up with an example if it's helpful, though.

Thanks!

View 7 Replies View Related

Modules & VBA :: Auto-number Stopping Entry

Jul 4, 2013

I have a form that contains an autonumber field. Before any data is entered into the form that field displays the word "(Autonumber)".While it does this the subforms on my form that use that same field to run their querys are empty

Code:

Me.AllowAdditions = True
DoCmd.GoToRecord , , acNewRec
[Forms]![InvoiceForm]![CreateNewRecord] = True
Me.AllowAdditions = False
[CreateNewRecord] is just a invisible tickbox that is used to enter some data on the form; I don't want to use it but haven't found another solution.

Anyway. When the user opens the form they have to press this button Unless a new record was previously created and that's confusing. I'd like it in the form load event except I can't "see" the value of the field while it displays "(Autonumber)".My code for the form load is:

Code:

If InvoiceNumber.Value = Null Then
Command82_Click
End If

Where invoicenumber is the autonumber field and command82 is the new record button.

View 11 Replies View Related

Modules & VBA :: Random Number Sequencing Based Off Of A Certain Criteria

Aug 21, 2013

I have a RN generator that looks at 9 different lanes and puts in a number based off of a certain criteria but I was wondering if in the frmLanes the actual "Go" button was an incoming number how could I get my current random number generator to look at that number and determine if the current number is within 5 of the "go" number?

Any way to have a beginning number that I would enter into a text box and have the number generator kick off based off of that number. I have included a copy of my db for viewing.

View 7 Replies View Related

Forms :: DLookup To Return Form Load

Dec 12, 2014

I am trying to create a multi-user web-based database where all users log-in and see different forms based on a field in the user table.

I am trying to use a DLookup to return the name of the Form I want open based on the command box that shows the userID and UserName

DoCmd.OpenForm , acNormal, DLookup("field", "table", "[UserID]=" & Me.cboName.Value)
cboName row source
SELECT [table].[UserID], [table].[UserName] FROM table ORDER BY [UserID]

View 5 Replies View Related

Modules & VBA :: Populate A Field With A Sequence Number Based On Two Criteria?

Sep 11, 2014

I am trying to get the max of an integer field if the ProjectNumber field is equal to the ProjectNumber field in the current record. I have used code similar to the one below before but it was on a date field, then add one.

Me.RevisionNumber = Nz(DMax("[RevisionNumber]", "dbo_ProposalLog", "[ProjectNumber] =" & Me.ProjectNumber), 0) + 1

The ProjectNumber field is a text field.

View 6 Replies View Related

Queries :: DLookup To Return Value Of Field In Query Expression

Oct 30, 2013

I have a field created in a query expression

BirthMon: Format([DOB],"mmmm")

I would like to create another field in the query using DLookup to return the value of the BrithMon.

BrithMonthID=DLookUp("MonID","tblListMons","BirthM on = Mon")

This works good if used with an unbound text box on a form, but when entered into a query expressions, an error is returned: cannot find the name 'BirthMon'

Can I used DLookUp in a Query expression to refer to another Query created field?

View 2 Replies View Related

Modules & VBA :: Return Rows In Query From Variant Array Return From UDF

Sep 16, 2014

I have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.

So if my input table looks like this

[strField]
"kick the ball"
"return the pass"

my query result should looks like this

[Orig] [new]
"kick the ball" "kick"
"kick the ball" "the"
"kick the ball" "ball"
"return the pass" "return"
"return the pass" "the"
"return the pass" "pass"

Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.

With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved