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 Replies


ADVERTISEMENT

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 :: Query Criteria And Variable In Email

Sep 6, 2013

There are two forms to be filled out on Access. One (maybe two) come from our Contractor. They will input the Bill of Materials, Scrap Rates, Inventory Turns and Cost. This goes to a non-official Bill of Materials table. The second form comes from our Supplier. They will input the the cost they charge the contractor for the given Bill of Materials. Access runs a report that will automatically compare the Contractor costs with the Supplier costs for each component on the Bill of Materials. If the component costs match, it will highlight the Contractor cost green, it they do not, it highlights red.

If all numbers are green, I hit the "Approve" button on the report and there are three queries that need to be ran. An Update Query that changes all the statuses for the components from "Unapproved" to "Approved", an Append Query to add the Bill of Materials to our official table, and a Delete Query that removes the components from the unofficial Bill of Materials table. Once this is all done, an e-mail needs to be sent to Finance to roll costing for the SKU. I do not know the best way, but I currently am having getting the Code to do the following:

1. Put the specific SKU in the e-mail, which is variable.
2. Change the Criteria of the Queries so they only run for the specific approved sku
3. Get the "Approve" and "Deny" buttons on the report to only function for the specific SKU.

Currently all the SKUs that are in "Unapproved" status show on the report. Once I hit approve, it wants to approve all the SKUs, not just the one I'm looking at.

Code:
Sub Command36_Click()
'''Current Issue: Need Query Criteria to be SKU specific'''
'Update Query to change BOM status to "Approved"
DoCmd.OpenQuery "(2302) BOM - 3PM Entry Query Approved", acViewNormal, acEdit

[code]....

View 2 Replies View Related

Modules & VBA :: Searching For A Variable With Date Criteria In Table

Jun 14, 2013

I'm trying to search for a variable (varCod) in a table(SerialNumberCustomer) but I want to only store the returned variable in an array if its shipDate is between 9/30/2001 and 10/1/2012

this is what my code looks like so far:

Set rst = CurrentDb.OpenRecordset( _
"Select * from SerialNumberCustomer WHERE SerialCardId = " & varCod & " AND (ShipDate BETWEEN #09/30/2001# AND #10/01/2012#) ")

The line of code works and it returns all of the values in SerialCardId where it's = to varCode but it doesn't go through with the shipDate requirement. It's instead returning all of the dates with the found value.

View 13 Replies View Related

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

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 :: Data Selected From A Combo On A Form For Variable Criteria

Jul 2, 2013

I manage an Access application that in many instances uses data selected from a combo on a form for variable criteria. In this instance it is in the form of:

[Forms]![Main Navigation]![Print Menu]![SchoolYear]

I recently wrote a routing that exports to Excel based on a record set derived from a query. In testing I hard-coded the criteria (School Year) in the query. Once everything worked I sustituted the variable above. Now, in the VBA, no records are put into the recordset, when I run the queries directly from Access they work correctly, drawing the results for the school year selected on the form referenced.

'Create The Recordset
If Me.Frame11 = 1 Then
strQueryName = "ExcelHS"
GroupTitle = "High School"
Else
strQueryName = "ExcelMS"
GroupTitle = "Middle School"
End If
Set objRst = Application.CurrentDb.OpenRecordset(strQueryName)

View 1 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 :: 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 6 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

Variable In Dlookup

Nov 21, 2005

I want to retrieve a users account balance for a certain year and was thinking dlookup may do it but am beginning to wonder.

I have fields in the table for years 2004 - 1012. Based on the users input I would like dlookup to fetch the info from a certain year for a particular person. Roughly presented, dlookup(yearx,"tbl_InfoTable",username = 'Mike')

Can I get dlookup to accept a variable for the field name, ie yearx?

Is there a better way to quickly grab the info I need?

TIA

Mike

View 1 Replies View Related

General :: Variable As FieldName In DLookup

Aug 12, 2014

I want to use a variable as FieldName in the DLookup function.

Normal DLookup:

Code:
DLookup("FieldName" , "TableName" , "Criteria= 'string'")

I want to use:

Code:
DLookup(variable, "TableName", "Criteria= 'string'")

Where variable is an integer, which represents the column number in the table.

Is this possible?

View 7 Replies View Related

Modules & VBA :: Error 91 - Object Variable Or With Block Variable Not Set

Jul 8, 2013

Error 91 - Object variable or With block variable not set

I am getting this error telling me that an object variable is not set.

I know which variable it is but when I step through the debugger it sets the variable and all is fine? Issue is that public variable of a class is not getting set when the VBA Editor is not open?

View 14 Replies View Related

Modules & VBA :: Sorting / Object Variable Or With Block Variable Not Set

Oct 3, 2014

This code runs fine the FIRST time, however trows up a message the SECOND time it is run.

The error is on the line ".Range"

I am trying to sort records which have been exported to Excel.

Dim LR As Integer
LR = 5
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set wbRef = xlApp.Workbooks.Add
With wbRef

wbRef.Activate
.Worksheets("Sheet1").Activate
With ActiveSheet
.Range("A2", .Cells(LR, "O").End(xlUp)).Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes
End With
end With

View 3 Replies View Related

Modules & VBA :: Update Contents Of Variable But Not The Variable Itself?

Aug 20, 2014

I look at a lot of files to see when they were last updated. I wanted to write a generic procedure to manage that so ..

Code:
Public fDate As Variant
Public vField As String
Public vFile As String

'GTSdata
vField = "txt_gts_data"

[Code] ....

What I hoped Me.vField would do is update the date field [txt_gts_data] on my form with the date the file was last saved.

i.e. me. txt_gts_data = fDate

What actually happens is the variable vfield gets updated from "txt_gts_data" to 19/08/2014 then later code falls over because the fieldname is lost .

Me.[vField] corrects itself to me.vField (and does not work)
Me!vfield falls over (cannot find the field vField, not surprising J)

How do I say update the contents of the variable, not the variable itself?

View 7 Replies View Related

DLookup With 2 Criteria

Sep 21, 2005

I have a table which contains the following:-

boardingpoint pickupcode pickuptime
bolton N1 07.30
bolton N2 08.00

each boarding point has 6 different pickupcodes and pickuptimes

i am trying to create a form, which is currently displaying data from an query depending on which tour is selected from the main form.

my current dlookup is like this.
=DLookUp("[pickuptime]","boardingpoints","[boardingpoint]=Forms![pickup list]![boardingpoint]")

what do i need to add to the end of this so that it also looks at the pickupcode as well as the boardingpoint to display the correct pickuptime and not just the first one it comes to?

View 4 Replies View Related

DLookup All Fields With Same Criteria

Aug 5, 2007

Hello,

I have an inquiry about using dlookup to retrive all fields from a table that are using the same criteria. Here's the table structure

Table1:
ID (Autonumber)
Number
Code
Grade

I have the following data:
Number Code Grade
4 15 68
4 52 78
4 17 69
5 85 83
5 28 17

I am using this to retrive values for number 4 but I only get the first row
DLookup("Code", "Table1", "[Number] = 4")
DLookup("Grade", "Table1", "[Number] = 4")

I get only as a result 15 and 68, how can I retrive all the rows that have Number 4?

Thanks in advance for your help

View 14 Replies View Related

Can You Use A DLookup In The Criteria Of A Query

Sep 23, 2005

Can you put a Dlookup in the criteria of a query?
I have table which contains bookings

customername excursionname etc

i want to include in my query results some information relating to the excursionname for a report, so that i can use them for sorting.

how do i add a dlookup to the query to display the pickupcode for the excursionname from the table boardingpoints

i have the fieldname Expr1 etc, it might look something like this:-

=DLookUp("[pickupcode]","Excursions","[Excursionname]=query![pickuplist]![Excursionname]")

Maybe?!?

View 3 Replies View Related

DLookUp With Muti Criteria

Jan 31, 2007

I have the following DLookup statement which if the number of bags is greater than 0 will find the previous accommodation name. How do I get it to only select the accommodation name if the CustomerID field is the same as the previous as well?

BagsFrom: IIf([Bags#]>0,DLookUp("[Accommodation_Name] ","TblAccomBagsSorted","[Test2ID]=" & [Test2ID]-1)," ")

Thanks
Gary

View 5 Replies View Related

Mutiple Criteria In DLookup

Apr 30, 2007

I'm back yet again with a new and intriguing set of problems.
I am building a fancy report and Come to find out you cannot use SQL to return one simple number in a text box because even though you know you will write the statement to return only one answer everytime, access doesnt understand that so we can do a domain aggregate thing with DLookup.
OK fine I have to do alot of them but still OK, here is my issue
I can get DLookup to give me what I want if i type into each control source the criteria that I want to return.


I want to lookup a number from a recordset but there are two things that make the number unique date and strain. strain is easy to cough up in the right place because i will hard code that into the control source dlookup, the part that is killing me is the date
I can get a dlookup to give me accurate results based on multiple criteria, but how can i get that to include the date for which I am running this report.
I do enter the date parameter each time i open the report.

Here's what i got so far

=DLookUp("csQuantity","SpawnScheduleCulSpawn","csstrain='AC-PA'" & " AND week = #4-29-07#")

That returns the answer i want, but i have 50 of these text boxes in one report so i cant go through and write the date in each one.

how can i make the part following " AND week =" dynamic so that it runs the date that i entered as the parameter for the report?

Thanks in advance!

View 1 Replies View Related

Queries :: Dlookup With A Between Criteria

Aug 6, 2013

I have two tables

Criteria_Ratings:
FFMin
FFMax
Rating

BaseData:
FF/M

What I am trying to do with a Dlookup is return the Rating when FF/M is between FFMin and FFMax. All fields are Numbers.

FFRating: DLookUp("Rating","Criteria_Ratings",[FFmin]<[FF/M] And [FFmax]>=[FF/M])

View 4 Replies View Related

Using DLookup With Multiple Criteria

Apr 8, 2013

I am using MS Access 2010 and have an issue with one of my forms. First off, I have a table called coefficient which contains a column MIN, a column MAX, and a column COEFF.In my form, called CALC, I want to grab a number that it is in textbox [Text12] and place the related coefficient number in textbox [Text15].I placed a control source in [Text15] using =DLookup("[COEFF]","coefficient","[Text12]>[MIN] And [Text12]<[MAX]").

View 2 Replies View Related

Pass Variable In Query Criteria

Mar 3, 2008

Is there any way to pass a variable from one criteria on a query to five other criterias in the same query.

I am using this code to select which person the query is running on

Like "*" & [Enter Part of The Landman Name or Enter For All Records] & "*"

I have five areas in this query where I would need to add the name, I just want to do it once?

Thanks.

Fen how

View 14 Replies View Related

DLookup With Criteria = User Input From Like()?

Dec 5, 2007

I seem to have run into a snag. I'm pretty sure this would work otherwise:

DLookup(iif(Left( [GenericFieldname1], 4) = "C101","Offered","NOT OFFERED"),"Generic Query")

When I added this field/function to the query, I got #error values and when I clicked on one, I got an error that said:

The expression you entered as a query parameter produced this error:The object doesn't contain the automation object 'Please Enter Year'.'

I assume the problem is that I have the user filter the report by use of the Like() function for [Year] in the query when the report is accessed. How do I tell the DLookup function to search the column as filtered by the Like() function?

View 1 Replies View Related

Reports :: DLookup With Multiple Criteria

Apr 11, 2013

creating a DLookup expression for an unbound control on a report. This report displays data collected from the users from a table called tblaudit completed. This table contains (among others) three fields labeled below

Brand
Form
Area Changed

I also have another reference table labeled refevalareas. The fields in this table are as follows

BrandName
FormName
AreaName
PointValue

I would like to create an unbound control on my report that "prints" the point value depending on the Brand, Form, and Area displayed for each record. Though the field names are different the data collected and displayed in each table is consistent. I haven't worked much with Lookups and the logic is challenging for me to grasp but I think I have the basic idea which is shown below..

DLookup( refevalareas.[PointValue], refevalareas, ("[BrandName] = '" & Reports!rptqryauditcompleted![Brand] = "'") & ("[FormName] = '" & Reports!rptqryauditcompleted![Form] = "'") & ("[AreaName] = '" & Reports!rptqryauditcompleted![Area] = "'")

View 4 Replies View Related

General :: DLookup With Date Criteria

Jun 29, 2012

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

Code:
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 are both 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

View 14 Replies View Related







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