Substracting from a value returned from function

Blastbeats

Registered User.
Local time
Today, 12:37
Joined
Feb 22, 2012
Messages
17
Hello Everyone

I was wondering if you know any way to substract 1 to the instr returned value inside the left function, im doing something like left ([Field],instr([field]," ")), except the instr value needs to have something like this left ([Field],instr([field]," ")-1) but access won't let me do it, im wondering if i can do this inside the whole function because they are inside a query, thanks in advance
 
CustomFieldNameHere: Left([Field],instr([Field]," ")-1)

That should work. Are you getting an error? Could you post some sample input data?
 
but access won't let me do it

Meaning what? You get an error message? You don't get the expected results?

Assuming the value returned in numeric, the following should work;

Left([TestField],InStr([TestField]," "))-1

From the immediate window;

?Left("123 456 789", Instr("123 456 789", " "))-1
122

Is this what you're attempting?
 
no its more like Left("Test 1", Instr("Test 1", " ")) will give me "Test " and i need "Test" because i will need to compare it later with another string, and it wont let me because im using it on an update query god knows why (one field of the table will be updated as "Test" to be clear), ill try to use the Custom field name though, sorry for not being clear enough
 
What does:

Left("Test 1", Instr("Test 1", " ")-1)

give you? Error message? Wrong returned data?
 
#Error on the datasheet

Because you're attempting to subtract 1 form a non-numeric value. What is the point of subtracting 1 here? What result do you want?
 
When I run

Code:
SELECT Left("Test 1",InStr("Test 1"," ")-1) AS TestString
FROM AnyTableInMyDatabase;


I get "Test" for every record in AnyTableInMyDatabase
 
Disregard my last, I was misreading what you were trying to do. Plog is right in that what you posted should work. Have you double checked that it is written in your your app the same as you posted here? Where are you using it? In a query? In a code module?
 

Users who are viewing this thread

Back
Top Bottom