Help Concatenating and Appending to new table

[Guest]

I'm need some assistance
I'm have this table that is importing 35 files, every morning.
these are the fields of this table
FILENAME,BATCH#,TRANS TYPE,CARD NUMBER,AMOUNT,AUTHo#,CASHIER
I need to append these files to another table, but the File name has to be
broken into 2 fields
File Date and Location.

an example of the FILENAME field is "PB010830", where "01" is the location
and "0830" is the
file date. I need to somehow break the whole name into these two fields but
what the location field has to be 4 digits, the number "78" has to be there
and then the next two.
now for the date field, it has to be xx/xx/xxxx format.

Just want to know, when i am appending all this new info onto the new table,
can i group all the data.
I have this statement that will search the batch #'s, only taking the first
4 digits, and if they are the same, they
will combine all teh same numbers and total their amount. Do you think if
what I ask for will effect this statement.
Thanks

 

[Guest]

Hi, Justin.

You didn’t say if the filename format is always the same fixed field length,
but assuming that it is, and that you wish to use the current year in the
date, expressions for the location and filedate are:

[Location] = “78†& Mid([FILENAME],3,2)

[FileDate] = DateValue(Mid([FILENAME],5,2) & "/" & Mid([FILENAME],7,2) &
"/" & Year(Date()))

Format the field control in form design view to display it in your desired
format.

For totaling the Amount field by each unique combination of the first four
characters of the BatchNumber field, you need to create a Totals query. The
SQL is:

SELECT Mid([BatchNumber],1,4) AS ModBatchNumber, Sum(YourTable.Amount) AS
SumOfAmount
FROM YourTable
GROUP BY Mid([BatchNumber],1,4);

Hope that helps.
Sprinks

 

[Guest]

Thank you, this is what Im looking for
and Yes, the filename format is always the same fixed field length of 12. I
forgot to mention that the Filename will be "xxxxxxxx.txt"

does the .txt make a difference or it doesn't matter

Hi, Justin.

You didn’t say if the filename format is always the same fixed field length,
but assuming that it is, and that you wish to use the current year in the
date, expressions for the location and filedate are:

[Location] = “78†& Mid([FILENAME],3,2)

[FileDate] = DateValue(Mid([FILENAME],5,2) & "/" & Mid([FILENAME],7,2) &
"/" & Year(Date()))

Format the field control in form design view to display it in your desired
format.

For totaling the Amount field by each unique combination of the first four
characters of the BatchNumber field, you need to create a Totals query. The
SQL is:

SELECT Mid([BatchNumber],1,4) AS ModBatchNumber, Sum(YourTable.Amount) AS
SumOfAmount
FROM YourTable
GROUP BY Mid([BatchNumber],1,4);

Hope that helps.
Sprinks

I'm need some assistance
I'm have this table that is importing 35 files, every morning.
these are the fields of this table
FILENAME,BATCH#,TRANS TYPE,CARD NUMBER,AMOUNT,AUTHo#,CASHIER
I need to append these files to another table, but the File name has to be
broken into 2 fields
File Date and Location.

an example of the FILENAME field is "PB010830", where "01" is the location
and "0830" is the
file date. I need to somehow break the whole name into these two fields but
what the location field has to be 4 digits, the number "78" has to be there
and then the next two.
now for the date field, it has to be xx/xx/xxxx format.

Just want to know, when i am appending all this new info onto the new table,
can i group all the data.
I have this statement that will search the batch #'s, only taking the first
4 digits, and if they are the same, they
will combine all teh same numbers and total their amount. Do you think if
what I ask for will effect this statement.
Thanks

 

[Guest]

Hi, Justin.

No, the .txt suffix won't affect what is returned from the function since
Mid is starting from the beginning of the field.

However, it occurred to me that, not knowing the origin of this data, the
filename might contain leading spaces, so I advise you trim them first with
the Trim function to ensure that Mid operates on the first genuine character
of the file name:

[Location] = “78†& Mid(Trim([FILENAME]),3,2)

[FileDate] = DateValue(Mid(Trim([FILENAME]),5,2) & "/" &
Mid(Trim([FILENAME]),7,2) & "/" & Year(Date()))

Sprinks

Thank you, this is what Im looking for
and Yes, the filename format is always the same fixed field length of 12. I
forgot to mention that the Filename will be "xxxxxxxx.txt"

does the .txt make a difference or it doesn't matter

Hi, Justin.

You didn’t say if the filename format is always the same fixed field length,
but assuming that it is, and that you wish to use the current year in the
date, expressions for the location and filedate are:

[Location] = “78†& Mid([FILENAME],3,2)

[FileDate] = DateValue(Mid([FILENAME],5,2) & "/" & Mid([FILENAME],7,2) &
"/" & Year(Date()))

Format the field control in form design view to display it in your desired
format.

For totaling the Amount field by each unique combination of the first four
characters of the BatchNumber field, you need to create a Totals query. The
SQL is:

SELECT Mid([BatchNumber],1,4) AS ModBatchNumber, Sum(YourTable.Amount) AS
SumOfAmount
FROM YourTable
GROUP BY Mid([BatchNumber],1,4);

Hope that helps.
Sprinks

I'm need some assistance
I'm have this table that is importing 35 files, every morning.
these are the fields of this table
FILENAME,BATCH#,TRANS TYPE,CARD NUMBER,AMOUNT,AUTHo#,CASHIER
I need to append these files to another table, but the File name has to be
broken into 2 fields
File Date and Location.

an example of the FILENAME field is "PB010830", where "01" is the location
and "0830" is the
file date. I need to somehow break the whole name into these two fields but
what the location field has to be 4 digits, the number "78" has to be there
and then the next two.
now for the date field, it has to be xx/xx/xxxx format.

Just want to know, when i am appending all this new info onto the new table,
can i group all the data.
I have this statement that will search the batch #'s, only taking the first
4 digits, and if they are the same, they
will combine all teh same numbers and total their amount. Do you think if
what I ask for will effect this statement.
Thanks