Dabase Field Types

Dabase Field Types

Post by David J. Harme » Wed, 11 Dec 1996 04:00:00



HI All..

VB 4.0 Prof
win95
crw 3.01
access dynaset database's

IN all my database I have a feild called labnumber
I assing this number to each job that I get.(get it from a database with
the last number used in the record and adds 1.0001 to it.

the number (ie:1000.0001) the digits before the decimal (1000) is the
jobnumber, the digits after the decimal(0001) is the materials number, so
if I have 3 jobs but all using the same material it would be
1000.0001
1001.0001
1002.0001
1003.0001

In clipper I used this method, with the DB feild as a decimal, 4 spaces on
each side.
in vb I stated to use double, then single and string, and currency but I
keep coming across several problems. like extra space in the data
looses the extra 0 in 1001.0010 (thats why I tried string)
huge numbers like 1001.000182372836234

ok so now Im lost and to add to that. I am using this code to print the 1
record
charstr = txtFields(0)
     CrystalReport1.SelectionFormula = "{jobs.labnumber}=" & charstr
     CrystalReport1.Action = 1
txtfields(0) is the labnumber (1001.0002)
and it either wont find the record, gives me a formula error at runtime, or
gives me a different lab number (like 1001.0001) but the right data (names,
etc) and the labnumber should gave been 1001.0002)

here is the code for getting the labnumber and incrementing it.

Dim labnum As Database, labtable As Recordset
Set labnum = Workspaces(0).OpenDatabase("c:\labdata\jobnumbers.mdb")
Set labtable = labnum.OpenRecordset("labnumber", dbOpenTable)
txtFields(0) = labtable("labnumber") + 1.0001

after I get the data from the user and they save/print it here is the
code..

Dim charstr As String
tbinternal.AddNew
tbinternal("labnumber") = txtFields(0)
tbinternal("datein") = txtFields(1)
tbinternal("salesman") = DBCombo1.Text
tbinternal("customer") = DBCombo2.Text
tbinternal("tech") = UCase(Trim(txtFields(3)))
tbinternal("Desc") = txtFields(2)
tbinternal![detail].AppendChunk txtFields(4)
tbinternal.Update

so Im relly lost, and tired cause Ive been playing with this problem for a
week now, and I cant find anything in the books  Lots of help would be
great!!!!! or a better way of doing this. The labnumber is the mose
important data, I have to be able to manipulte the number (1001.0001,
1001.0002, 1002.0003 etc...) to identfy the job and material.

Thanx for your time
David

--
David J. Harmer

 
 
 

Dabase Field Types

Post by John Beamis » Wed, 11 Dec 1996 04:00:00



> HI All..

> VB 4.0 Prof
> win95
> crw 3.01
> access dynaset database's

> IN all my database I have a feild called labnumber
> I assing this number to each job that I get.(get it from a database with
> the last number used in the record and adds 1.0001 to it.

> the number (ie:1000.0001) the digits before the decimal (1000) is the
> jobnumber, the digits after the decimal(0001) is the materials number, so
> if I have 3 jobs but all using the same material it would be
> 1000.0001
> 1001.0001
> 1002.0001
> 1003.0001

In Access, use 1 table for labs with a Counter field that you use for the lab number and
which you use as a unique key.  Use 1 table for for jobs with a Counter field that you use
as a unique key.  In that table have a foreign key (type Counter) which you set to point to
the lab doing that job.

Play with the actual display (try using edit masks or accessing the field as right(("00000"
& str(job_nbr),4) or something like that.  If you are *really* desperate, you could play
with the display until you have a character string that you like and then store that, too.

 
 
 

Dabase Field Types

Post by Paul Sampso » Fri, 13 Dec 1996 04:00:00



> HI All..

> VB 4.0 Prof
> win95
> crw 3.01
> access dynaset database's

> IN all my database I have a feild called labnumber
> I assing this number to each job that I get.(get it from a database with
> the last number used in the record and adds 1.0001 to it.

> the number (ie:1000.0001) the digits before the decimal (1000) is the
> jobnumber, the digits after the decimal(0001) is the materials number, so
> if I have 3 jobs but all using the same material it would be
> 1000.0001
> 1001.0001
> 1002.0001
> 1003.0001

I believe that your problem is fairly nominal, in that you're calling this field
a 'number' when in fact it's no such thing. I.e. you aren't doing arithmetic on
it, there's no concept of order or sequence (in what sense would material labelled
.0033 be 'greater' than one labelled .0005, for example?, or what would it mean to
'subtract' material .0002 from material .0097?).

Quite apart from anything else, trying to use a floating point number as a key field
or as an identifier is fraught with dangers at the best of times.

In addition you're attempting to impose two quite different semantics onto one
encoding (for what you have is a 'code', not a 'number'). The first identifies
an activity (a verbal identifier) and the second an object (a nominal one).

You'd do much better to trust your earlier instincts and go for a string. Obviously
this makes incrementing your code sequences a little more problematic, but this is
easily avoided by employing two separate counter key fields. If you haven't already,
you should have (at least) two tables in your database, one representing the set of
your jobs (indexed by one counter field) and another table for your materials (indexed
by the other). You may then have an additional table relating jobs to materials and
this table will comprise the two fields mentioned (each indexed, multiple values
allowed) plus any other fields you may feel are relevant to such a relation (perhaps
the date and time when the association was made, for example?). To compose, or
construct, your labnumber field then becomes simplicity itself. Renaming it (for
the purposes of argument) labcode, of type string, you'd have
        labcode = Format(jobIdent, "0000") & "." & Format(materialIdent, "0000")
If you don't like the idea of the database engine taking over your numbering (with
its auto incrementing counter types, then use a pair of Long instead).

Quote:> In clipper I used this method, with the DB feild as a decimal, 4 spaces on
> each side.
> in vb I stated to use double, then single and string, and currency but I
> keep coming across several problems. like extra space in the data
> looses the extra 0 in 1001.0010 (thats why I tried string)
> huge numbers like 1001.000182372836234 ...

Precisely why you do not use floats as identifiers.

Quote:> [ detail >8 omitted ]

--

Vox (0|+44)1642 216 200  }*|*{  *o|o*  }o|o{ Zetland Buildings, Exchange Square
Fax (0|+44)1642 216 201    o      o      *   Middlesbrough, Cleveland.  TS1 1DE
--------- "Estimate how many people in the UK can make this estimate" ---------