QueryExport and Input# Issue

[mrazanaqvee]

Hi
Here is what I am trying to achieve:

1) Export a query in CSV format to a temporary file
DoCmd.TransferText acExportDelim, , "qryFileExport", "C:\STH\SAureus.tmp"


2) Read each record in recordset
ReadF = "C:\STH\SAureus.tmp"
Open ReadF For Input As i
Do While Not EOF(i)
Input #i, Lab, OREQ, NHS, Hosp, DOB, Loc, DTR, SpecType, SpecSite, Surname, Forename, Unit

3) Assess if this recordset (Labno unique) already exists
If DCount("[Lab No]", "Details", "[Lab No] = '" & Lab & "'") > 0 Then List = List & Lab & ", ": E = E + 1: GoTo Assess14days:
If DCount("[Lab No]", "Details", "[Lab No] = '" & Lab & "'") = 0 Then GoTo
AddSample:

It was working fine till I added another date field in the query. The result I am now getting from the query are:

"MB00011F","643232322B","1010101010","KZ3393",01/01/1901 00:00:00,"WN",16/06/2016 20:18:00,"MBLD","PERIPHERAL","TESTPATIENT","LIVE","HOSPA"

You can see here the dates are not being enclosed in quote marks which means my reading routine is simply getting screwed as the code gets confused later on. How can I enclose the date field in quotes and for the purpose of the Input# command use it as a string but later on in the code maintain its date sanctity?

Any help greatly appreciated.

mrazanaqvee

____________________________________________________________

This is the full code:
Private Sub Command22_Click()
Dim i As Integer
Dim ReadF As String
Dim WritePath As String
Dim Lab As String, Temp As String, TempCount As Variant, TempSurname As String
Dim OREQ As String
Dim NHS As String
Dim Hosp As String
Dim DOB As String
Dim Loc As String
Dim DTR As Variant
Dim SpecType As String
Dim SpecSite As String
Dim Surname As String
Dim Forename As String
Dim Unit As String
Dim DTC As Variant, TempDTR As Variant
Dim List As String
Dim ListDone As String
Dim a As Integer, DaysDiff As Integer
Dim E As Integer
DoCmd.SetWarnings (False)
i = FreeFile
a = 0
E = 0
List = ""
ListDone = ""
Me.lstMyList.Visible = False
Me.lstMyList2.Visible = False

ReadF = "C:\STH\SAureus.tmp"
DoCmd.TransferText acExportDelim, , "qryFileExport", "C:\STH\SAureus.tmp"

Open ReadF For Input As i
Do While Not EOF(i)
Input #i, Lab, OREQ, NHS, Hosp, DOB, Loc, DTR, SpecType, SpecSite, Surname, Forename, Unit

If DCount("[Lab No]", "Details", "[Lab No] = '" & Lab & "'") > 0 Then List = List & Lab & ", ": E = E + 1: GoTo Assess14days:
If DCount("[Lab No]", "Details", "[Lab No] = '" & Lab & "'") = 0 Then GoTo AddSample:

AddSample:
a = a + 1
If [Surname] Like "*'*" Then GoSub UpdateSurname:

DoCmd.RunSQL "INSERT INTO [Details]([Lab No], [OREQ], [Last Edited by], [Date Last Edited], [NHS], [Hosp], [DOB], [Location], [DTR], [SpecType], [SpecSite], [Surname], [ForeName], [Unit]) Values ('" & Lab & "' , '" & OREQ & "', 'Automatic', " & Format(Now(), "\#mm\/dd\/yyyy\ hh\:nn\:ss\#") & ", '" & NHS & "', '" & Hosp & "', #" & DOB & "#, '" & Loc & "', " & Format(DTR, "\#mm\/dd\/yyyy\ hh\:nn\:ss\#") & ", '" & SpecType & "', '" & SpecSite & "', '" & Surname & "', '" & Forename & "', '" & Unit & "' );"
ListDone = ListDone & Lab & ", "
GoTo Moveon:

Assess14days:
TempDTR = DLookup("[DTR]", "Details", "[Lab No] = '" & Lab & "'")
DaysDiff = Abs(DTR - TempDTR)
If DaysDiff > 14 Then GoTo AddSample:

Moveon:
Loop
Me.lstMyList2.Visible = True
Me.lstMyList2.RowSourceType = "Value List"
Me.lstMyList2.RowSource = List

Me.lstMyList.Visible = True
Me.lstMyList.RowSourceType = "Value List"
Me.lstMyList.RowSource = ListDone

Me.txtAdded.Visible = True
Me.txtAdded = a
Me.txtExist.Visible = True
Me.txtExist = E
Close i
If IsLoaded("fmStaphAur") Then [Forms]![fmStaphAur].Requery
GoTo Finish:

UpdateSurname:
TempCount = InStr(Surname, "'")
Temp = Mid(Surname, 1, TempCount - 1)
Temp1 = Right(Surname, Len(Surname) - TempCount)
TempSurname = Temp & Temp1
Surname = TempSurname
Return



Finish:
DoCmd.SetWarnings (True)
End Sub

 

[Uncle Gizmo]

How about CDate(DTR)

more Here:- http://www.access-programmers.co.uk/forums/showthread.php?t=204287

 

[arnelgp]

one note if you have exported your table to a csv file, you can read them back as a recordset.

dim db as dao.database
dim rsCSV as dao.recordset
set db = currentdb
set rsCSV = db.OpenRecordset( _
"SELECT * " & _
"FROM [Text;HDR=Yes;IMEX=2;ACCDB=YES;DATABASE=C:\STH\].[SAureus#tmp]")

If Not (rsCSV.BOF And rsCSV.EOF) Then rsCSV.MoveFirst
While Not rsCSV.EOF
...
...
rsCSV.MoveNext
Wend
rsCSV.Close
Set rsSCV=Nothing
Set db=Nothing

 

[mrazanaqvee]

Thanks both. I did try CDate but couldn't get it to work as the date item was not being correctly read by the Input# command.
Didn't know we could use CSV as a recordset, will try it and report back.

 

[mrazanaqvee]

Arnelgp, it worked like a treat.

Interested people may want to note that to reference individual items (fields) in each recordset, you have to use this: rsCSV(0), rsCSV(1) and so on. So the first field in the recordset will be rsCSV(0), the second rsCSV(1) and so on.

 

[Uncle Gizmo]

Didn't know we could use CSV as a recordset

Neither did I!