Complicated Data Checking

I hv this field and sample data in my table

ID DOB
701011123214 11-Oct-70

ID is text field & DOB is date field.
First 6 digit in ID field represent date of birth (reversed sequence).

How to check records which are not in compliance with the structure as
mentioned.

Thanks

SELECT *
FROM [TABLE]
WHERE (((Left([ID],6))Format([DOB],"yymmdd")));

should find records where DOB and ID(dob) are not the same



"zyus" wrote in message
...
I hv this field and sample data in my table

ID DOB
701011123214 11-Oct-70

ID is text field & DOB is date field.
First 6 digit in ID field represent date of birth (reversed sequence).

How to check records which are not in compliance with the structure as
mentioned.

Thanks

Wonderful DanRoss.....It's very so uncomplicated to you...Thanks A LOT

"DanRoss" wrote:



SELECT *
FROM [TABLE]
WHERE (((Left([ID],6))Format([DOB],"yymmdd")));

should find records where DOB and ID(dob) are not the same



"zyus" wrote in message
...
I hv this field and sample data in my table

ID DOB
701011123214 11-Oct-70

ID is text field & DOB is date field.
First 6 digit in ID field represent date of birth (reversed sequence).

How to check records which are not in compliance with the structure as
mentioned.

Thanks

Need to get opinion on 2nd scenario
ID SEX
701011123214 M or F

Both are text field.
Last one digit in ID field indicates the gender. Odd=M and Even=F

How to check records which are not in compliance with the structure as
mentioned above.

Try to simulate from your earlier statement but failed

Thanks

"DanRoss" wrote:



SELECT *
FROM [TABLE]
WHERE (((Left([ID],6))Format([DOB],"yymmdd")));

should find records where DOB and ID(dob) are not the same



"zyus" wrote in message
...
I hv this field and sample data in my table

ID DOB
701011123214 11-Oct-70

ID is text field & DOB is date field.
First 6 digit in ID field represent date of birth (reversed sequence).

How to check records which are not in compliance with the structure as
mentioned.

Thanks

Try:



SELECT *
FROM [User]
WHERE (((User.Gender)IIf((CDbl(Right([UserID],1)) Mod 2)=0,"F","M")));





"zyus" wrote in message
...
Need to get opinion on 2nd scenario
ID SEX
701011123214 M or F

Both are text field.
Last one digit in ID field indicates the gender. Odd=M and Even=F

How to check records which are not in compliance with the structure as
mentioned above.

Try to simulate from your earlier statement but failed

Thanks

"DanRoss" wrote:



SELECT *
FROM [TABLE]
WHERE (((Left([ID],6))Format([DOB],"yymmdd")));

should find records where DOB and ID(dob) are not the same



"zyus" wrote in message
...
I hv this field and sample data in my table

ID DOB
701011123214 11-Oct-70

ID is text field & DOB is date field.
First 6 digit in ID field represent date of birth (reversed sequence).

How to check records which are not in compliance with the structure as
mentioned.

Thanks

Thanks Again,,

You have brighten my day

"DanRoss" wrote:

Try:



SELECT *
FROM [User]
WHERE (((User.Gender)IIf((CDbl(Right([UserID],1)) Mod 2)=0,"F","M")));





"zyus" wrote in message
...
Need to get opinion on 2nd scenario
ID SEX
701011123214 M or F

Both are text field.
Last one digit in ID field indicates the gender. Odd=M and Even=F

How to check records which are not in compliance with the structure as
mentioned above.

Try to simulate from your earlier statement but failed

Thanks

"DanRoss" wrote:



SELECT *
FROM [TABLE]
WHERE (((Left([ID],6))Format([DOB],"yymmdd")));

should find records where DOB and ID(dob) are not the same



"zyus" wrote in message
...
I hv this field and sample data in my table

ID DOB
701011123214 11-Oct-70

ID is text field & DOB is date field.
First 6 digit in ID field represent date of birth (reversed sequence).

How to check records which are not in compliance with the structure as
mentioned.

Thanks