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

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:

Related 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


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.


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)
rst.MoveLast
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?


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])


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


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?


Custom date format for custom combo box value list
I've written some vba to set a combo box value list as last month or the month before. Now I'm trying to format it & am having issues.

I've set the combo box itself to be mmm yyyy & once a selection is made, it displays like that, but the dropdown is still showing eg 01/02/2011. What do I need to do to get it to show in the dropdown as mmm yyyy while still being set as a date (needed for later)?
This is my code (please don't laugh! I try!):

Dim myDateArray(1) As String, myDateList As String, I As Integer, m As Integer, f As Integer, subi As Integer
If Int(VBA.Year(Now()) / 4) = VBA.Year(Now()) / 4 Then
f = 29
Else
f = 28
End If

For I = 0 To 1
If I = 0 Then
subi = 13
Else
subi = m
End If
m = VBA.Month(Now())
myDateArray(i) = CStr(VBA.DateSerial(VBA.Year(Now()), m, 1) - Choose(subi, 30, 31, 31, f, 31, 30, 31, 30, 31, 31, 30, 31, 0) - Choose(m, 31, 31, f, 31, 30, 31, 30, 31, 31, 30, 31, 30))
Next I
myDateList = Join(myDateArray, ";")

Me!cmbYr.RowSource = myDateList


Creating Primary Key value when adding a new record
I have a table with a primary key field (long integer) that is indexed, and is set to not allow duplicates. I am currently using DMax to identify the highest PK value and am adding 1 to this number. When I try to update the field with this number when adding a new record, I get a run time error stating I would end up with a duplicate value in the PK field, even though the number is unique


first create table in MS access data definition sql view
I tried to create my first table using sql in ms access but it failed.

My codes are like below. It was showing "Syntax error in field definition". What could be the problem?

CREATE TABLE loom ( column1 integer, column2 integer, column3 integer


Auto Show on form start
Im using this VBA code to insert a drawing numbers for our engineers.

Private Sub Form_BeforeInsert(Cancel As Integer) Me.Text505 = Nz(DMax("MyCounter", "tblAttributesGA")) + 1 Me.txtGADrawingNumber = Nz(DMax("MyCounter", "tblAttributesGA")) + 1 & "-01" End Sub

At the moment the form will open and you have change a field before the number appears in the txtDrawingNumber field.
What Id like is that when the form opens the number appears in the txtGAdrawingNumber box straight away.


Add support for the scroll wheel to the Microsoft Visual Basic for Applications 6 environment
When you use the Microsoft Visual Basic for Applications (VBA) 6 integrated development environment (IDE) - also referred to as the Visual Basic Editor - to add or edit a macro or module in your Microsoft Office 2003 program , the scroll wheel on your mouse might not function. That's because VBA is based on the Microsoft Visual Basic 6 IDE, which does not include built-in support for the scroll wheel. You can use the technique discussed here to add support for the scroll wheel to VBA 6.


New to VBA - Textbox/ List question
I decided to begin learning access vba to make my databases more user friendly/interactive to non-access users. Here is a picture of the controls on my form: http://imageshack.us/photo/my-images/683/35819300.png/

The idea is when a user enters a CIP number in the 'Add CIP' Field, an add will occur to the listbox as well.

Here is my first function:

Private Sub listBoxPopulate() Dim strTextInput As String Dim textInput As Integer
strTextInput = Text45.Text
If strTextInput <> Null Then textInput = CInt(strTextInput) List43.Add (textInput)
End If End Sub

When I try to compile it says access is missing an object reference. Now to use the text property it says I have to give the box focus


Converting this Excel VBA code to Access VBA. A really challenging task
Earlier I had a excel file with many columns.
I wrote a code in Excel VBA to fill up the last two columns according to the data in the other columns of the row.
It worked fine.
Now, the whole thing is transferred to an Access table.

Pls help me convert this Excel VBA code to Access VBA.

Sub GeneratePorEngID() Range("S2:T5000").Clear Dim PrevTaskID As Integer Dim intNO As Long Dim intCount As Integer intNO = 1 PrevTaskID = 1 For I = 2 To 5000 'If PrevTaskID = 1 Then intNO = 1
If Cells(i, "J") <> "" Then If PrevTaskID = Cells(i, "J") Then Cells(i, "S") = intNO intNO = intNO + 1 intCount = intCount + 1 Else For j = I - intCount - 1 To I - 1 Cells(j, "T") = intNO intNO = intNO + 1
Next Cells(i, "S") = intNO intNO = intNO + 1 intCount = 0 PrevTaskID = Cells(i, "J") End If End If Next


Access 2007: Percent Format Issue
I am trying to have a field be a Percent Format.

In access 2007, I am reading that I should just have the properties be:

Number
- Field Size: Long Integer
- Format: Percent
- Decimal Places: 2

However, in the table, when I enter a number that is smaller than 100 now, it automatically shows ZERO. When I enter 100, it shows it as 100% (and the same for any number above 100).

Then, I tried to just change the format to General Number, with 2 decimal places.

When I try to enter say, 0.45 - it changes it to zero. It does not show the two decimals. When I enter in 0.9, it rounds it up to


Convert Long Integer Number to Autonumber
Tried to convert a Long Integer column to Autonumber and get the error:
"Once you enter data in a table, you can't change th edata typ of any field to AutoNumber, even if you haven't yet added data to the field."

Just normalized many tables into one table by turning off the Autonumber.
Used a Hashing method to keep track across the many updates.

The field that I need to change back to an autonumber starts at 1,000 to 1,832 - has gaps (tht was the first table), then 2,000 to 2,304 - has gaps (that was the second table) . and so on.

All I need to do now is to convert my first column (Long Integer) back to an Autonumber - and keep the data carefully migrated in.


Importing a CSV file with VBA
I'm importing a standard CSV file into access using the following VBA:

DoCmd.TransferText acImportDelim (file specs/location follow)

For some reason, access is adding a .00 at the end of a text field (it's a text field but the data is a number). Does anyone know why this is happening? I've been using this same VBA every day with no issue until today. The field/column is formatted as text once it's in the table in access but now with the .00 at the end of every record. For example, the CSV file shows the field value as 011163, and once imported it shows