I have an ID field that is text. Here are some example ID's. The number is generated automatically and the letter is added by the user.
A10565
52073
C20633
RMA18941
I need to start numbering new id's automatically. I don't want to repeat numbers so I need to know which numbers have been used. So I am trying to remove the letters preceeding the numbers. However I can't seem to get rid of the records with more than 1 letter. Here is the what i've tried in the query -
test2: Left([ID],3)
newnum: IIf([test2]="A" Or [test2]="C" Or [test2]="F" Or [test2]="H" Or [test2]="R" Or IIf([test2]="RMA",Mid([ID],4),[ID]),Mid([ID],2),[ID])
after that is run this is what i get -
10565
52073
20633
MA18941
They are fine except the last one - MA18941 needs to be 18941.
I am using an Access 2010 DB to keep track of a schedule. Essentially, at least one person needs to be signed up to work for every hour of every day in a week.
Tables: Days with 7 records Hours with 24 records Workers with as many people that sign up to work the different hours Schedule signifying the worker, day, and hour which are signed up.
As of now i have a query that relates these results and gives me a line detailing the worker/time information for the slots that are signed up for.What I'm TRYING to do is to create a query that gives me BLANK worker info when there is no one signed up for a particular hour.Currently my Schedule table has the following:
What I'm looking to do is have this table matched up with another table (or query) that provides every combination of day/hour. When an day/hour combination is skipped, the query will be able to "fill in the blank" with a row. Like this:
This is an ongoing problem I have had for 4 weeks now.
I have made a a system thats acts like a clock In/clock out Out system.
the structure is somthing like this ID Username tblDailyLog TimeIn MorningBreakOut MorningBreakOut LunchOut LunchIn AfternoonOut AfternoonIn TimeOut
All fields apart from ID (autonumber) and username (String*255) are Date field (there are a few others like DateOfTimesheet etc but they arnt important here)
When a user arrives in the morning they make a record which they use for the day
They then have a form with a whole bunch of buttons which simply updates the correct field. For example they click the "Sign in for the Day" button and it updates the correct field with the current time.
Everything was going fine until people noticed that every now and again a sign in time dissapeared.
I have hacked myself to death trying to solve this problem but still the updates go Astray.
Now each time a time is updated the process goes somthing like this
1. the user opens their timesheet for the day (the RS is SNAPSHOT and no locks)
2. User Hits a sign in/out button 3. The record source is changed to "" and all buttons hidden (to ensure the record isnt locked and to make sure you dont do two things at once) 3. The table is updated with the new time (using some dynamic SQL) 4. The table is repeatadly checked using a DO loop to make sure the the correct time went in. 5. when the returned time value of the field matches the varaible used to update it, the form is returned to normal and the user carries on his/her merry way (if it never matches the screen should crash but this never happens). 6. A New record is added to another table called "tblbugfixinglog" which records which field was updated and when. This is so that I have two records in two different ways (figured if one went astray I could pull it back off the other) 7. Another new record is added to yet another table called tblSQLRecord, which simply logs all .RUNSQL statements that are executed.
I thought that the two extra tables (and the check that the record had been updated) would help me track down where the records are going missing, but this isnt the case.
Now it appears that some records arnt being added to tblBugFixingLog and to tblSQLRecord either and some of these tables are getting quite a few #ERROR's in them..
None of the tables are related to any other and i've no idea how #ERROR lines are appearing in a table that has 1 function... to recieve new records ... no editing, no viewing, no deleting.
Does anyone have any idea how these updates/inserts can go missing or create #ERRORs. I've built plenty of Databases in my time and have never come across this. __________________________________________________ ______________
This is the function I use to add a record to tblBugfixingLog and tblSQLRecord
Private Sub AddBugLog(ByVal TimesheetNumber As Long, ByVal FieldUpdating As String, ByVal NewFieldValue) Dim TempSQL As String TempSQL = "INSERT INTO tblBugFixingLog (TimeAndDateOfEntrySERVER,TimeAndDateOfEntryPC,Fie ldUpdated,NewEntry,UserID,TimesheetNumber,Computer AssetNo) VALUES (" & _ "#" & Format(ServerGetTime(Environ$("LOGONSERVER"))) & "#," & _ "#" & Now & "#," & _ "'" & FieldUpdating & "'," & _ "'" & NewFieldValue & "'," & _ "'" & GetNTUser & "'," & _ "'" & TimesheetNumber & "'," & _ "'" & fOSMachineName & "')" ' MsgBox TempSQL DoCmd.RunSQL "INSERT INTO tblSQLRecord (Username,DateAndTime,Screen,TheSQL) VALUES('" & LoginInfo.sUsername & "','" & CStr(Now) & "','Add Bug Log function','" & CleanData(TempSQL) & "')", False 'CleanData is a function that removes ' and " from the SQL string so i can easily add the SQL string into the table DoCmd.RunSQL TempSQL, False End Sub
Public Function CleanData(ByVal DataToClean As String) Dim TempData As String Dim i As Integer TempData = "" For i = 1 To Len(DataToClean) Select Case Mid(DataToClean, i, 1) Case "'" TempData = TempData & "`" Case """" TempData = TempData & "`" Case Else TempData = TempData & Mid(DataToClean, i, 1) End Select Next i CleanData = TempData End Function
There are two tables in my database. A and B. Each table has the same criteria: Name, Number, and Date.
I have created a formula in my query which will add the Number fields together when they both have the same name. My problem is that when there is not a row for a particular name on table B, the calculation does not list that row in the results.
I would like for my calculation to insert a zero in the space and add Number.
I have tried the Nz function, but this results in a missing name and date and just the numbers are shown.
How do I get the results to show a row where the name is only on one field and still make sure that it places a zero in its spot and add the zero and the other number together based on the name.
Yes, another of my query troubles. I am running a query that is showing the number of demos booked, number of demos executed, then the percentage of demos executed. I have 2 demos that have 1 or 2 booked, but they have not been executed. They should show up in the query so I can get a percentage for them, but they do not. Here is my SQL:
SELECT [Promo count].PromoNo, [Promo count].[# of Demos], Count(Query6.Status) AS CountOfStatus, IIf([CountOfStatus]=0,0,[CountOfStatus]/[# of Demos]) AS Percentage FROM [Promo count] INNER JOIN Query6 ON [Promo count].PromoNo = Query6.PromoNo WHERE (((Query6.Status)="E")) GROUP BY [Promo count].PromoNo, [Promo count].[# of Demos];
i have a query that contains two tables one contains the member details the other their transactions. the query is to show all members and transactions however if there is no transaction then the member details do not show - -the query only shows members with transactions . the query does have grouping to give totals of the transactions
I have a problem with a query. Just for info, I export some table from SQL to access. The same query in SQL work fine but when I try in Access come out this error:
------------------------------------------------------------------ Syntax error (missing operator) in query expression 'tbl_Style.FABRICID = tbl_Info.Infoid INNER JOIN tbl_Info AS tbl_Info_1 ON tbl_Info.Parent = tbl_Info_1.Infoid'. ------------------------------------------------------------------
The query:
SELECT tbl_Style.STYLECODE, tbl_Info_1.Infodata AS [Fabric Type]
FROM tbl_Style INNER JOIN
tbl_Info ON tbl_Style.FABRICID = tbl_Info.Infoid INNER JOIN
tbl_Info AS tbl_Info_1 ON tbl_Info.Parent = tbl_Info_1.Infoid
i have made a query.. when executed it returns 4 results..when i view the report however (made using the wizard based on that query) only 3 results are displayed..i then add a record to the database... the query returns 5 results.. and again the report only displays 4 results...i believe that the first record entered into the database is missing from the report.. but it is present in the query.. could i have accidentally deleted the first record from the report when i was altering the layout in design view??anyone come across this before?is there a general rule with reports based on queries that only display results with certain criteria or something??
I want to use a column in a query to show where data is missing in other fields.
In excel I have used this statement:
=IF(COUNTA(I5:J5)=2,"","error")
Basically, I have two fields PRICE and WEIGHT. I want a column in the query to show 'Error' (or any kind of flag) when either (or both) of these fields are blank.
I have a problem with a query. Just for info, I export some table from SQL to access. The same quary in SQL work fine but when I try in Access come out this error:
------------------------------------------------------------------ Syntax error (missing operator) in query expression 'tbl_Style.FABRICID = tbl_Info.Infoid INNER JOIN tbl_Info AS tbl_Info_1 ON tbl_Info.Parent = tbl_Info_1.Infoid'. ------------------------------------------------------------------
The query:
SELECT tbl_Style.STYLECODE, tbl_Info_1.Infodata AS [Fabric Type]
FROM tbl_Style INNER JOIN
tbl_Info ON tbl_Style.FABRICID = tbl_Info.Infoid INNER JOIN
tbl_Info AS tbl_Info_1 ON tbl_Info.Parent = tbl_Info_1.Infoid
I have a table for stock with two fields, KEY and DESCRIPTION; a table for manufacturers with two fields, KEY and NAME; and a third table which links to them both with a many-to-one join with three fields, KEY, STOCK-KEY and MFR-KEY. What I am trying to do is write a query that for a given manufacturer (entered via a parameter) shows a single line for all stock records that are NOT linked to it via the third table. I am sure it should be simple but all my attempts fail to exclude stock linked to the manufacturer if it is also linked to another manufacturer. Any ideas?
I have a master table that holds all of my data. The table details what qualifications someone is holding.I would like a query that would enable me to produce a list of people who DO NOT hold a qualification.
I have inherited a database that contains details of staff training data and the tables contain the following:
Personal Information Table: PersonalID Surname Forename EmploymentStatus (this contains either Staff, Operative, or Supervisor)
Training Courses Table: CourseID Course Name Course Description Supervisor (Yes/No) Operative (Yes/No) Staff (Yes/No)
Training Courses Attended Table: RecordID PersonalID CourseID Date
Each of the courses in the Training Courses Table should be attended by one or more of the groups identified in the EmploymentStatus field (ie. Supervisor, Operative, Staff) and the relevant field in the Training Courses Table is flagged eg. Training Courses Table: CourseID, CourseName, Supervisor, Operative, Staff 100, basic safety, Yes, Yes, Yes 101, safety management, Yes, No, No 102, working with ladders, No, Yes, No 103, VDU, No, Yes, Yes
I need to identify which individuals have not attended the courses that they should have been completed (ie. compare courses attended with the list of courses associated with the EmploymentStatus associated with individual staff members, and identify which courses have no attendance dates). How can I structure the query, I can't see how to do this with the existing tables, but I think it should be possible, but my Access expertise is just not good enough to work through this.
I got a problem regarding query work week in database. The database contain data of year 2006 and 2007. When i query about work week, some record is missing.
i wrote the sql statement as SELECT * FROM TBL WHERE FORMAT(MYDATE, 'WW', 1, 2) = 1 AND YEAR(2007);
There is one record missing... which is 31/12/2006 record.
I am working with a normalized database that has MANY tables. Most of these consist of lists of options to select from for the primary table. The primary table is linked to the secondary tables (and those to tertiary tables) by Primary Key ID fields, and the other tables contain additional information.
For Example, the Name list table is related to the Actions table by the SSN field, and the Actions table is related to the PayStatus table by an ID number collected by a lookup field.
When I pull all three tables into a query to display all the information related to a particular individual an his action, there are more records if I remove the PayStatus table from the query. It seems to only pull reports for which a PayStatus has been selected.
How can I get the query to display ALL the records, whether or not the individual has a pay status? Whether or not the individual has one is irrelevant, but I want his name to be displayed, whether or not he has one.
I have an Access MDB which uses a number of queries, reports, forms, and tables, and a module. When I make a new replica of it or try to synchronize it, several of the queries don't get put into the replicas. The data, however, appears to be synchronized perfectly.
I'm wondering if our use of replication is causing the problem. I have the database application running on three computers so different people can add data. The computers aren't networked. What we do to synchronize is copy the file used on one computer onto a CD-R, then copy that back onto the main computer, and then synchronize it there. Then we reverse the copying to put it back on the secondary computer. Am I doing this wrong?
Morning all The problem whihc i have is that, when trying to open the form "Create Timetable", through the switch board.... a message box appears saying "Syntax error (missing opertor) in query exprssion" Any ideas on why it is showing this message box??? Help would be appricated Thank you
After adding a radio type field in the form called Blocco (with yes or no values, working like the existing field "Bozza") I've modified the code adding something to update the db:
The page itself is loaded with no errors but when the form takes action I get this: Syntax error (missing operator) in query expression 'True Articoli.Bozza = False'.
why? I've just used the same code that worked with the field "Bozza" (which is a checkbox field too)
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 created query and all my values come into the table from the query fine.
When i then go and generate the report all of the boxes are there for the data but there is no data in them. For owner and Job Id all info is there but job name, Department and a few others there is no data in the boxes.
I'm trying to create a single, Update Query in Access 2007 that does all of these 3 things:
Update (skipping zero-length values) Append Automatically handle missing Fields without showing any "Enter Parameter Value" prompts.
I've managed to accomplish the 1st & 2nd items on my own, but I'm stumped on the 3rd. In essence, what I'm looking for is a way of telling Access to automatically use a zero-length/null value for any missing Field(s) the Query encounters, so long as it does not involve manually modifying the Table beforehand. Automation is key. And that's where you guys come in.
Attached, is a diagram of the desired results applied to a set of sample Tables. And here's the SQL code for the Update Query adjusted to work with those sample Tables:
Code: UPDATE Table_1 RIGHT JOIN Table_2 ON Table_1.ID = Table_2.ID SET Table_1.ID = NZ([Table_2].[ID],[Table_1].[ID]), Table_1.Company = NZ([Table_2].[Company],[Table_1].[Company]), Table_1.Address = NZ([Table_2].[Address],[Table_1].[Address]), Table_1.Phone = NZ([Table_2].[Phone],[Table_1].[Phone]);
I have an access database with several linked tables (linked to MySQL database) and several local tables. The theory is that if there's ever a connection issue, the device connected to the computer will continue logging data to the local tables. Once a connection is re-established, the linked tables should be updated with all the missing records which appear on the local tables.
I found several possibilities which I outlined below, but I've been asked to investigate whether there's a built in function in access that does this for you and use the other options as a last resort. Does access have any program feature that updates one table with missing data from another table, or will I have to write VBA code to do that? Options I've discovered:
1) Write unmatched query and insert missing data into table.
2) Create a linked table on MySQL that will link to the local tables on access, then compare the records there.The boss isn't happy with those options because he wants to keep the amount of code we add to a minimum. Ultimately, we hope that a program feature that does this is built in to access. If not, I have no problem adding code to do this instead.
I've got an Append Query in an Access 2000 database that is not visible on the Query tab. I know it exists because when I try to create a new query and call it the same name it responds with "The name entered already exists for another object of the same type in this database"
I've tried ticking the System and Hidden objects but to no avail. Somebody please tell me where this query is hiding??
Sub PickRandom() Dim db As DAO.Database Dim tdf As DAO.TableDef Dim fld As DAO.Field Dim rst As DAO.Recordset Dim strSQL As String Dim strTableName As String
' 1: Create a new temporary table containing the required fields strSQL = "SELECT tblWoodrow Odom.ReviewTopic,tblWoodrow Odom.TeamMember1, tblWoodrow Odom.TeamMember2, tblWoodrow Odom.TeamMember3, tblWoodrow Odom.TeamMember 4" & _ "INTO tblTemp " & _ "FROM tblWoodrow Odom;" DoCmd.SetWarnings False DoCmd.RunSQL strSQL (HERE I RECEIVE AN ERROR MESSAGE. WHAT I NEED TO DO HERE SO IT CAN RUN PROPERLY? ) DO I NEED TO DO A QUERY STRQSQL STATEMENT?) DoCmd.SetWarnings True
' 2: Add a new field to the new table Set db = CurrentDb() Set tdf = db.TableDefs("tblTemp") Set fld = tdf.CreateField("RandomNumber", dbSingle) tdf.Fields.Append fld
' 3: Place a random number in the new field for each record Set rst = db.OpenRecordset("tblTemp", dbOpenTable) rst.MoveFirst Do Randomize rst.Edit rst![RandomNumber] = Rnd() rst.Update rst.MoveNext Loop Until rst.EOF rst.Close Set rst = Nothing
' 4: Sort the data by the random number and move the top 25 into a new table strTableName = "tblRandom_" & Format(Date, "ddmmmyyyy") strSQL = "SELECT TOP 25 tblWoodrow Odom.ReviewTopic,tblWoodrow Odom.TeamMember1, tblWoodrow Odom.TeamMember2, tblWoodrow Odom.TeamMember3, tblWoodrow Odom.TeamMember 4" & _ "INTO " & strTableName & " " & _ "FROM tblTemp " & _ "ORDER BY tblTemp.RandomNumber;" DoCmd.SetWarnings False DoCmd.RunSQL strSQL DoCmd.SetWarnings True
' 5: Delete the temporary table db.TableDefs.Delete ("tblTemp") End Sub
