Link to home
Create AccountLog in
Avatar of ITPOL
ITPOLFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Complex Jet/Access Query Question

Hi,

Cant quite get my SQL query sorted.  I have a table that looks like;

MyTable
Reference, ContactAddress1, ContactPostCode, EmailAddress

Sample Data;
User generated image
Reference is an integer, the others are string types.

Now, I know the following is horrendous, but I have no control over the DB design!

I want to find all employees (references), where ;

ContactPostCode = 1  
OR  
(
 ContactPostCode = 0  
 AND
 (The First 2 characters in the EmailAddress Column = ContactAddress1)
 AND
 (The next 6 characters in EmailAddress, converted into a date > #01/01/2012#)
)

To make things harder the Email address may contain NULLs


Help, very much appreciated....
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

ContactPostCode = 1  
OR  
(
 ContactPostCode = 0  
 AND
 (Left$(NZ(EmailAddress,""),2) = ContactAddress1)
 AND
 (DateSerial(Mid$(NZ([EmailAddress],""),7,2),Mid$(NZ([EmailAddress],""),5,2),Mid$(NZ([EmailAddress],""),3,2)) > #01/01/2012#)
)


Is one way...

Jim.
since ContactPostCode is a string, and EmailAddress could be Null
ContactPostCode = "1"  
OR  
(
 ContactPostCode = "0" AND NZ(EmailAddress, "") != ""
 AND
 (Left$(NZ(EmailAddress,""),2) = ContactAddress1)
 AND
 (DateSerial(Mid$(NZ([EmailAddress],""),7,2),Mid$(NZ([EmailAddress],""),5,2),Mid$(NZ([EmailAddress],""),3,2)) > #01/01/2012#)
)

AW
Avatar of ITPOL

ASKER

OK, looks like i may have missed out that im using oledb, jet4.  which doesnt have an NZ function.

I've replaced with IIF(ISNULL) which seems to work.

SELECT     Reference, ContactAddress1, EmailAddress, ContactPostcode
FROM         sgtbl_EmployeePersonal
WHERE     (ContactPostcode = '0') AND (iif(isnull(EmailAddress), '', EmailAddress) <> '') AND (LEFT(iif(isnull(EmailAddress), '', EmailAddress), 2) 
                      = ContactAddress1) AND (DateSerial(Mid(iif(isnull(EmailAddress), '', EmailAddress), 7, 2), Mid(iif(isnull(EmailAddress), '', EmailAddress), 5, 2), 
                      Mid(iif(isnull(EmailAddress), '', EmailAddress), 3, 2)) > #1/1/2012#)

Open in new window


but If i remove the ContactPostcode =1 (so im just getting the ones with the email address nonsense) I get an error;
Datatype mismatch in criteria expression

any thoughts?

Thanks
<<OK, looks like i may have missed out that im using oledb, jet4.  which doesnt have an NZ function.>>

 Your running this in Access right and it's not a pass-through query correct?  I don't understand why NZ() would be a problem.  If it is, DateSerial() is not going to work either, nor the left() and Mid() functions.

Jim.
Avatar of ITPOL

ASKER

The database is an access one, but im not using Access.  I'm using .net, however, so far i've just used the query in the Visual Studio IDE.  The other functions appear to work correctly.
OK, that makes sense then.  

Datatype mismatch of course results from compairing a string to a numeric, so something is not quite lining up right.

Try:

SELECT     Reference, ContactAddress1, EmailAddress, ContactPostcode
FROM         sgtbl_EmployeePersonal
WHERE     (ContactPostcode = '0')
 AND (iif(isnull(EmailAddress), '', EmailAddress) <> '')
 AND (LEFT(iif(isnull(EmailAddress), '', EmailAddress), 2) = ContactAddress1)
 AND (DateSerial(Mid(iif(isnull(EmailAddress), '1800', EmailAddress), 7, 2), Mid(iif(isnull(EmailAddress), '1', EmailAddress), 5, 2), Mid(iif(isnull(EmailAddress), '1', EmailAddress), 3, 2)) > #1/1/2012#)

 to make sure there is a date value (assuming if e-mail address is null, you don't want it).

Jim.
Avatar of ITPOL

ASKER

Apologies for the delay,

Still no go unfortunately.

User generated image
ASKER CERTIFIED SOLUTION
Avatar of ITPOL
ITPOL
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Very good.  Make sure you accept your last comment as answer.

Jim.
Avatar of ITPOL

ASKER

cheers,

goes to show how a couple of days off can help :)
Avatar of ITPOL

ASKER

because it answers the question