How to support nullable integer (number) field in Access / VBA
I recall while developing with the DB2 database, that the DB2 driver / API supported the concept of fields having null indicator variables. That is a second variable that may be checked to find out the null state of the field in question. The correct way to deal with nullable fields on DB2 was to use that second variable, do the null check BEFORE attempting to access the variable which is suppose to contain the value of said field.
Does Access / VBA have anything comparable in ADODB.Recordset objects?
As-is, Access / VBA turns the NULL that was in that field of the record into 0 upon issuing a SQL UPDATE as the Integer variable defaults to 0.
Thank you for taking the time to report an issue.
What's wrong... Please write below.
What is the biggest integer value that Access10 (64bit) can support?
Also what is the maximum physical table size that Access10 (64bit) can support
I did the following steps:
File - Get External Data - Import
On the Import Text Wizard
Delimited - Tab(First Row Contains Field) - New Table
- Data Type
Field 1 - Text
Field 2 - Text
Field 3 - Text
Field 4 - Text
Field 5 - Date/Time
Field 6 - Long Integer
Filed 7 - Long Integer
Field 8 - Long Integer
Field 9 - Text
Field 10- Text
If Not FieldExists("Dosel", "BlockID") Then
dbs.Execute "ALTER TABLE Dosel ADD COLUMN BlockID INTEGER;", dbFailOnError End If
Field Type = Number
Field Size = Long Integer
Input Mask = 000\ 000\ 0000
Indexed = Yes (no duplicates)
I have created a form based on the this table for data input but when I put numbers in 'NHS Number fields' its giving me error saying "The value you entered for this field isn't validfor this field" For example you may have entered text in numeric field etc etc
I've tried INT() and VAL() and have had no luck.
Below is the syntax I'm using:
#Private Sub Text6_DblClick(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim I As Integer
Dim strSQL As String
Set dbs = CurrentDb
strSQL = "select * from [tbl sale invoices]"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
I = rst![Sale Invoice ID]
Forms![FRM Invoice dataview]!Text6 = I + 1
Set rst = Nothing
Set dbs = Nothing
now I don't know what the customer has done as when I handed it over this always worked as it should (generating the next highest number for there invoice number), but now it seems to be suck on a specific number "6009" if I keep double clicking on other orders it starts plusing one and then randomly goes back to "6009"? if I carrying on click again will again start adding from the highest number again?
I need it to show 2500000000. How can I accomplish this
Can I programmatically (VBA) change the datatype of my child table? and how?