Access/VBA Help

[KAMAC1]

I am trying to develop an application that will automatically assign serial numbers. The format is to be WeekNum&MonthNum&2DigitYearNum-(sequential number) The sequential number will be relative to the week number. I have written everything to determine what the serial number should be but I am having a hard time determining the sequential number. Here is my code

Private Function getlastseq(weekno As String) As String
Dim sqlstr As String
Dim weekmatch As String
Dim conntemp As ADODB.Connection
Dim rstemp As New ADODB.Recordset
Dim lastset As String
weekmatch = weekno
Set conntemp = New ADODB.Connection

conntemp.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\semserv3\users\kmaclennan\cubeserial.mdb;" & _
"User ID=Admin;" & _
"Password='';"


sqlstr = "SELECT Max(SN.seq) AS seq1 FROM sn WHERE sn.ser = '" & weekmatch & "' GROUP BY SN.ser;"
rstemp.Open sqlstr, conntemp, 3, 3
If rstemp.RecordCount < 1 Then
getlastseq = "0001"
Else
MsgBox Str(rstemp.RecordCount)
' MsgBox Str(rstemp.Fields(0))
' getlastseq = Str(rstemp.Fields(0))
End If



End Function

The problem that I'm having is that I have 3 records in the table but the query doesn't look beyond the first. The value returned should equal 3 but it equals 1

ser seq ExLot ExJob EmLot EmJob DiLot DiJob
210508- 1 123 123 123 123 123 123
210508- 2 456 456 456 456 456 456
210508- 3 555 555 555 555 555 555

If I take the space prior to the first record out I get 0 records returned

Here is the query string from my locals window

: sqlstr : "SELECT Max(SN.seq) AS seq1 FROM sn WHERE sn.ser = ' 210508-' GROUP BY SN.ser" : String

 

[GComyn]

Ok.. you can try using the dmax() function, but I think the problem is that you are grouping on the serial number, that would mean that EVERY serial number would show up in your query results...

so you can try with the sql, without the grouping, and see what that does, or you can use the DMAX() function instead...

dmax("[SEQ]","SN","[ser] like '" & weekmatch & "*'")

the above function is only a trial... you need to test it, and maybe change it to get it correct.

GComyn

 

[Golom]

I agree that the GROUP BY is irrelevant but the WHERE sn.ser = ' 210508-' should restrict the results to only one record.

With the two lines in the "Else" clause commented out, what you are seeing in the message box is the number of records in the recordset ... not the value of the MAX field.

I note that you are using the "Str" function to convert to a string. Note that, for example,

n = 123
? "'" & str(n) "'"
' 123'

i.e. it inserts a space before the value. I don't know if this has anything to do with the leading space problem but you might use

Trim$(Str(SomeValue))

to eliminate the leading space.

Try some code like

"SELECT Max(SN.seq) AS seq1 FROM sn 
 WHERE Trim$(sn.ser) = '210508-'"

 

[KAMAC1]

I understand that the result set would only be 1 record but the value should be 3 not 1. I will try the dmax function and see what that returns

 

[Golom]

It is probably coming back as 1 because the "ser" field has a leading space.

"[COLOR=black yellow] [/color]210508-" and "210508-" are not the same thing so, with your selection criteria

WHERE sn.ser = '[COLOR=black yellow] [/color]210508-'

you will get only the MAX on the first record (i.e. 1) and not the MAX for all three records.

Try the SQL I posted to eliminate the leading space in the sn.ser values.