Attention SQL guru's

[DougP]

I have a aquired a Fox Pro database<br>
It's an Order database.<br>
The order number for some &quot;Martian&quot; (read strange) reason is embeded in a description field like so:<br>
MARY SMITH (order #1075)<br>
This is in a order details table.<br>
So there is a record for each part on the order.<br>
I want to set the subforms recordsouce to just those records.<br>
My SQL code is normally:<br>
&quot;SELECT * FROM DetailsTable WHERE [IDESC] = &quot; & Me!OrderNum<br>
I changed it to <br>
&quot;SELECT * FROM DetailsTable WHERE [IDESC] Like '*&quot; & Me!OrderNum & &quot;*';&quot;<br>
But it returns more than just the order I need <br>
It returns all of the records for every order that has 75 in it<br>
like 175, 75, and 1075<br>
I need to be able to have a SQL statement that just looks at the 1075 inside of the IDESC field.<br>
Something on the lines of<br>
&quot;SELECT * FROM DetailsTable WHERE Mid([IDESC],15,4) Like '*&quot; & Me!OrderNum & &quot;*';&quot;<br>
Of course that does not work.<br>
<br>
TIA<br>
<br>
<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[JimHorton]

Is this a Fox Pro attached table in Access? I've never used Fox, so I can't answer anything if it's straight foxpro. But, if it's attached, I think you could make a query as a base for the subform, and have a derived field on the query, ie,<br>
Expr1: GetDetailId([IDESC])<br>
<br>
then, in the function (assuming the format is consistently &quot;xxxx order (#nnnn)&quot; would be something like<br>
Function GetDetailId( desc as string) as long<br>
'Assume some error control if desc is null, or below, if # not found, etc.<br>
dim pos, tmpstr<br>
pos = instr(desc,&quot;#&quot<br>
tmpstr = mid$(desc,pos+1,len(desc)) 'just use len(desc) mid allows you to overshoot.<br>
pos = instr(tmpstr,&quot&quot<br>
tmpstr = left$(tmpstr,Pos -1)<br>
GetDetailID = clng(tmpstr)<br>
end Function

 

[DougP]

Yes it is attached<br>
I already made funciton similar to what you have Jim.<br>
But how do I pass this to the subforms Recordsource.<br>
And the other problem is the number ranges from 1 to 10000<br>
so the number of characters varies.<br>
Like &quot;99&quot; is 2 characters and &quot;100&quot; is 3 characters and so forth.<br>
Mary Smith (Order#99)<br>
Or the next order would be <br>
Mary Smith (Order#100)<br>
<br>
How do I build this &quot;Expr1: GetDetailId([IDESC])&quot; into a SQL statement?<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[JimHorton]

Doug,<br>
In the qbe grid, (I just used expr1 as example) I'd put, say,<br>
NewID: GetDetailID([IDESC])<br>
Just put that in an empty field, make sure the 'show' is checked.<br>
Then, base the subform on that, and link NewiD as the LinkChild Fields, and whatever the OrderID is in the master table as LinkMaster fields.<br>
All that's needed is that the return value of GetDetailDesc() matches the datatype of the Master tables Order ID, and that errors are trapped and returned as null or zero.<br>
--Jim