Post your code for posterity?? And offcourse final analasys
mailman ok so nearly final product on this code.
Code:
Public Function SALES(Journal As String)
FileCopy Journal, "C:\TFRS REPORTS\AJ1.CSV"
Name Journal As Left(Journal, 21) & "old"
DoCmd.TransferText acImportDelim, "aj1import", "tblAJLTEMP", "C:\TFRS REPORTS\AJ1.CSV"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim RsSales As DAO.Recordset
Dim RsAccounts As DAO.Recordset
Dim RsFunctions As DAO.Recordset
Dim RsItems As DAO.Recordset
Dim RsMembers As DAO.Recordset
Dim RsRounding As DAO.Recordset
Dim RsString As DAO.Recordset
Dim RsTables As DAO.Recordset
Dim RsTenders As DAO.Recordset
Dim RsGst As DAO.Recordset
'Dim RsQuery As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim tbl As String
Dim strsql As String
Dim SALESID As Long
Dim delSQL As String
Dim qdel As DAO.QueryDef
tbl = "tblAJLTEMP"
Set db = CurrentDb
'Set qdf = db.QueryDefs("qryupdSALESTBL")
'Set qdel = db.QueryDefs("qrydeleteall")
Set rs = db.OpenRecordset("tblAJLTEMP", dbOpenTable)
Set RsSales = db.OpenRecordset("tblSales", dbOpenTable)
Set RsAccounts = db.OpenRecordset("tblACCOUNTTRX", dbOpenTable)
Set RsFunctions = db.OpenRecordset("tblFUNCTIONS", dbOpenTable)
Set RsItems = db.OpenRecordset("tblITEMSALES", dbOpenTable)
Set RsMembers = db.OpenRecordset("tblMEMBERTRX", dbOpenTable)
Set RsRounding = db.OpenRecordset("tblROUNDING", dbOpenTable)
Set RsString = db.OpenRecordset("tblString", dbOpenTable)
Set RsTables = db.OpenRecordset("tblTABLENUMBER", dbOpenTable)
Set RsTenders = db.OpenRecordset("tblTENDERS", dbOpenTable)
Set RsGst = db.OpenRecordset("tblGST", dbOpenTable)
'Set RsQuery = DB.OpenRecordset("SELECT * FROM tblSALESID ORDER BY SALESID", dbOpenDynaset)
Do While Not rs.EOF
Select Case rs!Field1
Case "H"
RsSales.AddNew ' Start a new record
RsSales!TRXTILLID = rs!Field2
RsSales!TRXNUMBER = rs!Field3
RsSales!TRXTIME = (rs!Field4 & ":" & rs!Field5)
RsSales!TRXDATE = (rs!Field6 & "/" & rs!Field7 & "/" & rs!Field8)
RsSales!TRXLOCATION = rs!Field9
RsSales.Update ' Close the previous record)
Case "P"
txtprice = rs!Field2
Case "C"
txtCASHIER = rs!Field2
Case "I"
RsItems.AddNew
RsItems!PLU = rs!Field2
RsItems!QUANTITY = rs!Field3
RsItems!AMOUNT = rs!Field4
RsItems!pricelevel = (txtprice)
RsItems!cashier = txtCASHIER
RsItems!SALESID = DMax("tblSALES.SALESID", "tblSALES")
RsItems.Update
Case "D"
RsTables.AddNew
RsTables!TABLENUMBER = rs!Field2
RsTables!SALESID = DMax("tblSALES.SALESID", "tblSALES")
RsTables.Update
Case "R"
RsRounding.AddNew
RsRounding!AMOUNT = rs!Field2
RsRounding!SALESID = DMax("tblSALES.SALESID", "tblSALES")
RsRounding.Update
Case "T"
Select Case rs!Field2
Case Is = "65496"
RsGst.AddNew
RsGst!TenderType = rs!Field2
RsGst!AMOUNT = rs!Field3
RsGst!SALESID = DMax("tblSALES.SALESID", "tblSALES")
RsGst.Update
Case Else
RsTenders.AddNew
RsTenders!TenderType = rs!Field2
RsTenders!AMOUNT = rs!Field3
RsTenders!SALESID = DMax("tblSALES.SALESID", "tblSALES")
RsTenders.Update
End Select
Case "A"
RsAccounts.AddNew
RsAccounts!ACCOUNT = rs!Field2
RsAccounts!AMOUNT = rs!Field3
RsAccounts!SALESID = DMax("tblSALES.SALESID", "tblSALES")
Case "M"
RsMembers.AddNew
RsMembers!MEMBER = rs!Field2
RsMembers!Points = rs!Field3
RsMembers!SALESID = DMax("tblSALES.SALESID", "tblSALES")
RsMembers.Update
Case "S"
RsString.AddNew
RsString!PLU = rs!Field2
RsString!INPUT = rs!Field3
RsAccounts!SALESID = DMax("tblSALES.SALESID", "tblSALES")
RsString.Update
Case "L"
RsFunctions.AddNew
RsFunctions!KEYTYPE = rs!Field2
RsFunctions!Function = rs!Field3
RsFunctions!SALESID = DMax("tblSALES.SALESID", "tblSALES")
RsFunctions.Update
End Select
rs.MoveNext
Loop
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDELTEMP"
DoCmd.Close
DoCmd.SetWarnings True
Kill "C:\TFRS REPORTS\aj1.csv"
End Functionso my only question is now the text file i am importing has dates from 2 days the reason for this is that the sales are from pubs where the trading of course runs past midnight, my question is should i leave the date as is in the text file or change it and just make my reports take the trading day end time into account
I am kinda guessing the 2nd way but just want to make sure and just am after opinions on my code
thanks
'
