Queries :: Formatting Date On Transfer To Temp Table
May 11, 2015
I am trying to export into a temp table (all text fields because it will be going into a text export later) and I'm having difficulty adding 0:00:00 onto the value of "ApptdateLast" for the update...
INSERT INTO cbt_Export_Temp ( TransactionType, ID, ApptdateLast )
SELECT "Add" AS TransactionType, "BC" & [TransId] AS ID, dbo_Info.ApptdateLast & " 0:00:00" AS ApptdateLast
FROM dbo_examInfo
I am trying to create a table from a form. The form has several fields but I need to take the value from 4 separate combo boxes ([cr] +[br] +[tr] and add them, then add the value from one more combo box [inc] to be my beginning value in a table.
I then need to add the last value [inc] to the total and that become the next line in the table. I would the like to add this value [inc] an infinite number of times until it reaches a max number.
The scenario would be something like this cr=3 br=2 tr=3 inc=1.5
So the first total would be 9.5. Then every row after that would be plus 1.5 11 12.5 14 15.5 17 and so on.
This would be a temp table that I would run a query on to let an operator know lengths they can choose from in a combo box. I don't know if this is even possible.
I have a query in my database called "Open Date" which is a date formatted field when imported as DD/MM/YYYY. The criteria is set to:
>=#06/04/2014# And <=#05/04/2015#
The query works just fine. My customer has come back and asked for the way the date is displayed to be in the YYYY-MM-DD format (i know it's not ideal but thats what they want). When i've done that using the following
Hoping someone can help me with this DELETE query. I have a Main table that's being updated by a Temp table that's an exact copy of the Main table but with a subset of records.
1) Insert records from Temp table NOT found in the Main table - this query I have worked out below - not tested, but the results look correct.
Need Help Here... 2) Delete Records from the Main that are not found in Temp table with an exception...only DELETE records where certain key fields are matching. i.e. If S.CAD_NAME, lngStoreNumber are a match to what's in the Main table. While Temp table: lngStoreNumber - CAD_NAME - lngcomponentSerial 1 - "CHK" - a 1 - "STK" - a 2 - "CHK" - a
Main table lngStoreNumber - CAD_NAME - lngcomponentSerial 1 - "CHK" - a - LEAVE (EXISTS In Both Tables) 1 - "CHK" - b - DELETE (lngStoreNumber & CAD_NAME composite Found /lngcomponentSerial NOT Found in Temp) 1 - "STK" - a - LEAVE (EXISTS In Both Tables) 1 - "RMM" - a - LEAVE (lngStoreNumber & CAD_NAME NOT Found in Temp) 2 - "STK" - a - LEAVE (lngStoreNumber & CAD_NAME NOT Found in Temp) 2 - "CHK" - b - DELETE (lngStoreNumber & CAD_NAME composite Found/lngcomponentSerial NOT Found in Temp) 3 - "CHK" - a - LEAVE (lngStoreNumber = 3 Not in Temp table Subset)
Rule: Only delete the records for a particular CAD_NAME and lngStoreNumber from the Main table leaving all other CAD_NAME/lngStoreNumbers.
I'm running these updates in batches of lngStoreNumber. So the Temp table will only contain subsets of what's to be deleted from the Main table thus the need to link on the key fields only NOT to delete a Subset of lngStoreNumber/CAD_NAME. I think I've tried every possible query that doesn't work.
Here is query #1 to insert records missing from the Main table that exist in the Temp table. I think what I need is a variation of this??? SELECT D.* FROM Main AS S RIGHT JOIN Temp AS D ON (S.CAD_NAME=D.CAD_NAME) AND (S.lngcomponentSerial=D.lngcomponentSerial) AND (S.lngStoreNumber=D.lngStoreNumber) WHERE S.lngcomponentSerial is null AND S.CAD_NAME is null AND S.lngStoreNumber is null;
I'm trying to copy the structure of a table to make a temp table. I'm using CopyObject (which also copies the data). So when I delete the data from the temp table, it also deletes data from the source table. Is the data linked? It should just be deleted from the temp table. Below is the beginning of the code. I've stepped through, and at the last step shown, the data in the source table deletes.
Code: Dim strFile As String Dim temp As String Dim tbl As String Dim db As DAO.Database
Is it possible to have a form that will filter my data to what i need, and place in a temperary table and then be able to display it in a report??? So I already have a filtered form, however I would like to be able to creat reports on the fly. So I will not need all my fields from my table everytime I filter. So If I create a report I can choose the fields that I need, however this is done will all records in my table and I would like to only use the data I have filters. What way would be best to accomplish this?? I already have the ability to load the report wizard by a command button. I just dont know the best way to use my filtered data with it. Any help would be great. thanks
Hellooo Hi gurus I have a smallish problem I have two tables that I need to join togther - normally no problem
I have one table with 1 event on it- easy however if I have more than 1 event on it I have another table that opens up and I add multiple evnets to it main id number 12345 with 1 event on it and 23456 may have 20 events on it on my other table (with multiples on it) i have this autonumbered (this is great unique id - now i need to make a temp table to include boths sets of data in one file
1 event table - easy multi event tabel I want it to get the main refernce fromt he first table (using 23456 as the example) and have it list these in the table - main refer 23456-multi table unique ref number 23456-1 , 23456-2
so my table should have 12345 23456-1 23456-2 23456-etc
I have been using append qry to make tables - any pointers would be great
I'm trying to create a simple 1 field temp table to populate a combo box with the name of the current user and the word "Company." However, after the user closes the form (or as soon as the Temp table is no longer necessary) I would like to delete the table. I can create the table, the fields, add the data, and populate the combo box just fine, but I'm having problems deleting the table after I'm done. I keep getting the error:
Run-Time Error 3211: The database engine could not lock table 'Temp' because it is already in use by another person or process.
here's my code:
Code:Option Compare DatabaseDim dbRoofing As DAO.Database Private Sub Form_Close()dbRoofing.TableDefs.Delete "Temp" 'where i get caught when i close the formEnd Sub Private Sub Form_Open(Cancel As Integer)Set dbRoofing = CurrentDb Dim tblTemp As TableDefDim rcdTemp As DAO.Recordset Set tblTemp = dbRoofing.CreateTableDef("Temp")tblTemp.Fields.Append tblTemp.CreateField("Owner", dbText)dbRoofing.TableDefs.Append tblTemp Set rcdTemp = dbRoofing.OpenRecordset("Temp", dbOpenDynaset)With rcdTemp.AddNew!Owner = CurrentUser.Update.AddNew!Owner = "Company".Update.CloseEnd WithOwner.RowSource = "SELECT Temp.Owner FROM Temp"End Sub
I have two tables that are formatted identically....
Table1 = MasterTable Table2 = TempTable
I am taking a copy of one of the entry from the master table and Copying it to the temp table.I then open a form on the TempTable that enables the user to modify the content without affecting the information in the MasterTable.On Completion and Save I wish to take this modified content and update the MasterTable Using the Field "ID"
the tables for example look like this
ID NAME AGE DATE1 DATE2
I am guessing I need to use a
Code: DoCmd.RunSQL UPDATE "MasterTable" WHERE "TempTable"
Type of command but not to sure on the correct syntax and as everything is the same the use of wildcards for all fields
I have a form with around 10 checkboxes which serve as a filter option...now, when I hit my cmdFilter button it works well with a simple MsgBox !Ime showing all the filtered names...now, I want to put the results into a table tblTemp so that I could show the results in my subform. I've tried with making a sql string something like "INSERT INTO tblTemp..." but it's still empty.:confused: Since this table will serve as a one time data, I will need to delete all records when I hit the Filter button next time...so, how do I send my recordset data into my table.
I'd like to copy data from an excel spreadsheet and paste it into a temp table in Access and then hit a button which will run an append query and append all the data in the temp table to a permenant table.ow to create a temp table?
All using access 2010. I have a multiuser database that I feel would benefit from splitting into a backend with multiple user frontends. My problem is that there are tables from make table queries processed every two weeks that all users need access to. As far as I know; you can not put a table in the backend that you will delete and remake or a temporary table and link to it in the front end. Is there any other way I would be able to split the database and have temporary tables linked from backend to frontend that I am not aware of? Is there another way to creating temporary tables and tables from make queries? This database has 9 users and counting and really needs to be split. r
I have a form where the user selects check boxes to choose what fields to include in a report. Because of the massive amount of data, I need to send it to a temp table and then on to Excel and not use a query.
I have this line of code, for the insert into temptables for other forms that don't require the yes/no box and it works very well, but I can't figure out how to do it with these yes/no conditions.
This is the execute line that inserts into the TempTable for the other forms:
How would I write: If Check1=True Then INSERT INTO TempPicktbl Field1 If Check 2=True Then INSERT INTO TempPicktbl Field2 If Check 3=True Then INSERT INTO TempPicktbl Field 3 etc.
I'm pretty sure it's the INSERT level where I need to put this code.
I am attempting to automate the import of a .csv file from a given directory and copy and append it to another table in a different layout. Below is my code. The error that i'm getting is it can't fine the copyobject in the database...
Option Compare Database Option Explicit Function DoImportandAppend() Dim strPathFile As String Dim strFile As String Dim strPath As String
I have some code that creates a table based off another table. This code works perfect but Im trying to alter my ID field and change it to an auto increment. How can I fix my code so that it alters my ID field?
Code: Private Sub Command0_Click() 'OBJECTIVE: to build a table by extracting some fields from a main database Dim rst As Recordset Dim strSQL As String Dim strSQL2 As String Dim intCount As Integer
I am trying to write a VBA Module that will look in a directory for Excel files with a specific file prefix, and then import specified cells into a temp table. Once it processes the first file, it copies the file to another folder, and then kills the file. However, when I run my current code, it goes through the loop the first time, but fails on the second attempt, because it is still looking for the first file it came across even though it has been moved. The code is pasted below:
Sub Count()
Dim xlwrksht As Excel.Worksheet Dim xlWrkBk As Excel.Workbook Dim nIndex As Integer Dim strMvPath As String Dim mvPath As String Dim strFile As String
I am attempting to insert a record with selected data into a temp table and I am getting "Run-time error '3075': Syntax error in (comma)...". Here is the code:
Code: Private Sub XferDataToTempTable() Dim db As Database Dim strSQL As String Set db = CurrentDb
i have 2 tables.. 1 with bookings and 1 with a BonusPeriod range.
in the Bookings table there is a field called [ServiceDate]
in the bonus table i have a field called [Period] which is text i also have 2 dates, [StartDate] And [EndDate] i need to Dlookup the Bonus table to return the value of the period table based on where the [ServiceDate] falls.
i dont want to use VBA (i really need it to work within a query that inserts it's data into another table)
i've tried everything but had no joy
Expr1: DLookUp("[Period]","BonusPeriods","[ServiceDate]" Between "#[StartDate]#" And "#[EndDate]#")
Expr1: DLookUp("[Period]","BonusPeriods","[Staff_BookingsAndQuotes_Master].[ServiceDate]" Between "#[StartDate]#" And "#[EndDate]#")
Expr1: DLookUp("[Period]","BonusPeriods","(FormatDateTime([ServiceDate]),"yyyy-mm-dd") Between (FormatDateTime([BonusPeriods]![StartDate]),"yyyy-mm-dd") and (FormatDateTime([BonusPeriods]![EndDate],"yyyy-mm-dd"))))
I need to extract a specific number of records into a table using a MakeTable or Append command using a temp variable, e.g. TempK&SA. Previously on the forum I was shown how code could be added to the OnOpen function to use a temp variable to select a specific number of records to report. ACCESS does not have the OnOpen function in the design view of a query like in the report. It does allow a SELECT TOP but only with fixed variables or percents (e.g. 25 in the code below).
The beginning code for the make table query (where 25 is the number of records added) is:
INSERT INTO [Output] ( RndNo, PointBiserial, BloomsTax, DateRevised, Exam1, Status, Exam2, Exam3, Exam4, [NCCPAKnowledge&Skills] ) SELECT TOP 25 TestBank.RndNo, TestBank.PointBiserial, TestBank.BloomsTax, TestBank.DateRevised, TestBank.Exam1, TestBank.Status, TestBank.Exam2, TestBank.Exam3, TestBank.Exam4, TestBank.[NCCPAKnowledge&Skills], * FROM TestBank WHERE (((TestBank.PointBiserial) Is Null Or (TestBank.PointBiserial) Between [TempVars]![TempPointBiserialLow] And .....
how to modify the code to allow a temp variable to determine the number of records to append to another table would be gratefully received. (This process then is repeated for a total of 7 append tables with different temp variables.)
I'm trying to create a right-click event on a listbox that will copy selected listbox item(s) to a temp table. So far, I've got this code to acknowledge the right click:
Code: Private Sub List0_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single) If Button = acRightButton Then MsgBox "You pressed the right button." End If End Sub
Problem is the selected item on the list box doesn't move until after the mouse down event so whatever code I would run would involve the wrong record(s).
I'm using Access 2000 and 2003. How to get the the correct record selected on mouse down, or point me to a working example of right-click functionality on a listbox.
I'm creating a training database. Individuals carry out their training and I enter their training regime in three fields - year 1, year 2 and year 3. For example, their year 1 training is 1 august 11, year 2 is 1 august 12 and year 3 is 1 august 13. I can then run reports on who is due over the next 3 years and when.
However, I'd like to archive their past training dates. How to do this.
Say individual had following dates in their record as due dates:
Year 1 - 1 august 11 Year 2 - 1 august 12 Year 3 - 1 august 13
They carry out their year 1 training so that field would now have to change to 1 august 2014 (this training just runs and runs). Is there a way to add a command button that when clicked archives the 1 august 11 date already there to year 1 archive field and clears the year 1 field so that new date can be added. I've tried below but probably not right.
We've got a database that was on an old computer (Windows 98), and we wanted to transfer this database to our new computers (Windows XP). The database transfers over fine, but there seems to be a problem.
When we try to run a report, it doesn't semem to work properly. We are a training agency and the report in question is designed to show the courses booked in for each tutor between two set dates. The dates are typed into a form (start date and end date), and the tutor can also be selected from this form, and then a report is generated.
Without dates being inputted, all the courses are shown (so the report is pages and pages long). This is working ok. However, when dates are inputted the report stops working and error messages appear on the report. Hence it seems to be a problem with the dates, but I can't understand what since all the boxes are exactly the same as the version on the other computer, and when transferred back to the other computer it also works fine.
Any help with what could be going wrong here would be GREATLY appreciated.
I have filed that has been uploaded from excel file in this format 20110307 , but I need this filed named postdate in date format such as 03/07/2011 . How to transfer text filed into date .I use Format([PostDate],'mm/dd/yy') in update query , but the data completely disappeared off the field.
I am creating an online post, similar to a guestbook. When a user submits an entry, I am storing the date and time in a column titled "Timestamp", which is formatted 8/4/2005 9:16:58 ("General Date"). I am running the table through a query which is then posted on the web.
What I would like to do is this: I would like to use Timestamp to display the date that the entry was submitted, but not the time (basically, "Short Date"). Can I exclude the time using a query without changing the actual data stored in each record of the table? I understand that the time stored is completely different than the time displayed, even in the table. How can I do this?