Problem Storing Date/Time In TEXT Field.
Hi,
I have an Access Table with a field called "VisitDate" and is a Text Field of size 50. I want to store current Date and Time in this field.
My program stores current date and time in a variable "ProgramDate" which is of Date DataType
I tried following code, but it generates this error:
Run-time error '-2147217887 (80040e21)'
The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
Code:
Dim myDateTime As String
myDateTime = CStr(ProgramDate) + " " + CStr(Time)
rsTempReport!VisitDate = myDateTime
I want to store data in this format, e.g: 23/9/2005 11:15 AM
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Grouping By Date Where Date Field Is Date/time
I have an SQL7 database storing datetime data, and totaliser reading, new records are added every 5 minutes.
I have written the following sql to give me the the min and max totaliser reading for each day - which works fine - except that the resultant Date field is a text field not a date field so the order statement generates an incorrect listing if the data selected sarts in one month and ends in the next, eg.
select left(Date, 12) as Date, Min(Reading) as Day_Start, Max(Reading) as Day_End from Steam where tag_no = 2 and (Date <= '24-November-2005 00:00:00' and Date >= '22-October-2005 00:00:00') Group By left(Date, 12) order by Date asc
Is there another function I can use to extract just the date from the date/time field as a date data type?
I cannot alter the database as its structure is predetermined by the remote field device supplying the data.
Only Want Date Part Of Date Time Field
How can I using an SQL statement just reitrieve the date part of a database field.
The data is stored as 10 05 2001 12:31.
At moment sql is bringing back the whole field. For grouping purposes I only want the date ie 10 05 2003.
Any ideas??
I Have A Problem With Storing A Date Into A Text File
Hi there,
My problem is I am saving a Start date and an End date to a .txt file. The end date stores in the file correctly in the format #2004-02-09# whereas the start date is stored as "09/02/2004". I can't see where I'm going wrong. I've checked the data format on the form and they are both set to date.
I have another form which is to show the start date , the end date , and today's date and that form is the same with just the end date being shown correctly. the others are shown on the form as 09:02:2004
Can anyone help with where the problem lies.
Below is my code:
Private Type Personal
strName As String
strAddress As String
strCity As String
strCounty As String
strPostcode As String
strTelephone As String
dteDOB As Date
dteHirestart As Date
dteHireend As Date
intDays As Integer
End Type
Dim recPers As Personal
Option Explicit
'Define Variables
Dim strName, strAddress, strCity, strCounty, strPostcode, strTelephone, strVehdetails As String
Dim dteDOB, dteHirestart, dteHireend As Date
Dim intTotalcost As Integer
Dim intDays As Integer
Dim intSeats, intCost As Integer
Dim intCount1, intCount2, intCounter, intLoop, intVehicles, intEntries As Integer
Dim intIndex As Integer
'Show available vehicles in combo box
Private Sub cboAvail_Click()
intIndex = Me.cboAvail.ListIndex + 1
intDays = txtDays.Text
txtTotalcost = "£" & intDays * recVehicles(intIndex).intCost
txtNodays = txtDays
'Display vehicle details when selected from combo box
txtVehdetails = recVehicles(intIndex).strVeh & " , " & recVehicles(intIndex).strReg & " , " & _
"No. of seats = " & recVehicles(intIndex).intSeats
End Sub
'Clear contents of all text boxes
Private Sub cmdClear_Click()
txtName.Text = ""
txtAddress.Text = ""
txtCity.Text = ""
txtCounty.Text = ""
txtPostcode.Text = ""
txtTelephone.Text = ""
txtDOB.Text = ""
txtAge.Text = ""
txtHirestart.Text = ""
txtDays.Text = ""
txtHireend.Text = ""
txtTotalcost.Text = ""
txtNodays.Text = ""
txtVehdetails.Text = ""
End Sub
'save whatever is in text boxes to these variables
Private Sub cmdConfirm_Click()
strName = txtName.Text
strAddress = txtAddress.Text
strCity = txtCity.Text
strCounty = txtCounty.Text
strPostcode = txtPostcode.Text
strTelephone = txtTelephone.Text
dteHirestart = txtHirestart.Text
dteHireend = txtHireend.Text
intTotalcost = txtTotalcost.Text
strVehdetails = txtVehdetails.Text
Open "A:AssignmentBooked.txt" For Append As #2
Write #2, strName, strAddress, strCity, strCounty, strPostcode, strTelephone, dteHirestart, _
dteHireend, recVehicles(intIndex).intCost, intTotalcost, recVehicles(intIndex).strVeh, recVehicles(intIndex).strID, _
recVehicles(intIndex).strReg
Close #2
End Sub
Private Sub Form_Load()
'load in vehicle list
intCount1 = 1
Open "A:AssignmentVehiclelist.txt" For Input As #1
Do While Not EOF(1)
Input #1, recVehicles(intCount1).strVeh, recVehicles(intCount1).strID, _
recVehicles(intCount1).strReg, recVehicles(intCount1).strLic, _
recVehicles(intCount1).intSeats, recVehicles(intCount1).intCost
intCount1 = intCount1 + 1
Loop
intVehicles = intCount1
Close #1
On Error GoTo ErrorHandler
'load in booked list
intCount2 = 1
Open "A:AssignmentBooked.txt" For Input As #2
Do While Not EOF(2)
Input #2, recBook(intCount2).strName, recBook(intCount2).strAddress, recBook(intCount2).strCity, _
recBook(intCount2).strCounty, recBook(intCount2).strPostcode, recBook(intCount2).strTelephone, _
recBook(intCount2).dteHirestart, recBook(intCount2).dteHireend, recBook(intCount2).intCost, _
recBook(intCount2).curTotalcost, recBook(intCount2).strVeh, recBook(intCount2).strID, recBook(intCount2).strReg
intCount2 = intCount2 + 1
Loop
intEntries = intCount2
Close #2
'store the number of records
ErrorHandler:
'if there hasn't been a file created originally then create one
If Err.Number <> 0 Then
Select Case Err.Number
Case 53
MsgBox "(Creating new Booked.txt File)"
Open "A:AssignmentBooked.txt" For Output As #2
Close #2
Exit Sub
Resume
End Select
End If
End Sub
'When you tab off No of days automatically calculate which vehicles are available
Private Sub txtDays_LostFocus()
Dim bolCheck As Boolean
Dim intAge, intCompare1, intCompare2 As Integer
dteHirestart = txtHirestart.Text
txtHireend = DateAdd("d", txtDays, txtHirestart)
cboAvail.Clear
bolCheck = True
intCompare1 = -1
intCompare2 = -1
For intCompare1 = 1 To 20
intCompare2 = 0
If intAge < 21 And recVehicles(intCompare1).strLic = "Over 21 years old" Then
bolCheck = True
Else
bolCheck = False
End If
Do While bolCheck = False And intCompare2 < intEntries
intCompare2 = intCompare2 + 1
If bolCheck = False And recVehicles(intCompare1).strID = recBook(intCompare2).strID Then
If recBook(intCompare2).dteHireend < dteHirestart Or _
recBook(intCompare2).dteHirestart > dteHireend Then
bolCheck = False
Else
bolCheck = True
End If
End If
Loop
If bolCheck = False Then
cboAvail.AddItem recVehicles(intCompare1).strVeh
End If
Next intCompare1
End Sub
'Calculate Date of Birth
Private Sub txtDOB_LostFocus()
Dim intMonths As Integer
Dim sinAge As Single
Dim dteDOB As Date
dteDOB = txtDOB
intMonths = DateDiff("m", dteDOB, Now())
sinAge = intMonths / 12
txtAge.Text = Int(sinAge)
End Sub
Private Sub cmdExit_Click()
frmHire.Hide
Load frmMenu
frmMenu.Show
End Sub
'Calculate total cost of hire
Function Total(Totalcost) As Integer
Totalcost = intDays * recVehicles(intIndex).intCost
End Function
Date Time Field
I have a table field that store date and time in the format of yyyy/mm/dd hh:mm:ss, how can i select the date only ?
Date & Time As Field Name
Hi all,
I have an application that reads the dbf file for data input, the application is working fine, except for some reasons it stops responding , means fails to read the dbf file.
The said dbf file contains two fields as Date & Time , the vb applications reads this fields first, is this the reason for system not responding.
Regards
JOhn
Date Being Put Into Field As Time
Hi,
I'm trying to add a date value to a field in access using SQL but it's showing up as a time.
Both the table and date field are being created at run-time.
The code used to create the "dDate" field is:
"ALTAR TABLE Table1 ADD COLUMN dDate DATE"
- This seems to create a Date/Time field just like any other.
The code I used to add a date value is
"UPDATE Table1 SET dDate = " & "01/01/2006"
I've also tried a date variable and a string variable formatting both as dates, not formatting either ... No matter what I do, it just inputs a time value.
All this is being done from outside of Access using VB & SQL.
Any comments and/or instructions would be greatly appreciated. Thanks in advance.
Paolo
Storing " " In Date Field.
I have a DATE/TIME field in Access 97 database. I want that if Date
Textbox is left blank in my form, a " " should be replace in
this field.
Presently it is giving "TYPE MISMATCH" error.
Add X Days To Date/time Field
Hello
I have a texbox (txtReceivedDate) that is populated from my MS Access Database Field receivedDate that is set to date/time.
What I am trying to do is add 10 days to the receivedDate and have that NEW date appear in my txtbox (txtNewDate)
Example: txtReceivedDate would be 7/28/2004 and the txtNewDate would be
8/07/2004
Any help with this would be appreciated
Tks
Bill
ADO Changing Time And Date Field???
How can I update a "DateTime" field in table.dbf with a date time value that does not take on the format that is set in the Windows "Regional Settings"
My Regional Settings are for short Date yy/MM/dd
My code is a follows:
Dim rs As adodb.Recordset
Set rs = New Recordset
rs.Open "Select * From table.dbf", "filedsn=mydsn;", adOpenDynamic, adLockOptimistic
rs.AddNew
rs!Timestamp = Format(Now, "mm/dd/yy")
rs.Update
rs.Close
When I look in table.dfb the date is stored as yy/MM/dd not as mm/dd/yy
Thanks in advance
Jeremy
How To Detect A Date/Time Field ?
I need to know how to determine a Date/Time field from an Access database, rs!inputdate is a Date/Time field, I use RS.ADDNEW to create this record but I did not write anything in it. But following is not working:
rs!inputdate = ""
rs!inputdate = Null
rs!inputdate = 0
rs!inputdate = vbNullString
Please help .....
How To Erase Date And Time Field
I have 2 fields defined as DAT/TIME. Returned_on has a DATE and Returned_at has TIME. How can I erase them thru the application?
I am using Access data base in my VB6 application.
UPDATE Contracts SET Returned_on = XX, Returned_at = YY WHERE Cont_id = 1
What should I set my XX and YY to.
Filling Date/Time Field With NULL
Shalom,
I have a Date/Time field in a Microsoft Access 2000 database and I need to fill it will nothing (NULL). Here is my current statement:
Code:
gtSQLStmt = "UPDATE TICKET_DETAILS SET" & _
" sched_dte = '" & Null & "', " & _
" alert_user = 0" & _
" WHERE index_num = " & Val(txtIndexNum)
StdLibDB.execute_sql_sub gtSQLStmt, adoConA
When I execute this code I get the following error:
"Data type mismatch in criteria expression"
(this is on the execute_sql_sub line which executes my sql of course)
How can I put a NULL or nothing in that Date/Time field?
GrayKnight
Addition To Date/time Field In Oracle
Hi Guyz,
Q1
Lets say i have the following data in the date time field..
6/25/2004 7:02:31 AM
How do i add another 8000seconds to this data in oracle?
Q2
And how do i search, for an example, give me all the list of data with 6/25/2005, no matter what time it is.
I tried using the like operator but it doesnt seem to work..
Any ideas?
thankx in advance
Clear ACCESS Date/time Field
The following code is a test to set a date/time field in ACCESS to a date and then try to set it back to a blank (clear the field). Setting the field to a valid date works ok, but when I try to clear out the field, I get a "Type Mismatch" error.
Does anyone know how to blank out a date time field from VB?
Code:
Private Sub Command_Click()
Set cndb = New ADODB.Connection
cndb.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:master.mdb;Persist Security Info=False"
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Shiplist WHERE [Order ID] = 'BL00004'", cndb, adOpenDynamic, adLockBatchOptimistic, adCmdText
If rs.EOF Then
Else
Debug.Print rs![Expected Ship Date]
rs![Expected Ship Date] = "9/2/2005"
rs.UpdateBatch
Debug.Print rs![Expected Ship Date]
'the next line will cause a "Type Mismatch" error
rs![Expected Ship Date] = "" 'trying to set the date/time field to a blank
rs.UpdateBatch
End If
rs.Close
Set rs = Nothing
cndb.Close
Set cndb = Nothing
End Sub
tia,
flynn
Date/Time Field - Select Returning Nothing
Hi all,
I have a vb app with an access 2000 db. The problem is that I have a field in the db called DateAtt where the type is Date/time (shortdate - dd/mm/yy). When I try to select values from this field vb returns a recordcount of 0.
I understand that you can enclose the date within 2#'s (date literal), but when I do this the recordcount is 0 even though the date entered exists in the db. This is because eg. the record 14/03/04 (in the db - dd/mm/yy) <> 03/14/04 (from the sql query mm/dd/yy).
sql = "Select DateAtt from Att where DateAtt = #" & dateEntered.text & "#"
Is there a way of getting around this problem and could you provide the sql for this? Could you use the date type instead of literals?
Also is there a way to select a date where the day is not known therefor bring back all records which have a month 03 and year 04. So in other words is there a way to put the % infront of the date selected (in this case just being the month and year). So it would look like %/03/04?
Thanks in advance!
Searching An Access Date/Time Field?
Good Morning.
I am having trouble searching an Access(2000) DataTime Field.
The field holds both the Date and Time: But I only want to search this field by the Date.
Below is my search string it returns no records. (Does not give an error, just no records.)
searchsql = "select * from slog where wdate=" & Chr$(35) & FormatDateTime(DTPicker2.Value, vbShortDate) & Chr$(35)
Select Statement With A Date/Time Field
Howdy!
I want to pull all records from earlier than 2 two days ago.
I am using Access97,with a Date/Time field, but I can not get the thing to work.
Here's what I have
Dim TimeLapse
Dim SQL
TimeLapse = Now - 2
Set adoMasterRS = New Recordset
SQL = "Select * from Master where RowUpdate >= '" & TimeLapse
adoMasterRS.Open SQL, db, adOpenStatic, adLockOptimistic
Thanx
Insertin Date And Time On Datetime Field
Hi everyone,
Do you guys know how to insert date and time values on a single SQL Server datetime field using ASP or VB?
I've tried using Convert but it doesn't seem to work... I always get error messages.
Thanks a lot.
Otavio
Comparing Date Time Stamps Field
I have a date time stamp field in my database. And i need to count calls for one day. So my sql statement looks something like this :
Code:
Dim bdwsmtCon As New Odbc.OdbcConnection("Driver={MySQL ODBC 3.51 Driver};server=192.168.2.5;port=3306;database=bdwsmt;pwd=knicks24;uid=tmstms;")
Dim bdwsmtCommande As New Odbc.OdbcCommand
Dim bdwsmtReader As Odbc.OdbcDataReader
Dim strSQL As String 'Select statment
Dim curDate As String 'curDate
Dim endDate As String 'end string for sql statment
curDate = startDateTimePicker.Value.AddDays(dblStartDay).ToString("M/d/yyyy")
endDate = startDateTimePicker.Value.AddDays(dblEndDay).ToString("M/d/yyyy")
strSQL = "SELECT Count(*) AS MyCount FROM wab_answers WHERE answerTime > #" & endDate & " 11:59:59 PM# AND answerTime < #" & curDate & " 11:59:59 PM# AND wabScreen=1;"
MsgBox(strSQL)
bdwsmtCommande = New Odbc.OdbcCommand(strSQL, bdwsmtCon)
bdwsmtCommande.Connection.Open()
bdwsmtReader = bdwsmtCommande.ExecuteReader(CommandBehavior.CloseConnection)
The error is the # # round my date and time. I tried to use ' ' around them but that just gave me 0 for the count. Any Ideas?
Ado (command) Updating Date/time Field
Hi,
I'm trying to update a field of type date/time to null value using sql query.
my sql query:
"Update jobs Set mydate.value = DBNull" ....
This doesn't work!
What do I need in this sql statement so that I can update date/time field with nothing (empty).
Thanks.
Activex Control For Date/Time Field
Hi,
Can anybody suggest me an activex control accepting Date and Time. For Date i found many Callendar controls(eg. sheridan) but i need a similar control for Time. All suggestions are welcome.
Thanks in Advance
Chandu
Returning A Date/Time Field To Empty
I am accessing a database in the following manner:
set db = OpenDatabase("c:Databasename.mbd", false)
set rec = db.openrecordset("tblName",dbOpenDynaset)
rec.MoveFirst
Do Until rec.eof
With rec
.Field("date")=vbNull
.MoveNext
End With
Loop
It continues to fill the date with "12/31/1899"
I can't figure out how to just delete the information in the field so that it is empty.
Jason Hatt
Create Date/Time Field And Loading Default Value
I am trying to add some fields programmatically to a database using DAO. I need to load some default value to the date/time fields in order that they ar not null. Here is what I tried:
xs = ""
On Error Resume Next
xs = db.TableDefs("Department").Fields("begHH").Name
On Error GoTo 0
If xs <> "reciprocal" Then
db.TableDefs("Department").Fields.Append _
db.TableDefs("Department").CreateField("begHH", dbDate / Time)
db.TableDefs("Department").Fields("begHH").DefaultValue = "12:00 AM"
'db.Execute "UPDATE Department SET begHH= "12:00 AM" WHERE
begHH IS NULL", dbFailOnError
End If
It does not like the string of "12:00 AM" at all. Does anyone know what value I should put in the newly created fields. Thanks fo rthe help.
Excel Won't Accept Date Time Field From SQL Server
Hi
I'm workin on a project at the moment that requires that I can write the contents of a datagrid to an excell work book, I have most of the code sorted however excel does not show the date time fields pulled from the SQL server. Here is my code
Private Sub btn_dumptoexcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_dumptoexcel.Click
Try
'Variables
Dim ExcelApp As New Excel.Application
Dim Wkbook As Excel.Workbook
Dim Wksheet As Excel.Worksheet
'Make the Excel Application visible
ExcelApp.Visible = True
'Get a new workbook and select the first sheet
Wkbook = ExcelApp.Workbooks.Add
Wksheet = ExcelApp.ActiveSheet
Dim dt As DataTable = ds.Tables(dsTableName) 'Dataset name used to populate the datagrid
Dim col As DataColumn
Dim n As Integer
'Add field names to each cell
For n = 0 To dt.Columns.Count - 1
Wksheet.Cells(1, n + 1).value = dt.Columns(n).ColumnName
Next
'Loop through the rows in the table and put the data into their cells
Dim row As DataRelation
For n = 0 To dt.Rows.Count - 1
Wksheet.Range("A" & n + 2, "G" & n + 2).Value = dt.Rows(n).ItemArray
Next
'Format field names as bold and center
With Wksheet.Range("A1", "L1")
.Font.Bold = True
.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
.Font.Size = 12
End With
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Anyone got any ideas or see where I'm going wrong?
Thanx
Phil
Inserting NULL Value Into Date/time Field - Sybase -
i cannot insert a null value into a date/time field in a sybase database
Code:
'STORED PROCEDURE
sybObj.CommandText = "Data_Put"
acci_insert(0) = "Null" 'GET IMPLICIT CONVERSION ERRORS
acci_insert(0) = "Null" 'TRIED Null, NULL, DBnull.value, "", '' - nothing works
'EXECUTE STORED PROCEDURE
sybObj.Execute , acci_insert
The field has been set to allow Null values.
Edited by - AbbydonKrafts on 3/20/2007 6:21:53 AM
Setting Label.Caption From A Date/Time Field Value
Hi,
I need to set a labe's caption to a date/time field from a SQL database. I have setup the connetion string and all and I define this variable in VB:
Dim dtDate As Date
dtDate = rsInfo.Fields("UploadDate").Value
The Field "Upload Date" looks like this:
4/27/2001 11:12:09 AM
But my label's caption show up like this:
12:00:00 AM
How can I set it correctly????? Please Help,
Herick
Dataformat = Date For Text Field
I have an app where a user must select "To" and "From" dates (using a calendar control) in a text field. I have set the dataformat property to date (format 07/07/2002) at design time.
I don't want the user to select today or a future date in the from field so I use this code:
If Text2.Text >= Format(Now, "Mm" & "/" & "Dd" & "/" & "Yy") Then
MsgBox "Cannot select present day or a future date.", vbExclamation, "Warning"
Text2.SetFocus
Exit Sub
End If
that works fine unless the user selects a date that does not have a 2 digit day (10/9/2002). Then it gives the above error even though it's not todays date or a future date.
I notice that when they select the date form the calendar, it populates the text field with the date, but not in the format I have chosen. It should read 10/09/2002 but reads 10/9/02.
any ideas?
How To Create Date Format In Text Field.
Hi,
I want to set date format in text field so that user can enter value only in the given format in given space. Like dd-mm-yy, means text field should show 2 blank space then - again 2 blank space then - and agin 2 blank space. I also want it should only take numbers in given space. characters entry should not be allowed.
please help me out for the above problem.
thank you,
Shiva
Run Time 13 - Text Field
Ok this should be a simple one.
I have alot of text boxes that are treated the same and need a way of keeping them the same during entry. The dataformat is currency, so I need them two decimals. If nothing is put in those fields then I am getting a runtime error (type mismatch) when submit (actually when put into an integer that is defined as currency). I need them all to be 2 decmals also. Is there a loop or something to that effect that I can frun these thru to evaluate them as 0 and also make sure they have 2 decimals for valid fields?
txtField1
txtField2
etc
Thanks Again !
Run Time Error Due To Text Field!!
Hello all,
I need an urgent help.
I am using MS Access & vb6.0 in my project. when I tried to insert a text field with a single quote (') placed in the data,
E.g.:1.don't 2.I've
it is giving Run time error:-214721900(80040e14)
Syntax error(missing opereator) in query expression.
Can anybody suggest me how to handle this error?
Thanks in advance
Regards
REQ Help: Calculating Elapsed Time From Date/time Began From Date/time Ended
I have four controls, two are Date-Time Pickers set for Date and the last two are text boxes set for Time with the "...If Not IsTime()..." validation.
It is the standard Date and Time problem began, Date and Time problem ended.
I am trying to calculate the elapsed time between these in "" DAYS "" HOURS ""MINUTES.
I coded this in Lotus Notes yet exporting my knowledge to VB is problematic.
Has anyone a code snippet of how to do this in VB - yes, I know about the DateDiff.
Many, MANY thanks!
Inputing A Date Time Text
Im using VB6 to SQL 2000
What I need help with is.
Im trying to use a Text box to put a time into a table but when it goes in it puts 1/1/1900 in as well infront of the time.
Please how can I stop this
Thanks in advance
Time And Date Text Boxes
H, I have 2 text boxes one for date and one for time the user has to enter a date and time manually, I was wondering if anyone has any suggestions on how to make these boxes more user friday e.g some kind of guideline to tel the user what format to enter the date or something p.s the data is saved to a database when entered. I have allready set the fields to Time/Date in the Database design.
Thanks
How To Display Date And Time In Two Text Box?
i want to display current date in the date text box and time in the time text box, but when i use the code below, it comes out the date and time together in one text box, how to split them??
Dim dttoday As Date
dttoday = Now
Text1.Text = dttoday
Storing A UDT In An OLEObject Field
Question?
I need to store a UDT in an MS Access table. How would I go about doing this? I suppose I would use an OLEObject (BLOB), but how would I go about Inserting and Retrieving the data from the table?
Thanks in advance
- Eric
Storing Calculated Field
Hello all,
I have a calculated field on a form, which I would like to store in a field in a table. I would think that this'd be a pretty common thing to do, but I haven't found any help files or threads that demonstrate how to do this. The field is a textbox and the expression is (of course) in the ControlSource property. Any suggestions for how I can store that field in a table? Thank you!
Cheers,
Stephanie
Edited by - CcSteff on 8/19/2004 4:35:32 AM
VB6 - WRITING TO A TEXT FILE -Date/Time Data
I am trying to write data to a text file by way of a query. The data is coming from joined Access 2000 tables. Everything writes to the text file properly except the 2 fields that are of DATE/TIME data type. They appear with # on either end. ie: #2003-06-15#.
It needs to appear mm/dd/yyyy without the #s. Please help!
Here is output sample and a code sample.
OUTPUT:
"10129498","C","PRUITT ","MARK ","12487 GLENGATE ","3142051540","M",#2003-06-15#,#2039-12-31#,"24.88","15"
CODE:
Open strFileName For Output As #1
Do Until strCurrent.EOF
strCurrent("account"), strCurrent("status"), strCurrent("lname"), strCurrent("fname"), strCurrent("addr"), strCurrent("hphone"), strCurrent("sex"), strCurrent("beg"), strCurrent("exp"), strCurrent("pay"), strCurrent("due"), strCurrent("eftrt"), strCurrent("accountnum"), strCurrent("ccexp"), strCurrent("email")
strCurrent.MoveNext
Loop
Close #1
QUERY:
"SELECT e.client_no as account, e.acct_type as status, e.last_name as lname, e.first_name as fname, c.address as addr, c.city, c.state, c.zip, (c.[hphoneac] & c.[hphone]) as hphone, c.wphone, c.gender as sex, e.start_date as beg, e.end_date as exp, e.monthly_fee as pay, e.billing_day as due, e.bank_aba as eftrt, e.acct_no as accountnum, e.acct_exp as ccexp, c.userd4 as ClubID, c.userd5 as AsfAcctNo, c.hemail as EMail FROM eft AS e INNER JOIN client_profile AS c ON e.client_no = c.client_no WHERE e.last_mdt >= #" & txtAsOfDt.Text & "# OR c.last_mdt >= #" & txtAsOfDt.Text & "# ORDER BY e.client_no"
Thanks All!
Find Date && Time From System And Display In Text Box
I am trying to retrieve the current date and time from the system and display it in a text box called 'txtDate'. I want the current date and time to show in this field when the program runs. It sounds really easy but I can not find any help through the MSDN help section.
Any help that is provided is GREATLY appreciated! Thanks!
How To Save A Text File With Date And Time As Filename
Hi, Plz help me out.
How to save a text file with date and time as filename.
My code is:
dt = Date
tt = Time
intFile = FreeFile()
Open App.Path & "" & dt & tt &.txt" For Output As #intFile
Write #intFile, sometext
Close #intFile
But it is not working.
Thanks in advance
VB6 - WRITING TO A TEXT FILE -Date/Time Data
I am trying to write data to a text file by way of a query. The data is coming from joined Access 2000 tables. Everything writes to the text file properly except the 2 fields that are of DATE/TIME data type. They appear with # on either end. ie: #2003-06-15#.
It needs to appear mm/dd/yyyy without the #s. Please help!
Here is output sample and a code sample.
OUTPUT:
"10129498","C","PRUITT ","MARK ","12487 GLENGATE ","3142051540","M",#2003-06-15#,#2039-12-31#,"24.88","15"
CODE:
Open strFileName For Output As #1
Do Until strCurrent.EOF
strCurrent("account"), strCurrent("status"), strCurrent("lname"), strCurrent("fname"), strCurrent("addr"), strCurrent("hphone"), strCurrent("sex"), strCurrent("beg"), strCurrent("exp"), strCurrent("pay"), strCurrent("due"), strCurrent("eftrt"), strCurrent("accountnum"), strCurrent("ccexp"), strCurrent("email")
strCurrent.MoveNext
Loop
Close #1
QUERY:
"SELECT e.client_no as account, e.acct_type as status, e.last_name as lname, e.first_name as fname, c.address as addr, c.city, c.state, c.zip, (c.[hphoneac] & c.[hphone]) as hphone, c.wphone, c.gender as sex, e.start_date as beg, e.end_date as exp, e.monthly_fee as pay, e.billing_day as due, e.bank_aba as eftrt, e.acct_no as accountnum, e.acct_exp as ccexp, c.userd4 as ClubID, c.userd5 as AsfAcctNo, c.hemail as EMail FROM eft AS e INNER JOIN client_profile AS c ON e.client_no = c.client_no WHERE e.last_mdt >= #" & txtAsOfDt.Text & "# OR c.last_mdt >= #" & txtAsOfDt.Text & "# ORDER BY e.client_no"
Thanks All!
Storing Command In Database Field
ok, this might seem weird but this is what i want to do...
I have multiple formats of fixed width files i want to read into a common table.
i do someting like this currently:
dim rs as adodb.recordset
set rs = new adodb.recordset
rs.open "Table", myconn
open Filename for input as #1
do until EOF(1)
line input #1, data, adOpenDynamic, adLockBatchOptimistic
rs.addnew
rs!Field1 = trim(mid(data, 1, 5))
rs!Field2 = trim(mid(data, 6, 5))
rs!Field3 = trim(mid(data, 11, 10))
loop
close #1
rs.updatebatch
set rs = nothing
to avoid making a script for each file, i want to know how to store the "trim(mid(Data, 1, 5)) portion in a database table so i can reuse the same file reading function as well as not have to recompile my software every time the format changes. is this possible?
please help.
|
TRACKER
