Queries :: Error 3134 On A Query That Worked Before
Aug 29, 2013
I ran a very similar code with near-identical SQL, with only the fields and tables changed, and it had worked before. However, after changing the fields and tables, I repeatedly get error 3134 for "syntax". The code was
Code:
Private Sub UpdateI_Click()
Dim PerName As String, strsubb As String, Popup As Role, Strsub As String
PerName = InputBox("What is their name?")
strsubb = "INSERT INTO Company-personID (CompanyNo, Name) VALUES ('" & Me.CompanyNo & "', '" & PerName & "');"
DoCmd.RunSQL strsubb
[Code] ....
You can ignore the userform and inputbox if they're not important in this, but I figured they might just be. When I run "Strsub" and "strsubb" by themselves, they both come up with errors, so it's not one or the other. There are two tables in this. A table of companies, which has its form open, and a child table of companies and their people.
I wanted strsubb to put in a record where a person is added, their name based on the inputbox, and their associated company to be marked as the one that is in the field "companyno".
Strsub is meant to set the same person's roles to be whatever was clicked on the userform. If it's important, the roles are in a lookup wizard with multiple choices.
I had a post last week with this same problem. Last time I was using a reserved word but I have ruled it out with this database because I changed every field name and I still get the error. I think it may have to do with the combo boxes I am using, but not really sure why I am getting the error. Any help would be great. The sql statement is:
This crosstab query has the employee's name as a row heading, each calendar day as a column heading and the sum of Hours worked as the value.
TRANSFORM Sum(tbl_Tracker.Hours) AS SumOfHours SELECT tbl_Employee_Master.Full_Name FROM tbl_Employee_Master INNER JOIN tbl_Tracker ON tbl_Employee_Master.user_no = tbl_Tracker.User_Id GROUP BY tbl_Employee_Master.Full_Name ORDER BY tbl_Employee_Master.Full_Name, tbl_Tracker.TheDate PIVOT tbl_Tracker.TheDate;
It returns data that looks like this: FullName 2/3/2014 2/4/2013 2/5/2014 John Smith 8 0 8 Joe Jones 0 8 0 Cathy Wise 8 8 8
I want the data returned to look like this: 2/3/2014 2/4/2014 2/5/2014 John Smith Joe Jones John Smith Cathy Wise Cathy Wise Cathy Wise
The hours worked are not important - only the names associated with the dates.
I have a table of employees, and dates they worked on. These are seasonal employees who want to get in as many days as possible before the season ends, but regulation states they must take a compulsory rest day after x days.So I need to create a query that can return the list of employees, with a count of consecutive workdays up until current date.
If today is 20/09/2013, and Johnny worked on, 19, 18, 17, 15, 14, 13, his count must be 3, because he was absent on 16. Therefore only from 17 through 19 is regarded as consecutive.
If Peter worked 19, 18, 17, 16, 15, 14, 13, his count would be 7, because unlike Johnny, Peter still worked on 16.
The plus indicates a night shift worker eg started at 19.18 and finished at 5.37 on the 12th
so for every record I have staff id , date, and clock time ( I have stripped out the + ) and created a yes/no field to identify the records where field2 should actually be field2+1
I have sat in query design screen for ages and cant think how on earth I am going to calculate hours worked for a given staff member and date combination.
for every combination of staff id and date there should be 2 records - a clock in and a clock out
I thought about creating new fields clockin and clockout but struggling to see how I can link the 2 "paired" records together
I have a form where a start date is inputted (Inputfrm , StartDate) and a form where the end date of the process is recorded (Inspectionfrm , EndDate) and these both record in the table InputTbl as StartDate and EndDate respectively.
I have created a union query which shows a list of all the dates where there is work recorded (WorkingDatesQry and the column of list of unique dates is "WorkingDate"), and as we run a highly varied schedule depending on time of the year and order numbers I cannot just use a query which says Monday-Friday or Tuesday - Saturday.What I am trying to do is to find the number of days between StartDate and EndDate where there is a date recorded in the WorkingDates query.
I have a database which among other things records how jobs are received i.e.: Telephone, Email, Mail, Facsimile or Web.For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.I have created a make table query for all jobs received between variable dates for a client entry of the name of the client and the start and finish dates are required to run the query.
I have a crosstab query set up to count each method of receipt and a final query to work out the percentages using the total from the crosstab query fields divided by the total of all methods.I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different methods of receipt may vary for the less active clients i.e.: they may only have telephone and email .
My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.
Hello!!! I've a trouble: I have an Access DataBase with two tables: 1--Workers with fields (ID, Name and $/hour) 2--Marks with fields(ID, WorkerID, Datetime of mark)
Well, I need to generate a report with the following structure:
Name Worker / Number of days worked / Total Cost in $ --------------------------------------------------------------------------- Worker 1 / 19 / 13000$ Worker 2 / 20 / 21354$ Worker 3 / 12 / 12000$ ...
Example of Workers Table Data: ID / Name / $/hour ------------------------------------------------------------------------- 21367 / Worker 1 / 21,34$ 54883 / Worker 2 / 54,87$ 87364 / Worker 3 / 5,38$
The condition to increment number of days is: YEAR(date-mark) != YEAR(date-mark) OR MONTH(date-mark) != MONTH(date-mark) OR DAY(date-mark) != DAY(date-mark)
for example: 1) 19/05/1982...time-mark // Worker 1 19/05/1982...time-mark // Worker 1 19/05/1982...time-mark // Worker 1 19/05/1982...time-mark // Worker 1 -----------------------------------> 1 days worked 2) 20/05/1982...time-mark // Worker 1 20/05/1982...time-mark // Worker 1 21/05/1982...time-mark // Worker 1 21/05/1982...time-mark // Worker 1 -----------------------------------> 2 days worked
Do you understand it?
Well, I've tried several SQL sentences, but I can't make the definitive sentence. Can you help me, please?
Note --> each Mark-Datetime represents an instant of time!!!
I need to create a method of calculating -week number,hours worked per week and a leiu hours total.
Explanation
Users enter their worked hours per day. I need to calculate the total hours worked per user per week and if this is greater or less than their contracted hours a record of this figure over a continual time period (or year).
Variables :- Time worked,Holiday hours,Leiu hours taken,Sick leave, Other authorised leave.
I realise that i'm asking on a lot but working for a charity has financial limits.
hi, i want to calculate how long each person has worked at a company.
i am using access 97.
would i be able to enter something in the criteria that would work this out for me?
something like year([START DATE]) - year(Now()) this does not work but i want to find out the number of years a person has worked for the company from the field START DATE .
I have a table in access that has 1 ) persons ID and ) log date/time 3) direction in or out
I need to calculate the amount of time spent by the person in the office. e.g User with ID 1 will come in at 8 in the morning and go out after an hour then come in again and go out. Till he leaves out for the day. I need to find the first time he came in and last time he went out and find the difference for the hours he worked
I am having problems adding details from StudentForm to student table. When I click on the add student button it throws back an error Microsoft Access cant append all the records in the append query.I have attached the error as a pdf and the database for info.
I have different product groups and they can have one of 3 rental rates(5,4,3%) I have written a nested query but I keep getting parenthesis errors, the last one I got told me I must enclose iif functions in parentheses. I know its probably a small thing but I just cant see it. By the way the server is German so ; need to be used instead of ,.
IIf([view_India]![Sub_Group] In (1730;1810;2870);5;iif Left([view_India]![Sub_Group];2) In (01;02;21;24;29) ;5;iif([view_India]![Sub_Group] In (3310;3330;3340;3360);3;4))))
(I'm not sure if this belongs in the macro or query forum)
I have a macro that opens 15 queries and ends with opening a report.
The final query asks the user to type in beginning date, then an end date. Now when there's a data mismatch (i.e., he spells feburary), the macro halts. No harm no foul, they can just click the command button again, but I'd rather not.
What I'd like to do is place a msgbox (i.e., "Check Your Spelling") and then reroute them back to the query.
I'm using Access 2007 and have a query in which I have a Where clause that includes a TempVars variable. Here is the SQL:
Code:
SELECT tbl_CostCenter.*, tbl_OpEx.*, qry_OpExByCC.* FROM (tbl_CostCenter INNER JOIN tbl_OpEx ON tbl_CostCenter.[Cost Center] = tbl_OpEx.[Cost Center]) INNER JOIN qry_OpExByCC ON tbl_CostCenter.[Cost Center] = qry_OpExByCC.[Cost Center] WHERE tbl_OpEx.[Base Year]=TempVars!CurrentBaseYear ORDER BY tbl_CostCenter.[Cost Center];
I get runtime error 3070 stating that "The Microsoft Access database engine does not recognize 'TempVars!CurrentBaseYear' as a valid field name or expression." When I run the query without the Where clause, it runs fine, except it includes all years, not just the one I need.
However, when I use the immediate window to check the value of TempVars!CurrentBaseYear, I get a result of 2012 which is what I expected. So how can it not be recognized in the query? I thought that one of the perks of using a TempVars variable is that it can be used in queries.
I am creating a database to analyze prices. I have 2 tables. One table has a bunch of dates, product names and the prices. There are many of the same dates and many of the same products. I.E. a product could get priced one day and than have a different price the next day.The other table has a bunch of information and dates and product names also. The goal is to add a week to the dates in this table and use that date and product name to look up the corresponding price for that date and product and add it to the record.
I am trying to use Dlookup but evey record is then filled with the same price. I am using this Expr1: DLookUp("Price","Qry_Historic_Price","[Date]=#[End Period]# And primary_id ='[primary_id']")...When I run the query I get a message that says "Syntax error in query expression" than a message box that pops and says "Unknown" with only "ok" to click.
Hello my beautiful worldwide friends :D I am trying to calculate employee Overtime hours from their recorded TimeIn and TimeOut if over 7.5 hours. So anyday they work past 7.5 hours should be calculated and totalled at the end of the week. And i can't seem to figure it out, maybe my maths is bad? Maybe it's my query? The section of my report? What could i be doing wrong here, i have spent hours and im getting no where.
I have attached my report. Can you help me figure out why my daily total shows but the weekly total is not showing?
My subreport provides appointment stats for every month and is grouped by month in the main report. I manage to include the number of days for each month using:
Access and receive the following message after trying to run a query: Syntax error (comma) in query expression, followed by the formula I wrote on the Query Builder. I use 4 tables to run the query, but only need to trim some stuff from one of them. This table is called BD_lamosa_corregida and have already selected Expression on the Total row in Design View. This is the formula:
The formula is trying to trim the left side of an expression (Part description) which may contain a code number in parenthesis or not, it might also have this parenthesis separated by a space or not.
The following code supposed to let me print all records OR only those where dAreaFK = myCBO currently I get an error message "Object missing".if i remove this: Or Me!cboStatsArea Is Null..from the last line the it works but only if i make selection in combo.
Code: Private Sub cmdPrintOpen_Click() 'Print open defects using R_Open_details Dim i As Integer i = DCount("*", "Q_Open_details", "dAreaFK=cboStatsArea OR cboStatsArea IS Null") 'MsgBox "The count of rows is " & i If i = 0 Then MsgBox "No Records available for print", _ vbOKOnly, "Error" Exit Sub End If DoCmd.OpenReport "R_Open_details", acPreview, , _ "dAreaFK=" & Me!cboStatsArea Or Me!cboStatsArea Is Null End Sub
I have a database that is used to create a data file for a customer. The database has two linked tables that are linked to tables that are both in another database (but within the same database as each other). Their structure is identical.I have a union query set up to combine both linked tables.
I am using a Macro to export that query but after running for a short while I get the error "The query cannot be completed. Either the size of the query set....."Does Access have a limitation on union query size? Combined, the tables are a lot of data but I'm confused as both the tables I'm combining are in the same database.
I cannot sort below query in descending order by absolute value. If I do not use sort, all works fine but as soon as I try to sort by absolute value I get message to 'Enter Parameter Value'. I tried to replace Abs([Variance (W2 - W1)]) with filed name AbsoluteValue and still the same result ;(
Code:
SELECT [Query Union].[Stock Code] AS SKU, [Query Union].[Pallet No] AS [Pallet No], [Query Union].[Batch No#] AS Batch, IIf(IsNull([qW1 SOH].[Physical stock]),0,CDbl([qW1 SOH].[Physical stock])) AS [W1 Qty], IIf(IsNull([W2 SOH].[Good Stock]),0,CDbl([W2 SOH].[Good Stock])) AS [W2 Qty], [W2 Qty]-[W1 Qty] AS [Variance (W2 - W1)], Abs([Variance (W2 - W1)]) AS AbsoluteValue
Before I jump in and start creating a way to record times worked I thought it best to ask for advice.
The db is created and the last part is to capture by member: Basically I have the following fields:
MemberID Job Type Date of Job Time Started Time Finished
From the data captured I need to create a report to show month and ytd hours worked.
It would be easy to ask the user to calculate the hours worked and input the number of hours, however I would like to capture start and finish times.
Can anyone give me any pointers on the best field types and field formats for capturing times which would then make it easier to work out hours worked.
I am building an access database for my college project and I essentially have a quotation form that when I click a button 'Convert to Invoice' it creates a new record in the invoice table and then creates new records in the invoice details table which match the quotation details table. This is working as it should but for only the first 2 customers in my customer table?
On the quote form I have a combo box which is linked to the customer table and updates the quote table based on the selection. If I select customer 1 or 2 and click 'convert to invoice' it works and opens an invoice form based on the inserted data however if I select any other customer it returns an error that the record wasn't added to the table due to key violations?
As far as I can tell I am not trying to update the primary keys in the Invoice Table or the Invoice Details Tables.
