By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,224 Members | 1,130 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

Code to look for and match a text string in a field

P: 15
Hi,

In a new record in a "Jobs" table, I enter the "ClaimantID" (the person who this job involves) but I want to know whether or not this particular Claimant has had jobs done on them previously, and if so, who the person responsible for working on that job was (found in a separate field called "PI").

Can I write code that asks the database to:
- search for and match a ClaimantID that I type in a text box in a form
- output who the PI (found in a different field) that worked on that previous job was
- sometimes a claimant might have several previous jobs

I will pay $50(au) to anyone's BSB account if they help me with this code. I have been working on this for too long and I am over it. I am not a programmer but have been forced into a situation where I need some code because Access is not capable of such a function without it.

deejow
Aug 7 '07 #1
Share this Question
Share on Google+
2 Replies


P: 47
I do this sort of thing quite often using the Dlookup function. Here's a sample that should work for you - I've used Message boxes to return the result of the lookup but obviously you can change this to call whatever process is appropriate in your case. If you want to determine the number of previous records for this claimant you can use Dcount with the same syntax.

Using nz forces the return of a null string in the event the lookup fails and prevents the code falling over.

<Code(vb)>
Sub txtClaimID_AfterUpdate
Dim sPI as string
sPI=nz(Dlookup("PI","Jobs","ClaimantID='" & txtClaimID &"'"),"")
IF sPI>"" then
Msgbox "Previous PI=" & sPI & " for this claimant"
Else
Msgbox "No previous record for this claimant"
End if
End Sub
</Code>
Aug 7 '07 #2

P: 15
I do this sort of thing quite often using the Dlookup function. Here's a sample that should work for you - I've used Message boxes to return the result of the lookup but obviously you can change this to call whatever process is appropriate in your case. If you want to determine the number of previous records for this claimant you can use Dcount with the same syntax.

Using nz forces the return of a null string in the event the lookup fails and prevents the code falling over.

<Code(vb)>
Sub txtClaimID_AfterUpdate
Dim sPI as string
sPI=nz(Dlookup("PI","Jobs","ClaimantID='" & txtClaimID &"'"),"")
IF sPI>"" then
Msgbox "Previous PI=" & sPI & " for this claimant"
Else
Msgbox "No previous record for this claimant"
End if
End Sub
</Code>
Thnx for reply. What is the Access equivalent for this code and where do I put it?

deejow
Aug 10 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.