Modules & VBA :: LIKE In DLookup After Syntax
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 Replies
ADVERTISEMENT
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
Feb 20, 2014
I need to use a dlookup on a report to look up fields on another query, other than what the report uses.
How do i use the dlookup syntax?
I have a field on another query called "Average" and the query name is AverageResults. The report is called Totals.
View 9 Replies
View Related
Mar 26, 2008
Hello,
I have a table called "ShrinkWrapBinding" where I am supposed to look up a "Price" from. As you see in the code below, I calculate a "totalPackages" number which comes from values in a form. My problem is that when I try to use this value (totalPackages) on the DLookUp function (as seen in this code) it gives me a syntax error exactly on the totalPackages variable. Please note that I added single quotes to the 'totalPackages' variable when I use it on the DLookUp function, and have tried every possible combination (single quotes, double quotes) but still doesn't work. What is it that I'm doing wrong?
Code:
Dim totalPackages As Integer
Dim tempPrice As Double
totalPackages = Me.QuantityBooks1 / Me.ShrinkWrapQty
tempPrice = DLookup("[ShrinkWrapBinding]! [Price]", "ShrinkWrapBinding", "'totalPackages' >= [ShrinkWrapBinding]![RangeField]")
Thank you
View 8 Replies
View Related
Jul 1, 2007
Hi all, instead of doing a dlookup via a query, i'd like to do a dlookup for price direct in a table where the criteria is the value in Text1 from Form1
outt = Nz(DLookup("[Price]", "Table1")) Where Product = ' Text1' from Form1
Whats the correct syntax for this please? Thanks
View 2 Replies
View Related
Jun 7, 2006
I am trying to implement DLookup for the following sql to a linked SQL Server table to verify that the currentuser is in my Traders table:
select tr_username from Traders where tr_username='edobbie'
When I enter the following DLookup command I get the error 'compile error expected ='
The offending line is:
DLookup("tr_username","Traders","[tr_username] = " &CurrentUser())
The Editor doesn't seem to see the = sign in my criteria. I've modified the syntax a number of ways to no avail.
Does anyone see the problem?
View 2 Replies
View Related
Dec 22, 2007
I wonder if you can help me. In my query, for this calculated field, i get an error message about a syntax error missing operator in query expression.
what did i do wrong? thanks
FormulaID is a text field.
UnitRM Combined: nz(DLookUp("UnitRM Combined","QryFormulaSummed","[QryFormulaSummed].[FormulaID]='" & tblCostJunction.FormulaID & "'"))
View 5 Replies
View Related
Jan 3, 2007
I have a report that when run, prompts a user for an id. The user enters and id and it pulls the information based on that id. The information is being pulled from data stored in Table A. I also have Table B that associates an id with a name. In my report I have the input being stored as a string, called strCampusId. I have a text box with called txtCampusMinistryName. I want the report to do a lookup based on the users input and display the CampusId Name, however I am getting an error. The error says, RunTime Error 3075, Syntax error (missing operator) in query expression 'ParishId=*0101'.
Table B (called CampusMinistry) only has 2 columns, ParishId and ParishName
Here is my code that I am trying to use.
txtCampusMinistryName = DLookup("ParishName", "CampusMinistry", "ParishId=" & strCampusId)
Thanks for any and all help.
View 2 Replies
View Related
Feb 24, 2015
Me.txtversion = "Ver:" & " " & DLookup("[txtVersionMajor] & " - " & [txtVersionMinor] & " - " & [txtVersionRevision]", "tblVersionInfo")
What's wrong with my syntax ? It's the "-" part that is the problem.
View 3 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
Apr 17, 2013
I am trying to create a Dlookup in Access 2010 within a query using query wizard. I want to lookup the tax rate for an employee based on a salary range and their 'tax category' (string). Through troubleshooting I can get the criteria to work separately.
These are: DLookUp("Base","TABWT","[TABWT].[Taxclass] = '" & [FirstOfTaxGroup] & "'")
DLookUp("Base","TABWT",[grosspay] & " Between [TABWT]![Minimum] And [TABWT]![MaxBracket]")
These work and return the correct values for each column/row when I run the query.
However, when I combine the criteria (using the build wizard) as follows:
Expr1: DLookUp("Base","TABWT","[TABWT].[Taxclass] = '" & [FirstOfTaxGroup] & "'" and [grosspay] & " Between [TABWT]![Minimum] And [TABWT]![MaxBracket]")
The Dlookup will returns 0 values but will not give an error message.
I've tried quite a few variations on syntax and quotes and so on. However, it's just not working for me.
View 3 Replies
View Related
Dec 9, 2014
sort this error out:
Code:
If Nz(DCount("*", "[Tblupdate]")) = 0 Then ' The count is zero
i = 1
Else
i = DMax("ID", "tblupdate")
End If
Dim ssql As String
Dim j, k As String
j = "P" & i
k = Environ("username")
ssql = "Insert into tblUpdate(Update_ID,Date,Username) values('" & j & "',#" & Format(Date, "dd/mm/yyyy") & "#,'" & k & "')"
CurrentDb.Execute ssql, dbFailOnError
View 2 Replies
View Related
Jul 2, 2014
I am working on a query and currently I am not getting an error, but I am getting a parameter undefined pop-up when I try to run the query. The parameter it doesn't understand is really a field value that I am trying to interrogate.
NewKeyShip is one of 5 choices for the Key Order Type field. I am trying to have the system input a field value in SW Key when NewKeyShip is the selected value. I should mention that the rest of the code works perfectly starting with "If Order Form Details, part Number is like "H40." and through the end. It is only the first part that is not working well.
[code]
Sw key: IIf([tbl_OrderForm].[KeyOrderType]=[NEWKEYSHIP],[tbl_OrderForm].[NewKeyNumber],IIf([tbl_OrderFormDetailsNew].[PartNumber] Like "H40*",IIf([tbl_OrderForm].[AlohaKey]>0 And [tbl_OrderForm].[NewKeyNumber] Is Null,[tbl_OrderForm].[AlohaKey],IIf([tbl_OrderForm].[NewKeyNumber]>0,[tbl_OrderForm].[NewKeyNumber],''))))
View 5 Replies
View Related
Mar 20, 2015
I want a code like this:
DoCmd.OpenReport "rptPrecintosAGDLLA", acViewReport, , "[Username]='" & Me.[Username] & "'"
But after the "Me.[Username]" part, I want it to also filter to only show me data that has null values on a column called "PN".Alternatively, I also need a code that instead of only showing me data that has null values, I want it to show me data that excludes the data with null values on the column called "PN".
View 8 Replies
View Related
Jun 11, 2013
Why I get a runtime 3134 error on this piece of code.
They are all text values
Code:
Dim strUserName As String
strUserName = Forms!FrmPrimaryData.FrmPrimaryDataInstallsSubFrm.Form.txtMacAddress
Dim strIDValue As String
strIDValue = Nz(DMax("[ID]", "radreply"), 0) + 1
Dim strAttribute As String
[Code] ....
View 6 Replies
View Related
Jan 22, 2014
I am having a problem with below and getting a run-time error 3134
Code:
LastOrderNumber = DMax("Order", "Model_types")
NewOrderNumber = CLng(LastOrderNumber + 1)
CurrentDb.Execute "INSERT INTO Model_types (Order) " _
& "VALUES (" & NewOrderNumber & ")"
The field 'Order' in Model_types is a Long Integer.
View 4 Replies
View Related
Aug 1, 2015
I have a Listbox and when i double click on a selected record (Student Name), I receive a syntax error. I am trying to open a form containing the student information.
VBA:
Private Sub List1_DblClick(Cancel As Integer)
DoCmd.OpenForm "NEP", , , "[Student Name] = Forms!NEP!Student Name"
End Sub
View 10 Replies
View Related
Feb 18, 2014
Somehow it doesn't take this variable msoFileDialogSaveAs. It tells me it's unknown.
Code:
Public Sub Command2_Click()
On Error GoTo MyError3
Dim filelocation As Variant
Dim g As Object
Set g = Application.FileDialog(msoFileDialogSaveAs)
[Code] .....
View 2 Replies
View Related
Dec 10, 2014
I need to get this syntax right. I have something similar that worked before to open a report but now I am using the same code structure on opening a form and I can't get it.
[prikey] is an autonumber and that has given me trouble before with the syntax. [EstimateFlagCleared] and [WarrantyFlagCleared] are Yes/No fields.
Dim maxFlag As String
Dim flagCriteriaWarranty As String
Dim flagCriteriaEstimate As String
[Code] .....
View 13 Replies
View Related
Jul 20, 2015
Syntax error in this append query. What is it?
Code:
Dim DataToAdd As String
DataToAdd = "INSERT INTO Address " & _
"customerId, addressNr, addressType, firstname, lastname, companyName, postalcode, country, workphone, email, notes, streetaddress, city, contactTypeId " & _
[code]...
I am trying to append data from the query into the table where the value on function field in the query is equal to the word ADD
View 7 Replies
View Related
Mar 13, 2015
I am getting a syntax error on my SQL statement.
On a form I have a sub form containing the field txtGuestID - whose control source is GuestID.
On the main form I have a button that fires the code below.
I am sure I am not referring to the control txtGuestID correctly.
Code:
Private Sub cmdInbound_Transport_Click()
Dim iProductID As Integer
Dim sSQL As String
On Error GoTo cmdInbound_Transport_Err
[Code] ....
View 6 Replies
View Related
Apr 13, 2015
I have the following code with dcount
If DCount("Username", "[tbl_userinformation]", "[Username] = " & Me![Text146] & " AND [actualdate]=" & Me![Text148] & " ") > 0 Then
but i have the following error ...
runtime erro 3075: syntax error in number in query expression '[username]=f15691b and [actualdate]=13.04.2015'
f15691b and 13.04.2015 are the values those i entered.
View 13 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
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
