• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

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.



1 Solution
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.

pcowenAuthor Commented:
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


PAQed with points refunded (250)

Community Support Moderator

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now