Query result from multiple unbound fields on a form

[NKA]

I don't really know where to start with this one guys and gals!

I have an unbound field (txtjob) on an unbound selection form. I run a query based on the value entered in (txtjob). The criteria is as follows: >=UCase([txtjob]) and <=Ucase([txtjob]) & 'z' and works a treat.

However, I would now like to be able to run the same (or similar) query but, looking at several values for (txtjob). Rather than putting the criteria in the query on 5 or 6 rows (looking at 5 or 6 unbound fields), I was wondering whether this might be a case of using an Array - or something fancy in the coding area!

Is there anyone who may have come across a similar problem and if so, how did you get over it? Any suggestions would be gratefully received. NKA

The answer's always easy - if you know it! ;-)

 

[scriverb]

Below is some code provide in another thread by CosmoKramer. See Thread#: Thread703-500227

I have made modified one line of the code(see red line) to work in your situation. When you run this you will be prompted for a list of jobs. Just type them in seperating them with commas and the search routine will select on all of them. The query searches the table for all of the jobs using the same criteria expression that you were using in your original query. You will have to update the code with the name of your tables field(also in Red).

Copy & paste these two functions into a new module:

'************************************************************
'Declarations section of the module.
'************************************************************

Option Explicit

'============================================================
' The GetToken() function defines the delimiter character.
'============================================================

Function GetToken (stLn, stDelim)
Dim iDelim as Integer, stToken as String
iDelim = InStr(1, stLn, stDelim)
If (iDelim <> 0) Then
stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
stLn = Mid$(stLn, iDelim + 1)
Else
stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
stLn = &quot;&quot;
End If
GetToken = stToken
End Function

Function InParam(Fld, Param)
Dim stToken as String
Fld = UCase(Fld)
Param = UCase(Param)
If IsNull(Fld) Then Fld = &quot;&quot;
Do While (Len(Param) > 0)
stToken = GetToken(Param, &quot;,&quot
'This is the line that I modified.
If LTrim$(RTrim$(Fld)) >= stToken AND (LTrim$(RTrim$(Fld)) & &quot;z&quot <= stToken Then
InParam = -1
Exit Function
Else
InParam = 0
End If
Loop
End Function

In your query, add an expression field something like this:

IDList: InParam([txtJobField],[Enter list of Jobs using commas in between])
In the criteria row of this new field: True

Get back with me if you have any trouble and I will help you.
Bob Scriver

 

[NKA]

Thanks for the quick reply!

The code has completely blown me away and I think I will tackle it tomorrow with a clearer head. NKA

The answer's always easy - if you know it! ;-)

 

[scriverb]

Don't worry about understanding it right now. Just follow the directions. Most of it is cut and paste. I can explain how it works after we get it to work for you. Bob Scriver

 

[NKA]

OK - so what don't I understand then? I have cut and paste as you said, created the expression field in the query and it all looks good....

BUT! (There's always one) now the query takes 10 times as long to process and returns no fields (and I know there is data there if I hard code the job numbers).

Perhaps I should change something else?

Would it also be helpful for you to know that the data is actually linked to the access database from an oracle database using SQL views? I am aware that access plays funny games with fields sometimes coming from ODBC connection. NKA

The answer's always easy - if you know it! ;-)

 

[scriverb]

Let's adjust one line of the code:
If LTrim$(RTrim$(Fld)) >= stToken AND (LTrim$(RTrim$(Fld)) & &quot;z&quot;) <= stToken Then

Take out the red code above. I had attached that to the wrong reference. Could you explain the need for putting that lower case &quot;z&quot; on the end of the jobnumber? If it is necessary to add it then we have to make further adjustments to the same line:
If LTrim$(RTrim$(Fld)) >= stToken AND (LTrim$(RTrim$(Fld)) <= stToken & &quot;z&quot; Then
Add the red code into the line as above.

Let me know if this works now.

Also, make sure that you put the True in the new fields criteria row. Bob Scriver

 

[NKA]

Oh dear! That completely destroyed it! My PC just hangs forever now!!

I cannot remember the reasoning behind the 'z' (the Oracle database owners deemed it necessary to pull all the records correctly). I think it has something to do with search capabilities.

Currently the user inputs a job number (ie ABC123). The job may or may not be a 'parent', so the query is supposed to return all jobs starting with ABC123 (so there could also be ABC123X or ABC123Y) - but the use may not necessarily know that it's a parent to other jobs.

I really appreciate your help on this one and I will pink star you when it works..... promise! NKA

The answer's always easy - if you know it! ;-)

 

[scriverb]

Substitute this line of code in the If statement we were using:
If LTrim$(RTrim$(Fld)) Like &quot;*&quot; & stToken & &quot;*&quot; Then

Let me know. I am glad you explained that the other code came from another process. The Like comparison in ACCESS will do the trick here. Bob Scriver

 

[NKA]

This is so frustrating! That also locks the system up!

OK. Here's the SQL for the original query (which pulls all jobs starting with ABC123)

Perhaps you can make some sense of it! All I want to do now is be able to collect more than one job number at a time!!! Am I asking too much??

SELECT JOB_MASTERS.JOB_CODE, JOB_TRANSACTIONS.JOB_NAME, JOB_MASTERS.PARENT_CODE, JOB_TRANSACTIONS.FINANCIAL_PERIOD, JOB_TRANSACTIONS.FINANCIAL_PD_YR AS FIN_PD_YR, Right([transaction_date],2) AS Day, Mid([transaction_date],5,2) AS Month, Mid([transaction_date],3,2) AS Year, [day] & &quot;/&quot; & [month] & &quot;/&quot; & [year] AS [Date], JOB_TRANSACTIONS.AMOUNT, JOB_TRANSACTIONS.COST_TYPE, JOB_TRANSACTIONS.COST_TYPE_DESC, JOB_TRANSACTIONS.TRANSACTION_DATE, JOB_TRANSACTIONS.NARRATIVE_1, JOB_TRANSACTIONS.REFERENCE, JOB_TRANSACTIONS.RESOURCE_ITEM, JOB_TRANSACTIONS.RESOURCE_QTY, JOB_TRANSACTIONS.RESOURCE_TYPE, JOB_TRANSACTIONS.RESOURCE_TYPE_DESC, JOB_TRANSACTIONS.SELL_VALUE, JOB_TRANSACTIONS.SOURCE, MyVersion.Ver, MyVersion.VerDate, JOB_TRANSACTIONS.AMOUNT_SET
FROM MyVersion, TblCriteria INNER JOIN (JOB_MASTERS INNER JOIN JOB_TRANSACTIONS ON JOB_MASTERS.JOB_CODE = JOB_TRANSACTIONS.JOB_CODE) ON TblCriteria.Fin_pd_yr = JOB_TRANSACTIONS.FINANCIAL_PD_YR
WHERE (((JOB_MASTERS.JOB_CODE)>=UCase([Forms]![frmselecttrans]![txtJob]) And (JOB_MASTERS.JOB_CODE)<=UCase([Forms]![frmselecttrans]![txtJob]) & &quot;z&quot AND ((JOB_TRANSACTIONS.AMOUNT_SET)=&quot;1&quot; Or (JOB_TRANSACTIONS.AMOUNT_SET)=&quot;9&quot AND ((JOB_TRANSACTIONS.DORIP_P)<>&quot;P&quot)
ORDER BY JOB_TRANSACTIONS.TRANSACTION_DATE DESC;
NKA

The answer's always easy - if you know it! ;-)

 

[scriverb]

Try this SQL. I have modified the query with the new code. The code in the Module should be okay.
SELECT JOB_MASTERS.JOB_CODE, JOB_TRANSACTIONS.JOB_NAME, JOB_MASTERS.PARENT_CODE, JOB_TRANSACTIONS.FINANCIAL_PERIOD, JOB_TRANSACTIONS.FINANCIAL_PD_YR AS FIN_PD_YR, Right([transaction_date],2) AS Day, Mid([transaction_date],5,2) AS Month, Mid([transaction_date],3,2) AS Year, [day] & &quot;/&quot; & [month] & &quot;/&quot; & [year] AS [Date], JOB_TRANSACTIONS.AMOUNT, JOB_TRANSACTIONS.COST_TYPE, JOB_TRANSACTIONS.COST_TYPE_DESC, JOB_TRANSACTIONS.TRANSACTION_DATE, JOB_TRANSACTIONS.NARRATIVE_1, JOB_TRANSACTIONS.REFERENCE, JOB_TRANSACTIONS.RESOURCE_ITEM, JOB_TRANSACTIONS.RESOURCE_QTY, JOB_TRANSACTIONS.RESOURCE_TYPE, JOB_TRANSACTIONS.RESOURCE_TYPE_DESC, JOB_TRANSACTIONS.SELL_VALUE, JOB_TRANSACTIONS.SOURCE, MyVersion.Ver, MyVersion.VerDate, JOB_TRANSACTIONS.AMOUNT_SET, InParam([JOB_CODE],[Enter list of Job Codes using commas in between]) as IDList
FROM MyVersion, TblCriteria INNER JOIN (JOB_MASTERS INNER JOIN JOB_TRANSACTIONS ON JOB_MASTERS.JOB_CODE = JOB_TRANSACTIONS.JOB_CODE) ON TblCriteria.Fin_pd_yr = JOB_TRANSACTIONS.FINANCIAL_PD_YR
WHERE ((InParam([Job_Code],[Enter list of Job Codes using commas in between]))= True AND ((JOB_TRANSACTIONS.AMOUNT_SET)=&quot;1&quot; Or (JOB_TRANSACTIONS.AMOUNT_SET)=&quot;9&quot AND ((JOB_TRANSACTIONS.DORIP_P)<>&quot;P&quot)
ORDER BY JOB_TRANSACTIONS.TRANSACTION_DATE DESC;

Let me know. Don't get discouraged.




Bob Scriver

 

[NKA]

I think it might be time to throw the teddy out of the pram!

If I put the code in the module back to version 1, then eventually the query returns no records (something I suppose although I am actually expecting 7 records). If I change the module to version 2 and 3, the system just hangs - nothing gets returned within 30 minutes anyway.

I'm trying not to be discouraged, but it's so frustrating when what I am asking seems simple. NKA

The answer's always easy - if you know it! ;-)