Invalid Procedure Call with table sort

[thenoisydrum]

Ok, this is driving me absolutely mad.
Background is that I am importing a text file invoice into my database.
The table that I import the text file to is deleted before the import takes place. The table has a field called Auto (Autonumber data type) which is sorted into ascending order.

This keeps my invoice in "order" - as it looks in the text file.

I am importing around 19 of this invoices a day and the glitch appears to me to be random.

Here is a screen dump of the first few records from the table (sensitive data scribbled out)


I want to extract the word INVOICE so use the following bit of code;

SELECT First(Trim(Left([All_Data],(InStr(1,[All_Data],":"))-1))) AS [Document Type]
FROM [250-005-Invoice_10];

At this stage I get the error message "Invalid Procedure Call"
To investigate further I decided to trim (scuse the punn) the formula down and use;
SELECT First([All_Data]) AS [Document Type]
FROM [250-005-Invoice_10];

...simply to find out what it regards as the "first" record.
This extracts the data contained from record 1,042 - which contains no ":" characters and hence why I get the "Invalid Procedure Call".

Even if I put a query in between that sorts the Auto column into ascending order it still refers to record 1,042 as being the first!

Why o why o why?

Can anybody please help?

Thanks

'drum

 

[Ranman256]

FIRST is not valid. Remove it.
ORDER BY is used to sort.

 

[thenoisydrum]

FIRST is not valid. Remove it.
ORDER BY is used to sort.

Thanks for the quick response.
Why is the First function not valid? I am using it to extract from the first record in the table, specifically the word before the first occurrence of the character ":"

 

[Minty]

The First () function is very unreliable.
https://www.access-programmers.co.uk/forums/showthread.php?t=205050 and https://www.access-programmers.co.uk/forums/showthread.php?t=29346

 

[thenoisydrum]

The First () function is very unreliable.
https://www.access-programmers.co.uk/forums/showthread.php?t=205050 and https://www.access-programmers.co.uk/forums/showthread.php?t=29346

Ok, I think I am going to revert to using the Top 1 record selection in the properties, sort on the Auto field and then remove the first function. This should work as the data that I need to extract is always in the first record.

edit; yes, this has worked.

Thanks guys

'drum

 

[plog]

1. First() (and Last()) should asphyxiate to death on the fumes of their own burning corpses. They don't do what people expect them to do. Never use them.

2. Your expectations are misguided to begin with. The data in a table has no order. There is no first record, there is no second record, no last, no next, no prior, no 312th. A table is just a bucket where rows of data are randomly placed.

Order exists on data only when you explicitly tell it to how to exist. No where in your code did you do that. You just hoped that your query would read your mind and know what field you wanted to use to determine order on your data.

Can you better explain what you are trying to accomplish? Don't explain this individual step, explain the big picture mission.

 

[thenoisydrum]

The solution that I have posted is working fine.
Thanks for caring, plog.