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
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
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.
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
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
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
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.
Dim dbtmp As DAO.Database Dim tblObj As DAO.TableDef Dim rs As DAO.Recordset Set dbtmp = OpenDatabase("C:WINDOWSBureaudevcli.xls", False, True, "Excel 8.0;") DoEvents Set rs = dbtmp.OpenRecordset("select * from [DEVCLI$A1:C10]")
in the last line i would like replace DEVCLI (the Excel sheet name) by a variable to make my program works with any excel file. What is the syntaxe for that ? Thanks in advance for help. VINCENT
Hi, I wud like to know why this syntax is not working. select * from table_name where col_val like 'a%'
This returns the column values that start with 'a'. I was getting the result properly few days ago. But now suddenly this query doesnot give the result. When i tried this out in a website it was working there, but not in access.
Can anybody see what is wrong in this syntax? If the syntax is right where wud be the fault?
Can someone tell me how to insert the contents of a variable into a table? I am trying to add selected items from a listbox (Members) into a temporary table (tempMembers). The code is below. On debugging, it picks up the contents of the variable (iMemberID) OK, but adds "0" to the temp table (both are integers). I'm pretty sure the problem is in the Insert statement below (stSQL3), but I can't figure out how to write it!
Any help would be greatly appreciated! Thanks
stSQL3 = "INSERT INTO tempMembers (MemberID) VALUES & iMemberID & ;" For Each varItm In Me!Members.ItemsSelected iMemberID = ctl.ItemData(varItm) DoCmd.RunSQL stSQL3 Next varItm
Can someone help me on the syntax for the following? I have two tables: one is TapeNumber and has one field (Tape_No) autonumbered PK. The other table has information about the video and uses the Tape_No as a secondary key.
I have a form to add information about videos. I have the form bound to the first table, so it adds a new TapeNumber when I add a record. I have a subform displaying the video information for that tape (there may be several videos recorded on one tape).
When I add a new tape (or video to an existing tape), how do I save the tape number to the second table?
have this code which keeps on giving me this error...I dont know whats wrong with the code please help tried removing the quotes (rstStudents.Open "SELECT * FROM Students WHERE Regno = " & _ txtReg , _ CurrentProject.Connection, _ adOpenStatic, adLockReadOnly, adCmdText) but then it wont retain any records even if they exist in the table
here is the original code
rstStudents.Open "SELECT * FROM Students WHERE Regno = '" & _ txtReg & "'", _ CurrentProject.Connection, _ adOpenStatic, adLockReadOnly, adCmdText
ERROR MESSAGE>>>>>>> Runtime Error '-2147217913 (80040e07) Data type mismatch in criteria expression
the code is supposed to use number input by user to search in a table and fill all the other fields in the form with the table data (it is run on lost focus of the text box were value has been input)
the whole code :::::
Private Sub txtReg_LostFocus() Dim rstStudents As ADODB.Recordset Dim blnFound As Boolean Dim fldItem As ADODB.Field
blnFound = False
If Me.txtReg = "" Then Exit Sub
Set rstStudents = New ADODB.Recordset rstStudents.Open "SELECT * FROM Students WHERE Regno = '" & _ txtReg & "'", _ CurrentProject.Connection, _ adOpenStatic, adLockReadOnly, adCmdText
With rstStudents While Not .EOF For Each fldItem In .Fields If fldItem.Name = "Regno" Then If fldItem.Value = txtReg Then Me.txtName = .Fields("Name") Me.txtAdd = .Fields("Address") Me.txttel = .Fields("Telno") Me.txtTutor = .Fields("TutorName") Me.txtbks = .Fields("NoBooksonloan") blnFound = True End If End If Next
.MoveNext Wend End With
If blnFound = False Then
MsgBox "No student record to display" ' ... and reset the form ' cmdReset_Click End If
I am trying to open a PDF from Access. I get it to work but it requires me to hard code the path and fiel name....
The path will always be the same although the file name will change. RIght now I get the fiel name from a textbox on a form...I am trying to change the below working code with a varaible instead of the path....I think there is some systax issues that I cannto fingure out
TotPath is the variable that holds the entire path to the file "X:Map_LibraryPark_and_Rec_AsbuiltPRA_19_73-01_W0.pdf"
This variable is rebuilt everytime the user selects something on the form....so the actual File name will change every time...
Does anyone have and ideas as to how to incorporate a variable instead of the hard coded path?
THank you all for your help.....
Code:WORKING CODE: 'Shell "C:Program FilesAdobeAcrobat 7.0AcrobatAcrobat.exe X:Map_LibraryPark_and_Rec_AsbuiltPRA_19_73-01_W0.pdf", vbMaximizedFocus ' open a txt documentNOT WORKING CODE:Shell "C:Program FilesAdobeAcrobat 7.0AcrobatAcrobat.exe TotPath", vbMaximizedFocus ' open a txt document
I'm familiar with SQL but not with Access97 - some wierd syntax here!
I'm creating a Query that is to Delete records from a table depending on a non-key field in a second table.
I first tried an INNER JOIN
DELETE Activity_Preferences.* FROM Activity_Preferences INNER JOIN Activity_Key Activity_Preferences.Activity_Key=Activity_Key.Act ivity_Key AND Activity_Key.Complete=1;
which was initially accepted but then complained about the INNER JOIN after I went in a second time to 'fix' the error.
I've finally got the following syntax accepted:-
DELETE Activity_Preferences.* FROM Activity_Preferences, Activity_Key WHERE Activity_Preferences.Activity_Key=Activity_Key.Act ivity_Key AND Activity_Key.Complete=1;
But when executed still gets the following error (same as for the INNER JOIN):-
Hi, In a form i use the following filter fonction : DoCmd.ApplyFilter , "CliNom = '" & RunCli & "'" The problem is that sometimes RunCli (wich is a name) contains ' symbol and then generates a syntaxe error, is there a way to prevent that ? Thanks in advance. VINCENT
On my form I have 2 radio buttons rdoAll and rdoSpecific. If rdoAll is true then it prints a report. THis part works fine. However if rdoSpecific is true then I make visable combo box to look up an ID. Then when I click the button I want the same form to open but with just the info pertaining to the ID selected. Here is my code:
Private Sub cmdLotHistory_Click()
Dim stDocName As String, stSelection As String
stDocName = "rptLotHistory"
If Me.rdoAll = True Then DoCmd.OpenReport "rptLotHistory", acViewPreview End If
If Me.cboLotLU.Value > 0 Then stSelection = "[LotID] =" & Me![cboLotLU] End If
I am using the folling code in a after update combobox event:
Dim rst As DAO.Recordset Dim sqlwhere As String
sqlwhere = " "
If IsNull(Me.comboSearchSerial) = False Then sqlwhere = "SerialNumber='" & Me.comboSearchSerial & "' and " End If
mysql = "SELECT TBL_RMA.* from [TBL_RMA] Where " mysql = mysql & sqlwhere mysql = mysql & ";" Forms!FRM_RMA.RecordSource = mysql
Exit_comboSearchSerial_AfterUpdate:
This is currently working for me with SerialNumber being a text field. I would like to use the same code substituting SerialNumber with a Date field. I know its probly an issue with the use of quotes, but I can't seem to figure it out. Also, if I wanted to use a number format, what would I use... Thanks Gregg
