Storing Integers Between 0 And About 808 Quadrillion -- Strings, Decimal, Or Variant?

Feb 25, 2008

I need to be able to precisely handle nonnegative integers (in the math sense of integer, not the VBA sense, of course) between the values of 0 and 808,035,046,350,570,000. (Don't ask.)

I'm wondering whether I should be handling these numbers in a string and do everything manually, or whether the datatype Decimal or Variant will work for me.

I read that Decimal handles +-79,228,162,514,264,337,593,543,950,335 with no decimal point, or +-7.9228162514264337593543950335 with 28 places to the right of the decimal.

I absolutely cannot have the "rounding type errors" and "oddities" that you get by storing integer values in doubles, for example, due to imprecission.

Seeing that Decimal seems to work in a decimal point manner worries me, that perhaps I shouldn't be using it for this scenario. However, it's range seems so much larger than what I need, I'm wondering if I would "get away" with it.

If needing no imprecission to creep in means that I have to use Strings and implement my own String math library, that's the route I would have to go.

Question 1 - If I need the numbers to be exact (again, nonnegative integers, no decimal points), do I need to use a String, or should I use a Decimal or Variant type?

Question 2 - What is the difference between Double and Variant, for the purposes of storing numeric values? I read that Doubles are 8 bytes, and Variants are 16 bytes, however the Variant description is "Any numeric value up to the range of Double", which confuses me since it's double the size.

View Replies


ADVERTISEMENT

Strings Within Strings Using Expression Builder

Jun 6, 2007

Hello

I have two tables with names of people in different forms.

table1 : [name] can be either in the form of :
- firstname & firstname surname
- surname, firstname

table2: [surname]

I'm trying to select all the records in table1 where [table2]![surname] is part of [table1]![name]

I've tried using

Like "*"&[surname]&"*"

This returns everything rather than the similar names.

I think this should be simple but can't work it out.

View 1 Replies View Related

Variant Not Getting The Value

Jun 14, 2005

Hi guys, this worked under access 2000, but not anymore under 2003
"strtrans" is not getting the value.
I can't find what's wrong, tried string, defininf them in a module. Nothing.
Anybody any idea why that is?
Here's the code:

Dim strTable As Variant
Dim strField As Variant
Dim strtrans As Variant
Dim stDocName1 As Variant
Dim stDocName As Variant
stDocName1 = "qerDelActieTabel"
stDocName = "qerUpdateActietabel"
On Error GoTo Err_Knop20_Click
Me.Probleem.SetFocus
If Me.Probleem.Text = "" Then
DoCmd.Close
Else
Me.Appcode.SetFocus
If Me.Appcode.Text = "" Then
strTable = "tblActie"
strField = "Probleem"
strtrans = Me.Ingavedatum.Value
strtrans = strtrans & "ac" & DCount(strField, strTable)
Me.Appcode.Text = strtrans
MsgBox sttrans & strField & strTable & stDocName & stDocName1
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.Close
Else
MsgBox sttrans & strField & strTable & stDocName & stDocName1
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenQuery stDocName1, acNormal, acEdit
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.Close
End If
End If
Exit_Knop20_Click:
Exit Sub

Err_Knop20_Click:
MsgBox Err.Description
Resume Exit_Knop20_Click

(eddited for clarity)

View 8 Replies View Related

Long Numeric Integers

Oct 6, 2005

Hi All,

This question has probably been asked before, but couldn't find a related topic. My question:

How can I import long numeric values (integers) greater than 2,147 billion (standard long integer). I tried a lot, but couldn't find the answer.

Thanks.

View 7 Replies View Related

Append Into Table All Integers Between A And B

Sep 11, 2006

Hi.
I have a form with two text boxes, call them FIRSTNO and LASTNO.
The user inputs a first and last number into these boxes, and what I want to do is append to table NOS all integers between these two.

E.g. user inputs 6 and 11 and I would like table NOS to contain 6 rows, with the first field containing this number, like so:
6
7
8
9
10
11

It sounds easy, but I’m struggling!
Any thoughts appreciated. Thanks in advance.

View 2 Replies View Related

Queries :: How To Exclude Non-integers In Query

Jul 29, 2013

I need to exclude non-integers in my query and have forgotten how to do this.

What i need to put into the criteria field within my query to to this?

View 2 Replies View Related

Queries :: Identifying Whole Numbers Vs Integers

May 2, 2013

I currently have a calculated field that will result in either a whole number or a decimal. From there, I need to set up a field that identifies if the previous field is either a whole number or a decimal.I have tried several different formulas, but always seem to get an error or a query that does not return any results.

View 3 Replies View Related

Round Up Decimal Point To 4 Decimal Places

Jun 2, 2005

Hi there,

I am not sure the best way to do this,

I have a field with values that look like: 1.69553366385509 or 0.0061705541157596

I want these values to look like: 1.6955 or 0.0062 (4 decimal places [rounded up if possible])

Thanks for your help with this

Sue

View 5 Replies View Related

Variant Data Type Acc97 And 2000

Feb 6, 2006

This is a follow up to a problem I had with a Subreport which I have now solved but would like additional information on the differences between 97 and 2000.

basically I ghad a subreport that worked in97 but gave Invalid Argument in 2000.

It appears that the linking field was too long and this caused the Invalid Argument.

The fileds were a concatonation of two fields. One Number and One Text Field. Yes they were a bit long in certain circumstances but for fairly good reasons. I believe these would be generated as Type Variant.

Does anybody know the differences in teh Variant Type between 97 and 2000. I have tried searching teh Help in 2000 but unable to find any info or indeed the correct phrase to search on.

len

View 3 Replies View Related

Modules & VBA :: Convert Variant To Access Table?

Aug 20, 2013

Creating a recordset from an Access table

Copy that recordset into a Variant variable, similar to a matrix

Run a Heuristic Algorithm on the matrix to populate values

Delete * from the orginal Access table

Input new data into the Original Access table by looping through matrix with an INSERT INTO statement.

This method works but I do not like the loop in step 5 as I am calling an INSERT INTO statement 800+ times and can be slow. Any way to view the variant as a whole and not have to loop through the entire matrix. Maybe possibly converting the variant to an Access tabledef.

View 2 Replies View Related

You Tried To Assign The Null Value To A Variable That Is Not A Variant Data Type. (Er

Jul 1, 2007

Hi,

I am getting this error 'You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162)' I know why I am getting the error, I found out that Form and Subform should be implemented only with one-to-many relationships where the Subform is the many end. However, I have the opposite of that for a reason.

Is this bad practice? Is there anyway around it or to fix the error please?

Any help will be very much appreciated,
B

View 14 Replies View Related

Queries :: Convert Boolean Fields To Integers / Blank For Null Values

May 22, 2015

I have a query which returns, among other things, a number of boolean fields. In some cases, there will be a genuine True or False value in each of these fields; in others, it can and should be Null (e.g. as a result of a 'failed' LEFT JOIN of some description, where there is no associated record in the joined table which fulfills the criteria)

So something like this :

Code:
SELECT [tblTable2].[fldBooleanField]....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]

However, I will be writing the result of the query to a text file and here's the problem. I want to show a numeric value for a genuine True / False (i.e. -1 and 0 respectively using the standard boolean conversions in Access) and a blank for any Null values.

So I tried this :

Code:
SELECT CInt([tblTable2].[fldBooleanField]) AS fldBooleanField....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]

However, currently when I look at the exported recordset in Notepad, I am getting 0's for both False and Null values (and -1 for True)

How I can adapt my query to keep Nulls...null? And convert the genuinely present boolean values to integer form?

Only thing I can think of is to use (untested) :

Code:
IIf([tblTable2].[fldBooleanField] Is Null, Null, CInt([tblTable2].[fldBooleanField]))

But there's a number of boolean fields in there, all requiring the same treatment.

View 14 Replies View Related

Forms :: Allowing Empty String In A Textbox - Variant Data Type

Jan 19, 2014

I have an Access 2010 database where we have a SQL Linked Table with a column that is nVARCHAR(20) Not Null data type. We have created a form for data entry. Currently when the user tries to erase a value or choose not to define a value we get the following error.

"You tried to assign the Null value to a variable that is not a Variant data type."

This field should accept a blank value "" as the user may not want to set the value. We do not have control over the DB schema, so how can I work around this issue in access?

View 4 Replies View Related

Modules & VBA :: Return Rows In Query From Variant Array Return From UDF

Sep 16, 2014

I have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.

So if my input table looks like this

[strField]
"kick the ball"
"return the pass"

my query result should looks like this

[Orig] [new]
"kick the ball" "kick"
"kick the ball" "the"
"kick the ball" "ball"
"return the pass" "return"
"return the pass" "the"
"return the pass" "pass"

Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.

With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.

View 3 Replies View Related

Unit Of Strings

Nov 8, 2005

Hi,

I have 2 strings:

str1 = SELECT Field1, Field2, Field3, Field4 WHERE (Criteria1)
str2 = SELECT Field1, Field2, Field3, Field5 WHERE (Criteria2)

I want to show in listbox only "Field1" from str1 and str2.
How unit 2 strings?

Thank You in advance.

View 4 Replies View Related

How Unit 2 Strings?

Mar 31, 2006

Hi all.

I have 2 strings:

strSQL1 = SELECT FieldA FROM table WHERE FieldA = xxx
strSQL2 = SELECT FieldB FROM table WHERE FieldB = xxx

I need unit this two strings to third string strSQL3.
I must have result StrSQL3 = SELECT FieldA, FieldB FROM table WHERE FieldA = xxx and FieldB = xxx

Is it possibly unit strings (not Select Query)?

View 4 Replies View Related

Adding Strings Together

Nov 3, 2007

So I need to know this. How do I:

*Add 2 text fields together with a space between?
*Truncating field 1 to the first character and field 2 to the two first characters?


Sorry for my noob questions :D

View 6 Replies View Related

> Than Comparision In Strings

May 23, 2005

Hi. I am trying to compare 2 string values.

5:00
5:45
9:54
10:15

Lets say those are my values. Well in the formula >"5". 10 Will not show up correctly. Does anybody have a piece of code or something that will correctly display 10 as >"5". Any help greatly appreciated. Thanks

View 3 Replies View Related

Joining Strings

Aug 23, 2005

Seems like a simple question but I am stuck.

I need to join two strings from different records in a table

table eg

ID Desc
1 blah blah
1 more blah
2 blahring
2 blah
3 and last one


I want to join all the "Desc" strings that have the same ID to get

1 blah blah more blah
2 blahring blah
3 and last one

Can anyone help?? :o

Thanks

Paul

View 1 Replies View Related

Splitting Strings

Dec 15, 2005

I am normalizing data from a spreadsheet of just over 4000 records. The spreadsheet has a "Model" field that contains both the model number and a model description, separated by a space. I would like to split this field into two different fields: "Model_Number" and "Model_Description". The model number is one word of varying lenths.

Any ideas?

View 5 Replies View Related

Null And Zero Strings

Nov 2, 2006

Hello,

I am having trouble with a query. I have a number of blanks for a field and I tried using the expression from the help:

=IIf(IsNull([fieldname]),"Unknown",Format([fieldname],"@;LS"))

But my result set is still a lot of blanks.

This is the expression I was trying to use:

IIf(IsNull([tblMainpersondata]![MailingAddrLine2]),[tblMainpersondata]![MailingAddrLine1],[tblMainpersondata]![MailingAddrLine2])

On most of these, the result was a blank, instead of MailingAddrLine1. If there was visible data for MailingAddrLine2, it appeared.

Any help or suggestions would be greatly appreciated!

View 2 Replies View Related

Comparing Two Strings

Jan 29, 2007

Hey Guys,

Just wondering how I would go about comparing two strings, and spitting the results out a similarity percentage?

E.G. (String 1) Postal Address: "11 John St"
(String 2) Street Address: "11 John Street"
(Output): 80%?

Cheers

View 5 Replies View Related

Compare Strings In SQL

Jul 10, 2007

Hello,
I would like to put a restriction on the SQL statement such that if one particular field contains the character "MO", then we take in the record, else we ignore them. May I know how could this be done in the "WHERE" part of the SQL statement? I do not think SQL would read things like Instr(). Thanks!

Regards,
Anyi

View 13 Replies View Related

So Confused With Strings!!! Please Help!!!

Nov 15, 2004

Hi all,

Please can someone help. I am trying to pass a query to the database from vba but can't seem to get the string concatenation of fields correct.

Clinical area and title of audit are database fields. Any ideas please? Thanks!

Here is what I have:

StLc = "[Clinical Area] = """ & Me![Combo309] & """ & """ And """ & [Title of Audit] = """ & Me![Combo309] & """"



View 1 Replies View Related

Counting With Strings

Dec 18, 2004

Hoi,


I have a loop where i use a string (strCount). The first time I run the loop strCount has de value A. The second time strCount must become B, the third time C, etc

I already tried to strCount = strCount + 1 but it did not work
does someone know how to do this?

greets
Koen

sorry formy wad english

View 2 Replies View Related

Help With Normalization (strings, Oh My)

Feb 9, 2005

This is a database that was handed off to me for upgrading.

The person who initially created it made the Name field in the table contain the entire name... So a single entry in the field looks like

CHRIS R LOUNSBURY

The table is in the attached table. This is merely a sampling. The main table has over 3,000 names in it. Is there an easy (or maybe not so easy) way to automatically parse that data and split the names into their own fields.

An example would be the string CHRIS R LOUNSBURY. Run code which takes the string left to right to the first blank space, and splits it off into First Name field. Take the last part of the string (right to left) to the first white space and put it in the Last Name field.

Is this possible? Or do I need to find myself a temp employee to data entry all this over again

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved