date field data not being written to access table.

[locutus]

Hi

Bit of a prob !
I have two tables , when I transfer the date field in table one which is a text field ( looks like this 20000801 ) into the table two which is waiting to recieve the data and the date field here is set up as a proper date with the format
yyyymmdd , but the text date never gets written to the sister field. Why wont it do this and can I get round this ?
How ?

This is also happening when I try to import my linked table ( which is linked to a text file ) into my real table. The same thing ! the text date can't be witten to the date formatted field.

Thanks
Locutus

[gerard]

Are you converting the text date to a date format in vb first before transferring it accross. If you are sending it to access youu may need to format as follows:

#dd/MM/yyyy#

Regards



Gerard

[locutus]

Thanks for your reply, I am not doing anything in VB to the text date field, and am simply doing an insert into sql that appends the text version of a record into the correctly formatted table.

ie
linked text file:
age name DOB
23 Mark 19690730

all fields here are text.

now I transfer the data into another table with an insert into command.

the new table has the same field layout except age is a number, name is text and DOB is a date formatted yyyymmdd
the only field lost on this transfer is the DOB transfer from text to date. Is this clear ?

Also to populate the database in the first place I copy a huge text version of it and do a paste data to existing table , again all date from text fields are lost.

Any insight
locutus

[gerard]

Not sure if this will help..below is a query that I had trouble with. the dtformat is a date varible I had to ensure that my date looke like this

#01/01/2000 PM#

If I left the AM or PM off, the query failed.


Dim dtPalmDate As String
Dim dtformat As Date

dtformat = Mid(MainMenu.cboPalm.Text, 1, 21)'cboPalm is a combo box

dtPalmDate = "#" & dtformat & "#"




dbs.Execute "INSERT INTO DRNITBL3 ( HOSPCODE, MRN, WARD, UNIT, ADMIT, DISCHARGE, SURVEILTYP, EVENTDATE, DEVICCAT, DEVICE, IMPREGNATE, REWIRE, PACINGWIRE, LUMENS, Drnitbl3bu.IMPREGNATE, Drnitbl3bu.REWIRE, Drnitbl3bu.PACINGWIRE, Drnitbl3bu.LUMENS, Drnitbl3bu.INSERTSITE, Drnitbl3bu.WARDINSERT, & "From Drnitbl3bu Where Drnitbl3bu.PalmName = " & "'" & strPalmName & "'" _
& " and Drnitbl3bu.DateHotSync=" & dtPalmDate & ""


Canyou show me any of the code you are using...?


Regards

Gerard

[locutus]

I have two tables, qcstore which is linked to an external text file and qcdata which is the real table inside the DB. QCstore acts as a halfway house , it recieves the text file and then loads the text data over into the proper table which is formatted etc. so the field names are exactly the same in both tables except the formatting of the fields is different.

the code says:
if the record current record in qcstore is not already in qcdata then insert into qcdata the current record from qcstore.
else
delete all occurences of this reelnumber first then insert the record in ( this will be updated data every two hours )

when the record from qcstore is written to qcdata the text date is not transfered to the date field in qcdata:

the fields in each table are called testdate and testtime.


For intcount = 1 To intreccount

ProgressBar.Value = rsqcstore.PercentPosition
Set rsTemp2 = reeldata_db.OpenRecordset _
("SELECT COUNT(*) As RecCount " _
& "FROM qcdata WHERE ReelNumber = '" _
& rsqcstore!reelNumber & "'")

rsTemp2.MoveLast


If rsTemp2!RecCount = 0 Then

StrSQL2 = "INSERT INTO qcdata SELECT qcstore.* FROM qcstore " & _
"WHERE ReelNumber = '" & rsqcstore!reelNumber & " '"

reeldata_db.Execute StrSQL2

Else

sqldelete2 = " Delete * from qcdata where reelnumber = '" & rsqcstore!reelNumber & " '"
reeldata_db.Execute sqldelete2



sqlinsert2 = "INSERT INTO qcdata SELECT qcstore.* FROM qcstore " & _
"WHERE reelNumber = '" & rsqcstore!reelNumber & " '"
reeldata_db.Execute sqlinsert2

End If

rsqcstore.MoveNext
DoEvents
'Loop
Next intcount

[JHausmann]

I suspect it's unable to insert the date because it doesn't look like a date (by format (xx/xx/xxxx, for example)) I'd even expect CDate(20000810) to complain. You could probably write a routine that parese the field and sets it to a more date-like value (using instr, for example) and then update the database with the new data.