could you please correct my following code

[polyubi]

After a textbox ("Name") I need a serial number in my form, so I write the following on the afterUpdate event of name:

*************
Dim sdate As String
Dim sNum As Integer

sdate = Format(Date, "yyyymmdd")

sNum = DMax("mid([dtjm],3,3)", "aa", "Right([dtjm],8)= sdate")


If Nz(DMax("right([dtjm],8)", "aa", ""), 0) <> sdate Then
Me.dtjm = "P-" & "001" & "-" & sdate

Else
Me.dtjm = "P-" & Format(Val(sNum) + 1, "000") & "-" & sdate
End If
**************
However it doesn't work, the problem seems to be from:
sNum = DMax("mid([dtjm],3,3)", "aa", "Right([dtjm],8)= sdate")
but if I change it like:
sNum = DMax("mid([dtjm],3,3)", "aa", "")
the whole code works well.
Could anybody help me?

 

[JamesMcS]

sNum = DMax("mid([dtjm],3,3)", "aa", "Right([dtjm],8)= sdate")

This will never pull anything back, you're looking for the right 8 characters to be equal to a 5-character string.

 

[polyubi]

thanks for your help.
what would be the correct expression then?

 

[JamesMcS]

Think about it mate. An 8 character string will never be equal to a 5 character string, because there are 5 CHARACTERS IN IT.

And the quotation marks are messed up. It should be 'sdate' not sdate.

 

[polyubi]

I know it should be 'sdate', and I've tried, but there's no reaction at all;
I checked sdate=20100607; why you say it is 5 characters?

 

[JamesMcS]

Right. Well what your code is doing is looking for the word sdate in the right hand 8 characters of the field dtjm. "sdate" being only 5 characters long means it'll never work as it's looking for an 8 character string.

What you actually want is

sNum = DMax("mid([dtjm],3,3)", "aa", "Right([dtjm],8)=" & [sdate])

But you will also have to consider where [sdate] is and refer to it properly, i.e. is it a control on a form, is it a field in a table and so on.

 

[polyubi]

James, it doesn't work neither.
actually I 've worked on this for over 10 hours. [sdate] is only a variant the table don't have this field.

 

[JamesMcS]

Take the [] out, sorry, I thought it was a field not a variable. Should have re-read your post. Also you might have to put Date() not just Date when populating that variable.

 

[polyubi]

still waiting for help......

 

[JamesMcS]

Oh sorry I'll get right on it!! You do know this isn't a paid support website dont you? That the people who choose to answer your threads are doing so out of the kindness of their hearts??

You could help yourself a lot by giving us some more info. When you say "It doesn't work neither" what error messages are you getting?

 

[JamesMcS]

How about this, while I'm "still waiting" for a response:

sNum = DMax("mid([dtjm],3,3)", "aa", "Right([dtjm],8)=" & format(date(),"yyyymmdd"))

 

[namliam]

Since dtjm contains "aa" this needs to be a text field, also the right () function will return a text, thus you need to quote the search:

sNum = DMax("mid([dtjm],3,3)", "aa", "Right([dtjm],8)= '" & format(date(),"yyyymmdd") & "'")

or

sNum = DMax("mid([dtjm],3,3)", "aa", "Right([dtjm],8)= '" & sDate & "'")

or

sNum = DMax("mid([dtjm],3,3)", "aa", "Right([dtjm],8)= "" & sDate & """")

still waiting for help......

Certainly wont help your situation....
Atleast give some form of informative feedback... or something.

 

[polyubi]

thanks for all the help from this forum! I have solved the problem, and the idea is from the replies of the teachers here.