Link to home
Start Free TrialLog in
Avatar of pcowen
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


Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Hi pcowen,

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
Avatar of pcowen
pcowen

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
ASKER CERTIFIED SOLUTION
Avatar of GranMod
GranMod

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial