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.
10  6965   ADezii 8,834
Recognized Expert Expert
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
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...
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 8,834
Recognized Expert Expert
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.
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"
-
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
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)>
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 8,834
Recognized Expert Expert
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"
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: peashoe |
last post by:
I have an asp page that uses a calendar.js (pop-up) file to add an
exact date format in the text field (txtDDate). My problem is I need
some javascript that sets an alert that does not allow them to select
today.
example:
var dtToday = Date()
if(document.frmSoftware.txtDDate.value == dtToday)
{
alert("You cannot select same day distributions. Please enter a new
|
by: usenet |
last post by:
I have inherited a table where date information was saved from PHP as a VARCHAR.
(Sigh.) This means that the so-called date fields look like this :
August 1, 2005, 9:09 am EDT
October 13, 2004, 12:28 pm EDT
This makes them essentially useless for sorting and logical evaluation.
Understandbly, the MySQL date functions don't seem able to convert the field to
a "real" date field. This yields a blank (or maybe null) "realdate" :
|
by: lsy |
last post by:
i wonder how can i order my statement interm of "ORDER BY FORMAT(Date, 'mmmm')" with using this it will order by alphabethical but i want it order by month... i had try "ORDER BY FORMAT(Date, 'mm')" but due to at the begining i have "SELECT FORMAT(Date, 'mmmm')FROM TABLE"
my actual statement will look like this
"SELECT FORMAT(Date, 'mmmm')AS DateMonth, COUNT(x) AS CounterX FROM Table WHERE FORMAT(Date, 'mmmm') BETWEEN 'January' AND 'August'...
|
by: Fran Zablocki |
last post by:
I have a process that exports an Access table to a comma-delimited
text file. One of the fields that is exported shows the date it was
exported, using the Date() function. In the Access table, the date
appears in the records in mm/dd/yyyy format, but after exporting, the
date appears as mm/dd/yyyy 0:00:00. For example, today is 11/06/2003.
It shows 11/06/03 in the Access table, but the exported text file
shows 11/6/03 0:00:00. I thought...
|
by: Stewart Allen |
last post by:
I'm trying to filter a table that has 2 date fields, the first date will
always have a value but the second will only occasionally has a value. Each
date field also has a corresponding text field to record the record's
status.
Sample data:
*tblTest*
Model Parts CDate CStatus RDate RStatus
616 $359.79 03-Nov-03 C
| | |
by: S. van Beek |
last post by:
Dear reader,
By a Date field with Now() as default value the content of the field is date
plus time.
As I need a filter in a query on date only (excluding time) I invented the
following solution:
|
by: Paul |
last post by:
I am trying to insert a date into a mysql table but everytime I attempt it
the date field is always empty.
The field is of Date type and the date in code is in European format
dd/mm/yyyy.
Does the date have to be formatted in any particualr way from vb.net.
|
by: G Gerard |
last post by:
Hello
I am having some problem comparing dates with the SQL statement below
MySQL = "SELECT Format(Date, 'yyyy/mmmm/dd') as FROM TblDates WHERE
_
Format(Date, 'yyyy/mmmm/dd') = #2006/April/18#"
Even though the date 2006/April/18 is in TblDates, it is never found by the
|
by: sparks |
last post by:
I am trying to find valid records in a table
but I must compare a date field to a date stored in the table
I can see where this is wrong (ok I guess its not text)
"= """ & Me.date & """"
so how do you format for a date comparison of a date field in a table
and compared to a text box with a date format?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
