Unanswered: date format in yyymmdd with no time in export
First my apologies, I am very novice in Microsoft Access (2003) and hope my questions are appropriate for this forum.
I have built an Access query that contains DOB. The date comes in from my client’ Cache database in the format 06/24/1924. I need it to display in an export file in the format ‘yyyymmdd’. I was able to accomplish this in the query by using the FORMAT in properties area.
Problem was when exporting query to a .csv the date displayed in 19240624 00:00:00.
I then used an expression DOB: DateValue([date_of_birth]) and reran the query. The date display in the correct format on the query but when exporting to a .csv it now display as 06/24/1924 00:00:00.
I then tried Right(date_of_birth,9). This resulted in the format in the time being removed in both the query and the export but would not honor the format. The query showed 06/24/1924 and the export showed 06/24/1924 with no time.
Can anyone give me guidance on how to format the date so it exports to a ‘yyyymmdd’ format without the time.
I will try to explain again as I fear I was not clear. I have used yyyymmdd in the Format function (YYYYMMDD) but that didn't get rid of the time. so I used the RIGHT(date,9) in the expression builder. the changed the format back to mm/dd/yyyy but fotunately did take away the zero time. So I thought they same as you, use the advanced unctions to to reformat. In the advanced section of the export I selected YMD with 4 year format, leading zeros in date with no '/' in the date.
It still exports as 06/24/1924
I tried datevalue([Date_of_birth],"yyyymmdd" and got what I needed.
I truly appreciate everyone efforts in help me gain more knowledge on this product. It is a far cry from what I am used to doing. I am an assembler/fortran programmer and don't have much of a chance to use a much more user friendly product. I think I am going to enjoy using Access.
You need to convert the date to a string using CStr. I have had to deal with the problem you are dealing with. Access tends to ignore formatting with numbers when exporting data to a file and the only way to get the format you want is to force it to a string.