By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

Format Date Field in Table using VB

Nathan H
100+
P: 104
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAWBCSINGLE_Click()
  2. 'On Error GoTo RUNAPP_ERROR
  3. DoCmd.SetWarnings False
  4. DoCmd.OpenQuery "qryAWBCTEST"
  5. DoCmd.OpenQuery "qryAWBCSINGLE"
  6. Table!tblAWBC![Street Date].Format = "YYYYMMDD"
  7. DoCmd.OutputTo acOutputTable, "tblAWBC", "*.xls", , YES
  8. txtLOC.Value = "AWBC Single Title Query Completed ---" & FullExcelPath
  9. Exit Sub
  10.  
  11. RUNAPP_ERROR:
  12. End Sub
  13.  
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.
Dec 11 '07 #1
Share this Question
Share on Google+
10 Replies


ADezii
Expert 5K+
P: 8,669
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAWBCSINGLE_Click()
  2. 'On Error GoTo RUNAPP_ERROR
  3. DoCmd.SetWarnings False
  4. DoCmd.OpenQuery "qryAWBCTEST"
  5. DoCmd.OpenQuery "qryAWBCSINGLE"
  6. Table!tblAWBC![Street Date].Format = "YYYYMMDD"
  7. DoCmd.OutputTo acOutputTable, "tblAWBC", "*.xls", , YES
  8. txtLOC.Value = "AWBC Single Title Query Completed ---" & FullExcelPath
  9. Exit Sub
  10.  
  11. RUNAPP_ERROR:
  12. End Sub
  13.  
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.
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.TableDefs("tblAWBC").Fields("[Street Date").Properties("Format") = "YYYYMMDD"
  2. DoCmd.OutputTo acOutputTable, "tblAWBC", acFormatXLS, , True
Dec 11 '07 #2

Nathan H
100+
P: 104
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.
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.TableDefs("tblAWBC").Fields("[Street Date").Properties("Format") = "YYYYMMDD"
  2. DoCmd.OutputTo acOutputTable, "tblAWBC", acFormatXLS, , True

I am getting a property not found error when it runs...
Dec 11 '07 #3

Nathan H
100+
P: 104
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.
Dec 11 '07 #4

ADezii
Expert 5K+
P: 8,669
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.
Dec 11 '07 #5

Nathan H
100+
P: 104
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"

Expand|Select|Wrap|Line Numbers
  1. CurrentDb.TableDefs("tblAWBC").Fields("[Street Date").Properties("Format") = "YYYYMMDD"
  2.  
Second Error:
When I tried this, because it looked like a typo I get this error: "Property Not Found"

Expand|Select|Wrap|Line Numbers
  1. CurrentDb.TableDefs("tblAWBC").Fields("Street Date").Properties("Format") = "YYYYMMDD"
  2.  
Dec 11 '07 #6

Nathan H
100+
P: 104
First Error:
When I run this I get: "Item Not Found in this Collection"

Expand|Select|Wrap|Line Numbers
  1. CurrentDb.TableDefs("tblAWBC").Fields("[Street Date").Properties("Format") = "YYYYMMDD"
  2.  
Second Error:
When I tried this, because it looked like a typo I get this error: "Property Not Found"

Expand|Select|Wrap|Line Numbers
  1. CurrentDb.TableDefs("tblAWBC").Fields("Street Date").Properties("Format") = "YYYYMMDD"
  2.  

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
Dec 11 '07 #7

missinglinq
Expert 2.5K+
P: 3,532
On this line
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.TableDefs("tblAWBC").Fields("[Street Date").Properties("Format") = "YYYYMMDD"
  2.  
you've got an unmatched square bracket before Street Date! Unmatched brackets re never happy!

Linq ;0)>
Dec 11 '07 #8

Nathan H
100+
P: 104
On this line
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.TableDefs("tblAWBC").Fields("[Street Date").Properties("Format") = "YYYYMMDD"
  2.  
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
Dec 11 '07 #9

ADezii
Expert 5K+
P: 8,669
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:
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.TableDefs("tblAWBC").Fields("[Street Date]").Properties("Format") = "YYYYMMDD"
Dec 12 '07 #10

Nathan H
100+
P: 104
As Linq pointed out, the only problem should be the missing right bracket in the [Street Date] Field. It should run now:
Expand|Select|Wrap|Line Numbers
  1. 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
Dec 12 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.