I have a query that formats a date/time field to eliminate the time portion. My query results show only the date, which is exactly what I want. However, when I use the TransferText macro action, the result for this field in the .csv file once again contains the 0:00. I have also tried a make table query and exported the table, but the result is the same. What can I do to keep the 0:00 out of the .csv file?
I read a previous post that indicates one person used a specification file to solve this same problem. If this is possible, how do I create a specification file?
8 3792
Only an idea:
Force your query field to a String data type.
for that use - YourFieldInQueryName:CStr(Formula that select only the Date part)
Thanks for your prompt reply.
I have tried all the following:
MyDate:CDate(Int([app_rec_date]))
MyDate:Format([app_rec_date],"mm/dd/yyyy")
MyDate:Cstr(Format([app_rec_date],"mm/dd/yyyy"))
MyDate:(Int([app_rec_date]))
MyDate:Cstr(CDate(Int([app_rec_date])))
I have tried formatting the field to a Short Date. I have tried a make table query and exported the table.
They all return the date without the time portion in Access. But, the 0:00 is still in the .csv file after the export.
Is there a way to specify that the date should not contain the 0:00 in a specification file? I am not currently using a spefication file in the macro that creates the query/table for export.
This work fine for me - MyDate: Left([app_rec_date],10)
The Left() function have as result a string that contain only the Date (not the Time).
I notice (Access 2007) that, if I try to export the query itself (as .txt), the Delimiter option do not work. The delimiter is a space whatever I have choose.
So, I transform my Select query into a Make Table one then I export the created table.
I was able to use a make table query successfully since the date fields become, in essence, text fields in the table. The only downside is that now I can't sort my .csv field on any of the date fields because they are no longer date fields. Thanks for your help!!!
Do the sorting in the query.
NeoPa 32,556
Expert Mod 16PB
It's hard to answer properly as you give us no indication of what you're exporting, and which lines of the data have problems. Essentially, you need to undrstand the difference between a date value, and a date string. They look similar to the naked eye, but not remotely similar to Access. Understand this distinction and I'm sure you will be able to find an easy solution to your quandary.
NeoPa 32,556
Expert Mod 16PB
As the link is to a Microsoft site (and they are the producers of the product in question) then yes, this is OK. Otherwise we would expect an answer to be posted as an explanation rather than a link.
My understanding of the solution is that the problem lies in the Export Specification. This may be able to be handled manually (In the Advanced section), but if not, then create the specification using textual data, save the specification, then use it later to export your numeric or date data.
In 2003, when in Advanced, there are a number of hidden columns for the column specs. If you drag across right from the Field Name column then double-click on the right side of the column, then all the invisible columns are resized and shown. If you specify "Text" for the Data Type column you should see it work more to your liking ;-)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ricky |
last post by:
I have a table with a field type date and null is set to yes.
How can I insert a blank value without it being save as 0000-00-00.
thanks
Ricky
|
by: peashoe |
last post by:
I have an asp page that uses a calendar.js (pop-up) file to add an
exact date format in the text field (txtDDate). My problem is I need
some javascript that sets an alert that does not allow them...
|
by: Fran Zablocki |
last post by:
I have a process that exports an Access table to a comma-delimited
text file. One of the fields that is exported shows the date it was
exported, using the Date() function. In the Access table, the...
|
by: hph |
last post by:
Please be gentle; I am a relative newbie for php/MySQL, and most of what
I am doing is based on being self-taught using someone else's
well-written code, and my own googling the answers to my...
|
by: Graham Feeley |
last post by:
I have a date field named rcdate
it is a general date eg:
12/08/2006 3:30:00 PM
12/08/2006 3:00:00 PM
I used to update another empty field named rdate with the
with using in a update query "...
|
by: clevelander |
last post by:
Hi,
Is there a way to add a permanent field to a table using a macro? I have a number of different tables that I need to add the same field name to, then I update that field based on info in the...
|
by: Killer42 |
last post by:
Hi all.
I have a question from a colleague. He is creating a form in Word2003, which has a date field on it. He wants to set the default value to the current date, but allow the user to overwrite...
|
by: MarkK |
last post by:
Hi all
I've used Access for years but I'm not a programmer so I need some help...
I want to regularly transfer files into a folder but want them to be tagged with the date of transfer (today's...
|
by: Dave |
last post by:
Hello,
I have a possible problem exporting a text field that happens to
contain dates (but is not a date field) when using TransferText in MS
Access 2000.
I am exporting a query to a text...
|
by: ollyb303 |
last post by:
Hi,
I am having a bit of a problem with TransferText macro.
I am using TransferText, Export Delimited (no field names) to export the results of a query as a .csv file.
The query is based on...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |