Complicated Data Checking

  • Thread starter Thread starter zyus
  • Start date Start date
Z

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
 
SELECT *
FROM

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

should find records where DOB and ID(dob) are not the same
 
Wonderful DanRoss.....It's very so uncomplicated to you...Thanks A LOT

DanRoss said:
SELECT *
FROM

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

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



zyus said:
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
Click to expand...
Click to expand...
 
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 said:
SELECT *
FROM

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

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



zyus said:
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
Click to expand...
Click to expand...
 
Try:



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





zyus said:
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 said:
SELECT *
FROM

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

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



zyus said:
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
Click to expand...
Click to expand...
Click to expand...
 
Thanks Again,,

You have brighten my day

DanRoss said:
Try:



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





zyus said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top