Modules & VBA :: DLookup With Date In Form
Mar 5, 2014
At the moment I have a form (InputFrm) for operators to enter production processes into the system. Each one of these has a certain weight which the operators enter and it all stores in InputTbl, which works great.
However I am trying to have a running total of the kilograms inputted on each day displaying when a new record is opened. To do this I have created a query (DailyDataQry) which provides me a running sum of how much input has gone through the process, and I can clearly see the data in this query.
I am attempting to have a text box in the upper right of my form which displays this number as after a certain weight of input the operators are needed to perform checks.
This is the code I have at the moment in the control source of a text box (DailyInputTotalTxt), but it is just returning a blank:
Code:
=DLookUp("[Total Input]","DailyDataQry","[Work Date] = #" & [Forms]![InputFrm]![WorkDateAtxt] & "#")
The work date is generated automatically when the new record is created by having Date() in the default value of the work date (WorkDateAtxt) on the form.
Is there any way which I can use this value to lookup the current total of input contained within DailyDataQry?
View Replies
ADVERTISEMENT
Dec 12, 2014
Cost can fluctuate through the year on a month to month basis. I want to create a report January - Feb... that has the cost for each month Table CostChange has [Date] [Item] [Cost] with a record ONLY when there is a change so: Item X has a cost in Jan and a new cost in Mar
I want report:
Jan Dlookup([Cost],[CostChange],[Date]<=1/31/2104 (should return Jan Cost)
Feb Dlookup([Cost],[CostChange],[Date]<=2/28/2104 (should return Jan Cost)
Mar Dlookup([Cost],[CostChange],[Date]<=3/31/2104 (should return Mar Cost)
What am I missing?
View 6 Replies
View Related
Mar 12, 2014
I have a form with Date of Death (DOD) field. I would like update DOD from a table dbo_patient into Z_Patients table.
I have set the datatype as Date/Time in the form for Date of Death.
Code:
Private Sub Update_DOD()
Dim rcMain As New ADODB.Recordset, rcLocalDOD As New ADODB.Recordset
Dim DOD As String
rcMain.Open "select distinct PatientKey from Z_Patients", CurrentProject.Connection
[Code] ....
However I am getting some error Run-time error '-2147217913 Date type mismatch in criteria expression in section below.
Code:
CurrentProject.Connection.Execute "update Z_MAIN_Processed_Patients set DateOfDeath = '" & rcLocalDOD!date_of_death & "' where PatientKey = " & !PatientKey
View 5 Replies
View Related
Jul 15, 2005
Hi,
I'm trying to return records from an orders table where the date of the order is >= to the date created on parts in a parts table. The syntax I'm using now is: >=DLookUp("[Date created]","[Parts]","[date created]") in the order date criteria field. I don't get any errors but the data returned is not correct..eg
Part number 802 was created on 07/10/2005 and there is an order with this part created on 07/04/2005, this order should not show but it does.
You probably need to know why this part is in the order but has been created after the order date....I'm adding inventory to an exsisting database and need to show parts on hand that are now in stock so I need to only do my calculations on parts sold that were created in the orders table with the date greater then the part created date.
Thanks for any help.
View 6 Replies
View Related
Mar 12, 2015
i have 2 tables.. 1 with bookings and 1 with a BonusPeriod range.
in the Bookings table there is a field called [ServiceDate]
in the bonus table i have a field called [Period] which is text i also have 2 dates, [StartDate] And [EndDate] i need to Dlookup the Bonus table to return the value of the period table based on where the [ServiceDate] falls.
i dont want to use VBA (i really need it to work within a query that inserts it's data into another table)
i've tried everything but had no joy
Expr1: DLookUp("[Period]","BonusPeriods","[ServiceDate]" Between "#[StartDate]#" And "#[EndDate]#")
Expr1: DLookUp("[Period]","BonusPeriods","[Staff_BookingsAndQuotes_Master].[ServiceDate]" Between "#[StartDate]#" And "#[EndDate]#")
Expr1: DLookUp("[Period]","BonusPeriods","(FormatDateTime([ServiceDate]),"yyyy-mm-dd") Between (FormatDateTime([BonusPeriods]![StartDate]),"yyyy-mm-dd") and (FormatDateTime([BonusPeriods]![EndDate],"yyyy-mm-dd"))))
View 10 Replies
View Related
Aug 12, 2005
Hi
I'm on the verge of successfully using a DLookup function (with the help of previous posts) in a query in order to obtain the value (volume1) of the previous record based on a primary field which happens to be a date (iddate).
The following results in sporadic success:
Expr1: (DLookUp("[volume1]","tbldailylog","[iddate]=#" & [iddate]-1 & "#"))
The iddate field is sorted and contains consecutive days.
Any help would be greatly appreciated.
View 4 Replies
View Related
Jun 27, 2007
Hi
I have a table named PO table...How do I get the fiscal month from the date column I dont want to search a particular date but the date field column and retrieve the fiscal month using DLookup function?....
Can anyone help me ASAP?....
Sriram
View 1 Replies
View Related
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
Apr 11, 2014
I have table [table1] which have 4 fields
EMPID - primary key
EMPFirstname
EMPLastname
EMPDOB
Input box as txtempid as string
I am trying to learn how I to display other field based on one field in my case last three field based on EMPID
I am actually getting syntax error when I dry to display DATE Field
Following are my attempts
Dim Verfirstname as string
Dim Verlastname as string
Dim VerDOB as date
Verfirstname = dlookup("[EMPfirstname]","table1","[EMPID]='" & me.txtempid & "'")
Msgbox Verfirstname
works fine. But when I use for Date, I am getting syntax error
VerDOB = dlookup("[EMPDOB]","table1","[EMPID]='" & me.txtempid & "'")
Msgbox VerDOB ---- now I am getting syntax error
I tried to understand over net and I believe i need to used with "#" before and after the date field but my criteria...
View 1 Replies
View Related
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
Dec 17, 2014
Ok, so I have this table (tblPayPeriod) with the fields Start Date, Stop Date, Pay Period & Percent FY. The Percent FY field is a Number field that gives off the percent of the fiscal year (FY) that we are in.
Then I have a report (rptDistrictMgr) that in the header I want it to display what percent of the fiscal year we are in based on the Auto_Date that is in the header.
So, if the Auto_Date is December 17, 2014, we are (according to tblPayPeriod) 24.1% into the fiscal year and I want the 24.1% to display in the text box which called Text99.
I would just like a simple dlookup that I can use as the control source of the text box or an expression to put into the On Load sequence.
View 6 Replies
View Related
Apr 22, 2015
I am trying to dlookup a field (field name [client]) which is on a form (form name bkpo)
The code i use is this
CLNM = DLookup("[ClientName]", "[BKPOCL]", "[ClCode] ='" & [Forms]![BKPO]![Client] & "'")
It works just perfect however if I use the form BKPO as a subform then the code doesn't work
The form is BK1b
and the subform is BKPO
I tryed this but doesn't work
CLNM = DLookup("[ClientName]", "[BKPOCL]", "[ClCode] ='" & [Forms]![BK1b].[BKPO]![Client] & "'")
I have tryed several other options like [Forms]![BK1b]!BKPO.[Client] or [Forms].[BK1b]![BKPO]![Client] but nothing
View 7 Replies
View Related
Nov 5, 2013
I am looking to use a "If" statement with DLookup to find a record and see if the field name begins with "TW" as the characters. Here is what I have so far:
Code:
Dim matchCriteria As String
matchCriteria = "LIKE 'TW*'"
If DLookup("end_user", "tbl_module_repairs", "prikey = " & Me.txt_rid1.Value) = matchCriteria Then
MsgBox "Success"
Else
MsgBox "FAILURE"
End If
I am trying to switch between two sets of pricing for customers that begin with "TW" and then all else. Would the use of a "case" statement be better? If so I do not have the "end_user" bound to the form so I would need to embed Dlookup in the "case" method.
View 5 Replies
View Related
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
Jun 26, 2015
I have a command button on a Access 2010 form that i am using as my switchboard. On this form i have a hidden unbound text box that captures the users environ"username". When the user hits a command button on the form the code looks at the name in the hidden textbox that captures the environ"username" and then DLooks up a table to see if there is a match. If yes then it will open the next form and if not then a message box appears.
Code:
Private Sub Command6_Click()
Dim TxtUsername As String
If Me.TxtUsername = DLookup("[OneLondon Login]", "TblAccessUsers") Then
DoCmd.OpenForm "Bakerloo_Main_Form"
Exit Sub
Else
MsgBox "You do not have permission to access this database"
End If
End Sub
This works fine apart from the fact it will only read the first name entered in the table. This table(TblAccessUsers) could have up to 50 names in it and possibly have names removed and re added at a later date. Is there a way i can get the code to look up every name in the table ??
View 3 Replies
View Related
Jul 31, 2013
I have a nested DLookup in a SQL UPDATE that is not working. The DLookup has an "AND" in the WHERE Statement as well. I cannot figure out the problem. I got a type mismatch with this code and when I take out the single quotes I do not get an error message but the data is not updated in the table.
Code:
SQLstock1 = "UPDATE TBL_STOCK SET Stock = '" & DLookup("RemainingQty", "QRY_STOCK", "ActionEntity = '" & Me.cmb_customer1 & "'" And "StockType = '" & Me.cmb_stock_type1 & "'") & "' WHERE StockEntity = '" & Me.cmb_customer1 & "'"
View 1 Replies
View Related
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
Mar 26, 2014
Here is my VBA Dlookup Code:
Code:
Public Sub test()
Dim frm As Form
Set frm = Forms!StationLevelSummary
[code]...
At the bottom im printing the content of the controls which are on my form. These should return 1 number, but for some reason it does not. Ive used this code many times but I cant figure out why nothing is being returned.
View 4 Replies
View Related
Aug 15, 2013
I'm pretty familiar with getting values from a table via Dlookup. What I want to do is almost the reverse if possible? I'm declaring a variable as follows:
Dim Ref as string
Ref = [lead_id]
This is from a form.What I'd like to be able to do is go to the table [list], reference the lead ID in the table via the variable then change the field [status] to "INCALL".Can this be done in a similar way to Dlookup?
UPDATE - here is the code I am trying to use
Dim ref As String
ref = [lead_id]
Dim MySQL As String
MySQL = "UPDATE vicidial_list SET"
MySQL = MySQL & "vicidial_list.status = 'INCALL' "
MySQL = MySQL & "WHERE (((vicidial_list.status)= Ref))"
DoCmd.RunSQL MySQL
Which gives me an update clause error
View 4 Replies
View Related
Feb 26, 2014
If I type 2014 instead of b everything works out.
Code:
Dim a As Double
Dim b As String
b = Right(RepMonth, 4)
a = DLookup("[EUR/TRY]", "Plan_FX_RATES", "YEAR = '" & b & "'")
Debug.Print a
Debug.Print b
Debug.Print RepMonth
View 4 Replies
View Related
Aug 15, 2015
I am trying to run this code but getting a run time error 94 - null value - when it shouldn't be.I am thinking I have written the Dlookup incorrectly?
Code:
Private Sub txtProductName_Click()
Dim iProdType As Integer
Dim ProductID As Integer
iProdType = DLookup("ProductTypeID", "tblProduct", "ProductID" = Forms![frmBooking]![cboProductID].[Value])
Note ProductID in an integer
How can I diagnose this or what could the solution be?
View 5 Replies
View Related
Feb 15, 2015
I set up an Access 2003 database where this code works OK:
Private Sub Street_Exit(Cancel As Integer)
Me.Suburb = DLookup("Suburb", "Streets", "StreetName = Forms![Add A New Member]!Street")
Me.StreetID = DLookup("ID", "Streets", "StreetName = Forms![Add A New Member]!Street")
Me.Postcode = DLookup("Postcode", "Streets", "StreetName = Forms![Add A New Member]!Street")
Me.Town = DLookup("Town", "Streets", "StreetName = Forms![Add A New Member]!Street")
End Sub
I've now migrated the tables to a back-end in Azure using ODBC to connect. The linked Streets table in my list displays as dbo_Streets but when I change the table name in my code I get an error.
View 4 Replies
View Related
Jun 27, 2013
I have the following SQL Statement which returns the desired result to me
Code:
SELECT tbl_p_stats.i_closing
FROM tbl_p_stats
WHERE (((tbl_p_stats.i_weekending)=[forms]![frm_a]![i_olddate]) AND ((tbl_p_stats.i_sacat)=[forms]![frm_a]![wfid1]) AND ((tbl_p_stats.i_complexity)=[forms]![frm_a]![wfc1]));
And am now trying to convert it to a Dlookup to provide the default value to a control on form Loading. The statement i came up with is
Code:
= DLookup("[i_closing]", "tbl_p_stats", "[i_weekending] = " & i_olddate & " AND [i_sacat] = " & wfid1 & " AND [i_complexity] = " & wfc1)
However, it is showing the control (wf1oi) as a blank control now. When I run the dlookup through the immediate window, the returned result is Null.
A little more detail
- All the mentioned controls live on the same form (frm_a)
- The control that this Dlookup is going into is called wf1oi
View 10 Replies
View Related
Jul 10, 2013
I need my INSERT statement to DLookup tbl_module_repairs and insert the information from a field based on a WHERE condition of primary key matching on the form and table. Here is what I have but it will not work on the last value:
Code:
Dim lookModType1 As Variant
lookModType1 = DLookup("module_type", "tbl_module_repairs", "prikey = " & Me.txt_prikey1.Value & "")
SQLtext4 = "INSERT INTO TBL_RF_TECH_LOG ([TechID], ActionStatus, Barcode, EquipmentDescription) Values (Left([txt_techid_rework_in], 2),'In Rework', '" & Me.txt_bc1 & "', " & lookModType1 & ");"
DoCmd.RunSQL SQLtext4
Note: My DLookup works fine by itself for other uses.
View 5 Replies
View Related
Jul 17, 2014
I am having problems with Dlookup from a table which is linked to Excel.The heading on the fields from Excel have been imported as F1, F2, F3 etc... the code I am using is as follows .....
LOOK = DLookup("F6", "DATABASE_LOOKUP", "F1 = 1338087")
F6 = A date I am looking up.
DATABASE_LOOKUP = table I am looking up.
F1 = 1338087 = The record I am looking for.
View 5 Replies
View Related
Apr 8, 2015
I Have one main form in access 2003 i want to use Dlookup function to 1 text box with 2 criteria is it possible?
My Formula:
Text67. Value = DLookup("Balance", "dbo_WbookEdit", "[Lotno] = '" & Forms![Edit]![LotNo] & "'" And " & [Description] = '" & Forms![Edit]![Description] & "'")
View 6 Replies
View Related
