Format Date Field in Table using VB

[Nathan H]

I have several queries that make a new table for export. I need to find a way to format the date field in the exporting table before I output it.

Here is what I am guessing at:

Private Sub cmdAWBCSINGLE_Click()
'On Error GoTo RUNAPP_ERROR
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAWBCTEST"
DoCmd.OpenQuery "qryAWBCSINGLE"
Table!tblAWBC![Street Date].Format = "YYYYMMDD"
DoCmd.OutputTo acOutputTable, "tblAWBC", "*.xls", , YES
txtLOC.Value = "AWBC Single Title Query Completed ---" & FullExcelPath
Exit Sub
 
RUNAPP_ERROR:
End Sub
 

Here is the piece I need help on (the code obviously does not work):

Table!tblAWBC![Street Date].Format = "YYYYMMDD"

The exported file needs the date field formatted this way. It goes into a third party system and does not validate without it. Opening up the spreadsheet and reformatting does not solve my problem...

Thanks for any help.

[Nathan H]

As Linq pointed out, the only problem should be the missing right bracket in the [Street Date] Field. It should run now:

CurrentDb.TableDefs("tblAWBC").Fields("[Street Date]").Properties("Format") = "YYYYMMDD"

Hey All,

Let me try to explain. The code does run fine, but only after I have opened up the created table, insert something in the "Format" properties box, close the table, and then run the code.

It does not work if I do not do these steps. The field does not have the "Format" property attached to it prior to these steps, so if I run the code it does not find the property "format".

I think that before this line of code, I need to either create an instance of the property "format" attached to the field, or something....it is frustrating.

thanks for all your brain power so far.

Nathan