simple query that shows results with 5 columns, one of which is a date based field. I set up a macro which allows my user to click a button which creates an excel sheet. The dates do not stay correct when exported to excel. What is wrong here?
View Repliesi have a small table, about 14,000 records. currently all the dates in the table are YYYY-MM-DD 00:00:00. so 2007-2-28 00:00:00
I built a query and set the format to MM/DD/YYYY when I run the query the date showed correctly 02/28/2007 (no time stamp). However when I did the export it exported to the TXT file as 2007-2-28 00:00:00 (notice the months is only 1 character and it added the time stamp and it exported as the table format, not the query format)
I need to export this to a tab delimited file to send to another person. They are specifying MM/DD/YYYY (no time extension) and two characters for month and day regardless of day, i.e 02 not 2 etc.
I looked at access help, and have done a quick search, and I can not figure out how to do this.
Any help would be appreciated
I have a complex query that I am exporting to a tab delimited text file.
The query has three date fields, and a time field. The query export results in both the date and time portions of the value being shown instead of one or the other.
I have tried using the format function to force the dates to only show the date portion, and the time field to only show the time portion. So far, without luck.
I would greatly appreciate help from someone who can spot what I am doing wrong. The sql for my query is below.
SELECT tblSiteVisits.RunID AS TripID, Format([tblSampleRuns]![RunDate],"mm/dd/yyyy") AS [Trip Start Date], Format([tblSampleRuns]![RunDate],"mm/dd/yyyy") AS [Trip End Date], [RunName] & " " & [tblSampleRuns]![RunDate] AS [Trip Name], "LUMMI001" AS [Project ID], tblSites.SiteNumber AS [Station ID], STORET_SiteVisitNum.VisitNum AS [Station Visit Number], [tblSiteVisits]![SiteVisitID] & "-" & [tblParameters]![Matrix] AS [Activity ID], [tblParameters]![Matrix] AS Medium, Storet_ActivityCategories.Activity_Type AS [Activity Type], Storet_ActivityCategories.[Activity Category], tblSampleData.QCIndicator AS [QC Indicator], STORET_Replicate.RepNo AS [Replicate Number], Format([tblSampleRuns]![RunDate],"mm/dd/yyyy") AS [Activity Start Date], "" & Format([VisitTime],"hh:nn:ss") & "" AS [Activity Start Time], STORET_TimeZoneOutput.TimeDatum AS [Activity Start Time Zone], "" AS [Depth to Activity], "" AS [Depth to Activity Units], tblSiteVisits.SiteComments AS [Activity Comments], Storet_MethodsProcedures.ProcedureID, IIf([LabParam]=-1,"WSWB","") AS [Gear ID], Storet_Characteristics.CharacteristicName, IIf(IsNull([PracticalDetectionLimit]),Str([Value]),IIf([Value]<[PracticalDetectionLimit],"Present < QL",Str([Value]))) AS [Result Value], tblUnits.UnitName AS [Result Value Units], Storet_Fractions.Fraction, "" AS [Result Comment], "" AS Personnel, IIf([tblParameters]![LabParam]=-1,[LabID],"") AS [Laboratory ID], Storet_MethodsProcedures.ProcedureID AS [Field/Lab Procedure], Storet_MethodsProcedures.SourceAcronym AS [Field/Lab Procedure Source], "" AS [Analysis Date], "" AS [Analysis Time], "" AS [Analysis Time Zone], "" AS [Lab Sample Prep Procedure], "" AS [Lab Sample Prep Procedure Source], tblSampleData.PracticalDetectionLimit AS [Detection Limit], tblSampleData.UnitID AS [Detection Limit Unit], "F" AS [Result Status], tblStatisticTypes.StatisticType AS [Statistic Type], IIf(IsNull([tblSampleData]![StatisticTypeID]),"Actual",[ValueType]) AS [Value Type]
FROM ((Storet_StaticFields RIGHT JOIN tblSites ON Storet_StaticFields.StaticFieldID = tblSites.StaticFieldID) RIGHT JOIN ((tblRuns RIGHT JOIN (tblSampleRuns LEFT JOIN STORET_TimeZoneOutput ON (tblSampleRuns.RunDate = STORET_TimeZoneOutput.RunDate) AND (tblSampleRuns.RunID = STORET_TimeZoneOutput.RunID)) ON tblRuns.RunTypeID = tblSampleRuns.RunTypeID) RIGHT JOIN (tblSiteVisits LEFT JOIN STORET_SiteVisitNum ON tblSiteVisits.SiteVisitID = STORET_SiteVisitNum.SiteVisitID) ON tblSampleRuns.RunID = tblSiteVisits.RunID) ON tblSites.SiteID = tblSiteVisits.SiteID) RIGHT JOIN ((Storet_Fractions RIGHT JOIN (Storet_Characteristics RIGHT JOIN tblParameters ON Storet_Characteristics.CharacteristicID = tblParameters.CharacteristicID) ON Storet_Fractions.FractionID = tblParameters.FractionID) RIGHT JOIN (Storet_MethodsProcedures RIGHT JOIN (Storet_ActivityCategories RIGHT JOIN (STORET_Replicate RIGHT JOIN (((tblSampleData LEFT JOIN tblStatisticTypes ON tblSampleData.StatisticTypeID = tblStatisticTypes.StatisticTypeID) INNER JOIN STORET_ActivityType ON tblSampleData.ResultID = STORET_ActivityType.ResultID) LEFT JOIN tblUnits ON tblSampleData.UnitID = tblUnits.UnitID) ON STORET_Replicate.ResultID = tblSampleData.ResultID) ON Storet_ActivityCategories.ID = tblSampleData.Activity_Category) ON Storet_MethodsProcedures.MethodID = tblSampleData.MethodID) ON tblParameters.ParameterID = tblSampleData.ParameterID) ON tblSiteVisits.SiteVisitID = tblSampleData.SiteVisitID
WHERE (((Storet_Characteristics.CharacteristicName) Is Not Null) AND ((IIf(IsNull([PracticalDetectionLimit]),Str([Value]),IIf([Value]<[PracticalDetectionLimit],"Present < QL",Str([Value])))) Is Not Null) AND ((tblSampleRuns.RunDate) Between [forms]![fmExportStoret]![StartExport] And [Forms]![fmExportStoret]![EndExport]) AND ((tblSampleData.SampleStrata)=1 Or (tblSampleData.SampleStrata)=2 Or (tblSampleData.SampleStrata)=3) AND ((tblSampleData.Exported_To_Storet)=[Forms]![fmExportStoret]![ExportType] Or (tblSampleData.Exported_To_Storet)=0));
and the code for the export is
Dim MYPATH As String
MYPATH = CurrentProject.Path & "EXPORTRESULTS" & Format(Date, "mmddyyyy") & ".txt"
DoCmd.TransferText acExportDelim, "STORETEXPORT", "STORET_MSRRESULTS_TABLE2", MYPATH, True
Thanks for taking the time to help :)
When exporting my tables from access to excel my table names change if they have a space in the name. Example table name "New Record" turns into "New_Record".
What I am trying to do is export the table data to excel than update my access program than imort the table data back. This way I can take a vertion of my access program and update/modify it as time permits than reinsert all current data with min down time.
The code I am using is as follows:
Dim td As DAO.TableDef, db As DAO.Database
Dim out_file As String
out_file = CurrentProject.Path & "excel_out"
Set db = CurrentDb()
For Each td In db.TableDefs
[Code] .....
I'm working on a report that highlights employees when they are leaving on travel and returning on travel and my problem is that the report is only run on the weekdays. It highlights all employees that returned yesterday so for instance on Monday's report it only shows people that returned Sunday and not Friday and Saturday.
My first thought was to make an update query convert those days to Sunday in a new column on my table which would then still cause the employee to be highlighted on monday. I made a table with all Fridays and Saturday's in the year and then in the second column is the Sunday Date to update the new field.
I haven't been able to get the update query to work correctly and was wondering if there would be a much easier solution so a person wouldn't continually extend the weekend table manually.
Any way to have a form with Dates as column headers to update a table where the dates are stored in rows???
The table set up is like this:
tblOpHdr
DiaryID (PK) - OpDate (Date)
tblOpDetail
DiaryID (FK) - CostCode - MachineNumber - MachineHours - etc
I'm just wondering if there's any way I can do this with a datasheet or a crosstab type setup?
It's Access 2010.
I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
View 1 Replies View RelatedI have a table of records, which has within it two date fields (effectively, a 'start' and 'end' date for that particular record)
I now need to create a query to perform a calculation for each date between the 'start' date and the 'end' date
So the first step (as I see it anyway) is to try to create a query which will give me each date between the two reference dates, in the hope that I can then JOIN that onto another query to perform the necessary calculation for each of the returned dates.
Is there a way to do this?
So basically, if for a particular record, the 'start' date is 01-Apr-2015 and the 'end' date is 09-Apr-2015, can I produce a dataset of 9 records as follows :01-Apr-2015
02-Apr-2015
03-Apr-2015
04-Apr-2015
05-Apr-2015
06-Apr-2015
07-Apr-2015
08-Apr-2015
09-Apr-2015
(The *obvious* solution would be to create a separate table of dates, from which I could just SELECT DISTINCT <Date> Between #04/01/2015# And #04/09/2015# - but that seems like a dreadful waste of space, if that table is only required to generate the above? And it would have to cover all possible options; so it would either have to be massive, and contain every possible date - ever! - or maintained, adding new dates as necessary when they are required. Seems horribly inefficient!)
Is it possible to just select each date between the two reference dates? Or can you only query something which exists somewhere in a table?
Hiya-
I have a database with 5000 entries, corresponding to about 10 entries for about 500 people. Each of the entries is dated, and I need to calculate the time intervals between each person's sequential entries in the table.
One way of doing this is to create another column that contains the date of the previous entry. I can then use DateDiff to subtract one date from the other and give me the difference in days.
This approach falls down if I then work with only a subset of the entries - I would have to re-enter the previous entry dates as the time intervals would have changed.
What I really need is a way of subtracting the date from the date in the cell directly above it. Will Access let me do this, or is there a better way?
Many thanks, Jules.
I have two tables with dates. Between (!) every two following dates in table1, I want to know the number of dates in table2. How do I write an SQL query for this? The tables I have are up to a few hundred records in table 1 and a few thousand records in table2. So to prevent that this takes hours I need a fast query.
To explain the query I need, for example:
table1
01/01/2014
15/01/2014
17/01/2014
30/01/2014
table2
01/01/2014
02/01/2014
05/01/2014
17/01/2014
18/01/2014
20/01/2014
21/01/2014
25/01/2014
So the answer of the query would be 2,0,4.
Explanation:
Between 01/01/2014 and 15/01/2014 in table 1 there are 2 dates in table2 (01/01/2014 is not included between the dates)
Between 15/01/2014 and 17/01/2014 in table 1 there are 0 dates in table 2
Between 17/01/2014 and 30/01/2014 in table 1 there are 4 dates in table 2
I have a master table which shows all transactions per record (person) over a financial year.
Each record person has a seperate package period over which their spend needs to be measured. Therefore although I have all their transactions for the year, I only want to sum their transactions between their given [start date] and [end date] which are in columns.
I need to be able to create a field which sums all expenditure per record between the start and end dates
Name Start Date End Date Invoice Date Amount
Matt 15/5/11 15/9/11 1/11/11 £100
Matt 15/5/11 15/9/11 7/7/11 £200
Matt 15/5/11 15/9/11 12/12/11 £200
In this case I would only want to sum 7/7/11 as this is between the start and end dates
I want to write something like sumif([Invoice Date] is between [start date] and [end date] - not sure where or how exactly
(The start date and end date will always be the same per person)
Is this possible in access?
I am trying to change the button color on a subform if a related form data changes.Main form is products with a continuous subform with serial numbers of products i.e, serial number, location, price and a button to add addtional issues if there are any for this particular serial number (this will open up another form related to the serial number so I can add an issues if there are any).The reason I would like the button to be a different color is so I can quickly see if there are any additional notes been added to the serial number. Just in case you may ask why not add the field to the continuos form is that the issues and be quite lengthy and there may be lots of serial numbers on the form
2346 location warehouse price 29.99 (button - green)
2347 location shop price 29.99 (button - red)
Hi,
Please bear with me here as it's a little involved.
I'm doing a staff profile website which includes a section where they can enter their annual/other leave details.
I decided to store their leave in two fields Start_Date | End_Date rather than each individual date that they took - the short and wide approach vs long and narrow.
This has left me needing to do a query that would return all the dates between the start and end dates inclusive.
Example:
StaffID---Start_Date---End_Date
---1-----12/12/2004--14/12/2004
Returns:
StaffID---Leave_Dates
--1-------12/12/2004
--1-------13/12/2004
--1-------14/12/2004
I appreciate i could do this using some script to loop through a recordset and build an array of dates but i wondered/hoped that it could be done using SQL.
As it is an asp page i can't use user defined functions in a VBA module in Access so the solution would need to be pure SQL.
Is this possible?
Any help v.much appreciated.
TS
I have a scenario where the first three rows of date which have dates of 4/1, 4/4/ 4/6 with ndc 5513026701; next six rows that have dates from 4/8 to 4/20 with ndc 5513014801; next three rows that have dates from 4/25, 4/27, 4/29 with ndc 5513026701.
The issue I am having is I do not know how to have separate min/max dates for ndc 5513026701 since when I group by ndc 5513026701 min = 4/1 ; max = 4/29. I need to have min = 4/1 and max = 4/6 for one row and another row of min = 4/25 and max = 4/29.
Any easy way to sequentially create min/max for each ndc 5513026701? I wasn't sure how to verbalize this so I have attached a sample worksheet.....
I have not done much work in later version of Access. Now I found if I change a design in one form and similar forms (names are different) which are linked to the same tables got changed as well without openning them up and making changes. Is this something new with Access 2003?
Thank you very much for help.
I'm not sure if I am biting off more than I can chew. I have a text field in each record in my database (Inherited) The db has nearly 5,000 records. I would like to split the field into records in a seperate table. An Example of the table as is now;
Code:
MemberIDBoats
5882Opossum(78-80) (87-89) Otter(80-84) Opportune(91-93) Turbulent(97-00).
5883Astute Auriga Aeneas Affray Amphion
2407H34 O10 Porpoise Trenchant Tapir.
I want to create a table as follows;
Code:
MemberIDBoatFromTo
5882Oppossum19781980
5882Oppossum19871989
5882Otter 19801984
5882Opportune19911993
5882Turbulent19972000
5883Astute
5883Auriga
5883Aeneas
5883Affray
5883Amphion
Etc.
Is this possible in one hit or do I need to process the records without dates first and then run another process to split those with Dates? I say dates but the field is a text field. About 15-20% of the records contain dates which are always enclosed in parenthesis.
Is there a way in this program to create a list of dates between 2 dates?
i.e I have Arrival Date and Departure Date. Is there a function or expression that will list all the dates on and between?
Hello All,
I have 2 reports that are exporting to 2 different .txt files. Currenlty, I am using the transfertext function in access. What is the best way to combine these 2 reports into one .txt file? I have 2 different specification criteria being used, but I need all the data in 1 report.
Thanks in advance,
mlr0911
What I want to do is to export about 300 rows of records at a time from a table to a text file (text001.txt). The next 300 will be in text002.txt and so forth.
What I am doing right now is -- I am looping through all the fields [rs(0)-rs(50)], put in a variable and then print to the text file then go to the next row.
Is there a way to print the whole row to a text file instead of looping through all the fields then print it.
Thanks.
ez
Hi,
Does anyone know how I can get a table thats been sorted by a specific field to export to .txt in its sorted format? For some reason whenever I have it sorted by one field and save its new formatting, it exports sorted by another field. Keep in mind it exports the way the table was originally sorted when it was first created.
Anything?
-Ross
I was just wondering if anyone knows of any way to export email addresses held within an access database to Outlook, using office 2000?
I work for a small charity who have a very large contacts database in access, but are now starting to send some of our info electronically and it would be a major time saver if we could send email addressed directly to outlook.
Hello. I've searched around the board and help files and couldn't find a straight answer for this. I have 7 or 8 different queries. Is there a way to export Query 1 to Sheet 1 of my excel workbook, then Query 2 to sheet 2 of the workbook, and so on? Thanks.
View 4 Replies View RelatedI want some guidance in regards to how should I export access form as a report to excel. I already have fields with formulas in it and here I have fields where I have written down the numbers, so when these numbers get exported to excel. Excel will be populated with with these numbers and all the calculation will be automatically done.
I hope I am making sense here.
I have fields in access that should export through a button and populate on excel sheet.
is there any vb scripting for this, I rem seeing something in the same context few months back, but I can't seem to find it rite now.
Thanks in adv
Wondered if someone could help.
I am trying to Export an access report from an .mde (File>Export) and receive an error message "Overflow". the report itself is only 17 pages long.
Can anyone advice?
Thanks
Paul
I am exporting a table into a text file. I have an intrest rate that is formatted in the table as as five characters past the decimal point xx.xxxxx. However, when I export it to a text file, I loose my 3rd through 5th position. Any ideas on how I can export to text and still keep all five positions past the decimal? The field is currently formatted as a double number in Access. Thanks for your input.
View 1 Replies View RelatedI often have to export data from a table to a .csv (text) format. It is very important that the data remains in the right order. I even add an indexed auto number to ensure this happens. Sometimes, but not always, the data gets out of order. Not completely scrambled but chunks of records just in the wrong place.
Has anyone come across this before or got any idea what causes it?