Selecting a Row from Text File

[WesParkinson]

Hi All,

I hope someone can help me out with this.

I have an access database that imports a text file into a table, updates the data accordingly and spits out a new text file in the format we require for our machines. Part of this process is to check that the first and last records match between the old and new file.

I have created a report to show this, and I am able to pull the first and last from the table.

Where I need assistance is here:

I need to prompt the user to select the first and last records from the original text file and paste it into a field on the report.

Can any one show me how this can be acheived.

 

[jdraw]

I would not rely on the user to copy/input the data correctly.

Can you create 2 text variables and then put those values into fields on a report?
Dim txtOrigFirstRec as string
Dim txtOrigLastRec as string
Assign appropriate values via code, not user input.

I haven't worked much with reports in a long time (5 yrs), so can't advise on the specifics without setting up a test db.

 

[WesParkinson]

Thanks, unfortunately I really do need to rely on the user as the text file sent by a company can be set up differently. The first row isnt always the data but can be a set up instruction etc.

Example

Lycamobile Sweden
15k pin split 343

343445645,56780001
387329437,56780002
and so on

so your method would pull Lycamobile Sweden and not 343445645,56780001

 

[jdraw]

I just tried creating a Form with some data from a different table (Animals) than the table used for a report (BooksInARoom).

I got the Minimum value for an AnimalId on the Form in text field text0.

I modified my Report about books to have a textbox in the report header.

If I leave the Form open, then Open the report, the textbox on the report gets the animalId values from the form and displays it on the report.

So you could use that technique if nobody responds.

Once you import the data into a table, you could use Dmin and Dmax to value 2 textboxes on a Form. (text1 and text2)

On your report add 2 textboxes, (rptText1 and rptText2)

the controlsource of the first textbox is Forms.YourFormName.text1, and for the second textbox is Forms.YourformName.text2

Good luck


EDIT: I was typing while you were responding.

Somehow you'll have to identify what/where the proper record(s) is/are -- I'd still try to work with no user input where errors are possible (and likely to happen).

 

[WesParkinson]

Thanks, I really appreciate your suggestion.

When selecting the orginal data from the text file, it has to come from the .txt file, I cant take it once imported. The reason being that the format of the orginal file has to be displayed on the report, so if it is : or ; delimited it has to show this. Once imported I lose that format so it wouldnt display like the original.

 

[jdraw]

Can you provide a sample .txt file. Doesn't have to be a real one, but something with similar strings including delimiters? Only a few records are needed.

 

[WesParkinson]

I have attached a sample file, please note that I have had to create this as I cannot send client data due to DPA regs.

However you will get the drift of what I mean.

The number of rows used can vary from customer to customer, so what I need is for the process to select the first and last actual data field (which in this instance has double quote qualifiers and a pipe delimiter)

I really appreciate your help.

Like I say I can do this for the finished product as I take it out of the table in access, but the data to compare the original data HAS to come from the original file.

Thanks

 

[jdraw]

Wes,
I won't be able to look at this in detail until tomorrow. I did look at your txt file.

I created a table (tblWesParkinson) with 1 field MyOrigText fixed width 80 and imported your txt into it.
To get the first record with "99999 I used

SELECT DMin("MyOrigText","tblWesParkinson") AS [Min]
FROM tblWesParkinson
WHERE (((tblWesParkinson.MyOrigText) Like '"*' And Not (tblWesParkinson.MyOrigText) Is Null))
GROUP BY DMin("MyOrigText","tblWesParkinson");

I am working on my cottage and will get back tomorrow, or maybe late tonight.

Let me know how you're doing.

 

[WesParkinson]

Hi JDraw,

I didnt use the code below, however you kick started my brain cells and I have managed to do it.

I used your idea of importing into the one field and wrote the code to directly place all text into one column.

I then used a LEFT function to find the first character and if it began with a letter I deleted the row, also nulls.

I was then left with the data set and used a noraml Top 1 Query.

Thanks for your suggestion as without it I would still be scratching my Brain.

I really appreciate the help I get from forums such as these.

Hope your cottage is coming on nicely.

Wes

 

[jdraw]

Glad you got the issue resolved. And yes, we did get some of the finishing work completed.

Good luck with your project.