Complicated Data Checking

[zyus]

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

 

[DanRoss]

SELECT *
FROM

WHERE (((Left([ID],6))<>Format([DOB],"yymmdd")));

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

 

[zyus]

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

SELECT *
FROM

WHERE (((Left([ID],6))<>Format([DOB],"yymmdd")));

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

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

 

[zyus]

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

SELECT *
FROM

WHERE (((Left([ID],6))<>Format([DOB],"yymmdd")));

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

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

 

[DanRoss]

Try:



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

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

SELECT *
FROM

WHERE (((Left([ID],6))<>Format([DOB],"yymmdd")));

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

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

 

[zyus]

Thanks Again,,

You have brighten my day

Try:



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

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

SELECT *
FROM

WHERE (((Left([ID],6))<>Format([DOB],"yymmdd")));

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



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