DMax Problem

[DS]

I have a DMax problem. I need to get the highest Number in a series
froma table.
The table has this info...
1
1a
1b
2
3
3a
3b
3c
3d

Here is the DMax Statement
Me.TxtNumber = DMax("[Number]", "tblTest", "Len(Number)-1 = " &
Me.TextStart)

Me.TextStart would have a number such as 1, 2 or 3 in it.

So if Me.TextStart has the number 3 in it Me.TxtNumber would return 3d
if Me.TextStart had 1, it would return 1b, if it had 2 then it ould
return 2....you get the picture. Any help appreciated.
Thanks
DS

 

[TonyT]

Try;
Me.TxtNumber = DMax("[Number]", "tblTest", "Left(Number,(Len(Number)-1) = "
& Me.TextStart & "")

You were asking for the max number where me.TextStart = 1 (from you sample)

TonyT..

 

[DS]

I have a DMax problem. I need to get the highest Number in a series
froma table.
The table has this info...
1
1a
1b
2
3
3a
3b
3c
3d

Here is the DMax Statement
Me.TxtNumber = DMax("[Number]", "tblTest", "Len(Number)-1 = " &
Me.TextStart)

Me.TextStart would have a number such as 1, 2 or 3 in it.

So if Me.TextStart has the number 3 in it Me.TxtNumber would return 3d
if Me.TextStart had 1, it would return 1b, if it had 2 then it ould
return 2....you get the picture. Any help appreciated.
Thanks
DS

This works as long as there are only records as such...1a 1b
The minute you have mixed data such as 1 , 1a , 2 ,etc..it doesn't work...

Me.TxtNumber = DMax("[Number]", "tblTest", "Left(Number, Len(Number) -
1) = " & Me.Text3)

 

[DS]

I have a DMax problem. I need to get the highest Number in a series
froma table.
The table has this info...
1
1a
1b
2
3
3a
3b
3c
3d

Here is the DMax Statement
Me.TxtNumber = DMax("[Number]", "tblTest", "Len(Number)-1 = " &
Me.TextStart)

Me.TextStart would have a number such as 1, 2 or 3 in it.

So if Me.TextStart has the number 3 in it Me.TxtNumber would return 3d
if Me.TextStart had 1, it would return 1b, if it had 2 then it ould
return 2....you get the picture. Any help appreciated.
Thanks
DS

This works as long as there are only records as such...1a 1b
The minute you have mixed data such as 1 , 1a , 2 ,etc..it doesn't work...

Me.TxtNumber = DMax("[Number]", "tblTest", "Left(Number, Len(Number) -
1) = " & Me.Text3)

Ok I have this.
Me.TxtNumber = DMax("[Number]", "tblTest", "Left(Number,1)= " & Me.Text3)
The only hitch is that it works only up to 9 or single digit numbers,
double digits and larger are a no go.
Thanks
DS

 

[TonyT]

Sorry not thinking straight there, try;
Me.TxtNumber = DMax("[Number]", "tblTest", "Left(Number,Len(" & me.Text3 &
") -1 ) = " & Me.Text3)

will find the highest 2 digit number if you enter 2 digits, 3 digit number
if you enter 3 etc etc.

TonyT..

I have a DMax problem. I need to get the highest Number in a series
froma table.
The table has this info...
1
1a
1b
2
3
3a
3b
3c
3d

Here is the DMax Statement
Me.TxtNumber = DMax("[Number]", "tblTest", "Len(Number)-1 = " &
Me.TextStart)

Me.TextStart would have a number such as 1, 2 or 3 in it.

So if Me.TextStart has the number 3 in it Me.TxtNumber would return 3d
if Me.TextStart had 1, it would return 1b, if it had 2 then it ould
return 2....you get the picture. Any help appreciated.
Thanks
DS

This works as long as there are only records as such...1a 1b
The minute you have mixed data such as 1 , 1a , 2 ,etc..it doesn't work...

Me.TxtNumber = DMax("[Number]", "tblTest", "Left(Number, Len(Number) -
1) = " & Me.Text3)

Ok I have this.
Me.TxtNumber = DMax("[Number]", "tblTest", "Left(Number,1)= " & Me.Text3)
The only hitch is that it works only up to 9 or single digit numbers,
double digits and larger are a no go.
Thanks
DS

 

[TonyT]

scrub that last one do this instead!!!!
Me.txtNumber = DMax("[Number]", "tblTest", "Left(Number,Len(" & Me.Text3 &
")) = " & Me.Text3)

I left the -1 in by mistake!!!

TonyT..

Sorry not thinking straight there, try;
Me.TxtNumber = DMax("[Number]", "tblTest", "Left(Number,Len(" & me.Text3 &
") -1 ) = " & Me.Text3)

will find the highest 2 digit number if you enter 2 digits, 3 digit number
if you enter 3 etc etc.

TonyT..

DS wrote:

I have a DMax problem. I need to get the highest Number in a series
froma table.
The table has this info...
1
1a
1b
2
3
3a
3b
3c
3d

Here is the DMax Statement
Me.TxtNumber = DMax("[Number]", "tblTest", "Len(Number)-1 = " &
Me.TextStart)

Me.TextStart would have a number such as 1, 2 or 3 in it.

So if Me.TextStart has the number 3 in it Me.TxtNumber would return 3d
if Me.TextStart had 1, it would return 1b, if it had 2 then it ould
return 2....you get the picture. Any help appreciated.
Thanks
DS

This works as long as there are only records as such...1a 1b
The minute you have mixed data such as 1 , 1a , 2 ,etc..it doesn't work...

Me.TxtNumber = DMax("[Number]", "tblTest", "Left(Number, Len(Number) -
1) = " & Me.Text3)

Ok I have this.
Me.TxtNumber = DMax("[Number]", "tblTest", "Left(Number,1)= " & Me.Text3)
The only hitch is that it works only up to 9 or single digit numbers,
double digits and larger are a no go.
Thanks
DS

 

[DS]

scrub that last one do this instead!!!!
Me.txtNumber = DMax("[Number]", "tblTest", "Left(Number,Len(" & Me.Text3 &
")) = " & Me.Text3)

I left the -1 in by mistake!!!

TonyT..

:

Sorry not thinking straight there, try;
Me.TxtNumber = DMax("[Number]", "tblTest", "Left(Number,Len(" & me.Text3 &
") -1 ) = " & Me.Text3)

will find the highest 2 digit number if you enter 2 digits, 3 digit number
if you enter 3 etc etc.

TonyT..

:

DS wrote:

DS wrote:


I have a DMax problem. I need to get the highest Number in a series
froma table.
The table has this info...
1
1a
1b
2
3
3a
3b
3c
3d

Here is the DMax Statement
Me.TxtNumber = DMax("[Number]", "tblTest", "Len(Number)-1 = " &
Me.TextStart)

Me.TextStart would have a number such as 1, 2 or 3 in it.

So if Me.TextStart has the number 3 in it Me.TxtNumber would return 3d
if Me.TextStart had 1, it would return 1b, if it had 2 then it ould
return 2....you get the picture. Any help appreciated.
Thanks
DS

This works as long as there are only records as such...1a 1b
The minute you have mixed data such as 1 , 1a , 2 ,etc..it doesn't work...

Me.TxtNumber = DMax("[Number]", "tblTest", "Left(Number, Len(Number) -
1) = " & Me.Text3)

Ok I have this.
Me.TxtNumber = DMax("[Number]", "tblTest", "Left(Number,1)= " & Me.Text3)
The only hitch is that it works only up to 9 or single digit numbers,
double digits and larger are a no go.
Thanks
DS

Ok Tony, It works until I go to double digits. Its says Data Mismatch?
This is what I have...
Me.TxtNumber = DMax("[Number]", "tblTest", "Left(Number,Len(" & Me.Text3
& ")) = " & Me.Text3)

Thanks for your help, it is appreciated!
DS

 

[DS]

OK Tony almost there. This almost works...

Me.TxtLast = DMax("[NumberID]", "tblTest", "Left(NumberID,Len(" &
Me.TxtCurrent & ")) = '" & Me.TxtCurrent & "'")

The only problem is if I have lets say a 4 and a 43 in the recordset
and Me.TxtCurrent is a 4 then instead of returning the highest record
for the 4 it will return the value for the 43 and not the 4.

Thanks
DS

 

[DS]

OK Tony almost there. This almost works...

Me.TxtLast = DMax("[NumberID]", "tblTest", "Left(NumberID,Len(" &
Me.TxtCurrent & ")) = '" & Me.TxtCurrent & "'")

The only problem is if I have lets say a 4 and a 43 in the recordset
and Me.TxtCurrent is a 4 then instead of returning the highest record
for the 4 it will return the value for the 43 and not the 4.

Thanks
DS

I just did some experimenting...
It only happens if 4 has no other records. If I add a 4a record then it
doesn't happen.
Thanks
DS