Left, Right, Mid in Forms VB Coding

[Kensgracie]

The following code works great. Until I try to add two more fields. I just
can’t seem to get it right. It's because of the reformatting, I think. The
fields, in the linked txt file are data type Text.

I want to add to the INSERT stmt AcctNbr and EntryDt. The Select stmt needs
something like;
& InFile & (Right,(AcctNbr,6) AS AcctNbr), & InFile & (Mid(EntryDtA,5,2) &
“-“ & Right(EntryDtA,2) & â€-“ & Left(EntryDtA,4)) As EntryDt).

I’ve tried a number of different combinations and have read quite a few
posts but with no success.

Can someone please clue me in on how to get these two fields to work?

Thanks.

Dim strSQL As String
Dim InFile As String
Dim iLoop As Long
Dim DbAny As DAO.Database

Set DbAny = CurrentDb()

'Start loop at 2. The first file was has already been exported to GLYr5 in
SQL, and that table linked to this db.
For iLoop = 2 To 52
InFile = "D" & Format(iLoop)

' This appends data to the SQL table GLYr5.

strSQL = "INSERT INTO GLYr5 ( CoCd, AcctTyp, ProfCntr, CostCntr, FY,
Period, DocNbr, DocTyp, LnNbr, DrCr, " & _
"Amt, TranCd, RefDocNbr, RevDocNbr, DocHdrTxt, ClearingEntryDt,
DocNbrofClearingDoc, PostKey, AssgnmntNbr, " & _
"ItemTxt, OrderNbr, BillingDoc, NetPmtPeriod, ReasonCodeforPayments,
NetPmt )" & _
"SELECT " & InFile & ".CoCd, " & InFile & ".AcctTyp, " & InFile &
".ProfCntr, " & InFile & ".CostCntr, " & _
InFile & ".FY, " & InFile & ".Period, " & InFile & ".DocNbr, " & InFile &
".DocTyp, " & InFile & ".LnNbr, " & _
InFile & ".DrCr, " & InFile & ".Amt, " & InFile & ".TranCd, " & InFile &
".RefDocNbr, " & InFile & ".RevDocNbr, " & _
InFile & ".DocHdrTxt, " & InFile & ".ClearingEntryDt, " & InFile &
".DocNbrofClearingDoc, " & InFile & ".PostKey, " & _
InFile & ".AssgnmntNbr, " & InFile & ".ItemTxt, " & InFile & ".OrderNbr,
" & InFile & ".BillingDoc, " & _
InFile & ".NetPmtPeriod, " & InFile & ".ReasonCodeforPayments, " & InFile
& ".NetPmt FROM " & _
InFile & ""

DbAny.Execute strSQL, dbFailOnError

Next iLoop

 

[Jeanette Cunningham]

Hi Kensgracie,
the problem will be with the double quotes.
Replace each single double quote with a pair of double quotes-->

& ""-""& Right(EntryDtA,2) & ""-"" & Left(EntryDtA,4)) As EntryDt).

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Kensgracie]

Thanks for your suggestion. But that did not work either. i get 'Expected
end of statement", I inpput & InFile & (Mid(EntryDta,5,2) & ""-"" &
Right(EntryDtA,2) & ""-"" & Left(EntryDtA,4)) As EntryDtA, It doesn't like
the "As".

Also, if you would, how do I handle the AcctNbr situation?

I appreciaste your help.

 

[Jeanette Cunningham]

I should have looked more carefully at *all* your code.

You need to add the names of the 2 extra fields to the first part of the
insert statement like this-->
strSQL = "INSERT INTO GLYr5 ( CoCd, AcctTyp, ProfCntr, CostCntr, FY,
Period, DocNbr, DocTyp, LnNbr, DrCr, " & _
"Amt, TranCd, RefDocNbr, RevDocNbr, DocHdrTxt, ClearingEntryDt,
DocNbrofClearingDoc, PostKey, AssgnmntNbr, " & _
"ItemTxt, OrderNbr, BillingDoc, NetPmtPeriod, ReasonCodeforPayments,
NetPmt , [stmt AcctNbr], EntryDtA)" & _

Note: the above assumes that there is a table field called stmt AcctNbr - as
2 words and another field called EntryDtA


Then you have to add the values of those 2 fields to the Select part of the
insert statememt.
Now I am assuming that AcctNbr and EntryDtA are both string data type

I find it easier to use variables like this-->
Dim strAcctNbr as String
Dim strEntryDtA as Variant

strAcctNbr = Right,(AcctNbr,6)
varEntryDtA = Mid(EntryDtA,5,2) & "-" & Right(EntryDtA,2) & "-" &
Left(EntryDtA,4)) As EntryDtA

The end of the select statement will look something like this-->

& InFile & ".ReasonCodeforPayments, " & InFile & .NetPmt, """ & strAcctNbt &
& """, """ & strEntryDtA & """ FROM


Hope this is enough help for you to use and adapt to get it to work.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Kensgracie]

:Jeanette,

I really appreciate your lastest response, but this still does not work.
I have the fields in the INSERT INTO statement and they are in the SQL table.

EntryDtA is in the incoming file and EntryDt is in the SQL table. That's
why I thought I had to use the AS EntryDt. I'm reformatting EntryDtA to
input into SQL GLYr5.EntryDt. As I am AcctNbrA to AcctNbr. It appears the
AS statement is the problem. That is what is highlighted with the error
Expected: end of statement.

I should have looked more carefully at *all* your code.

You need to add the names of the 2 extra fields to the first part of the
insert statement like this-->
strSQL = "INSERT INTO GLYr5 ( CoCd, AcctTyp, ProfCntr, CostCntr, FY,
Period, DocNbr, DocTyp, LnNbr, DrCr, " & _
"Amt, TranCd, RefDocNbr, RevDocNbr, DocHdrTxt, ClearingEntryDt,
DocNbrofClearingDoc, PostKey, AssgnmntNbr, " & _
"ItemTxt, OrderNbr, BillingDoc, NetPmtPeriod, ReasonCodeforPayments,
NetPmt , [stmt AcctNbr], EntryDtA)" & _

Note: the above assumes that there is a table field called stmt AcctNbr - as
2 words and another field called EntryDtA


Then you have to add the values of those 2 fields to the Select part of the
insert statememt.
Now I am assuming that AcctNbr and EntryDtA are both string data type

I find it easier to use variables like this-->
Dim strAcctNbr as String
Dim strEntryDtA as Variant

strAcctNbr = Right,(AcctNbr,6)
varEntryDtA = Mid(EntryDtA,5,2) & "-" & Right(EntryDtA,2) & "-" &
Left(EntryDtA,4)) As EntryDtA

The end of the select statement will look something like this-->

& InFile & ".ReasonCodeforPayments, " & InFile & .NetPmt, """ & strAcctNbt &
& """, """ & strEntryDtA & """ FROM


Hope this is enough help for you to use and adapt to get it to work.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Thanks for your suggestion. But that did not work either. i get
'Expected
end of statement", I inpput & InFile & (Mid(EntryDta,5,2) & ""-"" &
Right(EntryDtA,2) & ""-"" & Left(EntryDtA,4)) As EntryDtA, It doesn't
like
the "As".

Also, if you would, how do I handle the AcctNbr situation?

I appreciaste your help.

 

[DStegon via AccessMonster.com]

your field has a "-" in it????

I inpput & InFile & (Mid(EntryDta,5,2) & ""-"" &
Right(EntryDtA,2) & ""-"" & Left(EntryDtA,4)) As EntryDtA, It doesn't like
the "As".

Also to designate the field and table you need a "." (period) which you dont
have.

InFile & "." & (mid(.....)

Why is the InFile in this at all???? From the look you are trying to get
"values" from a table that you are parsing to insert into the other table,
correct? You would not need the InFile for that situation. It would be just
like inserting todays date into a field in a table while importing other
fields from another table. Example:

We want to take al the employees and add them to the customers table. Since
the customer table has a place for CompanyName, but there are no CompanyName
fields in the Employee table we want to add a "value" to customer table

INSERT INTO Customers ( ContactName, CompanyName )
SELECT [Employees].[LastName] & ", " & [Employees].[FirstName] AS ContactName,
"No Company Name" AS CompanyName
FROM Employees;

See?????

:Jeanette,

I really appreciate your lastest response, but this still does not work.
I have the fields in the INSERT INTO statement and they are in the SQL table.

EntryDtA is in the incoming file and EntryDt is in the SQL table. That's
why I thought I had to use the AS EntryDt. I'm reformatting EntryDtA to
input into SQL GLYr5.EntryDt. As I am AcctNbrA to AcctNbr. It appears the
AS statement is the problem. That is what is highlighted with the error
Expected: end of statement.

I should have looked more carefully at *all* your code.

[quoted text clipped - 114 lines]

 

[Jeanette Cunningham]

I'm not completely following what you are doing.
The first part of the insert into statement must have the exact name of each
field in the table.
So if the field is called AcctNbr and the other field is called EntryDt, the
InsertInto part would look like this:
See how the 2 additional fields are named.
strSQL = "INSERT INTO GLYr5 ( CoCd, AcctTyp, ProfCntr, CostCntr, FY, " & _
Period, DocNbr, DocTyp, LnNbr, DrCr, " & _
"Amt, TranCd, RefDocNbr, RevDocNbr, DocHdrTxt, ClearingEntryDt, " & _
"DocNbrofClearingDoc, PostKey, AssgnmntNbr, " & _
"ItemTxt, OrderNbr, BillingDoc, NetPmtPeriod, " & _
"ReasonCodeforPayments, NetPmt , AcctNbr, EntryDt )"


I'm not sure about needint to use the AS key word for a field, as often I
will append the current date and the name of the current user into a table
without using the AS keyword without any problems.


Still assuming that AcctNbr and EntryDt are string data types.
Change the variable part to -->
strAcctNbr = Right,(AcctNbr,6)
strEntryDt = Mid(EntryDtA,5,2) & "-" & Right(EntryDtA,2) & "-" &
Left(EntryDtA,4))


Now debug just the Select part of this query.
Run the form and paste the query string from the immediate window into a new
query and make sure that access can retrieve the correct records.
Once that is working, it will be easier to get the insert into working.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

:Jeanette,

I really appreciate your lastest response, but this still does not work.
I have the fields in the INSERT INTO statement and they are in the SQL
table.

EntryDtA is in the incoming file and EntryDt is in the SQL table. That's
why I thought I had to use the AS EntryDt. I'm reformatting EntryDtA to
input into SQL GLYr5.EntryDt. As I am AcctNbrA to AcctNbr. It appears
the
AS statement is the problem. That is what is highlighted with the error
Expected: end of statement.

I should have looked more carefully at *all* your code.

You need to add the names of the 2 extra fields to the first part of the
insert statement like this-->
strSQL = "INSERT INTO GLYr5 ( CoCd, AcctTyp, ProfCntr, CostCntr, FY,
Period, DocNbr, DocTyp, LnNbr, DrCr, " & _
"Amt, TranCd, RefDocNbr, RevDocNbr, DocHdrTxt, ClearingEntryDt,
DocNbrofClearingDoc, PostKey, AssgnmntNbr, " & _
"ItemTxt, OrderNbr, BillingDoc, NetPmtPeriod, ReasonCodeforPayments,
NetPmt , [stmt AcctNbr], EntryDtA)" & _

Note: the above assumes that there is a table field called stmt AcctNbr -
as
2 words and another field called EntryDtA


Then you have to add the values of those 2 fields to the Select part of
the
insert statememt.
Now I am assuming that AcctNbr and EntryDtA are both string data type

I find it easier to use variables like this-->
Dim strAcctNbr as String
Dim strEntryDtA as Variant

strAcctNbr = Right,(AcctNbr,6)
varEntryDtA = Mid(EntryDtA,5,2) & "-" & Right(EntryDtA,2) & "-" &
Left(EntryDtA,4)) As EntryDtA

The end of the select statement will look something like this-->

& InFile & ".ReasonCodeforPayments, " & InFile & .NetPmt, """ &
strAcctNbt &
& """, """ & strEntryDtA & """ FROM


Hope this is enough help for you to use and adapt to get it to work.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Thanks for your suggestion. But that did not work either. i get
'Expected
end of statement", I inpput & InFile & (Mid(EntryDta,5,2) & ""-"" &
Right(EntryDtA,2) & ""-"" & Left(EntryDtA,4)) As EntryDtA, It doesn't
like
the "As".

Also, if you would, how do I handle the AcctNbr situation?

I appreciaste your help.

:

Hi Kensgracie,
the problem will be with the double quotes.
Replace each single double quote with a pair of double quotes-->

& ""-""& Right(EntryDtA,2) & ""-"" & Left(EntryDtA,4)) As EntryDt).

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


The following code works great. Until I try to add two more fields.
I
just
can't seem to get it right. It's because of the reformatting, I
think.
The
fields, in the linked txt file are data type Text.

I want to add to the INSERT stmt AcctNbr and EntryDt. The Select
stmt
needs
something like;
& InFile & (Right,(AcctNbr,6) AS AcctNbr), & InFile &
(Mid(EntryDtA,5,2) &
"-" & Right(EntryDtA,2) & "-" & Left(EntryDtA,4)) As EntryDt).

I've tried a number of different combinations and have read quite a
few
posts but with no success.

Can someone please clue me in on how to get these two fields to
work?

Thanks.

Dim strSQL As String
Dim InFile As String
Dim iLoop As Long
Dim DbAny As DAO.Database

Set DbAny = CurrentDb()

'Start loop at 2. The first file was has already been exported to
GLYr5
in
SQL, and that table linked to this db.
For iLoop = 2 To 52
InFile = "D" & Format(iLoop)

' This appends data to the SQL table GLYr5.

strSQL = "INSERT INTO GLYr5 ( CoCd, AcctTyp, ProfCntr, CostCntr,
FY,
Period, DocNbr, DocTyp, LnNbr, DrCr, " & _
"Amt, TranCd, RefDocNbr, RevDocNbr, DocHdrTxt, ClearingEntryDt,
DocNbrofClearingDoc, PostKey, AssgnmntNbr, " & _
"ItemTxt, OrderNbr, BillingDoc, NetPmtPeriod,
ReasonCodeforPayments,
NetPmt )" & _
"SELECT " & InFile & ".CoCd, " & InFile & ".AcctTyp, " & InFile &
".ProfCntr, " & InFile & ".CostCntr, " & _
InFile & ".FY, " & InFile & ".Period, " & InFile & ".DocNbr, " &
InFile
&
".DocTyp, " & InFile & ".LnNbr, " & _
InFile & ".DrCr, " & InFile & ".Amt, " & InFile & ".TranCd, " &
InFile &
".RefDocNbr, " & InFile & ".RevDocNbr, " & _
InFile & ".DocHdrTxt, " & InFile & ".ClearingEntryDt, " & InFile &
".DocNbrofClearingDoc, " & InFile & ".PostKey, " & _
InFile & ".AssgnmntNbr, " & InFile & ".ItemTxt, " & InFile &
".OrderNbr,
" & InFile & ".BillingDoc, " & _
InFile & ".NetPmtPeriod, " & InFile & ".ReasonCodeforPayments, " &
InFile
& ".NetPmt FROM " & _
InFile & ""

DbAny.Execute strSQL, dbFailOnError

Next iLoop

 

[Kensgracie]

I am bring in 52 different fiiles. I have them all linked to Access. They
all have the layout. As linked tables I have them names D1, D2, etc. using
this loop it runs D2, inserts the data into my SQL db and does D3, then D4
and so on. Because of this I thought I had to use InFile for each SELECT
statement.

your field has a "-" in it????

I inpput & InFile & (Mid(EntryDta,5,2) & ""-"" &
Right(EntryDtA,2) & ""-"" & Left(EntryDtA,4)) As EntryDtA, It doesn't like
the "As".

Also to designate the field and table you need a "." (period) which you dont
have.

InFile & "." & (mid(.....)

Why is the InFile in this at all???? From the look you are trying to get
"values" from a table that you are parsing to insert into the other table,
correct? You would not need the InFile for that situation. It would be just
like inserting todays date into a field in a table while importing other
fields from another table. Example:

We want to take al the employees and add them to the customers table. Since
the customer table has a place for CompanyName, but there are no CompanyName
fields in the Employee table we want to add a "value" to customer table

INSERT INTO Customers ( ContactName, CompanyName )
SELECT [Employees].[LastName] & ", " & [Employees].[FirstName] AS ContactName,
"No Company Name" AS CompanyName
FROM Employees;

See?????

:Jeanette,

I really appreciate your lastest response, but this still does not work.
I have the fields in the INSERT INTO statement and they are in the SQL table.

EntryDtA is in the incoming file and EntryDt is in the SQL table. That's
why I thought I had to use the AS EntryDt. I'm reformatting EntryDtA to
input into SQL GLYr5.EntryDt. As I am AcctNbrA to AcctNbr. It appears the
AS statement is the problem. That is what is highlighted with the error
Expected: end of statement.

I should have looked more carefully at *all* your code.

[quoted text clipped - 114 lines]

Next iLoop

 

[DStegon via AccessMonster.com]

I think you are doing this all wrong. Why dont you just open up the other
dbs that you need, open up the table you want to get the data from, open up
the table you want to add it to and just loop through the records adding them
until .EOF. Far more control and you dont have this craziness. Are the
other 52 files in the same directory???

 

[Kensgracie]

The files I am importing are .txt files. I have them all liked to this db.
In fact there are another 52 files I would need to do the same thing to.

I link all of the .txt files to this db. I then rename them D1, D2,.. for
the detail files and L1, L2,.. for the ledger files. If they were in
different db's that would be different. But they are all .txt files. I am
trying to set a routine to import them directly into SQL Server. Everything
works great until I try to manipulate the acctnbr or any of the *Dt fields.

 

[DStegon via AccessMonster.com]

here would be code to open and insert
command0 is a command button on a form.

Private Sub Command0_Click()

Dim rst As New ADODB.Recordset
Dim rstImprt As New ADODB.Recordset
Dim i As Long

With rst
.Open "GLYr5", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
For i = 1 To 52

With rstImprt
.Open "D" & i, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Do Until .EOF
rst.AddNew
rst!CoCd = !CoCd
rst!AcctTyp = !AcctTyp
rst!ProfCntr = !ProfCntr
rst!CostCntr = !CostCntr
rst!FY = !FY
'And so on
rst!AcctNbr = Right(!AcctNbr,6)
rst!EntryDt= Mid(!EntryDta,5,2) & "-" & Right(!EntryDtA,2)
& "-" Left(!EntryDtA,4)
'i am assuming you are taking the month then date then
year because the linked stuff
'is coming yyyymmdd or yyyyddmm ???
rst.Update
.MoveNext
Loop
End With
Next i
End With

End Sub

Sometimes it is easier to write something like this then try and figure out
where in a SQL statement you are going wrong. Code will break at a specific
line and you can put a code break in at the routine itself and walk through
line by line to make sure the data is entering correctly. When all else
fails go back to the basics of DB management... steping through recordset and
adding, editing and saving...

)

The files I am importing are .txt files. I have them all liked to this db.
In fact there are another 52 files I would need to do the same thing to.

I link all of the .txt files to this db. I then rename them D1, D2,.. for
the detail files and L1, L2,.. for the ledger files. If they were in
different db's that would be different. But they are all .txt files. I am
trying to set a routine to import them directly into SQL Server. Everything
works great until I try to manipulate the acctnbr or any of the *Dt fields.

I think you are doing this all wrong. Why dont you just open up the other
dbs that you need, open up the table you want to get the data from, open up

[quoted text clipped - 7 lines]

 

[Kensgracie]

Thank you, thank you, thank you. I have not added this code yet but I will
shortly.

I really appreciate your attention to this post.

Both you and Jenaette have been very helpful!

here would be code to open and insert
command0 is a command button on a form.

Private Sub Command0_Click()

Dim rst As New ADODB.Recordset
Dim rstImprt As New ADODB.Recordset
Dim i As Long

With rst
.Open "GLYr5", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
For i = 1 To 52

With rstImprt
.Open "D" & i, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Do Until .EOF
rst.AddNew
rst!CoCd = !CoCd
rst!AcctTyp = !AcctTyp
rst!ProfCntr = !ProfCntr
rst!CostCntr = !CostCntr
rst!FY = !FY
'And so on
rst!AcctNbr = Right(!AcctNbr,6)
rst!EntryDt= Mid(!EntryDta,5,2) & "-" & Right(!EntryDtA,2)
& "-" Left(!EntryDtA,4)
'i am assuming you are taking the month then date then
year because the linked stuff
'is coming yyyymmdd or yyyyddmm ???
rst.Update
.MoveNext
Loop
End With
Next i
End With

End Sub

Sometimes it is easier to write something like this then try and figure out
where in a SQL statement you are going wrong. Code will break at a specific
line and you can put a code break in at the routine itself and walk through
line by line to make sure the data is entering correctly. When all else
fails go back to the basics of DB management... steping through recordset and
adding, editing and saving...

)

The files I am importing are .txt files. I have them all liked to this db.
In fact there are another 52 files I would need to do the same thing to.

I link all of the .txt files to this db. I then rename them D1, D2,.. for
the detail files and L1, L2,.. for the ledger files. If they were in
different db's that would be different. But they are all .txt files. I am
trying to set a routine to import them directly into SQL Server. Everything
works great until I try to manipulate the acctnbr or any of the *Dt fields.

I think you are doing this all wrong. Why dont you just open up the other
dbs that you need, open up the table you want to get the data from, open up

[quoted text clipped - 7 lines]

and so on. Because of this I thought I had to use InFile for each SELECT
statement.