Matching data structure of two variables in MS Access

[Medioman]

Hi all,

I've a quite simple data structure problem, which I unfortunately cannot
solve.

Problem description:

I've two variables: 'ID-number' and 'ID'.
I want to match/link each variable in a certain query.
So far, no problem. Just using the 'join-option' in MS Access.

However the data structure of both variables is quite different.


Short elaboration of my list:

'ID-number' 'ID'
00001 1
00003 3
00023 23
00027 27

So linking won't work because Access cannot make an identical match.
My plan is to add (dummy)nulls to the numbers 1, 3 en 23 of variable 'ID',
in such a case that it
perfectly matches the 5 characters of the 'ID-number'. So this means adding
four nulls before the 1 and 3. And adding three nulls to respectively 23 and
27.

However, I've forgotten how to do that in an expression of a query field.

Can someone give me a hint or solution !

Thanx, Medioman!

 

[Wolfgang Kais]

Hello "Medioman".

Hi all,

I've a quite simple data structure problem, which I unfortunately
cannot solve.

Problem description:

I've two variables: 'ID-number' and 'ID'.
I want to match/link each variable in a certain query.
So far, no problem. Just using the 'join-option' in MS Access.

However the data structure of both variables is quite different. Short
elaboration of my list:

'ID-number' 'ID'
00001 1
00003 3
00023 23
00027 27

So linking won't work because Access cannot make an identical match.

I guess that 'ID-Number' is Text and 'ID' is a number?

My plan is to add (dummy) nulls to the numbers 1, 3 and 23 of
variable 'ID', in such a case that it perfectly matches the 5
characters of the 'ID-number'. So this means adding four nulls
before the 1 and 3. And adding three nulls to respectively 23
and 27.

However, I've forgotten how to do that in an expression of a query
field.

Forgotten? There are several ways to do this, for example:

String(5-Len(CStr([ID])),"0") & [ID]
Right(CStr(100000 + [ID]), 5)
Right("00000" & [ID], 5)
Format([ID], "00000")

You will have to use a where clause instead a join.

 

[David F Cox]

0 is not Null
Null is not zero
I suggest that you read up on NULL, it is a very important concept.

Assuming ID-number is a text field I would try a join on:
V: Val([id-number])
and ID

or V: Cint([id-number])