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.
View Replies
ADVERTISEMENT
Jun 26, 2005
This is my code for the insert into command.
I can't seem to figure whats wrong.
DoCmd.SetWarnings False
If Not IsNull(Text96) Then
DoCmd.RunSQL "INSERT INTO walkers (Walker, Firstname, Lastname, Date, Visits) VALUES('" & Text96 & "', '" & Firstname & "', '" & Lastname & "', # & Date & #, " & Text98 & ");"
Text96.Value = Empty
Text98.Value = Empty
End If
I believe it has to with the date, if I take the date out it works...
thanks for any help...
View 5 Replies
View Related
Mar 16, 2005
Good day,
I was wondering if I can get some help with my sql statement, b/c I am a run-time error. I have attached my db but the statement is as follows:
SqlStr = "INSERT INTO tblpcomaward (recempid, reclname, recfname, recdate, givempid, givelname, givefname, desc, value, rebill, comments) Values" _
& "('" & Forms!frmpcom!txtempid & "', '" & Forms!frmpcom!txtlastname & "','" & Forms!frmpcom!txtfirstname & "', #" & Forms!frmpcom!txtdategiven & "#," _
& "'" & Forms!frmpcom!txtgiverempid & "', '" & Forms!frmpcom!txtgivelname & "', '" & Forms!frmpcom!txtgivefname & "', '" & Forms!frmpcom!cbodesc & "', '" & Forms!frmpcom!txtvalue & "', '" & Forms!frmpcom!cborebill & "', '" & Forms!frmpcom!txtcomments & "')"
Thanks for any help.
View 4 Replies
View Related
Mar 21, 2005
Good day,
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:
SqlStr = "INSERT INTO tblec (cbo1c, cbo2c, cbo3c, cbo4c, cbo5c, cbo6c, cbo7c, cbo8c, cbo9c, cbo10c, cbo11c, cbo12c, cbo13c, cbo14c, cbo15c, cbo16c cbo17c, cbo18c, cbo19c, submdate, ectl) Values" _
& "('" & Forms!frmenrollcenter!cbo1c & "', '" & Forms!frmenrollcenter!cbo2c & "','" & Forms!frmenrollcenter!cbo3c & "', '" & Forms!frmenrollcenter!cbo4c & "'," _
& "'" & Forms!frmenrollcenter!cbo5c & "', '" & Forms!frmenrollcenter!cbo6c & "', '" & Forms!frmenrollcenter!cbo7c & "', '" & Forms!frmenrollcenter!cbo8c & "'," _
& "'" & Forms!frmenrollcenter!cbo9c & "', '" & Forms!frmenrollcenter!cbo10c & "', '" & Forms!frmenrollcenter!cbo11c & "', '" & Forms!frmenrollcenter!cbo12c & "'," _
& "'" & Forms!frmenrollcenter!cbo13c & "', '" & Forms!frmenrollcenter!cbo14c & "', '" & Forms!frmenrollcenter!cbo15c & "', '" & Forms!frmenrollcenter!cbo16c & "', '" & Forms!frmenrollcenter!cbo17c & "', '" & Forms!frmenrollcenter!cbo18c & "', '" & Forms!frmenrollcenter!cbo19c & "', #" & Forms!frmenrollcenter!txtsubmdate & "#, '" & Forms!frmenrollcenter!txtectl & "')"
View 4 Replies
View Related
Feb 12, 2014
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.
View 2 Replies
View Related
Sep 19, 2013
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.
View 12 Replies
View Related
Jul 5, 2013
I have been tasked with importing data from a clocking on system to analyse worked hours.
There is no option to amend the format of the export which is not great
surnameforenameField2Field3xxx
xxx
11/06/201319.18
xxx
xxx
11/06/20135.37+
xxx
xxx
12/06/20135.41+
xxx
xxx
12/06/201319.21
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
vb script with dlookup for every record ?
View 10 Replies
View Related
Apr 14, 2014
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.
View 1 Replies
View Related
Aug 12, 2014
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.
View 9 Replies
View Related
Jun 3, 2006
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$
Example of Marks Table Data:
ID / IDWorker / DateTime-Mark
-------------------------------------------------------------------------
6754-54 / 21267 / 19/05/2006 8:05:32
4327-12 / 54883 / 19/05/2006 8:07:12
7645-98 / 87364 / 19/05/2006 8:13:54
5432-99 / 21267 / 19/05/2006 14:05:32
7584-23 / 54883 / 19/05/2006 14:07:12
9485-23 / 87364 / 19/05/2006 14:13:54
... / 21267 / 19/05/2006 16:05:32
... / 54883 / 19/05/2006 16:07:12
... / 87364 / 19/05/2006 16:13:54
... / 21267 / 19/05/2006 20:05:32
... / 54883 / 19/05/2006 20:07:12
... / 87364 / 19/05/2006 20:13:54
... / 21267 / 20/05/2006 8:05:32
... / 54883 / 20/05/2006 8:07:12
... / 87364 / 20/05/2006 8:13:54
... / 21267 / 20/05/2006 14:05:32
... / 54883 / 20/05/2006 14:07:12
... / 87364 / 20/05/2006 14:13:54
... / 21267 / 20/05/2006 16:05:32
... / 54883 / 20/05/2006 16:07:12
... / 87364 / 20/05/2006 16:13:54
... / 21267 / 20/05/2006 20:05:32
... / 54883 / 20/05/2006 20:07:12
... / 87364 / 20/05/2006 20:13:54
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!!!
View 1 Replies
View Related
Jan 22, 2006
Hi Guys
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.
Attached the basic database-so far
Thanks in advance for your help
Row
View 4 Replies
View Related
Jun 3, 2005
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 .
thanks for you help.
View 8 Replies
View Related
Sep 11, 2014
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
The data is in Device logs table.
View 3 Replies
View Related
Apr 27, 2014
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.
View 2 Replies
View Related
Apr 15, 2015
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))))
View 2 Replies
View Related
Aug 19, 2013
(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.
View 2 Replies
View Related
May 16, 2013
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.
View 3 Replies
View Related
Apr 15, 2014
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.
View 4 Replies
View Related
Oct 26, 2006
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?
View 9 Replies
View Related
Oct 6, 2013
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:
=DateSerial([YearNo],[MonthNo]+1,1)-DateSerial([YearNo],[MonthNo],1)
in a text box, and it works fine.
But I don't know the syntax to get these numbers:
the number of weekend days worked in that month, and the number of weekend day not worked in that month My field is call App_date.
Tried this but does not work - the numbers it returns are incorrect (in every month): =Count(Weekday([App_Date]))
The subreport's is based on a select query with only the Appointment table. It only has four "fields" - that work fine.
They are: the appointment date and its date parts - defined in numbers.
App_date
MonthNo: DatePart("m",[App_Date])
YearNo: DatePart("yyyy",[App_Date])
DayValue: Weekday([App_Date],2)
how to get my weekend work and not work total day count?
View 13 Replies
View Related
Jul 31, 2013
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:
parte: Trim(IIf(IIf(IsError(InStr(1,[Parte],"(",1)),"",InStr(1,[Parte],"(",1))="",[Parte],Left([Parte],IIf(IsError(InStr(1,[Parte],"(",1)),"",InStr(1,[Parte],"(",1))-1)))
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.
View 1 Replies
View Related
Feb 6, 2015
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
View 6 Replies
View Related
Jul 7, 2014
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.
View 14 Replies
View Related
Sep 16, 2014
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
[code]....
View 4 Replies
View Related
Jun 18, 2006
Hi, I am creating a time keeping db.
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.
Thanks in advance
Darrell.....
View 1 Replies
View Related
Oct 23, 2014
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.
View 1 Replies
View Related
