Two questions

[Dimitris]

Hello I have two issues that I don't know how to solve.

1. In a field with numbers entered. I want to find out in which entries the
fourth number is zero "0".

2. Also in another field again with numbers entered I need to delete the
first 4 numbers of each entry. So "12345678" will become "56789"

Can someone help me with these two questions?

Thank you
Dimitris

 

[Stefan Hoffmann]

hi,

1. In a field with numbers entered. I want to find out in which entries the
fourth number is zero "0".

Untested:

WHERE Len(Str([number])) > 4
AND Mid(Str([number]), Len(Str([number])) - 3, 1) = "0"

2. Also in another field again with numbers entered I need to delete the
first 4 numbers of each entry. So "12345678" will become "56789"

UPDATE yourTable
SET [number] = Int(Mid(Str([number]), 5))
WHERE Len(Str([number])) > 4

Backup your table(s) before changing your data.

mfG
--> stefan <--

 

[Dimitris]

Thank you very much for your answer Stefan but unfortunately nothing worked.

In the first case I get the error message: "Syntax error (missing operator)
in query expression..."

and in the second case the 3 first numbers are deleted instead of the first 4.

Any other ideas?
Dimitris

hi,

1. In a field with numbers entered. I want to find out in which entries the
fourth number is zero "0".

Untested:

WHERE Len(Str([number])) > 4
AND Mid(Str([number]), Len(Str([number])) - 3, 1) = "0"

2. Also in another field again with numbers entered I need to delete the
first 4 numbers of each entry. So "12345678" will become "56789"

UPDATE yourTable
SET [number] = Int(Mid(Str([number]), 5))
WHERE Len(Str([number])) > 4

Backup your table(s) before changing your data.

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Dimitris,

Thank you very much for your answer Stefan but unfortunately nothing worked.

They work, only I forgot the speciality of Str(): It adds a space before
the number. Use the CStr() function instead.

In the first case I get the error message: "Syntax error (missing operator)
in query expression..."

What does your SQL look like?


mfG
--> stefan <--

 

[Dimitris]

First case: Table Name: TL1, Field name: PEDIO8

SELECT TL1.PEDIO8
WHERE Len(Str([PEDIO8])) > 4
AND Mid(Str([PEDIO8]), Len(Str([PEDIO8])) - 3, 1) = "0"
FROM TL1;

Second case: Table Name: TL1, Field name: PEDIO1

UPDATE TL1
SET [PEDIO1] = Int(Mid(Str([PEDIO1]),5))
WHERE Len(Str([PEDIO1]))>4;

 

[Stefan Hoffmann]

hi Dimitris,

First case: Table Name: TL1, Field name: PEDIO8

SELECT TL1.PEDIO8
WHERE Len(Str([PEDIO8])) > 4
AND Mid(Str([PEDIO8]), Len(Str([PEDIO8])) - 3, 1) = "0"
FROM TL1;

Wrong order, the WHERE clause is the last part:

SELECT fieldList
FROM tableOrQuery
WHERE condition

Second case: Table Name: TL1, Field name: PEDIO1
UPDATE TL1
SET [PEDIO1] = Int(Mid(Str([PEDIO1]),5))
WHERE Len(Str([PEDIO1]))>4;

Yup, replace Str() with CStr()...


mfG
--> stefan <--

 

[John Spencer]

If this field is a text field then your criteria would be

Field: SomeField
Criteria: Like "###[0]*"
'Do you want to return Null if the field is only 1, 2, 3 or 4 characters
long? Or so you want to keep the value


In an update query
Field: SomeField
Criteria: Like "#####*"
UPDATE TO: MID(SomeField,5)

IF you need nulls then change the where to IS NOT NULL
UPDATE TO: IIF(Len(SomeField & "")<5, Null,Mid(SomeField,5)





'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Dimitris]

Thank you for your help.

If this field is a text field then your criteria would be

Field: SomeField
Criteria: Like "###[0]*"
'Do you want to return Null if the field is only 1, 2, 3 or 4 characters
long? Or so you want to keep the value


In an update query
Field: SomeField
Criteria: Like "#####*"
UPDATE TO: MID(SomeField,5)

IF you need nulls then change the where to IS NOT NULL
UPDATE TO: IIF(Len(SomeField & "")<5, Null,Mid(SomeField,5)





'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hello I have two issues that I don't know how to solve.

1. In a field with numbers entered. I want to find out in which entries the
fourth number is zero "0".

2. Also in another field again with numbers entered I need to delete the
first 4 numbers of each entry. So "12345678" will become "56789"

Can someone help me with these two questions?

Thank you
Dimitris