Topics Search

How to support nullable integer (number) field in Access / VBA

How to support nullable integer (number) field in Access / VBA
Views: 49
I have run into a dilemma that integer (number) variables in VBA do not seem to support null, but as for a column type in a table, that is allowed to support null.

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.
Sponsored Links:

More topics


Access 10 (64bit) -Integer & Table size

I have a potential need to use integer numbers up to 17 digits long. Access03 that I currently use can go up to 10 digits (32 bit restriction). I am wondering whether Access10 (64bit) woould solve the problem. I understand that it has three new keywords (2 data types and 1 modifier): LongPtr, LongLong and PtrSafe, a new function: CLngLng() and new compilation constants: VBA7 and Win64.

What is the biggest integer value that Access10 (64bit) can support?

Also what is the maximum physical table size that Access10 (64bit) can support

Field returns error when I import Text Files to Access

I tried importing a text file to Access and it always gives me error on the fields 5,6,7,8.

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

-Primary Key

Object does not support this method or property

I have a little code on the 'Current' event of a form. It examines certain fields in the current record in order to update some text fields on the form. It all works fine, except that the code refuses to handle one field (an integer), giving the message 'Object does not support this method or property'. I can display the value of all the other fields in the debug window, but this one throws that error no matter what I try to do. The field is called 'Interests' which so far as I can determine is not an Access keyword.

Adding column as INTEGER makes it a long integer?

I'm using the code below in access 2007. The field ends up being a long integer! What's up with that? Shouldn't INTEGER be an integer, and a LONG be a long?

If Not FieldExists("Dosel", "BlockID") Then
dbs.Execute "ALTER TABLE Dosel ADD COLUMN BlockID INTEGER;", dbFailOnError End If

VBA for next number stopped working

people I have a problem with some vba coding, basically I have this code

#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
End Sub#

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?

Numeric field in access 2007

I have a table called 'patients details' with NHS Number as primary key set up as 'Number'.

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

Conversion from Text to Integer/Number

I've created a column in a MS Access Query that I've converted to an Integer from Text. However, when I import the table into Excel, the field comes through as text.

I've tried INT() and VAL() and have had no luck.

Below is the syntax I'm using:

Amount: Int([Opportunity].[USDAmount])

decision support flag based on mulitple field values

I would like to create a field for decision support on the mainform.

This field needs to say "top" when other fields in the mainform have values. (texts)

So for example, field A says "Yes", field b says "Yes", and either field C or field D or field E has says "Yes" on this particular record, then I would like to flag this record by having this decision support field say "top".

If someone could point me in the right direction as to which event I should use in order to trigger this automatic input in Access 2003, it would be wonderful.

Make Long Integer Field Size show 2.5 Billion

The largest number I can show with the Long Integer field size seems to be 2100000000.

I need it to show 2500000000. How can I accomplish this

How to change/alter datatype of a field from a linked table

I have created a MS Access database where all data are stored in linked tables. I noticed that two of my linked tables (parent and child in a subform) are having different datatypes: the first is set to AutoNumber (Long Integer) while the other one is Integer only. These tables are combined in my forms & reports, and in my queries.

Can I programmatically (VBA) change the datatype of my child table? and how?