Convert String to Number 1

[jmeckley]

I have a field that is essentially a number, but the data type is a string. I need to:
1. convert the value to a number
2. find the max value
3. add 1 to the value and set it equal to a variable
4. convert it back to string and store it in a variable

here is what i have so far:

SqlStr3 = "select max(CInt(speedbidnum)) from bidders" ???
Set DB = CurrentDb
Set qdfTemp = DB.CreateQueryDef("", SqlStr)
Set ChkRst = qdfTemp.OpenRecordset(dbOpenDynaset, dbSeeChanges)

BidNum=ChkRst!speedbidnum + 1
Convert to String Function(BidNum) ???

ChkRst.Close
Set ChkRst = Nothing

'Apend leading zeros so the length is 6 characters
do until len(bidnum) = 6
bidnum = 0 & bidnum
loop

I have been experimenting with access queries to get the max value as a number, but I get an error:
Invalid Use of Null Thank you for your assistance

Jason Meckley
Database Analyst
WITF

 

[PaulBricker]

This should do what you need.

Dim BidNum as String
BidNum = Format(DMax("Val(speedbidnumber)","bidders&quot+1,"000000&quot

Paul

 

[jmeckley]

Thanx for your help Paul. The exact formula gave me syntax errors, but pointed me in the right direction. The following code was able to accomplish what I was looking for.
SqlStr = "SELECT Format(Max(Val([speedBidNumber]))+1,""000000"&quotas Num FROM bidder WHERE (((bidder.speedBidNumber) Is Not Null));"
Set DB = CurrentDb
Set qdfTemp = DB.CreateQueryDef("", SqlStr)
Set ChkRst = qdfTemp.OpenRecordset(dbOpenDynaset, dbSeeChanges)

BidNum = ChkRst!num

ChkRst.Close
Set ChkRst = Nothing

SqlStr = "UPDATE bidder SET bidder.speedBidNumber = '" & BidNum & "' WHERE bidder.BidderCode='" & Me.BidderCode.Value & "';"
DoCmd.SetWarnings False
DoCmd.RunSQL SqlStr
DoCmd.SetWarnings True
Forms!Results_frm.Requery Thank you for your assistance

Jason Meckley
Database Analyst
WITF

 

[PaulBricker]

Yes, putting it inside a Select string would change the syntax some. Glad it helped.

Paul