Field Sizes in Access driving me crazy - also dbs.OpenRecordset("Database", dbOpenDynaset).FindFirst woes

Posted on 2006-05-24
Last Modified: 2008-02-26
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.



Question by:pcowen
    LVL 77

    Expert Comment

    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.


    Author Comment

    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]) has more details on this subject



    Accepted Solution

    PAQed with points refunded (250)

    Community Support Moderator

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now