Search results

You can nest as many InStr functions as needed (AFAIK: I've done ten below) but with a rather severe constraint: The string you're searching must contain at least as many search characters as the number of nested InStr functions. IOW, If you need the 3rd space in a string, there must be at...

expr1: InStr(InStr(1,[myfieldname]," ")+1,[myfieldname]," ") Returns 0 if no first or second " ".

Isn't that the same as changing the Order By clause of the original SQL statement? Is it "better" to change the SQL via the Order By clause/property or to leave the SQL the same and having the function call in the SQL perform the necessary sort order calculations?

Say I have 3 fields in my table: FstName, MidName, LstName. I want to be able to dynamically change the sort order of a query to different sort orders using the same fields via a form. I may want to see the records sorted by LstName, MidName, Fstname; and another time see the list sorted by...

How about sorting by: Format([DateAdded],"yyyymm...............mmm-yy") Use spaces instead of "." in the format property of the control - only used here to show the spacing between... Then right align your report control (can grow = no, can shrink = no) so only the mmm-yy is displayed.

If every field begins with "CON WD ", there's no reason to sort by those characters: Sort using just the characters after "CON WD ". Try: SortCode:Format(Mid(tMyTableFieldName, 8), "0000000000") using a query with your table as the record source and sort the SortCode field ascending. The...

I believe you have been tasked with the impossible. You're being asked to catch (with code) every input error that anyone could possibly ever make. Good luck wid dat. Here's a starter proc that assumes a very specific user input string. Sub GetPartn() Dim ParsedElement As Variant Dim...

Function fParser(sPassed As String) As String Dim i As Integer Dim vElement As Variant fParser = "N/A" For Each vElement In Split(sPassed) If Len(vElement) = 7 Then Select Case Asc(Left(vElement, 1)) Case 67, 72, 83, 86 'C,H,S,V For i = 2 To 7...

Looks like a job for Select Case: Function fCharCheck(sPassed As String) As Boolean fCharCheck = False While Len(sPassed) Select Case Left(sPassed, 1) Case Is < "-", Is > "9", "." fCharCheck = True 'character not allowed, show record Exit Function End...

Status: IIf([Last Review Date]>=#11/1/2006# And [Last Review Date]<=#10/31/2007#,"Completed",IIf([Next Discussion Due]<Date(),"OverDue",IIf([Next Discussion Due]>Date(),"UpComing","")))

Iif([LastReviewDate]>=#11/1/2006# And ([LastReviewDate]<=#10/31/2007#, "Complete", ...

Oops! My Bad... The expression in the query should most likely be: IIf(IsNull([testdate]),"No Exam Found",Format([testdate],'mmm dd", "yyyy')) Unless you really need the NZ function. The NZ function is used to typically return a zero when a null is encounterd in an expression to prevent the...

Status: IIf([Last Review Date]>=#31 OCT 2008#,"Completed”,IIf([Next Discussion Due]<=Date(),"Overdue”,IIf([Next Discussion Due]>=Date(),"Upcoming”))) As a start, there isn't a false branch in the last Iif of the nested conditionals: Status: IIf([Last Review Date]>=#31 OCT...

In a new column in your query: expr1: IIf(nz([testdate]),Format([testdate],'mmm dd", "yyyy'),"No Exam Found")

Complex Alphanumeric Sorts One of the keys to understanding how to do this and why it works is to first realize that an Access ascending text sort will sort strings of digits perfectly well provided the digit characters are right justified. For example: tTxt 001 009 010 099 100 sorts exactly...

Try This: SELECT Siebel_mkt_contact_12012006_EMILE_HAS.FST_NAME FROM Siebel_mkt_contact_12012006_EMILE_HAS WHERE (((IIf(IsNull([FST_NAME]),False,IIf(StrComp(StrConv([FST_NAME],3),[FST_NAME],0),True,False)))=True));

Easy enough (I think this'll work...). Your SQL will be: SELECT YourTable.YourTableField, IIf(IsNull([YourTableField]),False,fstringcomp([YourTableField])) AS expr1 FROM YourTable WHERE (((IIf(IsNull([YourTableField]),False,fstringcomp([YourTableField])))=True)); And the code in the module...

Can you post your SQL statement? The one I'm using for my query looks like this: SELECT TRefs.tRef, IIf(IsNull([tref]),1,Asc(Left([tref],1))) AS expr1 FROM TRefs WHERE (((IIf(IsNull([tref]),1,Asc(Left([tref],1))))>64 And (IIf(IsNull([tref]),1,Asc(Left([tref],1))))<91)); It works fime for me...

Do you have empty records in your table? If so, try this: Expr1: IIf(IsNull([YourTableFieldNameGoesHere]),1,Asc(Left([YourTableFieldNameGoesHereAlso],1))) and the criterea as before.

Try this: expr1: IIf(Val([fieldname]),1,Asc(Left([fieldname],1))) in the criteria put > 96 AND < 123