pcowen
asked on
Field Sizes in Access driving me crazy - also dbs.OpenRecordset("Database", dbOpenDynaset).FindFirst woes
Hi MS Access gurus,
This is probably a really simple answer for you but it has me perplexed and bamboozled :)
The table I am looking at has a number which is set to an integer and want it to be able to take 6 digits rather than 5. If you try this you get the error
The value you entered isn't valid for this field For example, you may have entered text in a numeric field...
If I change to a longint then it displays weirdly like 1.23456 E ...
I have tried changing to a string value because I don't believe any maths is done on the number but then I get a problem with the code
strCriteria = "[Number] = " & CStr(Forms!worker.[Number] ) & " And [Number2] = " & CInt(Forms!worker.[Number2 ])
dbs.OpenRecordset("Worker" , dbOpenDynaset).FindFirst strCriteria
I get error Run-time error '3464' Data type mismatch in criteria expression.
Obviously it doesn't make sense to do a CStr on a string so I removed that but still get same error
Basically all I need is a way to have the Number field to allow 6 digits like 123456 without bringin up an error in the quickest/easiest way possible.
Thanks
Paul
This is probably a really simple answer for you but it has me perplexed and bamboozled :)
The table I am looking at has a number which is set to an integer and want it to be able to take 6 digits rather than 5. If you try this you get the error
The value you entered isn't valid for this field For example, you may have entered text in a numeric field...
If I change to a longint then it displays weirdly like 1.23456 E ...
I have tried changing to a string value because I don't believe any maths is done on the number but then I get a problem with the code
strCriteria = "[Number] = " & CStr(Forms!worker.[Number]
dbs.OpenRecordset("Worker"
I get error Run-time error '3464' Data type mismatch in criteria expression.
Obviously it doesn't make sense to do a CStr on a string so I removed that but still get same error
Basically all I need is a way to have the Number field to allow 6 digits like 123456 without bringin up an error in the quickest/easiest way possible.
Thanks
Paul
ASKER
Was being a dingbat - just change to longinteger and then change the Format to General Number
If I use string then the following code seems to work:
strCriteria = "[Number] = '" & Forms!worker.[Number] & "' And [Number2] = " & CInt(Forms!worker.[Number2 ])
http://support.microsoft.com/default.aspx/kb/136059 has more details on this subject
Thanks
Paul
If I use string then the following code seems to work:
strCriteria = "[Number] = '" & Forms!worker.[Number] & "' And [Number2] = " & CInt(Forms!worker.[Number2
http://support.microsoft.com/default.aspx/kb/136059 has more details on this subject
Thanks
Paul
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If the field is defined on the table as Integer then the max value is approx 32K.
To allow larger numbers modify the field definition to Long Integer.
Pete