Look up last value in field

[PsyberFox]

Hi there,

I have a form to create new outlet codes. This form has a field called Store
Type, which is set to get values off a combo list. Based on the value the
user selects, a field called Store Code should be Sxxx, Gxxx, Txxx or Wxxx
where xxx is the last code + 1. Is there a way to look up what the last code
was in this table for each Store Type?

Thanking you in advance,
W

 

[KARL DEWEY]

Several ways --
DMax
Max in a subquery
Max in a seperate query joined

 

[PsyberFox]

Hi Karl, thank you for your prompt response.

I need some more help (I fear it's too late at night for me to think
further, lol)
I am perhaps trying to be a wee bit too fancy, here's my code to generate
the next Store Code number. I am trying to eliminate the problem where single
and double unit numbers are not zero-filled, so I need to put this in as my
StoreCode field has a length of 4 characters.

Private Sub StoreType_AfterUpdate()
Dim GetStoreType As String
Dim GetStoreCode As String
Dim NextStoreNumber As String
Dim NextStoreCode As String

GetStoreType = Mid$([StoreType], 1, 1)
GetStoreCode = Mid$(DMax("StoreCode", "XOutlets", "StoreType =" &
[StoreType]), 2, 3)
NextStoreNumber = GetStoreCode + 1

If Len(NextStoreNumber) = 1 Then
NextStoreCode = GetStoreType & "00" & NextStoreNumber
ElseIf Len(NextStoreNumber) = 2 Then
NextStoreCode = GetStoreType & "0" & NextStoreNumber
ElseIf Len(NextStoreNumber) = 3 Then
NextStoreCode = GetStoreType & NextStoreNumber
End If

[StoreCode].Value = NextStoreCode

End Sub

My StoreTypes are either S/M; G/T; T/SI; T/SR or W/S. But this is producing
an error saying "The expression you entered as a query parameter produced
this error: G"
This is when I select a StoreType as G/T; it changes with the selection of
any other store type to the first letter of the StoreType.

Please advise...
And thank you again!

 

[John Spencer]

Private Sub StoreType_AfterUpdate()
Dim GetStoreType As String
Dim GetStoreCode As String
Dim NextStoreNumber As String
Dim NextStoreCode As String

GetStoreType = Left([StoreType], 1)
GetStoreCode = NZ(DMax("StoreCode", "XOutlets", _
"StoreCode Like " & [StoreType] & "*"),GetStoreType & "000")

NextStoreNumber = GetStoreType & Format(Val(Mid(GetStoreCode,2))+1,"000")

Me.[StoreCode].Value = NextStoreCode

End Sub

So, you will have a problem when you reach "999" for any StoreCode.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[PsyberFox]

Thank you very much for this... i am getting a wee syntax error but will
resolve.
With regards to your comment re 999, i have checked before starting this and
three digits (999 stores per type) will be enough to cover future expansion.
Rgds,
W

Private Sub StoreType_AfterUpdate()
Dim GetStoreType As String
Dim GetStoreCode As String
Dim NextStoreNumber As String
Dim NextStoreCode As String

GetStoreType = Left([StoreType], 1)
GetStoreCode = NZ(DMax("StoreCode", "XOutlets", _
"StoreCode Like " & [StoreType] & "*"),GetStoreType & "000")

NextStoreNumber = GetStoreType & Format(Val(Mid(GetStoreCode,2))+1,"000")

Me.[StoreCode].Value = NextStoreCode

End Sub

So, you will have a problem when you reach "999" for any StoreCode.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi Karl, thank you for your prompt response.

I need some more help (I fear it's too late at night for me to think
further, lol)
I am perhaps trying to be a wee bit too fancy, here's my code to generate
the next Store Code number. I am trying to eliminate the problem where single
and double unit numbers are not zero-filled, so I need to put this in as my
StoreCode field has a length of 4 characters.

Private Sub StoreType_AfterUpdate()
Dim GetStoreType As String
Dim GetStoreCode As String
Dim NextStoreNumber As String
Dim NextStoreCode As String

GetStoreType = Left([StoreType],1)
GetStoreCode = Mid$(DMax("StoreCode", "XOutlets", "StoreType =" &
[StoreType]), 2, 3)
NextStoreNumber = GetStoreCode + 1

If Len(NextStoreNumber) = 1 Then
NextStoreCode = GetStoreType & "00" & NextStoreNumber
ElseIf Len(NextStoreNumber) = 2 Then
NextStoreCode = GetStoreType & "0" & NextStoreNumber
ElseIf Len(NextStoreNumber) = 3 Then
NextStoreCode = GetStoreType & NextStoreNumber
End If

[StoreCode].Value = NextStoreCode

End Sub

My StoreTypes are either S/M; G/T; T/SI; T/SR or W/S. But this is producing
an error saying "The expression you entered as a query parameter produced
this error: G"
This is when I select a StoreType as G/T; it changes with the selection of
any other store type to the first letter of the StoreType.

.

 

[John W. Vinson]

With regards to your comment re 999, i have checked before starting this and
three digits (999 stores per type) will be enough to cover future expansion.

famous last words.... <g>

(I've read on another newsgroup that the 19-character Vehicle ID Number format
is running out of characters).