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
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
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.
What would be the correct DLookup syntax to perform the following operation:
I need to bring in a value from a field named "Description" from a table called "PNs and Desc". The "Description" should be relative to another field named "PN" (located in another table).
I have a textbox on a report that I wish to populate based on the value that is in another textbox/field on the report, and I thought DLookup was the way to go - however, I cannot seem to get it to work.
I have a table (ComplaintsResponses) that has two fields, both text
[ShortDescription] [ResponseText]
The text from [ShortDescription] is saved in a field on another table that contains all the other relevant information that is used in the report, and whilst this short description is mostly fine, I have one report where I need the data from the larger [ResponseText] field.
I am working with a database that I downloaded and am trying to modify to fit my needs.
This is an inventory database. The products table contains a description and pricing. I want the description and pricing to populate in the Purchase Order form, so I added Dlookup fields in the Purchase Order form. I was happy.
However, the pricing information is not populating to my Inventory Transactions Table from the Purchase Order form by way of this Dlookup feature, and therefore will not show on my report, and in turn does not show in my Total of my Purchase Order report.
As a work around, I tried creating a calculation in the purchase order report, of =[UnitsOrdered]*[Products.UnitPrice], and the pricing totals show fine on my report, but the subtotal doesn't work.
I was unable to upload my file...so a few notes of info...
There are no queries set up in the database for this report.
I had tried a sorting grouping thing (in the Report) by Subtotal, but now can't get rid of it.
When I show the field list for the report, across the top of the window reads:
I have a report with quite a few subreports in it. There are a number of calculated fields on the form, most of which use Dlookup to retrieve at least one of the figures required for the calculation. The Dlookup runs fine and the report opens but the calculated fields are devoid of data in Report view. When I switch to Print Preview view the fields are now populated. Below are two variations of the DLookup syntax I have used to try and alleviate this issue.
=DLookUp("[8]","qry_MonthlyTotalsByYearFirstAid","[ActivityType] = 'First Aid Injury (FAC) Reported in Safeguard'")/[sub_AllHours].[Report].[8]
=DLookUp("[8]","qry_MonthlyTotalsByYearFirstAid","[ActivityType] = 'First Aid Injury (FAC) Reported in Safeguard'")/[Reports]![rpt_AllFigures]![sub_AllHours]![8]
Note that the field [8] specified here is simply a month number and forms a column in the crosstab query for the corresponding query name.
I would add the query referred to in the DLookup to the source query for the report but the source report's data is derived from a Crosstab query, which only accepts one data field (Access terms this as the value field.
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
