Setting an Alpha Number

[eileen1309]

I have a two tables let's say table1 and table2

Table1 is where I set the seeds
Program
AlphaCode
Seed
Edate
Xdate

Table2 is where I need to update a field with the current AlphaCode+Seed.
DateofLoss
Prg
LegacyClmno
Prgclmno

What I am looking to do is compare the date of loss to the edate and xdate of Table 1 to ensure that I have the correct starting seed else I want it to dmax the prgclaimno for that prg +1

I've tried several dlookup expressions but can't seem to get it to change to the next number.

Any help would be appreciated.

 

[PCLewis]

Too hard to follow you. Can you post code or re-phrase please?

Assume we know absolutely nothing about your table and fields and how they relate to each other. That's a fairly safe bet.

(In case you think I am being pedantic, what does "compare the date of loss to the edate and xdate" mean? compare is a wooly word that in this context coul;d mean 'If ((DateOfLoss > xdate) and (DateOfLoss > eDate) then ...' or it could mean 'If ((DateOfLoss = xdate) or (DateOfLoss = eDate) then ...' or just about anything..)

 

[eileen1309]

sorry sometimes my head is so wrapped up in what it needs to do I can't explain it right.

Table 1 is nothing more then a reference I need to update the LegacyClaimNo with an Alpha Code from Table one where the date of loss falls between the edate and xdate of the alpha code.

The reason is there was a situation where a contract changed and the claim numbering for particular program needed to be restarted.

 

[eileen1309]

Myabe a better explaination would be..

I need to maximize the prgclmno from table 2 by program if there was never a claim enterd under that program I need to set the legacy claim number to equal the AlphaCode + Seed. If there was I need to increment to the next number.

 

[SkipVought]

What code do you have so far?

What do you mean by AlphaCode + Seed?

What is AlphaCode?

What is Seed?

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[eileen1309]

Ok this is the code that I have

Function LegClmNo()
On Error GoTo LegClmnoErr
Dim i As Long
Dim j As Date
Dim k As Date

j = DLookup("[edate]", "BtblProgramClaimNumbers", "[programcode] = forms![BfrmClaimSummary]![prg] and forms![BfrmClaimSummary]![dateofloss] >= [edate]")
k = DLookup("[xdate]", "BtblProgramClaimNumbers", "[programcode] = forms![BfrmClaimSummary]![prg] and forms![BfrmClaimSummary]![dateofloss] <= [xdate]")
i = Nz(DMax("[prgclmno]", "BtblClaimSummary", "[prgprefix]= forms![BfrmClaimSummary]![prgprefix] and forms![BfrmClaimSummary]![DateofLoss] between j and k"), 0)

If i = 0 Then
i = 1
Else
i = i + 1
End If

Forms![BfrmClaimSummary]![prgclmno] = i
Forms![BfrmClaimSummary]![legacyClmNo] = Trim(Forms![BfrmClaimSummary]![prgprefix] & i)



LegClmnoExit:
Exit Function
LegClmnoErr:
MsgBox Err.Number & " " & Err.Description
Resume LegClmnoExit



End Function

 

[eileen1309]

i is where it fails

 

[PCLewis]

I tried very very hard to interpret your requirement and failed. I can tell you how to fix your DMAX statement because that's just syntax. I have no idea whether you are going about your task in the most efficient manner. I am almost sure you aren't, since a single QUERY would probably suffice.

In any case, your syntax error is in no giving DMAX anything it can understand. It has no idea what a 'j' or a 'k' are.

Replace that line with my line and it should get you a lot closer to your goal.

Note: If j or k is 0 (i.e. the DMAX returns a 0), then you may get a result for i that you did not expect. In fact, if either is a 0 then my code will give you a different error.

i = Nz(DMax("[prgclmno]", "BtblClaimSummary", "[prgprefix]= forms![BfrmClaimSummary]![prgprefix] and forms![BfrmClaimSummary]![DateofLoss] between #" & j & "# and #" & k & "#"), 0)

 

[eileen1309]

Thanks for the tip you are absolutely correct, I was going about it wrong. I misunderstood what the client requirement was based on a document received. After conversation we found that an edate and xdate for comparing the date of loss too was not necessary, and the objective is now working.

Thanks for all your help