SQL query Behind Form

  • Thread starter Thread starter Kitty
  • Start date Start date
K

Kitty

I have a form where the user selects from two options. A
SQL query runs depending upon which option is selected.

In the SQL below, the last section (WHERE) is not
executing properly. Only the first part of the OR
statement is working - the query is not checking for the
second date. I designed the query using the query
designer, then copied it from the SQL view.

Any assistance is appreciated. Thank you!

Kitty

DoCmd.RunSQL "INSERT INTO tblYBSConcurrenceTesting (
BRANCH, ENT_CODE, CIF_NUMBER, LOAN_NUMBR, SHORT_NAME,
VOLUME, Association, Open_Date, LAST_RNWL )" & _
"SELECT tblLRMF.BRANCH, tblLRMF.ENT_CODE,
tblLRMF.CIF_NUMBER, tblLRMF.LOAN_NUMBR, blLRMF.SHORT_NAME,
tblLRMF.VOLUME, tblReviewGeneralInformation.ASSOCIATION,
tblLRMF.OPEN_DATE, tblLRMF.LAST_RNWL " & _
"FROM (tblYBSCIF INNER JOIN tblLRMF ON
tblYBSCIF.CIF_NUMBER = tblLRMF.CIF_NUMBER) INNER JOIN
tblReviewGeneralInformation ON tblLRMF.ASSN =
tblReviewGeneralInformation.ASSN " & _
"WHERE (((tblLRMF.OPEN_DATE)>#12/31/2000#) OR
((tblLRMF.LAST_RNWL)>#12/31/2000#));"
 
I could be wrong, but it looks like you have an extra opening parenthises
before tblLRMF.LAST...



I have a form where the user selects from two options. A
SQL query runs depending upon which option is selected.

In the SQL below, the last section (WHERE) is not
executing properly. Only the first part of the OR
statement is working - the query is not checking for the
second date. I designed the query using the query
designer, then copied it from the SQL view.

Any assistance is appreciated. Thank you!

Kitty

DoCmd.RunSQL "INSERT INTO tblYBSConcurrenceTesting (
BRANCH, ENT_CODE, CIF_NUMBER, LOAN_NUMBR, SHORT_NAME,
VOLUME, Association, Open_Date, LAST_RNWL )" & _
"SELECT tblLRMF.BRANCH, tblLRMF.ENT_CODE,
tblLRMF.CIF_NUMBER, tblLRMF.LOAN_NUMBR, blLRMF.SHORT_NAME,
tblLRMF.VOLUME, tblReviewGeneralInformation.ASSOCIATION,
tblLRMF.OPEN_DATE, tblLRMF.LAST_RNWL " & _
"FROM (tblYBSCIF INNER JOIN tblLRMF ON
tblYBSCIF.CIF_NUMBER = tblLRMF.CIF_NUMBER) INNER JOIN
tblReviewGeneralInformation ON tblLRMF.ASSN =
tblReviewGeneralInformation.ASSN " & _
"WHERE (((tblLRMF.OPEN_DATE)>#12/31/2000#) OR
((tblLRMF.LAST_RNWL)>#12/31/2000#));"
 
Nevermind. That is not it.

You are sure it is not working? You are not getting any results where last
renewal is <12/31/2000? By using "OR" you will get records where either
case is true. Do you want records where both cases are true? That would
require an "AND" statement.

Rick B




I have a form where the user selects from two options. A
SQL query runs depending upon which option is selected.

In the SQL below, the last section (WHERE) is not
executing properly. Only the first part of the OR
statement is working - the query is not checking for the
second date. I designed the query using the query
designer, then copied it from the SQL view.

Any assistance is appreciated. Thank you!

Kitty

DoCmd.RunSQL "INSERT INTO tblYBSConcurrenceTesting (
BRANCH, ENT_CODE, CIF_NUMBER, LOAN_NUMBR, SHORT_NAME,
VOLUME, Association, Open_Date, LAST_RNWL )" & _
"SELECT tblLRMF.BRANCH, tblLRMF.ENT_CODE,
tblLRMF.CIF_NUMBER, tblLRMF.LOAN_NUMBR, blLRMF.SHORT_NAME,
tblLRMF.VOLUME, tblReviewGeneralInformation.ASSOCIATION,
tblLRMF.OPEN_DATE, tblLRMF.LAST_RNWL " & _
"FROM (tblYBSCIF INNER JOIN tblLRMF ON
tblYBSCIF.CIF_NUMBER = tblLRMF.CIF_NUMBER) INNER JOIN
tblReviewGeneralInformation ON tblLRMF.ASSN =
tblReviewGeneralInformation.ASSN " & _
"WHERE (((tblLRMF.OPEN_DATE)>#12/31/2000#) OR
((tblLRMF.LAST_RNWL)>#12/31/2000#));"
 
Correct, Rick. I'm getting records with an open date >
12/31/00. I am not getting the records where the open
date is before 12/31/00 and the renewal date is after,
which is what I want. I know I should have some.

Thanks.

Kitty
 
This is one part of the equation. I have a similar query
that goes against a related table - I had a = instead of a
in the renewal date parameter. I hadn't copied that SQL
Click to expand...
statement to my original question; this part worked fine.

Sorry...
 
Back
Top