date

[dollygg]

The date is showing as "12:02:23 AM" in th backend table. I set the default value =Format(DateSerial(Year(Date()),Month(Date()),0)," dd/mm/yyyy") and the format = short date. Once that table get exported excel by running the VBA code , the date is showing as "00/01/1900 12:02:23 AM" . I am running a MDB file. Why is not working?

[ssanfu]

Is the field in the BE a date/time type field?

I would use
default value = DateSerial(Year(Date()),Month(Date()),0)

What does the data in the field look like in the BE?

The FORMAT command is how the value is displayed.

It is possible that the export is the wrong field/incorrect/changing the date field.
How is the export being performed?

[dollygg]

Yes, it's a date field.
It is showing as a time in the BE table. Therefore the export have problem.
if I run the query manually in Access , then the date is showing correctly . If I run the same query by code , then it's not.

[pbaldy]

The Format() function returns a text value, so try dropping that and just using the DateSerial() function.

[dollygg]

I did that already but didn't work. The problem is on VBA. I am getting two different result by running the same query manually or by the macro.

[pbaldy]

What's the VBA code?

[dollygg]

Docmd.runquery ("name if the query")

[pbaldy]

A query will not export to Excel by itself. Can you post the db here?

[dollygg]

A query will not export to Excel by itself. Can you post the db here?

I have another piece of code to export to excel. However the problem occur before the export
. The order is , run the query to update the table, then export to excel. However the date field is showing a time in the table before the export.
I don't mind uploading the code or db tomorrow.

[dollygg]

I have another piece of code to export to excel. However the problem occur before the export
. The order is , run the query to update the table, then export to excel. However the date field is showing a time in the table before the export.
I don't mind uploading the code or db tomorrow.

Never mind, I find another way to get around this problem. What I did is , write the excel vba code to change the date on the excel file.

[ssanfu]

OK, ready to mark this solved?

[dollygg]

OK, ready to mark this solved?

Yes, Please!