Hi everyone.

I'm trying to convert a SQL Statement from MS Access to a standard SQL statement.

Could anyone help me understand how I do this?

HAVING (((EMTADMIN_DR_MESSAGE.MDATE) Between #1/1/2006# And Date()-1) AND ((EMTADMIN_DR_MESSAGE.MESSAGE_NOTES) Like "PID #######" Or (EMTADMIN_DR_MESSAGE.MESSAGE_NOTES) Like "Program ID -*#######" Or (EMTADMIN_DR_MESSAGE.MESSAGE_NOTES) Like "*#######*")


Any Help Is greatly appreciated

Corey

Where is the rest of the statement?

Dim dtDate As Date

dtDate = Date()-1

HAVING [EMTADMIN_DR_MESSAGE.MDATE] Between #1/1/2006# And " & dtDate & " AND [EMTADMIN_DR_MESSAGE.MESSAGE_NOTES] Like 'PID #######' Or [EMTADMIN_DR_MESSAGE.MESSAGE_NOTES] Like 'Program ID -%#######' Or [EMTADMIN_DR_MESSAGE.MESSAGE_NOTES] Like '%#######%'

I am not sure about the # wildcards you are using. ## means date.

Did that help any? Do you have SQL Query Analyzer to try this out on your SQL database?




mmcdonal

Hi Again, can anyone help me identify why I'm getting zero records returned, when I know I should be getting over 10,000 records

SELECT *
FROM emtadmin_BD.NESSAGE
where(NESSAGE_NOTES) Like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]%’'

What do these refer to? [0-9][0-9][0-9][0-9][0-9][0-9][0-9]


mmcdonal

That is can be any number 0 through 9

Your trying to evaluate a regular expression in a like statement? Unfortunately, you are evaluating a string literal here so your statement is looking for a field that is similar to: [0-9][0-9][0-9][0-9][0-9][0-9][0-9]that is why your query returns 0 results.

You can evaluate Regular expressions from the Master stored procedures as to whether or not the given input matches the given expression, however, I don't know about searching a field based upon an expression.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

Maybe I'm over-simplifying but wouldn't you want:

SELECT *
FROM emtadmin_BD.NESSAGE
where(NESSAGE_NOTES) Like '%#######%’'

the hash mark is a wild card for numeric data only.