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.

[ADezii]

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.

Immediately prior to the Export, Format the Field exactly how you want it. Be advised that the change will persist (is permanent), so you may wish to revert back to its original Format after the Export.

CurrentDb.TableDefs("tblAWBC").Fields("[Street Date").Properties("Format") = "YYYYMMDD"
DoCmd.OutputTo acOutputTable, "tblAWBC", acFormatXLS, , True

[Nathan H]

Immediately prior to the Export, Format the Field exactly how you want it. Be advised that the change will persist (is permanent), so you may wish to revert back to its original Format after the Export.

CurrentDb.TableDefs("tblAWBC").Fields("[Street Date").Properties("Format") = "YYYYMMDD"
DoCmd.OutputTo acOutputTable, "tblAWBC", acFormatXLS, , True

I am getting a property not found error when it runs...

[Nathan H]

I am getting a property not found error when it runs...

This is a time sensitive project, so I am bouncing this back up the list.

[ADezii]

This is a time sensitive project, so I am bouncing this back up the list.

I'm not sure what the problem is, the code has been tested and is functional. Try checking you syntax.

[Nathan H]

I'm not sure what the problem is, the code has been tested and is functional. Try checking you syntax.

First Error:
When I run this I get: "Item Not Found in this Collection"

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

Second Error:
When I tried this, because it looked like a typo I get this error: "Property Not Found"

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

[Nathan H]

First Error:
When I run this I get: "Item Not Found in this Collection"

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

Second Error:
When I tried this, because it looked like a typo I get this error: "Property Not Found"

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

ADezii,

The code runs when I go into the table, define a format (like long date), close the table, and then run the code.

It looks like I have to go and create the property first. First time trying, so any pointers would be most helpful.

Nathan

[missinglinq]

On this line

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

you've got an unmatched square bracket before Street Date! Unmatched brackets re never happy!

Linq ;0)>

[Nathan H]

On this line

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

you've got an unmatched square bracket before Street Date! Unmatched brackets re never happy!

Linq ;0)>

Linq,

I think we have that fixed. Problem is now I have to create the "format" property in the code, but have no experience with that. Any advice?

Nathan

[ADezii]

ADezii,

The code runs when I go into the table, define a format (like long date), close the table, and then run the code.

It looks like I have to go and create the property first. First time trying, so any pointers would be most helpful.

Nathan

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"

[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