Query problem with Val() & Mid()

ajl37

New member
Local time
Today, 17:53
Joined
Oct 13, 2010
Messages
7
Hi,

Currently I have the following expression in one of my queries which converts a text field containing "Term 1" etc into just the number "1" but still as text:

Code:
Term: IIf(InStr([ajl_qryTerm.Term]," ")>0,Mid([ajl_qryTerm.Term],InStr([ajl_qryTerm.Term]," ")+1),[ajl_qryTerm.Term])
I thought I would be able to add the Val() function into this to convert the text "number" into an actual number. But it doesn't seem to work.

Code:
Term: IIf(InStr([ajl_qryTerm.Term]," ")>0,Val(Mid([ajl_qryTerm.Term],InStr([ajl_qryTerm.Term]," ")+1)),[ajl_qryTerm.Term])
Any ideas on why not, or any better was of performing the same task?

Many Thanks
Andrew
 
If it always is like Term 1, term 2 etc then

Term: Val([term])

You don't need to include ajl_qryTerm, only if there is an other recordsource present which also has a fieldname like Term, then you must use it.

JR
 
Term: Val([term]) will create a circular reference as the Alias is the same as the field.

The reason that the VAL is not "working" is that TERM is forced to be Text as
ajl_qryTerm.Term is Text and this is also part of the answer.

Brian
 
Term: Val([term]) will create a circular reference as the Alias is the same as the field.

oops wasen't thinking strait there. :(

would this work for you:

expr: Cstr(Right([Term],Len([Term])-5))

JR
 
Last edited:
Hi,

Thanks for the help.

@JANR
The line: expr:
Code:
Cstr(Right([Term],Len([Term])-5))
nicely converted the "Term 10" to "10" but still as text. I also have the problem of a field with no text returning "#Error".

Many Thanks
Andrew
 
Hi,

Interestingly, the following
Code:
Term: Val(IIf(InStr([ajl_qryTerm.Term]," ")>0,Mid([ajl_qryTerm.Term],InStr([ajl_qryTerm.Term]," ")+1),[ajl_qryTerm.Term]))
converts the numbers to numbers but still leaves me with "#Error" for the blanks.

Andrew
 
As does
Code:
expr: Val(CStr(Right([Term],Len([Term])-5)))
 
Well the CStr converts it to text if you want pure number, just remove it.

Right([Term],Len([Term])-5)

to test for NULL

Expr: IIF([Term] Is Null, [Term], Right([Term],Len([Term])-5)))

JR
 
@JANR

That sorts out the blanks - Thanks.
I still see the numbers as text though :( as they start off that way. Putting Val() around the Right statement takes me back to the problem I was having at the start where Val() did not work round the Mid().

Thanks
Andrew

Andrew
 
Cstr converts to text, Clng or CInt converts to number

Expr: IIF([Term] Is Null, [Term], CInt(Right([Term],Len([Term])-5)))

Change to CLng if the number can exeed 32000

JR
 
Hi JANR,

Thanks for the help, but that still gives the number as "text". Currently I have moved the Val() function to another field. It works, but I have two fields with the same info (one as text the other as a number). I now have the values to work with but would much prefer a cleaner way if anyone has any bright ideas.

Many Thanks
Andrew
 
My last expression does force it to be number but if you use Val then you reverse it somehow back again to "NumberText".

Val is not the function you want to use here.

I'm fresh out of ideas, sorry

JR
 
A "cleaner" way of doing what ajl? I'm would have thought JANR's proposed solutions does the trick. What exactly have you written?

I would do it slightly differently (provided the word "Term" is constant):
Code:
Expr: IIF(IsNull([Term]), [Term], Val(Mid([Term], 6)))
 
I have explained why the field is Text in my first post, but here goes again

The field Term is Text and one of the conditions is to continue with this field unaltered, therefore the Alias field will be text, it cannot be text for some entries and numeric for others.

Brian
 
Good answer, and for what its worth you don't need the VAL.

I assumed that the poster wanted [TERM] in there originally, but if it can only be NULL or Term 1 etc then

IIf(Not IsNull([Term]),Val(Mid([Term],6)))
is a simple answer

Brian
 
All,

Thanks for the help.

@Brian, Sorry - it looks like I skipped past your first message completely. It does explain exactly why I was seeing what I was explaining. Your latest expressions looks good. I will try it and see what I get.

Many Thanks
Andrew
 

Users who are viewing this thread

Back
Top Bottom